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,

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.