مشاركة عبر

  • مقالة

How to: Configure Publishing and Distribution (Replication Transact-SQL Programming)

Replication publishing and distribution can be configured programmatically using replication stored procedures.

To configure publishing using a local distributor

  1. Execute sp_get_distributor (Transact-SQL) to determine if the server is already configured as a Distributor.

    • If the value of installed in the result set is 0, execute sp_adddistributor (Transact-SQL) at the Distributor on the master database.

    • If the value of distribution db installed in the result set is 0, execute sp_adddistributiondb (Transact-SQL) at the Distributor on the master database. Specify the name of the distribution database for @database. Optionally, you can specify the maximum transactional retention period for @max_distretention and the history retention period for @history_retention. If a new database is being created, specify the desired database property parameters.

  2. At the Distributor, which is also the Publisher, execute sp_adddistpublisher (Transact-SQL), specifying the UNC share that will be used as default snapshot folder for @working_directory.

  3. At the Publisher, execute sp_replicationdboption (Transact-SQL). Specify the database being published for @dbname, the type of replication for @optname, and a value of true for @value.

To configure publishing using a remote distributor

  1. Execute sp_get_distributor (Transact-SQL) to determine if the server is already configured as a Distributor.

    • If the value of installed in the result set is 0, execute sp_adddistributor (Transact-SQL) at the Distributor on the master database. Specify a strong password for @password. This password for the distributor_admin account will be used by the Publisher when connecting to the Distributor.

    • If the value of distribution db installed in the result set is 0, execute sp_adddistributiondb (Transact-SQL) at the Distributor on the master database. Specify the name of the distribution database for @database. Optionally, you can specify the maximum transactional retention period for @max_distretention and the history retention period for @history_retention. If a new database is being created, specify the desired database property parameters.

  2. At the Distributor, execute sp_adddistpublisher (Transact-SQL), specifying the UNC share that will be used as default snapshot folder for @working_directory. If the Distributor will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @security_mode and the Microsoft SQL Server login information for @login and @password.

  3. At the Publisher on the master database, execute sp_adddistributor (Transact-SQL). Specify the strong password used in step 1 for @password. This password will be used by the Publisher when connecting to the Distributor.

  4. At the Publisher, execute sp_replicationdboption (Transact-SQL). Specify the database being published for @dbname, the type of replication for @optname, and a value of true for @value.

Example

The following example demonstrates how to configure publishing and distribution programmatically. In this example, the name of the server that is being configured as a publisher and a local distributor is supplied using 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".

-- Install the Distributor and the distribution database.
DECLARE @distributor AS sysname;
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @directory AS nvarchar(500);
DECLARE @publicationDB AS sysname;
-- Specify the Distributor name.
SET @distributor = $(DistPubServer);
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);
-- Specify the replication working directory.
SET @directory = N'\\' + $(DistPubServer) + '\repldata';
-- Specify the publication database.
SET @publicationDB = N'AdventureWorks'; 

-- Install the server MYDISTPUB as a Distributor using the defaults,
-- including autogenerating the distributor password.
USE master
EXEC sp_adddistributor @distributor = @distributor;

-- Create a new distribution database using the defaults, including
-- using Windows Authentication.
USE master
EXEC sp_adddistributiondb @database = @distributionDB, 
    @security_mode = 1;
GO

-- Create a Publisher and enable AdventureWorks for replication.
-- Add MYDISTPUB as a publisher with MYDISTPUB as a local distributor
-- and use Windows Authentication.
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
-- Specify the distribution database.
SET @distributionDB = N'distribution';
-- Specify the Publisher name.
SET @publisher = $(DistPubServer);

USE [distribution]
EXEC sp_adddistpublisher @publisher=@publisher, 
    @distribution_db=@distributionDB, 
    @security_mode = 1;
GO