Web Synchronization for Merge Replication With Pull Subscription For SQL Express

Introduction

In this article I will show you, how we can achieve merge replication by web synchronization in SQL Express. 

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

     In above diagram you can see data will replicate between publisher and there subscribers.

     Merge replication is implemented by the SQL Server Snapshot Agent and Merge Agent. If the publication is unfiltered or uses static filters, the Snapshot Agent creates a single snapshot. If the publication uses parametrized filters, the Snapshot Agent creates a snapshot for each partition of data. The Merge Agent applies the initial snapshots to the Subscribers. It also merges incremental data changes that occurred at the Publisher or Subscribers after the initial snapshot was created, and detects and resolves any conflicts according to rules you configure.

The following diagram shows the components used in merge replication.

Subscribe to Publications

A subscription is a request for a copy of the data and database objects in a publication. A subscription defines which publication will be received, and where and when it will be received. When planning for subscriptions, consider where you want agent processing to occur. The type of subscription you choose controls where the agent runs. With a push subscription, the Merge Agent or Distribution Agent runs at the Distributor, whereas with a pull subscription, agents run at the Subscribers. After a subscription is created, it cannot be changed from one type to another.

Subscription	Characteristics	Use When
Push Subscription	With a push subscription, the Publisher propagates changes to a Subscriber without a request from the Subscriber. Changes can be pushed to Subscribers on demand, continuously, or on a scheduled basis. The Distribution Agent or Merge Agent runs at the Distributor.	·    Data will typically be synchronized continuously or on a frequently recurring schedule. ·    Publications require near real-time movement of data. ·    The higher processor overhead at the Distributor does not affect performance. ·    Most often used with snapshot and transaction replication.
Pull Subscription	With a pull subscription, the Subscriber requests changes made at the Publisher. Pull subscriptions allow the user at the Subscriber to determine when the data changes are synchronized. The Distribution Agent or the Merge Agent runs at the Subscriber.	·    Data will typically be synchronized on demand or on a schedule rather than continuously. ·    The publication has a large number of Subscribers, and/or it would be too resource-intensive to run all the agents at the Distributor. ·    Subscribers are autonomous, disconnected, and/or mobile. Subscribers will determine when they will connect and synchronize changes. ·    Most often used with merge replication.

Web Synchronization for Merge Replication With Pull Subscription

When Web synchronization is used, updates at the Subscriber are packaged and sent as an XML message to the computer that is running IIS by using the HTTPS protocol. The computer that is running IIS then sends the commands to the Publisher in a binary format, typically by using TCP/IP. Updates at the Publisher are sent to the computer that is running IIS and then packaged as an XML message for delivery to the Subscriber.
The following illustration shows some of the components that are involved in Web synchronization for merge replication.

Web synchronization is an option only for pull subscriptions; therefore, a Merge Agent will always run on the Subscriber. This Merge Agent can be the standard Merge Agent, the Merge Agent ActiveX control, or an application that provides synchronization through Replication Management Objects (RMO). To specify the location of the computer that is running IIS, use the –InternetUrl parameter for the Merge Agent.
The SQL Server Replication Listener (Replisapi.dll) is configured on the computer that is running IIS and is responsible for handling messages that are sent to the server from the Publisher and Subscribers. Each node in the topology handles the XML data stream by using the Merge Replication Reconciler (Replrec.dll).

Now I will explain how to  Web Synchronization With Pull Subscription For SQL Express.

First I have to create new publisher for SQL server and map this publisher to IIS web site which will be consumed by  subscriber.
As we know SQL Express has no SQL Server Agent so we need create a agent which can get data from publisher.We can create that agent by  replication management objects(RMO Programming).

In following screen I am showing where we can use Web Synchronization.

When we have multiple stores and want to replicate data between stores by the help of publisher. We can achieve this by Web Synchronization.

Now I will show below things to you.

• How configure IIS for SQL publisher
• Create subscriber by RMO Programming
• Synchronization data by RMO Programming.

How configure IIS

These below steps for configure the IIS for web synchronization 

  1.      First of all you have open sql server run as administrator

  2.      Login with SQL credentials

  3.      Right click on publisher select  configure web synchronizations

  4.       After click on web synchronizations click on next you have two option

•           Create new virtual directory
•           Use existing virtual directory

            Checked Allow basic authentication

  Add user for virtual directory access.

  5.      Click on next your web synchronization configuration has been done. You can see you website in IIS 7

  6.      Click on SQLReplication virtual directory double click on handler mappings and add new module mapping.

  7.      Click on ok. Register C:\inetpub\wwwroot\SQLReplication\replisapi.dll by following command

  8.      Created new  self-signed certificate for web synchronization

  9.      Add created certificate with web synchronization web site.

  10.   Export the certificate by run above command and install in server and client sql server.

  11.  Run https://win-d838rz3d8ed/SQLReplication/replisapi.dll?diag on your machine you will get following screen

  12.   If you are getting error in window server which is running in 64-bit must run following command in command prompt.

cscript %SystemDrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32bitAppOnWin64 1

Create subscriber by RMO Programming

  We need to write below code for created subscriber agent for SQL express by help of RMO. You have to write below code.

    public static void CreatePullSubcription()

        {

            // Define the Publisher, publication, and databases.

            string publicationName =”pawnmasterPublication”;

            string publisherName = “WIN-D838RZ3D8ED”;

            string publisherUid = "deepakj";

            string publisherPass ="123";

            string publicationDbName = "demo";

            string subscriberName = @"WIN-D838RZ3D8ED\SQLEXPRESS";

            string subscriberUid = "deepakj";

            string subscriberPass = "123";

            string subscriptionDbName = "demo";

            string hostname =@"WIN-D838RZ3D8ED\deepakj";

            string winLogin = @"WIN-D838RZ3D8ED\deepakj";

            string winPassword = "123";

            string webSyncUrl = "https:// win-d838rz3d8ed/ SQLReplication/replisapi.dll";

            //Create the Subscriber connection.

            ServerConnection conn = new ServerConnection(new SqlConnection(sqlconnectionstring));

            // Create the objects that we need.

            MergePullSubscription subscription;

            try

            {

                // Connect to the Subscriber.

                conn.Connect();

                // Define the pull subscription.

                subscription = new MergePullSubscription();

                subscription.ConnectionContext = conn;

                subscription.PublisherName = publisherName;

                subscription.PublicationName = publicationName;

                subscription.PublicationDBName = publicationDbName;

                subscription.DatabaseName = subscriptionDbName;

                subscription.HostName = hostname;

                // Specify an anonymous Subscriber type since we can't

                // register at the Publisher with a direct connection.

                subscription.SubscriberType = MergeSubscriberType.Anonymous;

                // Specify the Windows login credentials for the Merge Agent job.

                subscription.SynchronizationAgentProcessSecurity.Login = winLogin;

                subscription.SynchronizationAgentProcessSecurity.Password = winPassword;

                // Enable Web synchronization.

                subscription.UseWebSynchronization = true;

                subscription.InternetUrl = webSyncUrl;

                // Specify the same Windows credentials to use when connecting to the

                // Web server using HTTPS Basic Authentication.

                subscription.InternetSecurityMode = AuthenticationMethod.BasicAuthentication;

                subscription.InternetLogin = winLogin;

                subscription.InternetPassword = winPassword;

                // Ensure that we create a job for this subscription.

                subscription.CreateSyncAgentByDefault = true;

                // Create the pull subscription at the Subscriber.

                subscription.Create();

            }

            catch (Exception ex)

            {

                // Implement the appropriate error handling here.

                throw new ApplicationException(String.Format(

                    "The subscription to {0} could not be created.", publicationName), ex);

            }

            finally

            {

                conn.Disconnect();

            }

        }

 Synchronization data by RMO Programming

We have to write a code for pull the data from publisher to subscriber database. Below code will help to us run the pull agent in subscriber machine.

        public static void RunPullAgent()

        {

            // Define the Publisher, publication, and databases.

            string publicationName =”pawnmasterPublication”;

            string publisherName = “WIN-D838RZ3D8ED”;

            string publisherUid = "deepakj";

            string publisherPass ="123";

            string publicationDbName = "demo";

            string subscriberName = @"WIN-D838RZ3D8ED\SQLEXPRESS";

            string subscriberUid = "deepakj";

            string subscriberPass = "123";

            string subscriptionDbName = "demo";

            string hostname =@"WIN-D838RZ3D8ED\deepakj";

            string winLogin = @"WIN-D838RZ3D8ED\deepakj";

            string winPassword = "123";

            string webSyncUrl = "https:// win-d838rz3d8ed/ SQLReplication/replisapi.dll";

            // Create a connection to the Subscriber.

            ServerConnection conn = new ServerConnection(new SqlConnection(sqlconnectionstring));

            MergePullSubscription subscription;

            MergeSynchronizationAgent agent;

            try

            {

                // Connect to the Subscriber.

                conn.Connect();

                // Define the pull subscription.

                subscription = new MergePullSubscription();

                subscription.ConnectionContext = conn;

                subscription.DatabaseName = subscriptionDbName;

                subscription.PublisherName = publisherName;

                subscription.PublicationDBName = publicationDbName;

                subscription.PublicationName = publicationName;

                // If the pull subscription exists, then start the synchronization.

                if (subscription.LoadProperties())

                {

                    // Get the agent for the subscription.

                    agent = subscription.SynchronizationAgent;

                    // Check that we have enough metadata to start the agent.

                    if (agent.PublisherSecurityMode == null)

                    {

                        // Set the required properties that could not be returned

                        // from the MSsubscription_properties table.

                        agent.PublisherSecurityMode = SecurityMode.Integrated;

                        agent.DistributorSecurityMode = SecurityMode.Standard;

                        agent.DistributorLogin = publisher.UserId;

                        agent.DistributorPassword = publisher.Password;

                        agent.Distributor = publisherName;

                        agent.HostName = hostname;

                        // Set optional Web synchronization properties.

                        agent.UseWebSynchronization = true;

                        agent.InternetUrl = webSyncUrl;

                        agent.InternetSecurityMode = SecurityMode.Standard;

                        agent.InternetLogin = winLogin;

                        agent.InternetPassword = winPassword;

                    }

                    // Enable agent output to the console.

                    agent.OutputVerboseLevel = 4;

                    agent.Output = subscriber.LogFilePath;

                    // Synchronously start the Merge Agent for the subscription.

                    agent.Synchronize();

                }

                else

                {

                    // Do something here if the pull subscription does not exist.

                    throw new ApplicationException(String.Format(

                        "A subscription to '{0}' does not exist on {1}",

                        publicationName, subscriberName));

                }

            }

            catch (Exception ex)

            {

                // Implement appropriate error handling here.

                throw new ApplicationException("The subscription could not be " +

                    "synchronized. Verify that the subscription has " +

                    "been defined correctly.", ex);

            }

            finally

            {

                conn.Disconnect();

            }

 }

 Enjoy the web synchronization