Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Replication publishing and distribution can be configured programmatically using replication stored procedures.
To configure publishing using a local distributor
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.
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.
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
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.
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 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.
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.
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
See Also
Tasks
How to: Configure Publishing and Distribution (RMO Programming)
Concepts
Programming Replication Using System Stored Procedures
Other Resources
How to: Configure Distribution (SQL Server Management Studio)
Configuring Distribution