One of the challenges in SQL Server replication is synchronizing data between the publisher and subscribers, especially when the machines are not on the same network. In such cases, alternative methods of data synchronization are required. In this blog post, we will explore how to set up SQL Server merge replication using web synchronization.

Install and Configure IIS

To begin, we need to have Internet Information Services (IIS) installed on our server. Here are the steps to install and configure IIS:

1. Open the Control Panel and click on “Programs” > “Turn Windows Features On or Off”.
2. Check “Internet Information Services” and click OK.
3. If you are using Windows Server, use Server Manager and go to “Features” > “Add Features” to enable these features.
4. Once installed, navigate to the C:\ drive and create a folder named “SQLReplication” under the “inetpub” folder.
5. Copy the file “replisapi.dll” from C:\Program Files\Microsoft SQL Server\100\COM to C:\inetpub\SQLReplication.
6. Open a command prompt and execute the following statements:

cd C:\inetpub\SQLReplication
regsvr32 replisapi.dll

Next, open IIS Manager, expand “Sites”, right-click on the “Default Web Site”, and add a virtual directory. Give it an alias of your choice and set the physical path to C:\inetpub\SQLReplication. Click on “Connect as…” and select a specific user, then provide the username and password of a user that has permissions on the folder. Test the settings.

Click on the web server, double-click on “Server Certificates”, and create a self-signed certificate. Then, click on “Default Web Site”, in the right pane click on “Bindings”, and add a binding of type “https” using the SSL certificate you created earlier.

Under the “Default Web Site”, select “SQLReplication” and go to “SSL Settings”. Check “Require SSL” and set “Client Certificates” to “Ignore”.

Under the “Default Web Site”, select “SQLReplication” and go to “Authentication”. Enable anonymous and Windows authentication.

Under the “Default Web Site”, select “SQLReplication” and go to “Handler Mappings”. Click on “Add Module Mapping” and fill in the necessary details. Then, click on “Request Restrictions” and enable “Invoke handler”, select “File”, and on the “Access” tab, select “Execute”.

After adding the module mapping, check if it is enabled. If it is disabled, right-click on it, select “Edit Feature Permissions”, and enable “Execute”.

Now, open a browser and check https://COMPUTERNAME/SQLReplication/replisapi.dll?diag. It should show the diagnostic mode.

Configure SQL Server Merge Replication

Now that IIS is configured, we can proceed to configure SQL Server merge replication. Follow these steps:

1. Create a SQL Server merge publication and generate a snapshot.
2. Share and give the required permissions on the folder called “repldata”.
3. Right-click on the merge publication and select “Configure Web Synchronization” > “Next”.
4. Browse the computer where you installed IIS and select the virtual directory you created earlier.
5. On the “Authenticated Access” window, enable basic and Windows authentication and provide the domain name.
6. On the “Directory Access” page, add an account and provide the shared path of the snapshot folder (share of “repldata” which you created earlier).
7. Right-click on the merge publication and select “Properties”. On the “FTP Snapshot and Internet” tab, check “Allow Subscribers to synchronize by connecting to a Web server” and provide the link, such as https://COMPUTERNAME/SQLReplication/replisapi.dll.

Creating Subscriber to Use Web Synchronization

To create a subscriber that uses web synchronization, execute the following queries on the publisher and subscriber databases:

On the publisher database:

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

On the subscriber database:

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'

Finally, 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.

With these steps, you have successfully set up SQL Server merge replication with web synchronization. This allows data to be transferred using the HTTPS protocol, making it suitable for pull subscriptions where the subscriber is not always connected.