แก้ไข

แชร์ผ่าน

sp_adddistributor (Transact-SQL)

  • บทความ

Applies to: SQL Server Azure SQL Managed Instance

Creates an entry in the sys.servers table (if there isn't 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's also executed at the Publisher from the master database to register the remote distributor.

Transact-SQL syntax conventions

Syntax

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

Arguments

[ @distributor = ] N'distributor'

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.

Note

Server name can be specified as <Hostname>,<PortNumber> for a default instance or <Hostname>\<InstanceName>,<PortNumber> for a named instance. Specify the port number for your connection when SQL Server is deployed on Linux or Windows with a custom port, and the browser service is disabled. The use of custom port numbers for remote distributor applies to SQL Server 2019 (15.x) and later versions.

[ @heartbeat_interval = ] heartbeat_interval

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 = ] N'password'

The password of the distributor_admin login. @password is sysname, with a default of NULL. If the password is 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.

Important

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

@from_scripting is bit, with a default of 0. Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.

Return code values

0 (success) or 1 (failure).

Remarks

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

Examples

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

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

Permissions

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

Related content