How to: Enable Updating Subscriptions for Transactional Publications (Replication Transact-SQL Programming)
Note
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
When creating a transactional publication programmatically using replication stored procedures, you can enable either immediate or queued updating subscriptions.
.gif "Security note") Security Note |
|---|
When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access. |
To create a publication that supports immediate updating subscriptions
If necessary, create a Log Reader Agent job for the publication database.
If a Log Reader Agent job already exists for the publication database, proceed to step 2.
If you are unsure whether a Log Reader Agent job exists for a published database, execute sp_helplogreader_agent (Transact-SQL) at the Publisher on the publication database. If the result set is empty, a Log Reader Agent job must be created.
At the publisher, execute sp_addlogreader_agent (Transact-SQL). Specify the Microsoft Windows credentials under which the agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the Microsoft SQL Server login information for @publisher_login and @publisher_password.
Execute sp_addpublication (Transact-SQL), specifying a value of true for the parameter @allow_sync_tran.
At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 2 for @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.
Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).
At the Subscriber, create an updating subscription to this publication. For more information, see How to: Create an Updatable Subscription to a Transactional Publication (Replication Transact-SQL Programming).
To create a publication that supports queued updating subscriptions
If necessary, create a Log Reader Agent job for the publication database.
If a Log Reader Agent job already exists for the publication database, proceed to step 2.
If you are unsure whether a Log Reader Agent job exists for a published database, execute sp_helplogreader_agent (Transact-SQL) at the Publisher on the publication database. If the result set is empty, then a Log Reader Agent job must be created.
At the publisher, execute sp_addlogreader_agent (Transact-SQL). Specify the Windows credentials under which the agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password.
If necessary, create a Queue Reader Agent job for the Distributor.
If a Queue Reader Agent job already exists for the distribution database, proceed to step 3.
If you are unsure whether a Queue Reader Agent job exists for the distribution database, execute sp_helpqreader_agent (Transact-SQL) at the Distributor on the distribution database. If the result set is empty, then a Queue Reader Agent job must be created.
At the Distributor, execute sp_addqreader_agent (Transact-SQL). Specify the Windows credentials under which the agent runs for @job_name and @password. These credentials are used when the Queue Reader Agent connects to the Publisher and Subscriber. For more information, see Replication Agent Security Model.
Execute sp_addpublication (Transact-SQL), specifying a value of true for the parameter @allow_queued_tran and a value of pub wins, sub reinit, or sub wins for @conflict_policy.
At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 3 for @publication and the Windows credentials under which the Snapshot Agent runs for @snapshot_job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.
Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).
At the Subscriber, create an updating subscription to this publication. For more information, see How to: Create an Updatable Subscription to a Transactional Publication (Replication Transact-SQL Programming).
To change the conflict policy for a publication that allows queued updating subscriptions
- At the Publisher on the publication database, execute sp_changepublication (Transact-SQL). Specify a value of conflict_policy for @property and the desired conflict policy mode of pub wins, sub reinit, or sub wins for @value.
Example
This example creates a publication that supported both immediate and queued updating pull subscriptions.
-- To avoid storing the login and password in the script file, the values
-- are passed into SQLCMD as scripting variables. 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".
--Declarations for adding a transactional publication
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks';
SET @publication = N'AdvWorksProductTran';
SET @login = $(Login);
SET @password = $(Password);
USE [AdventureWorks]
-- Enable transactional replication on the publication database.
EXEC sp_replicationdboption
@dbname=@publicationDB,
@optname=N'publish',
@value = N'true';
-- Execute sp_addlogreader_agent to create the agent job.
EXEC sp_addlogreader_agent
@job_login = @login,
@job_password = @password,
-- Explicitly specify the use of Windows Integrated Authentication (default)
-- when connecting to the Publisher.
@publisher_security_mode = 1;
-- Create a transactional publication that supports immediate updating,
-- queued updating, and pull subscriptions.
EXEC sp_addpublication
@publication = @publication,
@status = N'active',
@allow_sync_tran = N'true',
@allow_queued_tran = N'true',
@allow_pull = N'true',
@independent_agent = N'true',
-- Explicitly declare the related default properties
@conflict_policy = N'pub wins';
-- Create a new snapshot job for the publication, using a default schedule.
EXEC sp_addpublication_snapshot
@publication = @publication,
@job_login = @login,
@job_password = @password,
-- Explicitly specify the use of Windows Integrated Authentication (default)
-- when connecting to the Publisher.
@publisher_security_mode = 1;
GO
--Declarations for adding an article.
DECLARE @publication AS sysname;
DECLARE @article AS sysname;
DECLARE @owner AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @article = N'Product';
SET @owner = N'Production';
-- Add a horizontally and vertically filtered article for the Product table.
USE [AdventureWorks]
EXEC sp_addarticle
@publication = @publication,
@article = @article,
@source_table = @article,
@vertical_partition = N'false',
@type = N'logbased',
@source_owner = @owner,
@destination_owner = @owner;
GO