.png)
sp_adddistributiondb (Transact-SQL)
SQL Server 2012
Creates a new distribution database and installs the Distributor schema. The distribution database stores procedures, schema, and metadata used in replication. This stored procedure is executed at the Distributor on the master database in order to create the distribution database, and install the necessary tables and stored procedures required to enable the replication distribution.
sp_adddistributiondb [ @database= ] 'database'
[ , [ @data_folder= ] 'data_folder' ]
[ , [ @data_file= ] 'data_file' ]
[ , [ @data_file_size= ] data_file_size ]
[ , [ @log_folder= ] 'log_folder' ]
[ , [ @log_file= ] 'log_file' ]
[ , [ @log_file_size= ] log_file_size ]
[ , [ @min_distretention= ] min_distretention ]
[ , [ @max_distretention= ] max_distretention ]
[ , [ @history_retention= ] history_retention ]
[ , [ @security_mode= ] security_mode ]
[ , [ @login= ] 'login' ]
[ , [ @password= ] 'password' ]
[ , [ @createmode= ] createmode ]
[ , [ @from_scripting = ] from_scripting ]
sp_adddistributiondb is used in all types of replication. However, this stored procedure only runs at a distributor.
You must configure the distributor by executing sp_adddistributor before executing sp_adddistributiondb.
Run sp_adddistributor prior to running sp_adddistributiondb.
-- 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'AdventureWorks2012';
-- 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 AdventureWorks2012 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
