Replication System Stored Procedures Concepts

项目
07/04/2024

在 SQL Server 中，系统存储过程提供对复制拓扑中所有用户可配置功能的编程访问。虽然存储过程可以使用 SQL Server Management Studio 或 sqlcmd 命令行实用工具单独执行，但编写 Transact-SQL 脚本文件可能会很有用，这些文件可以执行复制任务的逻辑序列。

编写复制任务脚本具备以下优点：

保存用于部署复制拓扑的步骤的永久副本。
使用单个脚本配置多个订阅服务器。
通过让新数据库管理员评估、了解、更改代码以及解决代码相关问题来快速培训新数据库管理员。

重要

脚本可能会带来安全漏洞；它们可以在用户毫不知情或无用户干预的情况下调用系统函数，并且可能包含纯文本形式的安全凭据。请在使用之前检查脚本是否存在安全问题。

创建复制脚本

从复制的角度来看，脚本是一系列一个或多个 Transact-SQL 语句，其中每个语句执行复制存储过程。脚本为文本文件，文件扩展名通常为 .sql，可使用 sqlcmd 实用工具来运行。运行脚本文件时，此实用工具将执行存储在该文件中的 SQL 语句。同样，脚本可以存储为 SQL Server Management Studio 项目中的查询对象。

可以通过下列方式创建复制脚本：

手动创建脚本。
使用复制向导或
SQL Server Management Studio。有关详细信息，请参阅 Scripting Replication。
使用复制管理对象 (RMO) 以编程方式生成脚本，从而创建 RMO 对象。

手动创建复制脚本时，请注意以下事项：

Transact-SQL 脚本具有一个或多个批处理。 GO 命令表示一个批处理的结束。如果 Transact-SQL 脚本没有任何 GO 命令，则它作为单个批处理执行。
在单个批处理中执行多个复制存储过程时，批处理中第一个存储过程后的所有存储过程前面都要有 EXECUTE 关键字。
在批处理执行前，该批处理中的所有存储过程都必须先编译。即便如此，编译完批处理并创建了执行计划后，仍有可能出现运行时错误。
创建脚本以配置复制时，应使用 Windows 身份验证，以避免在脚本文件中存储安全凭据。如果必须在脚本文件中存储凭据，则必须保护文件以防止未经授权的访问。

复制脚本示例

执行下面的脚本可在服务器上设置发布和分发。

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

然后，可将此脚本在本地保存为 instdistpub.sql，以便在需要时可运行或再次运行此脚本。

前面的脚本包括 sqlcmd 脚本变量，这些变量用于 SQL Server 联机丛书中的许多复制代码示例。脚本变量是使用 $(MyVariable) 语法定义的。变量的值可以传递到命令行或 SQL Server Management Studio 中的脚本。有关详细信息，请参阅本主题中的下一部分“执行复制脚本”。

执行复制脚本

创建完复制脚本后，可以通过下列方式之一执行所创建的复制脚本：

在 SQL Server Management Studio 中创建一个 SQL 查询文件

复制 Transact-SQL 脚本文件可以作为 SQL Server Management Studio 项目中的 SQL 查询文件创建。编写完脚本后，可以为此查询文件创建一个与数据库的连接，然后即可执行该脚本。有关如何使用 SQL Server Management Studio 创建 Transact-SQL 脚本的详细信息，请参阅查询和文本编辑器（SQL Server Management Studio）。

若要使用包含脚本变量的脚本，SQL Server Management Studio 必须在 sqlcmd 模式下运行。在 sqlcmd 模式下，查询编辑器可接受特定于 sqlcmd 的附加语法，如可用于变量值的 :setvar。有关 sqlcmd 模式的详细信息，请参阅使用查询编辑器编辑 SQLCMD 脚本。在下面的脚本中，:setvar 用于为 $(DistPubServer) 变量提供值。

:setvar DistPubServer N'MyPublisherAndDistributor';  
  
-- 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);  
  
--  
-- Additional code goes here  
--

从命令行使用 sqlcmd 实用工具

下面的示例说明如何在命令行中使用 sqlcmd 实用工具来执行 instdistpub.sql 脚本文件：

sqlcmd.exe -E -S sqlserverinstance -i C:\instdistpub.sql -o C:\output.log -v DistPubServer="N'MyDistributorAndPublisher'"

在此示例中， -E 该开关指示连接到 SQL Server 时使用 Windows 身份验证。使用 Windows 身份验证时，不需要在脚本文件中存储用户名和密码。脚本文件的名称和路径由 -i 开关指定，输出文件的名称由 -o 开关指定（使用此开关时，SQL Server 的输出将写入此文件而不是控制台）。通过此 sqlcmd 实用工具，可以使用开关在运行时 -v 将脚本变量传递到 Transact-SQL 脚本。在此示例中，sqlcmd 在执行前将脚本中的每个 $(DistPubServer) 实例替换为值 N'MyDistributorAndPublisher'。

注意

-X 开关可禁用脚本变量。

自动执行批处理文件中的任务

通过使用批处理文件，可在同一批处理文件中实现复制管理任务、复制同步任务以及其他任务的自动执行。下面的批处理文件使用 sqlcmd 实用工具删除并重新创建订阅数据库，并添加一个合并请求订阅。然后，该文件调用合并代理来同步新订阅：

REM ----------------------Script to synchronize merge subscription ----------------------  
REM -- Creates subscription database and   
REM -- synchronizes the subscription to MergeSalesPerson.  
REM -- Current computer acts as both Publisher and Subscriber.  
REM -------------------------------------------------------------------------------------  
  
SET Publisher=%computername%  
SET Subscriber=%computername%  
SET PubDb=AdventureWorks  
SET SubDb=AdventureWorksReplica  
SET PubName=AdvWorksSalesOrdersMerge  
  
REM -- Drop and recreate the subscription database at the Subscriber  
sqlcmd /S%Subscriber% /E /Q"USE master IF EXISTS (SELECT * FROM sysdatabases WHERE name='%SubDb%' ) DROP DATABASE %SubDb%"  
sqlcmd /S%Subscriber% /E /Q"USE master CREATE DATABASE %SubDb%"  
  
REM -- Add a pull subscription at the Subscriber  
sqlcmd /S%Subscriber% /E /Q"USE %SubDb% EXEC sp_addmergepullsubscription @publisher = %Publisher%, @publication = %PubName%, @publisher_db = %PubDb%"  
sqlcmd /S%Subscriber% /E /Q"USE %SubDb%  EXEC sp_addmergepullsubscription_agent @publisher = %Publisher%, @publisher_db = %PubDb%, @publication = %PubName%, @subscriber = %Subscriber%, @subscriber_db = %SubDb%, @distributor = %Publisher%"  
  
REM -- This batch file starts the merge agent at the Subscriber to   
REM -- synchronize a pull subscription to a merge publication.  
REM -- The following must be supplied on one line.  
"\Program Files\Microsoft SQL Server\130\COM\REPLMERG.EXE"  -Publisher  %Publisher% -Subscriber  %Subscriber%  -Distributor %Publisher%  -PublisherDB  %PubDb% -SubscriberDB %SubDb% -Publication %PubName% -PublisherSecurityMode 1 -OutputVerboseLevel 1  -Output  -SubscriberSecurityMode 1  -SubscriptionType 1 -DistributorSecurityMode 1 -Validate 3

编写常见复制任务脚本

下面是一些可使用系统存储过程来编写脚本的最常见复制任务：

配置发布和分发
修改发布服务器和分发服务器属性
禁用发布和分发
创建发布及定义项目
删除发布和项目
创建请求订阅
修改请求订阅
删除请求订阅
创建推送订阅
修改推送订阅
删除推送订阅
同步请求订阅

另请参阅

复制编程概念
 复制存储过程（Transact-SQL）
编写复制脚本

通过