Applies To: SQL Server 2014, SQL Server 2016 Preview
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.
- [ @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.
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; 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
Only members of the sysadmin fixed server role can execute sp_adddistributor.