MS SQL Merge Web Synchronization

It was decided to write this instruction manual to help my friends and colleagues, as most of them did not deal with setting up web synchronization in MS SQL, and I hope that it will avoid some minor problems that I myself encountered when setting up . I hope you, dear habrasociety, it can come in handy.

There is information on setting up the Internet, but it seemed to me that everything is very fragmented and for the most part there is no practical part in setting up. Perhaps I was looking badly.

Why and for what

And so we need to synchronize the information between the two stores, so that they always receive the latest quantities of goods, directories, etc., and so that all this is regulated from one back office, and the stores would only have front offices.

Why not configure replication with white IPs or through a VPN:

1. The provider does not provide a white IP.
2. Mobile internet is used with poor speed.
3. When using PPTP, providers block GRE.
4. VPN has the ability to break off, although after restoring the connection.
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 after a while, with not frequent synchronization, 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 is synchronized via HTTPS and updates are sent as XML, this will avoid the problems described above, maybe not all, 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 breaks according to my observations.
5. The merge agent is located on the subscriber, which reduces the load on the server itself.

Training

You can work with web synchronization starting with MS SQL 2005 and older, a Standard edition or older is required on a central server, an Express edition is sufficient for a subscriber.

This example uses:

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

IIS setup

1. First of all, we will prepare IIS for work, create a directory for the service C: \ inetpub \ wwwroot \ WebSQL. If the server is supposed to work with multiple 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 multiple publications, copy to WebSQL \ RCU.
3. On many Internet resources it is recommended to register the library, but in my case, oddly enough, this was not required. To register, use the regsvr32 command “C: \ inetpub \ wwwroot \ WebSQL \ RCU \ replisapi.dll” as the administrator.
4. Create a new website through IIS Manager. The physical path indicates the directory created in the first step, if there are subdirectories inside, we leave the root directory in any case.
   
   
   Adding a website

   
   
   
   
5. After we add a virtual directory to our site. We indicate the alias according to the name of the directory and indicate the physical path to it. If the directory is a single path, you can point to the root directory.
   
   
   Adding a virtual directory

   
   
   
   
6. The next step is to set the permission for executing replisapi.dll for this in the IIS Manager, select the virtual directory and in the central panel in the IIS category we will find the handler mapping item. Next, in the action bar, select the add module mapping option.
   
   
   Handler mapping

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

   
   
   
   
   • 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 name field, specify Replisapi
   • Click the restrict button and go to the access tab and select execution.
   
   
   Request Limitations

   
   
   
   
   • We confirm the changes in the query restriction window and in the module mapping. Upon confirmation, we will be warned whether we really want to allow this ISAPI extension, click yes.
   
   
   Allow this extension

   
   
   
   
   • Add a handler, you need to enable it, because by default it will not start, for this we will find it and select it in the list of handlers and in the action panel, click change the permissions of the function and in the window that opens, mark execution and confirm the changes.
   
   
   Change function permissions

   
   
   
   
7. For proper operation, you will need to configure authentication for the entire site in IIS. In the central pane, in the IIS category, select authentication and turn off anonymous authentication and enable basic authentication. Again, if this item is not available in IIS, you need to add this feature in Windows components.
   
   
   Authentication

   
   
   
   
8. The next step is to bind the SSL certificate to the site, I will not describe the procedure for obtaining the certificate, since this information is abundant everywhere, including on Habrahabr.
   
   
   • We get an SSL certificate, for example, at startssl and form a PFX file.
   • In the IIS manager for the IIS server, select server certificates in the central panel and select import in the action panel, specify the path to the saved certificate and its password.
   
   
   Certificate Import

   
   
   
   
   • After importing, we again select the site in the dispatcher and select the bindings in the action panel and select add in the window that opens. We select the type of https and in the list of SSL certificates we select the one that was imported in the previous step and confirm the changes. The binding for http can be removed as unnecessary.
   
   
   Adding Binding

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

   
   

Create and configure publishing

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

   
   
   
   
   • Choosing a Replication Type - Merge Replication (Merge)
   
   
   Replication type

   
   
   
   
   • Choosing which versions of SQL Server replication will be compatible. I usually use SQL Server 2005 and SQL Server 2008 and older, as there are very weak machines.
   
   
   Compatibility

   
   
   
   
   • Specify which tables will participate in synchronization. I would like to additionally note if synchronization will not take place often, and there will be many 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 values, since the range can quickly it will end when there are a large number of subscribers and they are often recreated, I also want to note if using Automatically manage identity ranges, increase the percentage to highlight a new range when reaching the threshold (Range threshold percentage), for example, 90-95.
   
   
   Tables

   
   
   
   
   Publisher and Subscriber Range

   
   
   
   
   • This step is left unchanged for all tables, a column with a unique identifier is added.
   
   
   Unique identifier (optional)

   
   
   
   
   • We keep the synchronized data filtering unchanged, if filtering is required it can be added at any time.
   
   
   Filtering (optional)

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

   
   
   
   
   • Specify security settings for the snapshot agent. We select in the settings "Run SQL Server Agent with the service account" and the connection to the publisher "By impersonating the process account" and confirm the changes.
   
   
   Agent Security

   
   
   
   
   • The next step is left without a default; it proposes to confirm the creation of the publication and generate a script for the steps taken to create the publication.
   • Specify the name of the publication and click Finish. Usually, for convenience, I write the name like "pub_name"
   
   
   Publication Name

   
   
   
   
   • We are waiting for the publication to finish creating and check that all items are completed correctly.
   
   
   Create publication

   
   
   
   
2. After creating the publication, we will need to make additional settings. To do this, select the created publication in Object Explorer -> Replication -> Local Publication -> publication_name and open the settings.
   
   
   • In the main settings, we indicate that the subscription will never expire, this is necessary if it is planned that subscribers can be offline for a long time and there will not be many operations. Otherwise, specify how long the replication metadata will be deleted.
   
   
   Subscription Validity

   
   
   
   
   • In the Snapshot settings, we indicate an alternative location for storing the snapshot; in this case, the directory must be accessible for general access on the network and the path to the directory must be indicated by the network. In this example, it will be \\ Replication \ ftproot. Also set compression for the image in this folder.
   
   
   Snapshot Location

   
   
   
   
   • Next, in the FTP and Internet settings for a snapshot (FTP Snapshot and internet), we enable the synchronization of subscribers through the web server, as well as the address of the previously created site https://server.domain.com/rcu/replisapi.dll
   
   
   Subscriber Sync

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

   
   
   
   
   Publication Access List

   
   

Subscription creation and synchronization

1. You will need to create a subscription during web synchronization using scripts, and not through a wizard, because we will perform synchronization with a subscription upon request, and also because the agent cannot be started on MSSQL Server Express. Scripts in any case are much faster.
   
   
   • First, add a subscriber to 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 at the subscriber
   
   

   USE имя_базы
   GO
   EXEC sp_addmergepullsubscription
   @publisher = 'MainServer', -- Указываем имя сервера с публикацией
   @publication = N'Microinvset_pub', -- Указываем имя публикации
   @publisher_db = N'Microinvest'; -- Указываем имя базы данных публикации
   

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

   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 thing before the first synchronization, as Express has already written before, the MSSQL edition does not allow you to run the built-in agent, but no one forbids making your agent using RMO, we will go in a simpler way and start the merge agent manually using a batch file, which after just 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.
   
   
   Sync Startup Summary

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

   
   

Afterword

Web synchronization is configured, we only need to place the batch file created above in the Windows scheduler and run it automatically, for example, every 5-10 minutes, of course, if necessary. When starting the batch file and synchronization, a window with CMD will constantly pop up, in order to avoid this, you can start 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. Additionally, I note if synchronization is planned rather voluminous data transfer, 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 consumed virtual memory also increases.

References

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

I tried to indicate all the nuances that I myself had to face even which could be very banal, but such platitudes can sometimes take a lot of time, especially if you do not know where to look.

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