Web merge sync on MS SQL

It was decided to write this instructional article to help my friends and colleagues, since most of them did not encounter setting up web synchronization in MS SQL, and I hope that it will allow you to avoid some minor problems that I myself encountered while setting up . I hope you, dear habrasoobschestvu, it can be useful.

Information on tuning on the Internet is available, but it seemed to me that everything is very fragmented and for the most part there is no practical part in tuning. Maybe I was looking bad.

Why and for what

And so we need to synchronize information between the two stores, so that they always receive the actual quantities of goods, reference books, etc., and that this is all regulated from one back office, and the stores would only have front offices.
')
Why not set up replication with white IP or via VPN:

1. The provider does not provide white IP.
2. Used mobile Internet with bad speed.
3. When using PPTP, providers block GRE.
4. VPN has a feature of breaking off, although it restores the connection afterwards.
5. Due to a slight loss of communication, the SQL Server agent does not always have time to connect to the subscriber, and the next attempt is scheduled according to a certain time schedule; if it is not frequently synchronized, this can be quite critical.
6. With a large number of publications and subscribers connecting to the server, again, the agent does not always “manage” to connect to the subscriber. It usually helps to set up a schedule so that synchronization does not take place for everyone at the same time, which is also not always convenient.

When working with web synchronization in merge replication, data synchronization is via HTTPS and updates are sent as XML, this will avoid the problems described above, perhaps not everything, but still:

1. We do not need a permanent IP and, accordingly, the user is not tied to the workplace and can be with his laptop anywhere.
2. The connection uses a secure protocol.
3. It is unlikely that the provider will block port 443.
4. With a slow Internet connection, synchronization is faster and there are much fewer cliffs, according to my observations.
5. The merge agent is located at the subscriber, which reduces the load on the server itself.

Training

You can work with web synchronization starting with MS SQL 2005 and later, on a central server you will need Standard edition or older, for the subscriber, Express edition is enough.

In this example, are used:

• Microsoft SQL Server 2014 Standard as a publisher.
• Microsoft SQL Server 2014 Express as a subscriber.
• Installed on a central IIS server
• Deployed the main database of our store on the publisher.
• Created an empty subscriber database.
• Windows user with IIS_IUSRS group permissions
• A little patience.

IIS Setup

1. First of all, let's prepare IIS for work, create a directory for the C: \ inetpub \ wwwroot \ WebSQL service. If the server is supposed to work with several publications, create separate directories in WebSQL \ RCU.
2. Copy replisapi.dll from C: \ Program Files \ Microsoft SQL Server \ 120 \ COM to the directory created in the first step in WebSQL, when working with several publications, copy to WebSQL \ RCU.
3. It is recommended to register the library on many Internet resources, but in my case, oddly enough, this was not required. To register, use the regsvr32 "C: \ inetpub \ wwwroot \ WebSQL \ RCU \ replisapi.dll" command as an administrator.
4. Create a new website through the IIS Manager. Physical path specify the directory created in the first step, if there are subdirectories inside, leave in any case the root directory.
   
   
   Adding a website

   
   
   
   
5. After we add a virtual directory to our site. We specify the alias according to the name of the directory and specify the physical path to it. If the directory is one path you can point to the root directory.
   
   
   Add virtual directory

   
   
   
   
6. The next step is to configure the resolution for executing replisapi.dll for this in the IIS Manager, select the virtual directory and in the central panel in the IIS category, find the item matching handlers. Next, in the action panel, select the item to add a module mapping.
   
   
   Handler mapping

   
   
   
   
   • In the field the path of the request we specify replisapi.dll
   • In the list of modules, select IsapiModule. If this module is not listed, then in the Windows components for IIS services, you need to add ISAPI extensions.
   
   
   Adding Windows Components

   
   
   
   
   • For the executable file, specify the path to the library in the virtual directory C: \ inetpub \ wwwroot \ WebSQL \ RCU \ replisapi.dll. I want to note that again, if there are several publications, then you add a handler for each virtual directory with its own path.
   • In the field name indicate Replisapi
   • Click the limit button and go to the access tab and select the execution.
   
   
   Request Restrictions

   
   
   
   
   • We confirm the changes in the request restriction window and in the module mapping. Upon confirmation, we will get a warning about whether we really want to allow this ISAPI extension, click yes.
   
   
   Allow this extension

   
   
   
   
   • Add a handler required to enable it, since by default it will not be launched, for this we will find and select it in the list of handlers and in the action panel click the change of function permissions and in the opened window check the execution and confirm the changes.
   
   
   Change function permissions

   
   
   
   
7. To work properly, you need to configure authentication for the entire site in IIS. In the central pane, in the IIS category, select authentication and disable anonymous authentication and enable basic authentication. Again, if this item is absent in IIS, you need to add this feature in Windows components.
   
   
   Authentication

   
   
   
   
8. The next step we need to bind the SSL certificate to the site, I will not describe the procedure for obtaining the certificate, since there is plenty of this information everywhere, including in Habrahabr.
   
   
   • We get an SSL certificate for example, on startssl and form a PFX file.
   • In IIS Manager for the IIS server itself, select server certificates in the central pane and select import in the action pane, specify the path to the stored certificate and its password.
   
   
   Import certificate

   
   
   
   
   • After importing, we again select the site in the dispatcher and in the action panel we select bindings and in the opened window we select to add. Select the https type and in the list the SSL certificate select the one that you imported in the previous step and confirm the changes. Binding for http can be removed as unnecessary.
   
   
   Add a binding

   
   
   
   
9. The last step in configuring IIS is to check the service. To do this, go to this address https://server.domain.com/rcu/replisapi.dll?diag and enter the Windows username and password (I recommend creating a separate user for each publication). If the connection is successful, diagnostic information should appear, as in the screenshot below.
   
   
   Diagnostic information

   
   

Creating and setting up a publication

1. Connect to the central server with Microsoft SQL Server 2014 Standard via Management Studio in Object Explorer -> Replication -> Local Publication -> New Publication (in the context menu), then follow the steps of the New Publication Wizard. At this stage, setting up merge replication using web synchronization is no different from setting on the local network.
   
   
   • Select a database for publication.
   
   
   Base selection

   
   
   
   
   • Select the type of replication - merge replication (Merge)
   
   
   Replication type

   
   
   
   
   • Choose which versions of SQL Server replication is compatible with. I usually use SQL Server 2005 and SQL Server 2008 and older, as there are quite weak machines.
   
   
   Compatibility

   
   
   
   
   • Specify which tables will participate in the synchronization. I would like to additionally note if synchronization will take place infrequently, and there will be a lot of records on some tables, I recommend increasing the values ​​for Publisher range size and Subscriber range size, for example, to 1,000,000. It is not recommended to set more, because the range can quickly will end with a large number of subscribers and frequent re-creation, I also want to note if Automatically manage identity ranges are used, increase the percentage to highlight the new range when the threshold reaches a threshold, for example, 90-95.
   
   
   Tables

   
   
   
   
   Publisher and Subscriber Range

   
   
   
   
   • We leave this step unchanged for all tables. A column with a unique identifier will be added.
   
   
   Unique identifier (optional)

   
   
   
   
   • We leave the filtering of synchronized data unchanged, if you need filtering, you can add it at any time.
   
   
   Filtering (optional)

   
   
   • Specifying a schedule for taking a snapshot, this step can also be left as default or you can change its schedule as you like.
   
   
   Snapshot (optional)

   
   
   
   
   • Specify security settings for the snapshot agent. Select in the settings “Execute SQL Server Agent with service account” and connect with the publisher “By impersonating the process account” and confirm the changes.
   
   
   Agent security

   
   
   
   
   • We leave the next step without a default in it to confirm the creation of the publication and generate a script for the completed steps to create the publication.
   • Specify the name of the publication and click Finish. Usually, for convenience, I write the name like “database_name_pub”
   
   
   Publication name

   
   
   
   
   • We wait until the creation of the publication is completed and check that all the items are completed correctly.
   
   
   Creating a publication

   
   
   
   
2. After creating the publication we need to make additional settings. To do this, select the created publication in the Object Explorer -> Replication -> Local Publication -> publication_name and open the settings.
   
   
   • In the basic settings, we point out that subscriptions never expire, this is necessary if it is planned that subscribers can be offline for a long time and there will not be many transactions. Otherwise, specify after what period the replication metadata will be deleted.
   
   
   Subscription Term

   
   
   
   
   • In the snapshot settings, we specify an alternative location for storing the snapshot, while the directory must be available for public access on the network and the path to the directory must be indicated as network. In this example, it will be \\ Replication \ ftproot. Also set the compression for the snapshot in this folder.
   
   
   Snapshot Location

   
   
   
   
   • Next, in the FTP and Internet settings for the snapshot (FTP Snapshot and internet), we allow synchronization of subscribers via the web server, and also specify the address of the previously created site https://server.domain.com/rcu/replisapi.dll
   
   
   Subscribers Sync

   
   
   
   
   • The final step in setting up a publication is to add a user to the Publication Access List. We create the user in advance for each publication, then add it to the MSSQL Server, match it with the database for publication and indicate membership in the db_owner role.
   
   
   User mapping

   
   
   
   
   Publication Access List

   
   

Subscription creation and synchronization

1. It is necessary to create subscriptions for web synchronization using scripts, not through a wizard, because we will have to perform synchronization with the subscription on demand, and also because the agent cannot be started on MSSQL Server Express. Scripts in any case turns out much faster.
   
   
   • In the beginning we will add the subscriber on the central server with the publication
   
   

   USE _ GO EXEC sp_addmergesubscription @publication = N'Microinvset_pub', --    @subscriber = 'Kassa01-PC', --     @subscriber_db = N'MicroinvestFront', --        @subscription_type =N'pull'; 

   
   
   • Next, add a subscription on request already on the subscriber
   
   

    USE _ GO EXEC sp_addmergepullsubscription @publisher = 'MainServer', --      @publication = N'Microinvset_pub', --    @publisher_db = N'Microinvest'; --      

   
   
   • Also on the subscriber we add the task for the agent to synchronize the subscription on demand
   
   

    USE _ GO EXEC sp_addmergepullsubscription_agent @publisher = 'MainServer', --      @publisher_db = N'Microinvest', --      @publication = N'Microinvest_pub', --    @distributor = 'MainServer', --      @use_web_sync = 1, --  WEB . @internet_security_mode = 0, --   ,        (    ,    HTTP). @internet_url = 'https://server.domain.com/rcu/replisapi.dll', --      @internet_login = '  Windows,          ', @internet_password = '  Windows,          ', @internet_timeout = 9999; --           - 

   
   
2. Finally we get to the most important one before the first synchronization, as the Express edition of MSSQL has already written before, it doesn’t allow the built-in agent to be launched, but nobody forbids you to make your agent using RMO, we’ll go more simply and start the merge agent manually using which then simply put in the Windows scheduler.
   
   

    @echo OFF SET Publisher=MainServer SET Subscriber=Kassa01-PC SET PublicationDB=Microinvest SET SubscriptionDB=MicroinvestFront SET Publication=Microinvest_pub REM --    REPLMERG.EXE      MSSQL Server "C:\Program Files\Microsoft SQL Server\120\COM\REPLMERG.EXE" -Publication %Publication% -Publisher %Publisher% -Subscriber %Subscriber% -Distributor %Publisher% -PublisherDB %PublicationDB% -SubscriberDB %SubscriptionDB% -DistributorSecurityMode 1 -SubscriptionType 1 -SubscriberSecurityMode 1 

   
   
3. Upon completion of synchronization, you will see approximately the following information in the CMD, as in the screenshot.
   
   
   Total Sync Start

   
   
   
   
4. You can monitor the progress of replication, as before through the replication monitor.
   
   
   Replication monitor

   
   

Afterword

Web synchronization is configured, we can only place the batch file created above into the Windows scheduler and run it automatically, for example, once every 5-10 minutes, of course, if required. When you start the batch file and the synchronization will constantly pop up a window from the CMD, to avoid this, you can run the batch file through WScript.exe, for example, like this:

 Set WshShell = CreateObject("WScript.Shell") WshShell.Run "startsync.bat", 0, false Set WshShell = Nothing WScript.Quit 

After that, this script is specified to run in the scheduler. In addition to synchronization, I’ll note that if you plan on quite large data transfers, you may need to increase the value for WebSyncMaxXmlSize. This parameter is configured in the registry HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Microsoft SQL Server \ 120 \ Replication \, be careful with this setting, since as the value increases, the amount of virtual memory consumed also increases.

Links

1. Web synchronization for merge replication
2. How to synchronize a subscription on demand (replication programming)
3. How to synchronize a subscription on demand (programming RMO objects)

I tried to point out all the nuances that I myself had to face, even if they could not be very commonplace, but such banality sometimes can take a lot of time, especially if you don’t know where to look.

Thanks to everyone who has mastered the article to the end, I will be glad to any of your comments.