复制系统存储过程概念

2017-03-06

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

脚本复制任务具有以下优势：

保留用于部署复制拓扑的步骤的永久副本。
使用单个脚本配置多个订阅服务器。
通过使新数据库管理员能够评估、了解、更改或排查代码问题，快速培训新数据库管理员。

重要

脚本可以是安全漏洞的来源;他们可以在没有用户知识或干预的情况下调用系统函数，并且可以在纯文本中包含安全凭据。在使用这些脚本之前，请查看有关安全问题的脚本。

创建复制脚本

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

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

手动创建脚本。
使用复制向导中提供的脚本生成功能或
SQL Server Management Studio。有关详细信息，请参阅脚本复制。
使用复制管理对象（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'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

然后，此脚本可以保存在本地 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\120\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）
脚本复制

通过