How to: Create a Push Subscription (Replication Transact-SQL Programming)

Article
12/03/2008

Push subscriptions can be created programmatically using replication stored procedures. The stored procedures used will depend on the type of publication to which the subscription belongs.

Security Note:
When possible, prompt users to enter security credentials at run time. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

To create a push subscription to a snapshot or transactional publication

At the Publisher on the publication database, verify that the publication supports push subscriptions by executing sp_helppublication.
- If the value of allow_push is 1, push subscriptions are supported.
- If the value of allow_push is 0, execute sp_changepublication, specifying allow_push for @property and true for @value.
At the Publisher on the publication database, execute sp_addsubscription. Specify @publication, @subscriber and @destination_db. Specify a value of push for @subscription_type. For information about how to update subscriptions, see How to: Create an Updatable Subscription to a Transactional Publication (Replication Transact-SQL Programming).

At the Publisher on the publication database, execute sp_addpushsubscription_agent. Specify the following:

The @subscriber, @subscriber_db, and @publication parameters.
The Microsoft Windows credentials under which the Distribution Agent at the Distributor runs for @job_login and @job_password.

Note

Connections made using Windows Integrated Authentication always use the Windows credentials specified by @job_login and @job_password. The Distribution Agent always makes the local connection to the Distributor using Windows Integrated Authentication. By default, the agent will connect to the Subscriber using Windows Integrated Authentication.
(Optional) A value of 0 for @subscriber_security_mode and the Microsoft SQL Server login information for @subscriber_login and @subscriber_password. Specify these parameters if you need to use SQL Server Authentication when connecting to the Subscriber.
A schedule for the Distribution Agent job for this subscription. For more information, see How to: Specify Synchronization Schedules (Replication Transact-SQL Programming).

Security Note:
When creating a push subscription at a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

To create a push subscription to a merge publication

At the Publisher on the publication database, verify that the publication supports push subscriptions by executing sp_helpmergepublication.
- If the value of allow_push is 1, the publication supports push subscriptions.
- If the value of allow_push is not 1, execute sp_changemergepublication, specifying allow_push for @property and true for @value.
At the Publisher on the publication database, execute sp_addmergesubscription, specifying the following parameters:
- @publication. This is the name of the publication.
- @subscriber_type. For a client subscription, specify local and for a server subscription, specify global.
- @subscription_priority. For a server subscription, specify a priority for the subscription (0.00 to 99.99).
  For more information, see Advanced Merge Replication Conflict Detection and Resolution.

At the Publisher on the publication database, execute sp_addmergepushsubscription_agent. Specify the following:

The @subscriber, @subscriber_db, and @publication parameters.
The Windows credentials under which the Merge Agent at the Distributor runs for @job_login and @job_password.

Note

Connections made using Windows Integrated Authentication always use the Windows credentials specified by @job_login and @job_password. The Merge Agent always makes the local connection to the Distributor using Windows Integrated Authentication. By default, the agent will connect to the Subscriber using Windows Integrated Authentication.
(Optional) A value of 0 for @subscriber_security_mode and the SQL Server login information for @subscriber_login and @subscriber_password. Specify these parameters if you need to use SQL Server Authentication when connecting to the Subscriber.
(Optional) A value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. Specify these values if you need to use SQL Server Authentication when connecting to the Publisher.
A schedule for the Merge Agent job for this subscription. For more information, see How to: Specify Synchronization Schedules (Replication Transact-SQL Programming).

Security Note:
When creating a push subscription at a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

Example

The following example creates a push subscription to a transactional publication. Login and password values are supplied at run time by using sqlcmd scripting variables.

-- 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".

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorksReplica';

--Add a push subscription to a transactional publication.
USE [AdventureWorks]
EXEC sp_addsubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @destination_db = @subscriptionDB, 
  @subscription_type = N'push';

--Add an agent job to synchronize the push subscription.
EXEC sp_addpushsubscription_agent 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @job_login = $(Login), 
  @job_password = $(Password);
GO

The following example creates a push subscription to a merge publication. Login and password values are supplied at run time by using sqlcmd scripting variables.

-- 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".

DECLARE @publication AS sysname;
DECLARE @subscriber AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @hostname AS sysname;
SET @publication = N'AdvWorksSalesOrdersMerge';
SET @subscriber = $(SubServer);
SET @subscriptionDB = N'AdventureWorksReplica'; 
SET @hostname = N'adventure-works\david8'

-- Add a push subscription to a merge publication.
USE [AdventureWorks]
EXEC sp_addmergesubscription 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @subscription_type = N'push',
  @hostname = @hostname;

--Add an agent job to synchronize the push subscription.
EXEC sp_addmergepushsubscription_agent 
  @publication = @publication, 
  @subscriber = @subscriber, 
  @subscriber_db = @subscriptionDB, 
  @job_login = $(Login), 
  @job_password = $(Password);
GO

Share via

How to: Create a Push Subscription (Replication Transact-SQL Programming)

To create a push subscription to a snapshot or transactional publication

To create a push subscription to a merge publication

Example

See Also

Tasks

Other Resources

Help and Information

Additional resources