How to: Enable Updating Subscriptions for Transactional Publications (Replication Transact-SQL Programming)

When creating a transactional publication programmatically using replication stored procedures, you can enable either immediate or queued updating subscriptions.

安全性注意事項：
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

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

2. Execute sp_addpublication (Transact-SQL), specifying a value of true for the parameter @allow_sync_tran.

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

4. Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

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

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

2. 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 複寫代理程式安全性模型.

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

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

5. Add articles to the publication. For more information, see How to: Define an Article (Replication Transact-SQL Programming).

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

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

範例

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

請參閱

工作

How to: Create a Publication (Replication Transact-SQL Programming)
How to: Create an Updatable Subscription to a Transactional Publication (Replication Transact-SQL Programming)

其他資源

交易式複寫的可更新訂閱
以指令碼變數使用 sqlcmd

說明及資訊

取得 SQL Server 2005 協助