sp_adddistributiondb (Transact-SQL)

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.

Transact-SQL Syntax Conventions

Syntax

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 ]

Arguments

• [ @database=] 'database'
  Is the name of the distribution database to be created. database is sysname, with no default. If the specified database already exists and is not already marked as a distribution database, then the objects needed to enable distribution are installed and the database is marked as a distribution database. If the specified database is already enabled as a distribution database, an error is returned.

• [ @data_folder=] 'data_folder'
  Is the name of the directory used to store the distribution database data file. data_folder is nvarchar(255), with a default of NULL. If NULL, the data directory for that instance of Microsoft SQL Server is used, for example, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.1\Data.

• [ @data_file=] 'data_file'
  Is the name of the database file. data_file is nvarchar(255), with a default of database. If NULL, the stored procedure constructs a file name using the database name.

• [ @data_file_size=] data_file_size
  Is the initial data file size in megabytes (MB). data_file_size is int, with a default of 5MB.

• [ @log_folder=] 'log_folder'
  Is the name of the directory for the database log file. log_folder is nvarchar(255), with a default of NULL. If NULL, the data directory for that instance of SQL Server is used (for example, C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL.1\Data).

• [ @log_file=] 'log_file'
  Is the name of the log file. log_file is nvarchar(255), with a default of NULL. If NULL, the stored procedure constructs a file name using the database name.

• [ @log_file_size=] log_file_size
  Is the initial log file size in megabytes (MB). log_file_size is int, with a default of 0 MB, which means the file size is created using the smallest log file size allowed by SQL Server.

• [ @min_distretention=] min_distretention
  Is the minimum retention period, in hours, before transactions are deleted from the distribution database. min_distretention is int, with a default of 0 hours.

• [ @max_distretention=] max_distretention
  Is the maximum retention period, in hours, before transactions are deleted. max_distretention is int, with a default of 72 hours. Subscriptions that have not received replicated commands that are older than the maximum distribution retention period are marked as inactive and need to be reinitialized. RAISERROR 21011 is issued for each inactive subscription. A value of 0 means that replicated transactions are not stored in the distribution database.

• [ @history_retention=] history_retention
  Is the number of hours to retain history. history_retention is int, with a default of 48 hours.

• [ @security_mode=] security_mode
  Is the security mode to use when connecting to the Distributor. security_mode is int, with a default of 1. 0 specifies SQL Server Authentication; 1 specifies Windows Integrated Authentication.

• [ @login=] 'login'
  Is the login name used when connecting to the Distributor to create the distribution database. This is required if security_mode is set to 0. login is sysname, with a default of NULL.

• [ @password=] 'password'
  Is the password used when connecting to the Distributor. This is required if security_mode is set to 0. password is sysname, with a default of NULL.

• [ @createmode=] createmode
  createmode is int, with a default of 0, and can be one of the following values.

  Value	Description
  0 (default)	CREATE DATABASE by attaching the distribution database using a copy of the distribution database model files (distmdl.mdf)
  1	CREATE DATABASE or use existing database and then apply instdist.sql file to create replication objects in the distribution database.
  2	For internal use only.

• [ @from_scripting= ] from_scripting
  For internal use only.

Return Code Values

0 (success) or 1 (failure)

Remarks

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.

Permissions

Only members of the sysadmin fixed server role can execute sp_adddistributiondb.

Example

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

Reference

sp_changedistributiondb (Transact-SQL)
sp_dropdistributiondb (Transact-SQL)
sp_helpdistributiondb (Transact-SQL)
System Stored Procedures (Transact-SQL)

Other Resources

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

Help and Information

Getting SQL Server 2005 Assistance