SQL 2005 Web Synchronization for Merge Replication
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.
Using FTP is inherently insecure because:
- 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 illustrates what a web synchronization topology might look like.
)
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.
Configuring your Web Server for Web Synchronization
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.
To install the certificate on your web server, open up the Internet Information Services MMC, expand IIS, expand the node for your server/workstation, locate the web site that will host your web synchronization process, right-click on it, select Properties, and click the Directory Security tab. Click the Server Certificate button to install the certificate. After this is complete, open up your browser and enter https://localhost/ to verify that SSL works. Also try the fully qualified domain name that your subscribers will be using to connect to your web server for web synchronization. You should get a prompt to accept your certificate if SSL is installed correctly.