How to: Configure a Subscription to Use Web Synchronization (Replication Transact-SQL Programming)

The procedure in this topic is the third step in configuring Web synchronization for merge replication. You perform this step after you enable the publication and configure the computer that is running Microsoft Internet Information Services (IIS). For an overview of the configuration process, see How to: Configure Web Synchronization for Merge Replication (Replication Transact-SQL Programming). When you configure a subscription to use Web synchronization for Subscribers that can only connect to the Publisher through HTTP, you must properly configure the Publication. For more information, see How to: Configure a Publication to Allow for Web Synchronization (Replication Transact-SQL Programming). After you complete the procedure in this topic, synchronize the subscription you have created. For more information, see How to: Synchronize a Pull Subscription (Replication Programming).

This topic describes the parameters that are required by Web synchronization. For more information about how to create pull subscriptions, see How to: Create a Pull Subscription (Replication Transact-SQL Programming).

Important

The URL of the Web server that is used for Web synchronization (such as https://server.domain.com/directory/replisapi.dll) specifies the location of replisapi.dll. If the server is configured to use a port other than the default port 443 for Secure Sockets Layer (SSL), you must also supply the port as: https://server.domain.com:PortNumber/directory/replisapi.dll. The name of the server in the URL must be the same as the name that was used when the certificate was created. For example, on an intranet, you might be able to access a Web server through https://server/. However, if the fully qualified name (such as https://server.domain.com/) was used when the certificate was created, you must use this fully qualified name in the Web service URL.

To configure a subscription to use Web synchronization

  1. At the Publisher, execute sp_addmergesubscription. Specify values for @publication, @subscriber, @subscriber_db, and a value of pull for @subscription_type. This registers the pull subscription at the Publisher.

  2. At the Subscriber, to create the pull subscription, execute sp_addmergepullsubscription, specifying values for @publication, @publisher, and @publisher_db.

  3. At the Subscriber, execute sp_addmergepullsubscription_agent, specifying values for @publisher, @publisher_db, @publication, a value of 1 for @use_web_sync, and values for following parameters:

    • @internet_url is the location of replisapi.dll.

    • @internet_security_mode is the security mode that the Merge Agent will use when making connections from the Subscriber to the computer that is running IIS. A value of 0 specifies that Basic Authentication is used; a value of 1 (default) specifies that Windows Integrated Authentication is used.

    • @internet_login is the login that the Merge Agent will use when making connections from the Subscriber to the computer that is running IIS by using Basic Authentication.

    • @internet_password is the password that the Merge Agent will use when making connections from the Subscriber to the computer that is running IIS by using Basic Authentication.

    Note

    To synchronize a subscription by using Web synchronization, both the upload and download phases must be enabled.

To configure a subscription to use Web synchronization for Subscribers that can only connect to the Publisher through a Web server using HTTP

  1. At the Subscriber, to create the pull subscription, execute sp_addmergepullsubscription, specifying a value of anonymous for @subscriber_type and values for @publication, @publisher, and @publisher_db.

  2. At the Subscriber, execute sp_addmergepullsubscription_agent, specifying values for @publisher, @publisher_db, @publication, a value of 1 for @use_web_sync, and values for following parameters:

    • @internet_url is the location of replisapi.dll.

    • @internet_security_mode is the security mode that the Merge Agent will use when making connections from the Subscriber to the computer that is running IIS. A value of 0 specifies that Basic Authentication is used; a value of 1 (default) specifies that Windows Integrated Authentication is used.

    • @internet_login is the login that the Merge Agent will use when making connections from the Subscriber to the computer that is running IIS by using Basic Authentication.

    • @internet_password is the password that the Merge Agent will use when making connections from the Subscriber to the computer that is running IIS by using Basic Authentication.

    Note

    To synchronize a subscription by using Web synchronization, both the upload and download phases must be enabled.

Example

The following example creates a subscription that is synchronized with the Publisher by using Web synchronization.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Execute this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @websyncurl AS sysname;
DECLARE @security_mode AS int;
DECLARE @login AS sysname;
DECLARE @password AS nvarchar(512);
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks2008R2';
SET @websyncurl = 'https://' + $(WebServer) + '/WebSync';
SET @security_mode = 0; -- Basic Authentication for IIS 
SET @login = $(Login);
SET @password = $(Password);

-- At the subscription database, create a pull subscription 
-- to a merge publication.
USE [AdventureWorks2008R2Replica]
EXEC sp_addmergepullsubscription 
    @publisher = @publisher, 
    @publication = @publication, 
    @publisher_db = @publicationDB;

-- Add an agent job to synchronize the pull subscription. 
EXEC sp_addmergepullsubscription_agent 
    @publisher = @publisher, 
    @publisher_db = @publicationDB, 
    @publication = @publication, 
    @distributor = @publisher, 
    @job_login = @login, 
    @job_password = @password,
    @use_web_sync = 1,
    @internet_security_mode = @security_mode,
    @internet_url = @websyncurl,
    @internet_login = @login,
    @internet_password = @password;
GO

USE [AdventureWorks2008R2]
GO

-- Execute this batch at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'AdvWorksSalesOrdersMergeWebSync';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorks2008R2Replica';

-- At the Publisher, register the subscription, using the defaults.
EXEC sp_addmergesubscription 
    @publication = @publication, 
    @subscriber = @subscriber, 
    @subscriber_db = @subscriptionDB, 
    @subscription_type = N'pull';
GO

The following example creates a subscription that is synchronized with the Publisher by using Web synchronization for a Subscriber that can only connect to the Publisher through a Web server by using HTTP.

-- This script uses sqlcmd scripting variables. They are in the form
-- $(MyVariable). For information about how to use scripting variables  
-- on the command line and in SQL Server Management Studio, see the 
-- "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".

-- Publication must support anonymous Subscribers.
-- Execute this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @websyncurl AS sysname;
DECLARE @security_mode AS int;
DECLARE @login AS sysname;
DECLARE @password AS nvarchar(512);
SET @publication = N'AdvWorksSalesOrdersMergeWebSync';
SET @publisher = $(PubServer);
SET @publicationDB = N'AdventureWorks2008R2';
SET @websyncurl = 'https://' + $(WebServer) + '/WebSync';
SET @security_mode = 0; -- Basic Authentication for IIS
SET @login = $(Login);
SET @password = $(Password);

-- At the subscription database, create a pull subscription 
-- to a merge publication.
USE [AdventureWorks2008R2Replica]
EXEC sp_addmergepullsubscription 
    @publisher = @publisher, 
    @publication = @publication, 
    @publisher_db = @publicationDB,
    @subscriber_type = N'anonymous';

-- Add an agent job to synchronize the pull subscription. 
EXEC sp_addmergepullsubscription_agent 
    @publisher = @publisher, 
    @publisher_db = @publicationDB, 
    @publication = @publication, 
    @distributor = @publisher, 
    @job_login = @login, 
    @job_password = @password,
    @use_web_sync = 1,
    @internet_security_mode = @security_mode,
    @internet_url = @websyncurl,
    @internet_login = @login,
    @internet_password = @password;
GO