SQL Merge Replication over SSL

I’ve spent the last few days pulling my hair out trying to get SQL Merge replication over SSL working on SQL Server 2008 R2.  The situation I have is basically I have a SQL server behind our corporate firewall with some data.  We are setting up a SQL VM on Azure and need to constantly replicate data out to the Azure server.  The only built-in option in SQL server is to perform merge replication with the help of an IIS server.

To understand the setup you can refer to this msdn article, it does a decent job so I’m going to retype it.

There are a few catches and gotcha’s, that drove me crazy and I had to scour the web to find answers on why things weren’t working.

These are the things that I used, it may not be best practices but it worked out in the end:

  • SQL Server 2008 R2 (with source data, inside corporate firewall) (Publisher)
  • SQL Server 2008 R2 Azure VM (server to have data replicated to) (Subscriber)
  • Windows 2008 R2 with IIS installed (WebServer)
  • SSL Cert for above web server (not self-signed)
  • Domain Account, used to access the publication
  • Local Account, on WebServer for IIS application pool
  • Publically available dns name (I’ll use https://replication.domain.com)
  • SQL Management Studio (SSMS)

Setup of WebServer

All the instructions I read really only covered configuring the website in IIS for replication, but no one really described what you needed from scratch.  I started with a fresh install of Windows 2008 R2.

  1. Add the “Web Server” role with the following features (in addition to default features):
    • Application Development > ISAPI Extensions, ISAPI Filters
    • Security > Basic Authentication
    • Management Tools > IIS Management Console, IIS Management Service
  2. Configure IIS for Web Synchronization, this is a great step by step at msdn.  While going through this guide, the 2 accounts they mention are the ones listed above in what you need.  The domain credentials are the ones you will use at the end to test the replisapi.dll connection, once basic authentication is enabled.

There were however 2 issues when following the msdn article.

  • If you are on a 64-bit machine, the replisapi.dll may not be the correct version, if you are receiving an “HTTP 500.0 – Internal Server Error”.  The two versions are different sizes and I believe are compiled for 64bit and 32bit.  Even if you follow the instructions and use the wizard, it still copied the wrong version in my case. I believe I had to copy the version fron the x86 directory.
    • C:\Program Files\Microsoft SQL Server\100\COM\
    • C:\Program Files (x86)\Microsoft SQL Server\100\COM\
  • If you get a plain page with “Access Denied” in when going to http://replication.domain.com/replisapi.dll?diag , it is most likely the fact that the credentials you use need to be an Administrator on the WebServer.  In my case, I was using the domain credentials, so I had to add those to the local Administrators group.
    Hopefully at this point, you can see the replisapi.dll?diag page and we can move on to configuring the replication.

Configuring Replication

Part of the trick wit this step is that you apparently have to setup and do the initial replication locally, you cannot setup replication over SSL until after you setup both the Publisher and Subscriber.  Once setup you simply change over the settings and theoretically it’s good to go.

This part of the setup is fairly straight forward.  On the publisher, open up replication and add a new “Local Publication”, be sure to select “Merge Replication”.  If you select the default location, make sure you share the directory and call it “ReplicationSnapshots”:

Snapshot Directory: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\repldata\unc\[server_snapshot name]

Share Directory: C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\repldata

Share UNC Path: \\[servername]\ReplicationSnapshots

Be sure to add your domain credentials to the directory security and share permissions with read only permissions.

On the subscriber, if it’s off site (ex. SQL Azure VM), you’ll need to connect into your corporate network using vpn to do the initial configuration.  In SSMS, open up replication and add a new “Local Subscription”.  You will be pointing it to the publisher and selecting merge replication.  Once you finish the wizard, it will fail the first time because the default directory it is looking at for the snapshot is inaccessible.

For some reason or another it sets it up using the local path, instead of the unc path.  Simple fix, right click on the subscription and click on properties.  Scroll down to “Snapshot” section and change “Snapshot location” from default to “Alternate Folder” and fill in the “Snapshot folder” to the unc path shared above.  Once this is complete open up the “Synchronization Monitor” and run it, it should replicate the data to your subscriber.

Configure Web Synchronization

If you’ve made it this far, then you are almost done!  Now that the merge replications are working, we just need to point it to our WebServer and have it make it’s data requests through the server.

First we’ll configure the Publisher.  In SSMS, open up the replication folder and right click on the Local Publication you created earlier and select “Properties”.  Check the “Allow Subscribers to synchronize….” and fill in the address for the web server.

image

Next we’ll configure the subscriber.  It’s just as simple as with the publisher.  Open up a connection in SSMS to the subscriber and go to the replication folder.  Right click on select “Properties” on the subscription created before.  Scroll down to the “Web Synchronization” section and change “Use Web Synchronization” to true, fill in the Web server address, Change the Web Server Connection to use your domain credentials (it should change to “basic Authentication” as seen below).  You should be able to run the synchronization from the subscriber without vpn now.

image

Sit back and let the kudos roll in

Theoretically, if nothing funky goes wrong, you should be ready to go.

Update: So, I appeared to have overlooked one important detail.  In my case, my subscriber is a SQL Azure VM (using SQL authentication) and my publisher is a local VM using Integrated Authentication.  Subscriber is not part of the domain, publisher is part of a domain.  And if you’ve used SSMS in the past, you’ll know that it doesn’t let you enter domain credentials when selecting integrated authentication.  So how do we solve this problem?  I found this great little solution posted here.

The basic solution is this:

runas /netonly /user:DOMAIN\username ssms.exe
Advertisements

8 thoughts on “SQL Merge Replication over SSL

  1. Thank you for your useful guide. I’m currently following your steps to setup the web sync. May I know which SQL Server 2008 R2 component I need to install in the Web Server. I believe we do not have to install the whole suite and do we need a SQL Licence on that? Hope to hear from you soon. Appreciate it.

    • I believe the only things you need installed are “Database Engine Services” and “SQL Server Replication”. I’m not an expert on licensing, but I believe since it’s just a feature as long as you have a valid SQL Server license you should be fine.

  2. Thanks Alan. Another question is, I’m setting up the subscriber now and I’m wondering when is the initial load happens? i.e. the Publisher has a database with 10G of data, and if I rely on the subscriber to do the initial load, will it kills the sync? Or should I preload my subscriber data with the 10G data and then setup the Configure Web Sync section? Your help is much appreciated!

    • The load should happen once you complete the setup. I’m not sure you would benefit much from pre-loading it as it could cause problems. If you aren’t worried about the time it takes, I would just let it perform the load once you complete the setup.

  3. Thanks Alan. You mentioned to use Local Account, on WebServer for IIS application pool, can it actually worked? If you use local account, how can the login able to access the shared path (you cannot add \login to a different server folder permission) and be associated with the publication database?

    • It’s been a while since I’ve done this, but I believe the reason that it still works is because the local account is only used to run the IIS website. The actual authentication to access the shares that contain the replication data is done with the credentials passed from the subscriber (configured in the last screenshot), under the “Web Server Connection” option.

  4. Hi Alan. I have set up the distributor and publisher in the same db server. Then, I set up the web server at another IIS server which has a valid certificate. When come to subscriber, I set up my own machine as a subscriber, with a sample database which is the same as I created at the publisher. My machine is not part of a domain as the publisher resides. After all setup done, I tried to run the job but I received the following error,

    The process could connect to distributor ”

    Login failed for user ”

    What is the cause of this? May I know this error happen only between the web server and publisher/distributor? could it be related to the subscriber?

    • I believe it’s related to the subscriber. It may be trying to use integrated authentication which obviously would not work properly since your subscriber is not on the domain. Please check the “Web Server Connection” option (refer to the last screenshot), I believe you can configured a specific set of credentials to use for authentication.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s