設定發行和散發Configure Publishing and Distribution

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL 受控執行個體Azure SQL Managed InstanceYesAzure SQL 受控執行個體Azure SQL Managed Instance

本主題描述如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio或 Replication Management Objects (RMO),在 Transact-SQLTransact-SQL中設定發行和散發。This topic describes how to configure publishing and distribution in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or Replication Management Objects (RMO).

開始之前Before You Begin

SecuritySecurity

如需詳細資訊,請參閱檢視及修改複寫安全性設定For more information, see View and modify replication security settings.

使用 SQL Server Management StudioUsing SQL Server Management Studio

使用「新增發行集精靈」或「設定散發精靈」來設定散發。Configure distribution using the New Publication Wizard or the Configure Distribution Wizard. 設定散發者之後,請檢視並修改 [散發者屬性 - <Distributor>] 對話方塊中的屬性。After the Distributor is configured, view and modify properties in the Distributor Properties - <Distributor> dialog box. 如果您想設定「散發者」讓 db_owner固定資料庫角色的成員可以建立發行集,或者因為您想設定非「發行者」的遠端「散發者」,則請使用「設定散發精靈」。Use the Configure Distribution Wizard if you want to configure a Distributor so that members of the db_owner fixed database roles can create publications, or because you want to configure a remote Distributor that is not a Publisher.

若要設定散發To configure distribution

  1. MicrosoftMicrosoft SQL Server Management StudioSQL Server Management Studio 中,連線到將成為「散發者」的伺服器 (在許多情況下,「發行者」與「散發者」是同一部伺服器),然後展開伺服器節點。In MicrosoftMicrosoft SQL Server Management StudioSQL Server Management Studio, connect to the server that will be the Distributor (in many cases, the Publisher and Distributor are the same server), and then expand the server node.

  2. 在以滑鼠右鍵按一下 [複寫] 資料夾,然後按一下 [設定散發]Right-click the Replication folder, and then click Configure Distribution.

  3. 遵循「設定散發精靈」的指示執行下列項目:Follow the Configure Distribution Wizard to:

  • 選取「散發者」。Select a Distributor. 若要使用本機「散發者」,請選取 [ServerName 將扮演本身的散發者; SQL Server 將建立散發資料庫和記錄]。To use a local Distributor, select ServerName will act as its own Distributor; SQL Server will create a distribution database and log. 若要使用遠端散發者,請選取 [使用下列伺服器做為散發者] ,然後選取伺服器。To use a remote Distributor, select Use the following server as the Distributor, and then select a server. 伺服器必須已設定為散發者,且必須先啟用發行者才能使用散發者。The server must already be configured as a Distributor, and the Publisher must be enabled to use the Distributor. 如需詳細資訊,請參閱在散發者端啟用遠端發行者 (SQL Server Management Studio)For more information, see Enable a Remote Publisher at a Distributor (SQL Server Management Studio).

    如果您選取遠端「散發者」,則必須在 [管理密碼] 頁面上輸入密碼才能從「發行者」連接到「散發者」。If you select a remote Distributor, you must enter a password on the Administrative Password page for connections made from the Publisher to the Distributor. 此密碼必須符合發行者於遠端散發者啟用時所指定的密碼。This password must match the password specified when the Publisher was enabled at the remote Distributor.

  • 指定根快照集資料夾 (針對本機「散發者」)。Specify a root snapshot folder (for a local Distributor). 快照集資料夾只是指定為共用的目錄;讀取並寫入此資料夾的代理程式必須具有足夠的權限才能對其進行存取。The snapshot folder is simply a directory that you have designated as a share; agents that read from and write to this folder must have sufficient permissions to access it. 使用此「散發者」的每個「發行者」在根資料夾下建立資料夾,每個發行集在要儲存快照集檔案的「發行者」資料夾下建立資料夾。Each Publisher that uses this Distributor creates a folder under the root folder, and each publication creates folders under the Publisher folder in which to store snapshot files. 如需適當設定資料夾安全性的詳細資訊,請參閱保護快照集資料夾For more information on securing the folder appropriately, see Secure the Snapshot Folder.

  • 指定散發資料庫 (針對本機「散發者」)。Specify the distribution database (for a local Distributor). 散發資料庫為異動複寫中所有類型的複寫與交易,儲存中繼資料和歷程資料。The distribution database stores metadata and history data for all types of replication and transactions for transactional replication.

  • 選擇性地啟用其他「發行者」來使用「散發者」。Optionally enable other Publishers to use the Distributor. 如果啟用其他「發行者」來使用「散發者」,則必須在 [散發者密碼] 頁面上輸入密碼才能從這些「發行者」連接到「散發者」。If other Publishers are enabled to use the Distributor, you must enter a password on the Distributor Password page for connections made from these Publishers to the Distributor.

  • 選擇性地編寫組態設定的指令碼。Optionally script configuration settings. 如需詳細資訊,請參閱 Scripting ReplicationFor more information, see Scripting Replication.

使用 Transact-SQLUsing Transact-SQL

您可以使用複寫預存程序來以程式設計的方式設定複寫發行和散發。Replication publishing and distribution can be configured programmatically using replication stored procedures.

使用本機散發者設定發行To configure publishing using a local distributor

  1. 請執行 sp_get_distributor (Transact-SQL) 來判斷伺服器是否已經設定為散發者。Execute sp_get_distributor (Transact-SQL) to determine if the server is already configured as a Distributor.
  • 如果結果集中 installed 的值是 0,請在 master 資料庫的「散發者」上執行 sp_adddistributor (Transact-SQL)If the value of installed in the result set is 0, execute sp_adddistributor (Transact-SQL) at the Distributor on the master database.

  • 如果結果集中 distribution db installed 的值是 0,請在 master 資料庫的「散發者」上執行 sp_adddistributiondb (Transact-SQL)If the value of distribution db installed in the result set is 0, execute sp_adddistributiondb (Transact-SQL) at the Distributor on the master database. 針對 @database 指定散發資料庫的名稱。Specify the name of the distribution database for @database. 您可以選擇針對 @max_distretention 指定最大交易保留期限,並針對 @history_retention 指定記錄保留期限。Optionally, you can specify the maximum transactional retention period for @max_distretention and the history retention period for @history_retention. 如果正在建立新的資料庫,請指定所要的資料庫屬性參數。If a new database is being created, specify the desired database property parameters.

  1. 在散發者 (同時也是發行者) 上執行 sp_adddistpublisher (Transact-SQL),指定將作為 @working_directory 預設快照集資料夾使用的 UNC 共用。At the Distributor, which is also the Publisher, execute sp_adddistpublisher (Transact-SQL), specifying the UNC share that will be used as default snapshot folder for @working_directory.

    針對 SQL 受控執行個體上的散發者,請為 @working_directory 使用 Azure 儲存體帳戶,並為 @storage_connection_string 使用儲存體存取金鑰。For a distributor on SQL Managed Instance, use an Azure storage account for @working_directory and the storage access key for @storage_connection_string.

  2. 在發行者上,執行 sp_replicationdboption (Transact-SQL)At the Publisher, execute sp_replicationdboption (Transact-SQL). 針對 @dbname 指定發行的資料庫、針對 @optname 指定複寫的類型,並針對 @value 指定 true 的值。Specify the database being published for @dbname, the type of replication for @optname, and a value of true for @value.

使用遠端散發者設定發行To configure publishing using a remote distributor

  1. 請執行 sp_get_distributor (Transact-SQL) 來判斷伺服器是否已經設定為散發者。Execute sp_get_distributor (Transact-SQL) to determine if the server is already configured as a Distributor.

    • 如果結果集中 installed 的值是 0,請在 master 資料庫的「散發者」上執行 sp_adddistributor (Transact-SQL)If the value of installed in the result set is 0, execute sp_adddistributor (Transact-SQL) at the Distributor on the master database. 針對 @password 指定強式密碼。Specify a strong password for @password. distributor_admin 帳戶的這個密碼將會由發行者連接到散發者時使用。This password for the distributor_admin account will be used by the Publisher when connecting to the Distributor.

    • 如果結果集中 distribution db installed 的值是 0,請在 master 資料庫的「散發者」上執行 sp_adddistributiondb (Transact-SQL)If the value of distribution db installed in the result set is 0, execute sp_adddistributiondb (Transact-SQL) at the Distributor on the master database. 針對 @database 指定散發資料庫的名稱。Specify the name of the distribution database for @database. 您可以選擇針對 @max_distretention 指定最大交易保留期限,並針對 @history_retention 指定記錄保留期限。Optionally, you can specify the maximum transactional retention period for @max_distretention and the history retention period for @history_retention. 如果正在建立新的資料庫,請指定所要的資料庫屬性參數。If a new database is being created, specify the desired database property parameters.

  2. 在散發者上執行 sp_adddistpublisher (Transact-SQL),指定將作為 @working_directory 預設快照集資料夾使用的 UNC 共用。At the Distributor, execute sp_adddistpublisher (Transact-SQL), specifying the UNC share that will be used as default snapshot folder for @working_directory. 如果散發者將在與發行者連線時使用「SQL ServerSQL Server 驗證」,則也必須針對 @security_mode 指定 0 的值,並針對 @login@password 指定 MicrosoftMicrosoft SQL ServerSQL Server 登入資訊。If the Distributor will use SQL ServerSQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @security_mode and the MicrosoftMicrosoft SQL ServerSQL Server login information for @login and @password.

    針對 SQL 受控執行個體上的散發者,請為 @working_directory 使用 Azure 儲存體帳戶,並為 @storage_connection_string 使用儲存體存取金鑰。For a distributor on SQL Managed Instance, use an Azure storage account for @working_directory and the storage access key for @storage_connection_string.

  3. 在 master 資料庫的發行者上,執行 sp_adddistributor (Transact-SQL)At the Publisher on the master database, execute sp_adddistributor (Transact-SQL). 針對 @password 指定步驟 1 中使用的強式密碼。Specify the strong password used in step 1 for @password. 這個密碼將會由發行者連接到散發者時使用。This password will be used by the Publisher when connecting to the Distributor.

  4. 在發行者上,執行 sp_replicationdboption (Transact-SQL)At the Publisher, execute sp_replicationdboption (Transact-SQL). 針對 @dbname 指定發行的資料庫、針對 @optname 指定複寫的類型,並針對 @value 指定 true 的值。Specify the database being published for @dbname, the type of replication for @optname, and a value of true for @value.

範例 (Transact-SQL)Example (Transact-SQL)

下列範例會示範如何以程式設計方式設定發行和散發。The following example demonstrates how to configure publishing and distribution programmatically. 在此範例中,會使用指令碼變數來提供設定為發行者和本機散發者的伺服器名稱。In this example, the name of the server that is being configured as a publisher and a local distributor is supplied using scripting variables. 您可以使用複寫預存程序來以程式設計的方式設定複寫發行和散發。Replication publishing and distribution can be configured programmatically using replication stored procedures.

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

使用 Replication Management Objects (RMO)Using Replication Management Objects (RMO)

在單一伺服器上設定發行和散發To configure publishing and distribution on a single server

  1. 使用 ServerConnection 類別建立與伺服器的連接。Create a connection to the server by using the ServerConnection class.

  2. 建立 ReplicationServer 類別的執行個體。Create an instance of the ReplicationServer class. 傳遞步驟 1 的 ServerConnectionPass the ServerConnection from step 1.

  3. 建立 DistributionDatabase 類別的執行個體。Create an instance of the DistributionDatabase class.

  4. Name 屬性設定為資料庫名稱,並將 ConnectionContext 屬性設定為步驟 1 中的 ServerConnectionSet the Name property to the database name and set the ConnectionContext property to the ServerConnection from step 1.

  5. 呼叫 InstallDistributor 方法來安裝散發者。Install the Distributor by calling the InstallDistributor method. 傳遞步驟 3 的 DistributionDatabase 物件。Pass the DistributionDatabase object from step 3.

  6. 建立 DistributionPublisher 類別的執行個體。Create an instance of the DistributionPublisher class.

  7. 設定 DistributionPublisher的以下屬性:Set the following properties of DistributionPublisher:

  1. 呼叫 Create 方法。Call the Create method.

使用遠端散發者設定發行和散發To configure publishing and distribution using a remote Distributor

  1. 使用 ServerConnection 類別建立與遠端散發者伺服器的連接。Create a connection to the remote Distributor server by using the ServerConnection class.

  2. 建立 ReplicationServer 類別的執行個體。Create an instance of the ReplicationServer class. 傳遞步驟 1 的 ServerConnectionPass the ServerConnection from step 1.

  3. 建立 DistributionDatabase 類別的執行個體。Create an instance of the DistributionDatabase class.

  4. Name 屬性設定為資料庫名稱,並將 ConnectionContext 屬性設定為步驟 1 中的 ServerConnectionSet the Name property to the database name, and set the ConnectionContext property to the ServerConnection from step 1.

  5. 呼叫 InstallDistributor 方法來安裝散發者。Install the Distributor by calling the InstallDistributor method. 指定安全密碼 (連接到遠端散發者時由發行者使用) 及步驟 3 中的 DistributionDatabase 物件。Specify a secure password (used by the Publisher when connecting to the remote Distributor) and the DistributionDatabase object from step 3. 如需詳細資訊,請參閱保護散發者For more information, see Secure the Distributor.

    IMPORTANT!! 可能的話,會在執行階段提示使用者輸入安全性認證。IMPORTANT!! When possible, prompt users to enter security credentials at runtime. 如果您必須儲存認證,請使用 Windows .NET Framework 提供的 密碼編譯服務 MicrosoftMicrosoftIf you must store credentials, use the cryptographic services provided by the MicrosoftMicrosoft Windows .NET Framework.

  6. 建立 DistributionPublisher 類別的執行個體。Create an instance of the DistributionPublisher class.

  7. 設定 DistributionPublisher的以下屬性:Set the following properties of DistributionPublisher:

  1. 呼叫 Create 方法。Call the Create method.

  2. 使用 ServerConnection 類別建立與本機發行者伺服器的連接。Create a connection to the local Publisher server by using the ServerConnection class.

  3. 建立 ReplicationServer 類別的執行個體。Create an instance of the ReplicationServer class. 傳遞步驟 9 的 ServerConnectionPass the ServerConnection from step 9.

  4. 呼叫 InstallDistributor 方法。Call the InstallDistributor method. 傳遞遠端散發者的名稱以及步驟 5 中指定之遠端散發者的密碼。Pass the name of the remote Distributor and the password for the remote Distributor specified in step 5.

重要

可能的話,會在執行階段提示使用者輸入安全性認證。When possible, prompt users to enter security credentials at runtime. 如果您必須儲存認證,請使用 Windows .NET Framework 提供的 密碼編譯服務If you must store credentials, use the cryptographic services provided by the Windows .NET Framework.

範例 (RMO)Example (RMO)

您可以使用 Replication Management Objects (RMO) 以程式設計的方式設定複寫發行和散發。You can programmatically configure replication publishing and distribution by using Replication Management Objects (RMO).

// Set the server and database names
string distributionDbName = "distribution";
string publisherName = publisherInstance;
string publicationDbName = "AdventureWorks2012";

DistributionDatabase distributionDb;
ReplicationServer distributor;
DistributionPublisher publisher;
ReplicationDatabase publicationDb;

// Create a connection to the server using Windows Authentication.
ServerConnection conn = new ServerConnection(publisherName);

try
{
    // Connect to the server acting as the Distributor 
    // and local Publisher.
    conn.Connect();

    // Define the distribution database at the Distributor,
    // but do not create it now.
    distributionDb = new DistributionDatabase(distributionDbName, conn);
    distributionDb.MaxDistributionRetention = 96;
    distributionDb.HistoryRetention = 120;

    // Set the Distributor properties and install the Distributor.
    // This also creates the specified distribution database.
    distributor = new ReplicationServer(conn);
    distributor.InstallDistributor((string)null, distributionDb);

    // Set the Publisher properties and install the Publisher.
    publisher = new DistributionPublisher(publisherName, conn);
    publisher.DistributionDatabase = distributionDb.Name;
    publisher.WorkingDirectory = @"\\" + publisherName + @"\repldata";
    publisher.PublisherSecurity.WindowsAuthentication = true;
    publisher.Create();

    // Enable AdventureWorks2012 as a publication database.
    publicationDb = new ReplicationDatabase(publicationDbName, conn);

    publicationDb.EnabledTransPublishing = true;
    publicationDb.EnabledMergePublishing = true;
}
catch (Exception ex)
{
    // Implement appropriate error handling here.
    throw new ApplicationException("An error occured when installing distribution and publishing.", ex);
}
finally
{
    conn.Disconnect();
}
' Set the server and database names
Dim distributionDbName As String = "distribution"
Dim publisherName As String = publisherInstance
Dim publicationDbName As String = "AdventureWorks2012"

Dim distributionDb As DistributionDatabase
Dim distributor As ReplicationServer
Dim publisher As DistributionPublisher
Dim publicationDb As ReplicationDatabase

' Create a connection to the server using Windows Authentication.
Dim conn As ServerConnection = New ServerConnection(publisherName)

Try
    ' Connect to the server acting as the Distributor 
    ' and local Publisher.
    conn.Connect()

    ' Define the distribution database at the Distributor,
    ' but do not create it now.
    distributionDb = New DistributionDatabase(distributionDbName, conn)
    distributionDb.MaxDistributionRetention = 96
    distributionDb.HistoryRetention = 120

    ' Set the Distributor properties and install the Distributor.
    ' This also creates the specified distribution database.
    distributor = New ReplicationServer(conn)
    distributor.InstallDistributor((CType(Nothing, String)), distributionDb)

    ' Set the Publisher properties and install the Publisher.
    publisher = New DistributionPublisher(publisherName, conn)
    publisher.DistributionDatabase = distributionDb.Name
    publisher.WorkingDirectory = "\\" + publisherName + "\repldata"
    publisher.PublisherSecurity.WindowsAuthentication = True
    publisher.Create()

    ' Enable AdventureWorks2012 as a publication database.
    publicationDb = New ReplicationDatabase(publicationDbName, conn)

    publicationDb.EnabledTransPublishing = True
    publicationDb.EnabledMergePublishing = True

Catch ex As Exception
    ' Implement appropriate error handling here.
    Throw New ApplicationException("An error occured when installing distribution and publishing.", ex)

Finally
    conn.Disconnect()

End Try

另請參閱See Also

檢視及修改散發者和發行者屬性View and Modify Distributor and Publisher Properties
Replication System Stored Procedures ConceptsReplication System Stored Procedures Concepts
設定散發Configure Distribution
複寫管理物件概念Replication Management Objects Concepts
設定 AlwaysOn 可用性群組的複寫 (SQL Server)Configure Replication for Always On Availability Groups (SQL Server)