sp_adddistributiondb
Creates a new distribution database and installs the Distributor schema. The distribution database stores procedures, schema, and meta data 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.
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 ]
Arguments
[@database = ] 'database'
Is the name of the distribution database to be created. database is sysname, with no default.
[@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\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 2 MB.
[@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\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.
[@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 creating the distribution database objects. security_mode is int, with a default of 0. 0 specifies SQL Server Authentication; 1 specifies Windows Authentication.
[@login = ] 'login'
Is the login name used when connecting to the Distributor to create the distribution database objects when running instdist.sql. This is required if security_mode is set to 0. login is sysname, with a default of sa.
[@password = ] 'password'
Is the password used when connecting to the Distributor to run instdist.sql. 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 these 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. |
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.
Run sp_adddistributor prior to running sp_adddistributiondb.
Permissions
Only members of the sysadmin fixed server role can execute sp_adddistributiondb.