SQL Server 2005 Express Edition – Part 18 – Merge Web Synchronization Setup

In the previous
installment
of our series presenting an overview of SQL Server 2005 Express
Edition characteristics, we have described a scenario that can be used to
utilize its limited replication capabilities. The underlying concept, called
Web Synchronization, facilitates merge replication, in which remote subscribers
exchange data with a publisher via HTTPS protocol (allowing implementations
across potentially unreliable or insecure TCP/IP networks). Our sample
configuration involved communication between clients running SQL Server 2005
Express Edition and a central instance of SQL Server 2005 Enterprise Edition,
with a Web server hosting IIS component serving as an intermediary,
authenticating client connections as well as decrypting incoming messages,
translating them from XML to binary format, and forwarding to the publisher
(and reversing these actions when relaying updates back to subscribers). In
order to demonstrate this process in the most concise manner, we have made a
few simplifying assumptions, which, depending on circumstances, might not
apply. For the sake of the completeness, we will review additional, more
complex configuration settings.

As we have pointed out earlier, the ability to secure HTTP traffic between
remote clients and an IIS server relies on a digital certificate associated
with a target Web site. To obtain it, it is necessary to create a request that is
submitted to a Certificate Authority. Once its processing is successfully
completed, the resulting certificate is imported into the Web site (note that subscribers
need to trust the Certificate Authority in order to accept certificates it
issued, which can be accomplished by installing its own certificate in the
Trusted Root Certificate Authority node of their local certificate stores).
There are three basic methods of implementing this procedure:

  • submit an online request to a Certificate Authority using
    "Send the request immediately to an online certificate authority"
    option of IIS Certificate Wizard (invoked via the Server Certificate… command
    button on the Directory Security tab of the target Web Site Properties dialog
    box). Providing that the CA is accessible and configured (via its policy
    module) to automatically issue a certificate, its installation will be
    performed during the final stage of the wizard. This is the simplest way of
    handling this task and the one that we have described previously.
  • submit an offline request to a Certificate Authority using the
    "Prepare the request now, but send it later" option of the IIS
    Certificate Wizard. While you will be prompted to provide the same information
    as in the first case (with the online approach), your settings will be stored
    in a certificate file (saved to an arbitrary location specified on the
    Certificate Request File Name page), which you can submit to a CA once that
    becomes available.
  • In the case of a Windows-based Public Key
    Infrastructure, you can use the Certificate Services Web site (accessible
    typically via http://CAServerFQDN/certsrv,
    where CAServerFQDN
    designates the fully qualified domain name of the computer hosting Certificate
    Authority) for this purpose. On its initial page, select the "Request a
    certificate" link, followed by "advanced certificate request".
    Next, click on "Submit a certificate request by using a base-64-encoded
    CMC or PKCS #10 file, or submit a renewal request by using a base-64-encoded
    PKCS #7 file" entry. The "Browse for a file to insert" option
    appearing in the Saved Request section will allow you to point to the request
    file you created earlier (alternatively, you can open it in Notepad and paste
    its entire content directly into the textbox on the page). In the Certificate
    Template section, select the Web Server entry and click on the Submit command
    button. Assuming that the CA policy module is configured for immediate
    processing, you will be able to download the resulting certificate in either
    DER encoded or Base 64 encoded format once you reach the next Web page. (Otherwise,
    you will need to connect to the Certificate Authority via the management
    console and accept the appropriate entry in the Pending Requests folder in
    order to issue the certificate). Pick the desired format and specify the
    download location, saving the file with an arbitrary name (or certnew.cer
    default).

    Return to the Directory Security tab on the Web Site
    Properties dialog box and click on the Server Certificate command button. This
    will invoke the IIS Certificate Wizard, prompting you whether to delete the
    pending request or process it and install the certificate. Choose the latter
    and provide ther location of the certificate file. Next, specify the SSL port
    that the site will be using, review and verify settings on the Certificate
    Summary page, and click on the Finish command button to assign them.

  • submit a request to Certificate Authority without using the Web
    Certificate Wizard. This can be done by employing the Certificate Services Web
    site-based process. Start by connecting to http://CAServerFQDN/certsrv,
    choose the link labeled "Request a certificate" on the initial page,
    followed by "advanced certificate request" and "Create and
    submit a request to this CA". This will trigger a display of the Advanced
    Certificate Request form. Pick the Web Server entry in the Certificate Template
    listbox and populate the Identifying Information, Key Options, and Additional
    Options sections with the appropriate values (matching the ones you entered via
    the IIS Certificate Wizard). Once you have completed this step, click on the
    Submit command button to deliver your request to the Certificate Authority. Assuming
    that the CA policy module allows for automatic processing, you will be able to
    install the corresponding certificate in the local certificate store (otherwise
    you will have to approve the request via the Certificate Authority management
    console and download it manually), and import it afterwards into the target Web
    site using the "Assign an existing certificate" option in the IIS
    Certificate Wizard.

Another factor that you should keep in mind when planning implementation of
Web Synchronization for merge subscriptions is the location and mode of
operation of your remote clients. In our initial demonstration, we have
conveniently assumed that subscriptions already have been set up prior to
running REPLMERG.EXE for the first time (which implies that our subscriber had
direct access to the publisher), however you might have to deal with scenarios,
in which this is not the case. While creating a subscription in the manner we
described earlier (via New Subscription Wizard from SQL Server Management
Studio Express interface) is not possible without direct RPC-based connectivity
between replication partners (which applies to remote, Internet-based clients),
there are alternative methods of accomplishing this goal. One of them (which we
will describe here) involves sp_addmergesubscription and sp_addmergepullsubscription
replication stored procedures (on the publisher and subscribers, respectively).
Providing that the merge publication based on the SalesTaxRate(Sales) table in
AdventureWorks database already exists on the distributor/publisher located on
the default SQL Server 2005 Enterprise Edition instance hosted on the computer
ALPHA, and that our SQL Server 2005 Express Edition-based subscription database
called AdventureWorksRepl resides on computer OMEGA, you would start by
executing the following on ALPHA (which generates a pull subscription entry):

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @hostname AS sysname;
SET @publication = N'SalesTaxRate(Sales)';
SET @subscriber = N'OMEGASQLEXPRESS';
SET @subscriptionDB = N'AdventureWorksRepl'; 
SET @hostname = N'OMEGA'
USE [AdventureWorks]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'pull',
  @hostname = @hostname;

Keep in mind that, as before, you need to perform additional steps to enable
Web synchronization of this publication, including successfully completing the
Web Synchronization wizard resulting in the creation of its virtual directory,
as well as adding a URL pointing to the replisapi.dll in the FTP Snapshot and
Internet section of its Properties dialog box (for details regarding this
procedure, refer to our
previous article
). Next, apply sp_addmergepullsubscription stored procedure
to the subscriber in order to create the corresponding entry in its Local
Subscriptions folder by running the T-SQL code listed below:

DECLARE @publisher AS sysname;
DECLARE @publication AS sysname;
DECLARE @publisher_db AS sysname;
SET @publisher = N'ALPHA';
SET @publication = N'SalesTaxRate(Sales)';
SET @publisher_db = N'AdventureWorks';
USE [AdventureWorksRepl]
EXEC sp_addmergepullsubscription 
 @publisher = @publisher, 
 @publication = @publication, 
 @publisher_db = @publisher_db;

At this point, you should be able to trigger synchronization using the
REPLMERG.EXE utility (residing in the Program
FilesMicrosoft SQL Server90COM
folder) in the same manner as
described earlier.
Note, however, that the syntax we used leverages Basic authentication to
validate credentials of the user connecting to the Web Server (which, in turn,
are relayed to the SQL Server 2005-based distributor/publisher). In the next
installment of our series, we will describe other authentication mechanisms
that can be employed in this arrangement.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles