sp_adddistributor (Transact-SQL)
Collapse the table of content
Expand the table of content

sp_adddistributor (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Creates an entry in the sys.sysservers table (if there is not one), marks the server entry as a Distributor, and stores property information. This stored procedure is executed at the Distributor on the master database to register and mark the server as a distributor. In the case of a remote distributor, it is also executed at the Publisher from the master database to register the remote distributor.

Topic link icon Transact-SQL Syntax Conventions

sp_adddistributor [ @distributor= ] 'distributor' 
    [ , [ @heartbeat_interval= ] heartbeat_interval ] 
    [ , [ @password= ] 'password' ] 
    [ , [ @from_scripting= ] from_scripting ]

[ @distributor=] 'distributor'

Is the distribution server name. distributor is sysname, with no default. This parameter is only used if setting up a remote Distributor. It adds entries for the Distributor properties in the msdb..MSdistributor table.

[ @heartbeat_interval=] heartbeat_interval

Is the maximum number of minutes that an agent can go without logging a progress message. heartbeat_interval is int, with a default of 10 minutes. A SQL Server Agent job is created that runs on this interval to check the status of the replication agents that are running.

[ @password=] 'password']

Is the password of the distributor_admin login. password is sysname, with a default of NULL. If NULL or an empty string, password is reset to a random value. The password must be configured when the first remote distributor is added. distributor_admin login and password are stored for linked server entry used for a distributor RPC connection, including local connections. If distributor is local, the password for distributor_admin is set to a new value. For Publishers with a remote Distributor, the same value for password must be specified when executing sp_adddistributor at both the Publisher and Distributor. sp_changedistributor_password can be used to change the Distributor password.

Security note Security Note

When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

[ @from_scripting= ] from_scripting

Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

0 (success) or 1 (failure)

sp_adddistributor is used in snapshot replication, transactional replication, and merge replication.

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

-- 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, 
    @security_mode = 1;

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

Community Additions

© 2015 Microsoft