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