Configure SQL Server Merge Replication with Web Syncronization

By: Ranga Babu   |   Updated: 2016-08-31   |   Comments (3)   |   Related: > Replication

Problem

One of the things that needs to occur for SQL Server replication is the synchronization of data between the publisher and subscribers. In some cases machines may not be on the same network, so there is a need for other methods of data synchronization. In this tip we will see how to setup SQL Server merge replication using web synchronization.

Solution

Following are the steps to setup SQL Server merge replication with web synchronization.  This allows data to be transferred using the HTTPS protocol.  This is used for pull subscriptions where the subscriber is not always connected.

Install and Configure IIS

For this tip I am using Windows 8.  We need to have IIS installed on our server, so go to Control Panel and click on Programs > Turn Windows Features On or Off.  Check Internet Information Services and click OK.

If you are using Windows Server, use Server Manager and go to Features > Add Features to enable these features.

Once installed go to the C:\ drive and you should see a folder called inetpub. Create a folder named "SQLReplication" under inetpub. Copy file "replisapi.dll" from C:\Program Files\Microsoft SQL Server\100\COM to C:\inetpub\SQLReplication.

Open a command prompt and execute the below statements.

cd C:\inetpub\SQLReplication

regsvr32 replisapi.dll

Open IIS Manager, expand Sites and right click on the Default Web Site and add a virtual directory. Give the alias of your choice and the physical path which you created C:\inetpub\SQLReplication.

Click on Connect as... and select a specific user then provide the username and password of user that has permissions on the folder and then test the settings.

Click on the web server and double click on server certificates and Create Self-Signed Certificate.

Click on Default Web Site and in the right pane click on bindings and Add...  Select https as the Type and select the SSL Certificate you created earlier.

Click on SQLReplication under the Default Web Site and select SSL Settings.  Check require SSL and client certificates as ignore.

Click on SQLReplication under Default Web Site and select Authentication.  Enable anonymous and windows authentication.

Click on SQLReplication under Default Web Site and select Handler Mappings. Then click on Add Module Mapping and fill in as shown below.

Click on Request Restrictions and enable invoke handler, select file and on access tab select execute.

After adding module mapping check if it is enabled as shown below. If disabled, right click Edit Feature Permissions and select execute.

Now open a browser and check https://COMPUTERNAME/SQLReplication/replisapi.dll?diag it should show the diagnostic mode as shown below. (Please use computer name or fully qualified domain name in place of COMPUTERNAME. I blanked out my server name to keep confidential.)

Configure SQL Server Merge Replication

Now configure a SQL Server merge publication and generate a snapshot.

You need to share and give the required permissions on the folder called repldata. See the below image for the location of the repldata folder.

Right click on the merge publication and select Configure Web Synchronization > Next. Browse the computer where you installed IIS (in my case I used the same computer for my publisher, subscriber and IIS).

Now click on configure an existing virtual directory and select the virtual directory which you created above.  On the Authenticated access window enable basic, windows authentication and provide domain name.  On the directory access page add account and give shared path of snapshot folder (share of repldata which you created above).

Right click on merge publication and select properties.  On FTP Snapshot and Internet, check "Allow Subscribers to synchronize by connecting to a Web server" and give the link such as https://COMPUTERNAME/SQLReplication/replisapi.dll.

Creating subscriber to use web synchronization

On publisher database we need to execute the below query (replace values with your information):

sp_addmergesubscription 
  @publication = 'PublicationName', 
  @subscriber = 'SubscriberServerName', 
  @subscriber_db ='SubscriberDBname', 
  @subscription_type='pull'

On subscriber database execute the below queries (replace values with your information):

sp_addmergepullsubscription 
  @publication='PublicationName', 
  @publisher='PublisherServerName', 
  @publisher_db='PublisherDBname'

sp_addmergepullsubscription_agent 
  @name ='WebSync Agent',  
  @publisher = 'PublisherServerName', 
  @publisher_db = 'PublisherDBname', 
  @publication = 'PublicationName', 
  @use_web_sync =1, 
  @internet_url = 'https://COMPUTERNAME/SQLReplication/replisapi.dll', 
  @subscriber = 'SubscriberServerName', 
  @subscriber_db = 'SubscriberDBname', 
  @internet_security_mode = 0, 
  @internet_login = 'LoginNametoconnectIIS', 
  @internet_password = 'passwordOfLogin'

Now go to SQL Server Agent Job Activity Monitor on the subscriber and you will see a job named WebSync Agent. Schedule it to run when SQL Server Agent starts and run it now so it starts.

Next Steps

• Check out these other SQL Server Replication Tips.

About the author

Ranga Babu is a SQL Server DBA with experience on performance tuning and high availability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips