如何为带有参数化筛选器的合并发布创建快照（复制 Transact-SQL 编程）

在使用参数化筛选器为发布生成快照时，必须先生成一个包含所有已发布数据和订阅的订阅服务器元数据的标准快照或架构快照。有关详细信息，请参阅如何创建初始快照（复制 Transact-SQL 编程）。创建完架构快照后，便可生成包含特定于订阅服务器的已发布数据分区的快照。

使用存储过程和快照代理，可执行以下操作：

• 允许订阅服务器在第一次同步时请求快照生成和应用。

• 为每个分区预生成快照。

• 手动为每个订阅服务器生成快照。

  安全说明   请尽可能提示用户在运行时输入安全凭据。如果必须在脚本文件中存储凭据，则必须保护文件以防止未经授权的访问。

创建允许订阅服务器启动快照生成和传递的发布

1. 在发布服务器上，对发布数据库执行 sp_addmergepublication (Transact-SQL)。指定下列参数：

   • 将 @publication 指定为发布的名称。

   • 将 true 值指定为 @allow_subscriber_initiated_snapshot，这样可使订阅服务器启动快照进程。

   • （可选）将 @max_concurrent_dynamic_snapshots 指定为可并发运行的动态快照进程数。如果正在运行的进程数达到了最大值，并且订阅服务器尝试生成快照，则该进程将被置于队列中。默认情况下，并发进程的数量不受限制。

2. 在发布服务器中，执行 sp_addpublication_snapshot (Transact-SQL)。将 @publication 指定为在步骤 1 中使用的发布名称，并将 @job_login 和 @password 指定为用于运行复制快照代理的 Microsoft Windows 凭据。如果代理在连接到发布服务器时将使用 SQL Server 身份验证，则还必须将 @publisher_security_mode 的值指定为 0，并为 @publisher_login 和 @publisher_password 指定 Microsoft SQL Server 登录信息。此操作将为发布创建一个快照代理作业。有关生成初始快照以及为快照代理定义自定义计划的详细信息，请参阅如何创建初始快照（复制 Transact-SQL 编程）。

   安全说明
   使用远程分发服务器配置发布服务器时，为所有参数提供的值（包括 job_login 和 job_password）都会以纯文本方式发送到该分发服务器。在执行此存储过程之前，应该对发布服务器及其远程分发服务器之间的连接进行加密。有关详细信息，请参阅加密与 SQL Server 的连接。

3. 执行 sp_addmergearticle (Transact-SQL) 以向发布添加项目。必须对发布中的每个项目执行一次此存储过程。使用参数化筛选器时，必须使用 @subset_filterclause 参数为一个或多个项目指定参数化行筛选器。有关详细信息，请参阅如何为合并项目定义和修改参数化行筛选器（复制 Transact-SQL 编程）。

4. 如果将基于该参数化行筛选器筛选其他项目，请执行 sp_addmergefilter (Transact-SQL) 以定义项目间的联接或逻辑记录关系。必须对要定义的每个关系执行一次此存储过程。有关详细信息，请参阅如何定义和修改合并项目之间的联接筛选器（复制 Transact-SQL 编程）。

5. 当合并代理请求快照初始化订阅服务器时，将自动生成请求订阅的分区的快照。

创建发布并预生成或自动刷新快照

1. 执行 sp_addmergepublication (Transact-SQL) 以创建发布。有关详细信息，请参阅如何创建发布（复制 Transact-SQL 编程）。

2. 在发布服务器中，执行 sp_addpublication_snapshot (Transact-SQL)。指定在步骤 1 中对 @publication 使用的发布名称以及快照代理针对 @job_login 和 @password 运行的 Windows 凭据。如果代理在连接到发布服务器时将使用 SQL Server 身份验证，则还必须将 @publisher_security_mode 的值指定为 0，并为 @publisher_login 和 @publisher_password 指定 SQL Server 登录信息。此操作将为发布创建一个快照代理作业。有关生成初始快照以及为快照代理定义自定义计划的详细信息，请参阅如何创建初始快照（复制 Transact-SQL 编程）。

   安全说明
   使用远程分发服务器配置发布服务器时，为所有参数提供的值（包括 job_login 和 job_password）都会以纯文本方式发送到该分发服务器。在执行此存储过程之前，应该对发布服务器及其远程分发服务器之间的连接进行加密。有关详细信息，请参阅加密与 SQL Server 的连接。

3. 执行 sp_addmergearticle (Transact-SQL) 以向发布添加项目。必须对发布中的每个项目执行一次此存储过程。使用参数化筛选器时，必须使用 @subset_filterclause 参数为一个项目指定参数化行筛选器。有关详细信息，请参阅如何为合并项目定义和修改参数化行筛选器（复制 Transact-SQL 编程）。

4. 如果将基于该参数化行筛选器筛选其他项目，请执行 sp_addmergefilter (Transact-SQL) 以定义项目间的联接或逻辑记录关系。必须对要定义的每个关系执行一次此存储过程。有关详细信息，请参阅如何定义和修改合并项目之间的联接筛选器（复制 Transact-SQL 编程）。

5. 在发布数据库的发布服务器上，执行 sp_helpmergepublication (Transact-SQL)，将 @publication 指定为步骤 1 中得到的值。请注意结果集中 snapshot_jobid 的值。

6. 将步骤 5 中得到的 snapshot_jobid 的值转换为 uniqueidentifier。

7. 在 msdb 数据库的发布服务器上，执行 sp_start_job (Transact-SQL)，将 @job_id 指定为在步骤 6 中得到的转换后的值。

8. 在发布服务器的发布数据库中，执行 sp_addmergepartition (Transact-SQL)。将 @publication 指定为来自步骤 1 的发布名称，并将 @suser_sname（如果在筛选子句中使用 SUSER_SNAME (Transact-SQL)）或 @host_name（如果在筛选子句中使用 HOST_NAME (Transact-SQL)）指定为用于定义分区的值。

9. 在发布数据库的发布服务器上，执行 sp_adddynamicsnapshot_job (Transact-SQL)。将 @publication 指定为来自步骤 1 的发布名称，并将 @suser_sname 或 @host_name 指定为来自步骤 8 的值，同时为作业指定一个计划。此操作将创建为指定分区生成参数化快照的作业。有关详细信息，请参阅如何指定同步计划（复制 Transact-SQL 编程）。

   注意
   此作业运行时使用的 Windows 帐户与步骤 2 中定义的初始快照作业相同。若要删除参数化快照作业及其相关数据分区，请执行 sp_dropdynamicsnapshot_job (Transact-SQL)。

10. 在发布数据库的发布服务器上，执行 sp_helpmergepartition (Transact-SQL)，将 @publication 的值指定为来自步骤 1 的值，将 @suser_sname 或 @host_name 的值指定为来自步骤 8 的值。请注意结果集中 dynamic_snapshot_jobid 的值。

11. 在 msdb 数据库的分发服务器上，执行 sp_start_job (Transact-SQL)，将 @job_id 指定为在步骤 9 中获得的值。此操作将启动分区的参数化快照作业。

12. 重复步骤 8-11，分别为每个订阅生成一个分区快照。

创建发布并为每个分区手动创建快照

1. 执行 sp_addmergepublication (Transact-SQL) 以创建发布。有关详细信息，请参阅如何创建发布（复制 Transact-SQL 编程）。

2. 在发布服务器中，执行 sp_addpublication_snapshot (Transact-SQL)。指定在步骤 1 中对 @publication 使用的发布名称以及快照代理针对 @job_login 和 @password 运行的 Windows 凭据。如果代理在连接到发布服务器时将使用 SQL Server 身份验证，则还必须将 @publisher_security_mode 的值指定为 0，并为 @publisher_login 和 @publisher_password 指定 SQL Server 登录信息。此操作将为发布创建一个快照代理作业。有关生成初始快照以及为快照代理定义自定义计划的详细信息，请参阅如何创建初始快照（复制 Transact-SQL 编程）。

   安全说明
   使用远程分发服务器配置发布服务器时，为所有参数提供的值（包括 job_login 和 job_password）都会以纯文本方式发送到该分发服务器。在执行此存储过程之前，应该对发布服务器及其远程分发服务器之间的连接进行加密。有关详细信息，请参阅加密与 SQL Server 的连接。

3. 执行 sp_addmergearticle (Transact-SQL) 以向发布添加项目。必须对发布中的每个项目执行一次此存储过程。使用参数化筛选器时，必须使用 @subset_filterclause 参数为至少一个项目指定参数化行筛选器。有关详细信息，请参阅如何为合并项目定义和修改参数化行筛选器（复制 Transact-SQL 编程）。

4. 如果将基于该参数化行筛选器筛选其他项目，请执行 sp_addmergefilter (Transact-SQL) 以定义项目间的联接或逻辑记录关系。必须对要定义的每个关系执行一次此存储过程。有关详细信息，请参阅如何定义和修改合并项目之间的联接筛选器（复制 Transact-SQL 编程）。

5. 启动快照作业或从命令提示符下运行复制快照代理，以生成标准快照架构及其他文件。有关详细信息，请参阅如何创建初始快照（复制 Transact-SQL 编程）。

6. 再次从命令提示符下运行复制快照代理以生成大容量复制 (.bcp) 文件，为 -DynamicSnapshotLocation 指定分区快照的位置，同时指定用于定义分区的以下一个或两个属性：

   • -DynamicFilterHostName - 使用 HOST_NAME (Transact-SQL) 时的值。

   • -DynamicFilterLogin - 使用 SUSER_SNAME (Transact-SQL) 时的值。

7. 重复步骤 6，分别为每个订阅生成一个分区快照。

8. 对每个订阅运行合并代理以在订阅服务器上应用初始分区快照，并指定以下属性：

   • -Hostname - 在 HOST_NAME 的实际值被覆盖时用于定义分区的值。

   • -DynamicSnapshotLocation - 此分区的动态快照的位置。

注意
有关复制代理编程的详细信息，请参阅复制代理可执行文件概念。

示例

此示例使用参数化筛选器创建合并发布，其中由订阅服务器启动快照生成进程。@job_login 和 @job_password 的值通过脚本变量进行传递。

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks2008R2';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks2008R2];

-- Enable AdventureWorks2008R2 for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication with Subscriber requested snapshot
-- and using the default agent schedule. 
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks2008R2.', 
  @allow_subscriber_initiated_snapshot = N'true',
  @publication_compatibility_level = N'90RTM';

-- Create a new snapshot job for the publication, using the default schedule.
-- Pass credentials at runtime using sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains salesperson information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the agent job to generate the full snapshot for the publication.
-- The filtered data snapshot is generated automatically the first time 
-- the subscription is synchronized. 
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

此示例使用参数筛选器创建发布，通过传递分区信息，其中的每个订阅服务器均有自己的分区（通过执行 sp_addmergepartition 进行定义）和经过筛选的快照作业（通过执行 sp_adddynamicsnapshot_job 来创建）。@job_login 和 @job_password 的值通过脚本变量进行传递。

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks2008R2';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks2008R2];

-- Enable AdventureWorks2008R2 for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication.  
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks2008R2.', 
  @allow_subscriber_initiated_snapshot = N'false';

-- Create a new snapshot job for the publication, using the 
-- default schedule. Pass credentials at runtime using 
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(Login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains customer information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the snapshot agent job.
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

PRINT '*** Waiting for the initial snapshot.';
GO

-- Create a temporary table to store the filtered data snapshot 
-- job information.
CREATE TABLE #temp (id int,
    job_name sysname,
    job_id uniqueidentifier,
    dynamic_filter_login sysname NULL,
    dynamic_filter_hostname sysname NULL,
    dynamic_snapshot_location nvarchar(255),
    frequency_type int, 
    frequency_interval int, 
    frequency_subday_type int,
    frequency_subday_interval int, 
    frequency_relative_interval int, 
    frequency_recurrence_factor int, 
    active_start_date int, 
    active_end_date int, 
    active_start_time int, 
    active_end_time int
)

-- Create each snapshot for a partition 
-- The initial snapshot must already be generated.
DECLARE @publication AS sysname;
DECLARE @jobname AS sysname
DECLARE @hostname AS sysname
SET @publication = N'AdvWorksSalesPersonMerge';
SET @hostname = N'adventure-works\Fernando';

WHILE NOT EXISTS(SELECT * FROM sysmergepublications 
    WHERE [name] = @publication 
    AND snapshot_ready = 1)
BEGIN
    WAITFOR DELAY '00:00:05'
END

-- Create a data partition by overriding HOST_NAME().
EXEC sp_addmergepartition 
  @publication = @publication,
  @host_name = @hostname;

-- Create the filtered data snapshot job, and use the returned 
-- information to start the job.
EXEC sp_adddynamicsnapshot_job 
  @publication = @publication,
  @host_name = @hostname;

INSERT INTO #temp (id, job_name, job_id, dynamic_filter_login,
    dynamic_filter_hostname, dynamic_snapshot_location,
    frequency_type, frequency_interval, frequency_subday_type,
    frequency_subday_interval, frequency_relative_interval, 
    frequency_recurrence_factor, active_start_date, active_end_date, 
    active_start_time,active_end_time)
EXEC sp_helpdynamicsnapshot_job;

SELECT @jobname = (SELECT DISTINCT job_name FROM #temp WHERE dynamic_filter_hostname = @hostname);

EXEC msdb..sp_start_job @job_name = @jobname;
DROP TABLE #temp;
GO

此示例使用参数化筛选器创建发布，其中每个订阅服务器都必须通过提供分区信息来创建它自己的数据分区和已筛选的快照作业。手动运行复制代理时，订阅服务器使用命令行参数提供分区信息。此示例假设还对发布创建了订阅。

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. 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".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks2008R2';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks2008R2];

-- Enable AdventureWorks2008R2 for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication.  
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks2008R2.', 
  @allow_subscriber_initiated_snapshot = N'false';

-- Create a new snapshot job for the publication, using the 
-- default schedule. Pass credentials at runtime using 
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(Login), 
  @job_password = $(Password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains customer information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[BusinessEntityID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

REM Line breaks are added to improve readability. 
REM In a batch file, commands must be made in a single line.
REM Run the Snapshot agent from the command line to generate the standard snapshot 
REM schema and other files. 
SET DistPub=%computername%
SET PubDB=AdventureWorks2008R2 
SET PubName=AdvWorksSalesPersonMerge

"C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE" -Publication %PubName%  
-Publisher %DistPub% -Distributor  %DistPub%  -PublisherDB %PubDB%  -ReplicationType 2  
-OutputVerboseLevel 1  -DistributorSecurityMode 1

PAUSE

REM Run the Snapshot agent from the command line, this time to generate 
REM the bulk copy (.bcp) data for each Subscriber partition.  
SET DistPub=%computername%
SET PubDB=AdventureWorks2008R2 
SET PubName=AdvWorksSalesPersonMerge
SET SnapshotDir=\\%DistPub%\repldata\unc\fernando

MD %SnapshotDir%

"C:\Program Files\Microsoft SQL Server\100\COM\SNAPSHOT.EXE" -Publication %PubName%  
-Publisher %DistPub%  -Distributor  %DistPub%  -PublisherDB %PubDB%  -ReplicationType 2  
-OutputVerboseLevel 1  -DistributorSecurityMode 1  -DynamicFilterHostName "adventure-works\Fernando"  
-DynamicSnapshotLocation %SnapshotDir%

PAUSE

REM Run the Merge Agent for each subscription to apply the partitioned 
REM snapshot for each Subscriber.  
SET Publisher = %computername%
SET Subscriber = %computername%
SET PubDB = AdventureWorks2008R2 
SET SubDB = AdventureWorks2008R2Replica 
SET PubName = AdvWorksSalesPersonMerge 
SET SnapshotDir=\\%DistPub%\repldata\unc\fernando

"C:\Program Files\Microsoft SQL Server\100\COM\REPLMERG.EXE" -Publisher  %Publisher%  
-Subscriber  %Subscriber%  -Distributor %Publisher%  -PublisherDB %PubDB%  
-SubscriberDB %SubDB% -Publication %PubName%  -PublisherSecurityMode 1  -OutputVerboseLevel 3  
-Output -SubscriberSecurityMode 1  -SubscriptionType 3 -DistributorSecurityMode 1  
-Hostname "adventure-works\Fernando"  -DynamicSnapshotLocation %SnapshotDir%

PAUSE

请参阅

任务

如何创建具有参数化筛选器的合并发布的快照（RMO 编程）

如何创建初始快照（复制 Transact-SQL 编程）

概念

参数化行筛选器

复制系统存储过程概念

带有参数化筛选器的合并发布的快照