Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configure SQL Server Merge Replication with Web Syncronization


By:   |   Last Updated: 2016-08-31   |   Comments (1)   |   Related Tips: More > 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.

Install and Configure IIS for SQL Server Replication

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

Enable the SQL Server Merge Replication API

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.

Configure the Virtual Directory in IISmanager

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

Create a Self-Signed Certificate for SQL Server Replication

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.

Add Site Binding for the Default IIS Website for SQL Server Replication

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

SSL Settings for SQL Server Replication

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.

Add Module Mapping for SQL Server Replication in IIS

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.

Handler Mappings for SQL Server Replication

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.)

SQL Websync Diagnostic Information

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.

Configure SQL Server Merge Replication

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.

SQL Server Replication Publication Properties
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.

SQL Server Agent Job to support Replication called WebSyncAgent
Next Steps


Last Updated: 2016-08-31


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, March 17, 2019 - 12:30:46 PM - Sajan Pullattu Back To Top

Thanks for your great post. When i click on the web syncronization wizard and connecting with virtual directory, it throws an error saying IIS was not found on the web server. I am using Sql Server 2016 and  IIS 8. Could you please let me know what is the problem.

Thanks in adavance!


Learn more about SQL Server tools