SQL Server 2005 Express Edition - Part 18 - Merge Web Synchronization SetupMarch 11, 2008 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:
In the case of a Windows-based Public Key
Infrastructure, you can use the Certificate Services Web site (accessible
typically via 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. 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'OMEGA\SQLEXPRESS'; 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 |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
