sp_adddistpublisher (Transact-SQL)sp_adddistpublisher (Transact-SQL)

适用于: 是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: YesSQL Server YesAzure SQL Database (Managed Instance only) NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

配置发布服务器以使用指定的分发数据库。Configures a Publisher to use a specified distribution database. 此存储过程在分发服务器上的任何数据库中执行。This stored procedure is executed at the Distributor on any database. 请注意,在使用此存储过程之前,必须先运行sp_adddistributor (transact-sql)(sp_adddistributiondb的存储过程。Note that the stored procedures sp_adddistributor (Transact-SQL) and sp_adddistributiondb (Transact-SQL) must have been run prior to using this stored procedure.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

  
sp_adddistpublisher [ @publisher= ] 'publisher'   
        , [ @distribution_db= ] 'distribution_db'   
    [ , [ @security_mode= ] security_mode ]   
    [ , [ @login= ] 'login' ]   
    [ , [ @password= ] 'password' ]   
    [ , [ @working_directory= ] 'working_directory' ]   
    [ , [ @storage_connection_string= ] 'storage_connection_string']
    [ , [ @trusted= ] 'trusted' ]   
    [ , [ @encrypted_password= ] encrypted_password ]   
    [ , [ @thirdparty_flag = ] thirdparty_flag ]  
    [ , [ @publisher_type = ] 'publisher_type' ]  

参数Arguments

[ @publisher = ] 'publisher'发布服务器名称。[ @publisher = ] 'publisher' Is the Publisher name. 发布服务器sysname,无默认值。publisher is sysname, with no default.

[ @distribution_db = ] 'distribution_db'分发数据库的名称。[ @distribution_db = ] 'distribution_db' Is the name of the distribution database. distributor_db sysname,无默认值。distributor_db is sysname, with no default. 复制代理使用该参数连接到发布服务器。This parameter is used by replication agents to connect to the Publisher.

[ @security_mode = ] security_mode实现的安全模式。[ @security_mode = ] security_mode Is the implemented security mode. 此参数仅供复制代理用于连接到排队更新订阅的发布服务器或非SQL ServerSQL Server发布服务器。This parameter is only used by replication agents to connect to the Publisher for queued updating subscriptions or with a non- SQL ServerSQL Server Publisher. security_modeint,可以是下列值之一。security_mode is int, and can be one of these values.

ValueValue 说明Description
00 分发服务器中的复制代理使用 SQL ServerSQL Server 身份验证连接到发布服务器。Replication agents at the Distributor use SQL ServerSQL Server Authentication to connect to the Publisher.
1 (默认值)1 (default) 分发服务器中的复制代理使用 Windows 身份验证连接到发布服务器。Replication agents at the Distributor use Windows Authentication to connect to the Publisher.

[ @login = ] 'login'登录名。[ @login = ] 'login' Is the login. 如果security_mode0,则此参数是必需的。This parameter is required if security_mode is 0. login 的数据类型为 sysname,默认值为 NULL******。login is sysname, with a default of NULL. 复制代理使用该参数连接到发布服务器。This parameter is used by replication agents to connect to the Publisher.

[ @password = ] 'password']密码。[ @password = ] 'password'] Is the password. password的值为sysname,默认值为 NULL。password is sysname, with a default of NULL. 复制代理使用该参数连接到发布服务器。This parameter is used by replication agents to connect to the Publisher.

重要

不要使用空密码。Do not use a blank password. 请使用强密码。Use a strong password.

[ @working_directory = ] 'working_directory'用于存储发布的数据和架构文件的工作目录的名称。[ @working_directory = ] 'working_directory' Is the name of the working directory used to store data and schema files for the publication. working_directorynvarchar (255),而默认为此实例的 ReplData 文件夹SQL ServerSQL Server,例如。 C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplDataworking_directory is nvarchar(255), and defaults to the ReplData folder for this instance of SQL ServerSQL Server, for example C:\Program Files\Microsoft SQL Server\MSSQL\MSSQ.1\ReplData. 名称应按 UNC 格式指定。The name should be specified in UNC format.

对于 Azure SQL 数据库,请\\<storage_account>.file.core.windows.net\<share>使用。For Azure SQL Database, use \\<storage_account>.file.core.windows.net\<share>.

[ @storage_connection_string = ] 'storage_connection_string'对于 SQL 数据库是必需的。[ @storage_connection_string = ] 'storage_connection_string' Is required for SQL Database. 使用 Azure 门户中的 "存储 > 设置" 下的访问密钥。Use the access key from Azure Portal under storage > settings.

Azure SQL Database 上的发布服务器和分发服务器数据库需要托管实例有关详细信息,请参阅与 Azure SQL database 复制Publisher and distributor databases on Azure SQL Database require Managed Instance for more information, see Replication with Azure SQL Database.

[ @trusted = ] 'trusted'此参数已弃用,提供此参数只是为了向后兼容。[ @trusted = ] 'trusted' This parameter has been deprecated and is provided for backward compatibility only. trustednvarchar (5),将其设置为任何值,但false将导致错误。trusted is nvarchar(5), and setting it to anything but false will result in an error.

[ @encrypted_password = ] encrypted_password不再支持设置encrypted_password[ @encrypted_password = ] encrypted_password Setting encrypted_password is no longer supported. 尝试将此参数设置为1将导致错误。Attempting to set this bit parameter to 1 will result in an error.

[ @thirdparty_flag = ] thirdparty_flag发布服务器是SQL ServerSQL Server时为。[ @thirdparty_flag = ] thirdparty_flag Is when the Publisher is SQL ServerSQL Server. thirdparty_flagbit,可以是下列值之一。thirdparty_flag is bit, and can be one of the following values.

ValueValue 说明Description
0 (默认值)0 (default) SQL ServerSQL Server数据.database.
11 SQL ServerSQL Server 数据库。Database other than SQL ServerSQL Server.

[ @publisher_type = ] 'publisher_type'当发布服务器不SQL ServerSQL Server是时指定发布服务器类型。[ @publisher_type = ] 'publisher_type' Specifies the Publisher type when the Publisher is not SQL ServerSQL Server. publisher_type sysname,可以是下列值之一。publisher_type is sysname, and can be one of the following values.

ValueValue 说明Description
MSSQLSERVERMSSQLSERVER

(默认值)(default)
指定 SQL ServerSQL Server 发布服务器。Specifies a SQL ServerSQL Server Publisher.
联手ORACLE 指定标准的 Oracle 发布服务器。Specifies a standard Oracle Publisher.
ORACLE GATEWAYORACLE GATEWAY 指定 Oracle 网关发布服务器。Specifies an Oracle Gateway Publisher.

有关 Oracle 发布服务器与 Oracle 网关发布服务器之间的差异的详细信息,请参阅配置 Oracle 发布服务器For more information about the differences between an Oracle Publisher and an Oracle Gateway Publisher, see Configure an Oracle Publisher.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

sp_adddistpublisher用于快照复制、事务复制和合并复制。sp_adddistpublisher is used by snapshot replication, transactional replication, and merge replication.

示例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'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 

权限Permissions

只有sysadmin固定服务器角色的成员才能sp_adddistpublisher执行。Only members of the sysadmin fixed server role can execute sp_adddistpublisher.

另请参阅See Also

配置发布和分发 Configure Publishing and Distribution
sp_changedistpublisher (Transact-sql) sp_changedistpublisher (Transact-SQL)
sp_dropdistpublisher (Transact-sql) sp_dropdistpublisher (Transact-SQL)
sp_helpdistpublisher (Transact-sql) sp_helpdistpublisher (Transact-SQL)
(Transact-sql)系统存储过程 System Stored Procedures (Transact-SQL)
配置分发Configure Distribution