SQL Server 2005 Express Edition - Part 17 - Merge Web Synchronization

February 25, 2008

As part of our series dedicated to SQL Server 2005 Express Edition, we recently have focused on its replication characteristics. As we have explained, its limitations in this area (such as agentless nature or inability to function in a role other than the subscriber) necessitated development of new mechanisms facilitating its participation in snapshot, transactional, or merge replication. So far, we have presented a couple of examples illustrating this paradigm. The first one involved use of Windows Synchronization Manager, which offers a friendly graphical interface for managing individual subscriptions. The second leveraged replmerg.exe and distrib.exe command line utilities, that can be employed to invoke a replication process (for merge and transactional subscriptions, respectively), instead of relying on SQL Server Agent components (missing from SQL Server 2005 Express Edition). In this article, we will explore another variation of this mechanism, called Web Synchronization, with SQL Server 2005 Express Edition instances operating as merge replication subscribers connecting to a publisher via HTTPS protocol.

In previous versions of SQL Server, setting up replication that traversed non-secure networks typically required either opening extra firewall ports (with potential vulnerability implications) or resorting to VPN-based solutions (with their deployment, maintenance, and bandwidth overhead). This situation has changed with introduction of Web Synchronization functionality in SQL Server 2005. Its primary goal is to allow merge replication with pull subscriptions via HTTPS protocol, considered as the de facto standard for secure communication over the Internet (with a fair share of firewalls having its default TCP port 443 open). Its typical implementation consists of a publisher (an instance of SQL Server 2005 Enterprise Edition), remote subscribes running any of SQL Server 2005 products (including SQL Server 2005 Express or Mobile Edition), and a Web server hosting IIS and SQL Server replication components (serving as an intermediary, responsible for accepting pull subscription requests and translating binary-format updates originating on the publisher into XML-formatted data relayed to subscribers and reversing that process for changes made by subscribers, which need to be merged back at the publisher). Furthermore, since the communication with subscribers must be encrypted, you might benefit from having an internal Certificate Authority infrastructure (although you can also use third party certificates).

Let's review the process of setting up such an environment (note that for the sake clarity, we will make several simplifying assumptions to present the main concept and its most essential characteristics, leaving discussion on more advanced topics till the next article). In our sample demonstration, the publisher will be running on a Windows Server 2003 domain-based system. The same domain will also contain an IIS Web Server (while it is possible to have this system operate in the stand-alone mode, such arrangement introduces additional caveats in regard to authentication and certificate installation) with the SQL Server replication components and direct access to Enterprise Certification Authority hierarchy. Finally, our subscriber, hosted on a Windows XP Professional computer with SQL Server 2005 Express Edition installed, will have (initially) direct connectivity to the publisher (which will permit us to create a subscription directly from the SQL Server Management Studio Express interface).

To start, designate a target Web site on your IIS server. We will use the default one for this purpose. (Your can obviously deviate from this approach by creating a custom site, as long as its IP address, port number, or host header is different from other sites located on the same server). Locate it in the IIS Manager console, display its Properties dialog box (via its context sensitive menu), and switch to the Directory Security tab. Click on the Server Certificate command button in the Secure communications section, which will trigger the Web Server Certificate Wizard. After the initial informational page, you will be presented with several choices, allowing you to Create a new certificate (which will be our choice), Assign an existing certificate, Import a certificate from a Key Manager backup file, Import a certificate from a .pfx file, as well as Copy or Move a certificate from a remote server site to this site. After selecting the first option button, you will next need to decide whether you want to prepare the request right away but submit it at a later time or send the request immediately to an online certification authority. If your Certificate Authority server is accessible directly from the local computer, pick the latter (it is simpler) otherwise use the former. In either case, you will be asked to provide a descriptive name of the certificate (so you can easily identify it later), bit length of the encryption key (the longer ones increase security but have detrimental impact on performance), as well as organization and organizational unit information (corresponding typically to the legal name of the organization and department, which are considered owners of the site). You also have to specify a common name for the site, which should match the Internet URL that subscribers will be using when initiating replication with the publisher. In addition, the wizard requests geographical information, including country/region, state/province, and city/locality (which are embedded into the certificate). When prompted for SSL port, use the default of 443 (standard practice) or assign a custom port (but keep in mind that this will need to be taken into consideration when setting up client connections). If your intention is to submit your request directly to a Certification Authority, select its entry on the "Choose a Certification Authority" page. After you confirm your choices and click on Next, the wizard will complete and install the certificate (which you can verify afterwards by clicking on View Certificate command button on the Directory Security tab of the Web Site Properties dialog box). To confirm successful outcome of this procedure, launch Internet Explorer on one of your client computers and point to target URL with the https:// prefix.

In order for the IIS server to facilitate interaction between the publisher and its subscribers, it also must contain SQL Server Connectivity Components (included with SQL Server 2005 source files). To accomplish this, launch the SQL Server 2005 Setup Wizard (from the product CD), select Connectivity Components (a subnode of the Client Components), assign the "Entire feature will be installed on local hard drive" setting to it, and proceed with the installation.

At this point, we are ready to enable Web Synchronization functionality on the publisher. Launch SQL Server Management Studio and connect its Object Explorer interface to the database engine. Drill down to the Local Publications subnode under the Replication node, locate the merge publication whose creation we described in our previous article, and activate the Configure Web Synchronization... item from its context sensitive menu, resulting in the invocation of the Configure Web Synchronization Wizard. Its first configuration page will prompt you to pick the subscriber type - giving you the choice between SQL Server Mobile Edition and SQL Server. After selecting the latter and clicking on the Next command button, you will be able to point the Web Server (by specifying its name) and Web Site that will be hosting the target virtual directory. If you decide to have a dedicated virtual directory (rather than use an existing one), select the "Create a new virtual directory" option, provide its alias (we will go with Websync) and a local file system path of the parent folder. In either case, the wizard will automatically copy to the location you specified the SQL Server Replication WebSync ISAPI DLL (replisapi.dll) from the Program Files\Microsoft SQL Server\90\COM folder on the SQL Server 2005 Enterprise Edition computer. You will also need to choose the authentication method you want to apply when verifying client credentials. The possibilities include integrated Windows authentication, Digest authentication for Windows domain servers, or Basic authentication (for which you can assign a default domain, corresponding to the Windows Active Directory domain where remote users’ accounts reside, and realm, matching the server certificate name that, in our case, is the same as the fully qualified domain name of our IIS server). Even though the last of these choices means that users will need to type in their username and password when synchronizing subscriptions, potential exposure is limited due to SSL encryption employed by the HTTPS protocol. (This is the option we will apply in our sample demo, but we will explore the remaining two in the next article). Finally, the wizard will prompt you for the list of groups and users that will be granted required access to the virtual directory and to the snapshot share (which translates into NTFS Read & Write permissions to the physical path, Read & Execute permissions to the replisapi.dll in the virtual directory, NTFS Read permissions to the snapshot directory, and Read share permissions to the snapshot share). Make sure that the account you will be using for synchronization is a member of this group and is included in the Publication Access List (accessible from the Publication Properties dialog box in the Object Explorer). Note also that the wizard insists on connecting to the remote Web site via the administrative share corresponding to the drive where the target folder (represented by the virtual directory) resides, which implies that, in order to complete this step successfully, you must have local Administrator's privileges on the Web server.

You also have to enable the Web synchronization option for the publication. This is done via the FTP Snapshot and Internet section of its Properties window (which you invoke using the publication's context sensitive menu in the Object Explorer interface of SQL Server Management Studio), marking the "Allow Subscribers to synchronize by connecting to a Web server" checkbox, and specifying the Internet URL of WebSync ISAPI DLL we have just configured (in the format https://WebSite/WebSync/replisapi.dll), where WebSite and WebSync are the FQDN of target Web site and the name of its virtual directory, respectively) in the textbox labeled "Address of Web Server to which Subscribers should connect".

Subscribers will be able to access the publication by pointing to the newly created virtual directory and its WebSync ISAPI DLL as the target Internet URL in the format https://WebSite/WebSync/replisapi.dll (note that you might have to install the certificate on the client prior to connecting for the first time). To implement the synchronization process, we will leverage the replmerg.exe utility discussed in our previous article. While all of its command line switches we used before are applicable here, we will need to also include:

• -InternetURL (pointing to the location of the replisapi.dll),
• -InternetLogin (name of the user connecting to the Web Site via Basic Authentication),
• -InternetPassword (password of that user).

Assuming (as before) that:

• our publisher/distributor is located on the default SQL Server 2005 Enterprise Edition instance on the computer ALPHA,
• our SQL Server 2005 Express Edition-based subscription database called AdventureWorksRepl resides on OMEGA,
• our publication is called ProductDescription(Production),
• our target Internet URL is https://www.databasejournal.com/WebSync/replisapi.dll, and
• credentials used to connect to it via Basic Authentication are SyncU$eR (which is a member of the DatabaseJournal.local Active Directory domain) with SyncP@$$w0rD,

then the following would allow you to trigger synchronization from the Command Prompt. (Note that the entire content below constitutes a single command and should be typed in without any line breaks):

replmerg -Publisher [ALPHA] -PublisherDB [AdventureWorks] -Publication [ProductDescription(Production)] 
 -Distributor [ALPHA] -SubscriptionType 1 -Subscriber [OMEGA\SQLEXPRESS]
 -SubscriberSecurityMode 1 -SubscriberDB [AdventureWorksRepl] 
 -InternetURL [https://www.databasejournal.com/WebSync/replisapi.dll]
 -InternetLogin [DatabaseJournal\SyncU$eR] -InternetPassword [SyncP@$$w0rD]

In our next article of this series, we will explore several scenarios in which the Merge Web Synchronization can be applied (differentiated by such factors as placement and domain membership of a publisher, subscribers, and IIS Server, their respective authentication mechanisms, and source of SSL certificates employed to secure HTTPS communication) and describe their implementation details.

» See All Articles by Columnist Marcin Policht