Microsoft SQL Server merge replication includes a new option for securely replicating data between sites or companies. In versions of Microsoft SQL Server older than SQL Server 2005, the only ways to do that were to use a direct connection or a site-to-site VPN (or some other VPN connection), or configure SSL encryption on the SQL Server with the database directly connected to the Internet.
With Microsoft SQL Server 2005 and higher, a new method is available; it uses the Windows Web server Internet Information Services (IIS) as a proxy between the Internet and SQL Server. This allows the subscriber to connect to the IIS Web server instead of the SQL Server, allowing the database to remain securely behind the firewall and exposing only the IIS service to the Internet.
There are a few requirements for using IIS with SQL Server merge replication, called web synchronization.
- Web synchronization requires that pull subscriptions be used where the distribution agent runs on the subscriber.
- All members of the replication must be running Microsoft SQL Server 2005 or higher.
- The IIS Server must be configured for secure communication over HTTPS.
- The SQL Server Replication Listener (Replisapi.dll) must be installed and configured on the web server.
The basic process for SQL Server merge replication is something like this:
- On the subscriber, the distribution agent is started.
- The distribution agent connects to the subscriber’s database.
- Changes are pulled from the database and bundled as an XML document.
- A HTTPS request is made to the IIS server.
- The XML Document is uploaded to the IIS server.
- The changes that need to be put into the subscriber are downloaded from the IIS web server. (Read on to learn how this is done).
- The XML document is extracted, and the changes are pushed into the subscription database.
- The distribution agent sends an acknowledgement to the IIS server stating that everything is complete.
On the IIS server the process works something like this:
- The subscriber connects to the web server.
- The web server connects to the publisher and uploads the changes from the XML document.
- Any changes the publisher has that aren’t in the subscriber are pulled from the publisher and bundled into an XML document.
- The XML Document is pulled down by the subscriber via the secure IIS connection.
Web-based subscriptions aren’t designed for high-load server-to-server replication configurations. The solution is designed more for users running SQL Server locally on laptops out in the field, or for lower-load situations. The reason for this is the amount of time and resources that are taken in bundling up the XML documents and passing the data from server to server.
Setting up SQL Server merge replication over IIS is easy. After configuring a pull subscription through the process of your choice, either pushing a snapshot or via backup and restore, edit the distribution job and add in the following parameters:
- -InternetURL The fully qualified path to the website on which the replisapi.dll is configured.
- -InternetLogin The username that should be used to authenticate against the website when basic web authentication is being used.
- -InternetPassword The password that goes with the username specified in the -InternetLogin parameter when basic web authentication is being used.
- -InternetProxyServer If a proxy server is needed to connect to the website specified in the -InternetURL parameter.
- -InternetProxyLogin If a proxy server is needed to connect to the website, the username for the proxy server.
- -InternetProxyPassword If a proxy server is needed to connect to the website, the password for the proxy account login.
- -InternetTimeout The HTTP timeout setting used when passing information to and from the server.
These settings can also be set by using the sp_changemergepullsubscription system stored procedure in the subscriber’s database. This procedure accepts five parameters.
- @publication The name of the publication to be modified.
- @publisher The name of the SQL Server that hosts the publisher.
- @publisher_db The name of the database from which the publication is being published.
- @property The name of the property to be modified.
- @value The new value of the property being modified.
The @property values are similar to the names of the parameters:
- internet_login The username that should be used to authenticate against the website when basic web authentication is being used.
- internet_password The password that goes with the username specified in the -InternetLogin parameter when basic web authentication is being used.
- internet_security_mode Specifies if basic or Windows authentication should be used. A value of 0 specifies basic authentication and a value of 1 specifies Windows authentication.
- internet_timeout The HTTP timeout setting used when passing information to and from the server.
- internet_url The fully qualified path to the website on which the replisapi.dll is configured.
- user_web_sync Specifies that the distribution agent should be connecting to a website for data replication.
SQL Server merge replication using a website for distribution makes configuring distribution to remote computers much easier to set up and manage in a secure environment. Typically, when laptops are deployed with SQL Databases being replicated to them, they need to be able to connect from anywhere, and that usually requires that the SQL Server serving as the distributor be available from the public Internet. That means anyone could attempt an attack on the database server. By running the replication through IIS, the database instance is no longer directly available on the Internet, making for a much more secure installation.