Prior to SQL 2005, if you wanted to replicate over the Internet your network administrator had to configure a VPN or open the firewall ports 1433 (the ip port that SQL Server runs on by default) and 21 (the ip port that TP runs on) for inbound communication.

VPNs are not popular because:

• They tend to be slow.
• They are difficult to set up and maintain in a highly secure manner.

Most network administrators are averse to opening up port 1433 and 21 because doing so will expose your SQL Server to the Internet and will be discovered by hackers running port scanners.

• If you are not using anonymous authentication, your account and password will travel the ether plain text.
• If you are using anonymous authentication, anyone can access your ftp server and download its contents.

Fortunately, Microsoft SQL 2005 provides a secure way to do merge replication synchronizations over the Internet. Web synchronization is secure because:

• Only port 443 is open for inbound communication at the firewall. Port 443 is the port on which secure web server communication (https) is done, and most network administrators open this port. All communications traveling over port 443 are encrypted, including account names, passwords, and all synchronization data.
• Merge replication subscribers connect through a web server to synchronize with SQL Server—SQL Server is not exposed on the Internet at all.

Web synchronization is available only on SQL Server 2005 Enterprise Edition, running on a Windows 200x Enterprise Edition server running IIS. Although it is possible to configure web synchronization on SQL Server 2005 Developer Edition, running on Windows XP Professional and above for testing purposes, it is licensed only for testing.
DBAs and SQL developers will be familiar with web synchronization if they have worked with replicating to Pocket PCs running SQL CE. In fact, Microsoft has taken web synchronization from SQL CE and made it secure by restricting it to communication only over https. It is extended not only to SQL CE but also to SQL Mobile, SQL Express, and all variants of SQL 2005.

Figure 1 Typical topology for a web synchronization environment

Now that you have a good idea of what the topology looks like, let’s go through the steps required to configure it.

The first step is to configure your web server for web synchronization. To do this, you have to obtain a certificate. You can generate one yourself by using Certificate Services, which ships with Windows 200x Enterprise Edition, or you can purchase a certificate from a certificate provider such as Thwarte, Verisign, and so on.

Configuring Your SQL Server Publisher for Web Synchronization

To configure your SQL Server Publisher for web synchronization, connect to SQL Server Publisher using SQL Server Management Studio (SSMS), expand the Replication folder, expand the Local Publications folder, and right-click on the merge publication that you want to enable for web synchronization.

You will see the Welcome to Configure Web Synchronization Wizard splash screen. Click Next.

In the Subscriber Type dialog box, select the type of subscribers you will have. The two choices are SQL Server 2005 subscribers (Enterprise, Standard, Workgroup, Developer, and Express) and SQL Server 2005 Mobile clients. Make your selection and click Next.

You will be prompted to enter the name of the web server that will host the web synchronization process. For security reasons, this server should be different from your SQL Server Publisher. Select Create A New Virtual Directory or Configure An Existing Virtual Directory. After selecting your web server, you can select existing virtual directories or choose the web site in which you want to create your new virtual directory.

NOTE: A virtual directory is a path that is accessible through your browser (https://http://www.MyServerName.com/MyVirtualDirectory). It is called a virtual directory because the virtual name can be different from the physical directory to which it maps (MyVirtualDirectory could map to d:\ReplicationFolder) and it can be in a different path from the root of your web server (by default, c:\inetpub\wwwroot).

If you select a new virtual directory, you are prompted to create a new physical folder (if it does not already exist). You are prompted to accept the copying of an ISAPI extension that will process your web synchronization requests. Accept Yes for both of these prompts.

You then see a dialog box for Authentication Access. Microsoft recommends that you use Basic Authentication. For the domain, enter the name of the domain as it appears on the certificate (MyDomain.com); for the realm, enter the name of your fully qualified domain name as it appears on your certificate (Server.MyDomain.com). You can connect to your web server, right click on your web site, select Properties and Directory Security, and click View Certificate if you have forgotten these names.

After clicking Next, you see the Directory Access dialog box. Select an account or a group here that will be used to connect to your snapshot share.

NOTE: For management purposes, you should use a group instead of an individual account because you will have to set permissions only once.

This group should have rights to access the snapshot share which could be on the publisher, an ftp server, or a file server. By default, the snapshot share will have everyone’s read permission. When you share the snapshot share, ensure that the group that you will be using to access the snapshot share has read and list folder contents permissions. If you look at the dialog box closely, you will notice that it has a default check box: Grant The Above Users Permission To Access The UNC Snapshot Share. If you are using an FTP server, ensure that you are using anonymous authentication, or the account you are using has rights to read and list folder contents permissions. Click on the Add button in this dialog box and browse to locate the group.

Click on Next to advance to the Snapshot Share Access dialog box. Enter the name of the share your snapshot share is on as a UNC: \\MyServerName\ShareName. The share must preexist. Click on Next. If you have not already configured a publication to use this share as its snapshot folder, you will get a prompt telling you that the share is empty. Ensure that this is the share that you want to use and click on Next to continue to the Complete The Wizard dialog box. Evaluate your choices and click on Finish.

After the Web Configuration Wizard completes, you will get a success/failure report that will enable you to determine which component failed and rerun the wizard to correct portions that failed.

Configuring Your Publication for Web Synchronization

Whew! After all these steps you will be happy to know that configuring your publication for web synchronization involves two steps. Connect to your SQL Server using SSMS, expand the Replication folder, expand Local Publication, right-click on your merge publication and select Properties. Click on the FTP Snapshot and Internet tab, and check Allow Subscribers To Synchronize By Connecting To A Web Server. At the very bottom of the dialog box, enter the path to your Web Server and its virtual directory. The path should look like this: https://MyServerName.MyDomainName.com/MyVirtualDirectory

Figure 2 Publication Properties dialog box, illustrating the FTP Snapshot and Internet tab

Make sure that you test your publication setup by navigating in IE to the path:
Your web server will return a status page that will be helpful for diagnosing any configuration errors.

Configure Your Local Subscribers for Web Synchronization

If you can register your subscribers and publisher in SSMS, all you have to do is create a new pull subscription on the subscriber. Connect to the subscriber, expand the Replication folder, right-click on the Local Subscriptions folder, and select New Subscription.
You will see the New Subscription Wizard splash screen. Click Next. In the Publisher drop-down box, select your publisher, expand your publication database, select your publication, and click Next.
If your publisher does not appear here, select the Find SQL Server Publisher in the drop-down box and try to connect to it. If your publisher is not listed, add it via an alias using SQL Server Configuration Manager (you can find this program by clicking Start, pointing to All Programs, Microsoft SQL Server 2005, and Configuration Tools). Expand SQL Server Native Client Configuration and right-click on the alias node to create a new alias to the publisher—use TCP/IP and use the server name of the publisher as the alias name. You can determine the server name of the publisher by issuing a select @@server name on the publisher. For the server, you can enter the IP address. There is a good chance that if your subscriber is separated from the publisher by a different domain or by a highly restrictive firewall, LAN, or WAN, you will need to follow the instructions in the section "Configure Your Remote Subscribers for Web Synchronization."
In the Merge Agent Location dialog box, select Run Each Agent At Its Subscriber (Pull Subscriptions) and click Next. In the Subscribers dialog box, select your subscriber and the database you want the subscription to be pulled to. You have the option to select multiple subscribers here, each with a different subscription database. Click Next.
In the Merge Agent security dialog box you can select the accounts that will be used to connect to the publisher and subscriber. There are two enabled options:

• Specify The Domain Or Machine Account Under Which The Merge Agent Process Will Run When Synchronizing This Subscription
• Connect To The Subscriber

The first option (the publisher account) must be in the PAL, have security to read and list folder contents on the snapshot share, and be in the db owner in the distribution database.

It is the account that is used to connect and authenticate to the web server and then connect to the database and synchronize. This should be a publisher domain or local machine account and entered in the dialog box as DomainName\AccountName.
The subscriber account should also be a subscriber domain or local machine account that is in the db owner in the subscription database.

Figure 3 Enabling your subscription for Web synchronization

Click Next. In the for the Subscription Type dialog box, select Client in the Subscription Type drop-down box.
In the Wizard Actions dialog box, accept the defaults and click Next. Review the options selected in the Complete The Wizard dialog box and click Finish to implement them. Your subscription will be built.

Configure your Remote Subscribers for Web Synchronization

If your subscriber is in a different domain or if there is a firewall between your publisher and subscriber, you cannot register the publisher in SSMS, so you cannot pull the subscription using the wizard. The only ways to pull your subscription is through SQL RMO, ActiveX controls, or replication stored procedures. Script 1a demonstrates pulling a subscription using SQL RMO using VB.Net, Script 1b demonstrates pulling a subscription using SQL RMO using C Sharp, Script 2a demonstrates using ActiveX controls using VB.Net, Script 2b demonstrates using ActiveX controls using C Sharp, and Script 3 demonstrates how to pull a subscription using replication stored procedures.

Summary

This completes the tutorial on how to use web synchronization. The most complex part of the process is configuring your certificate and the correct security. Web synchronization provides a high performance, and a  highly secure mechanism to replicate over the Internet.