通过参数化筛选器为合并发布管理分区Manage Partitions for a Merge Publication with Parameterized Filters

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

本主题说明如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio或复制管理对象 (RMO) 在 Transact-SQLTransact-SQL中通过参数化筛选器为合并发布管理分区。This topic describes how to manage partitions for a merge publication with parameterized filters in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or Replication Management Objects (RMO). 参数化行筛选器可用于生成不重叠的分区。Parameterized row filters can be used to generate nonoverlapping partitions. 可以限制这些分区,以便只有一个订阅接收到给定分区。These partitions can be restricted so that only one subscription receives a given partition. 在这类情况下,大量订阅服务器将导致大量的分区,从而需要同等数量的分区快照。In these cases, a large number of subscribers will result in a large number of partitions, which in turn requires an equal number of partitioned snapshots. 有关详细信息,请参阅 参数化行筛选器For more information, see Parameterized Row Filters.

本主题内容In This Topic

开始之前Before You Begin

建议Recommendations

  • 如果对复制拓扑编写脚本(建议这样做),则发布脚本包含用于创建数据分区的存储过程调用。If you script a replication topology, which is recommended, publication scripts contain the stored procedure calls to create data partitions. 该脚本提供了对所创建的分区的引用和一种在必要时重建分区的途径。The script provides a reference for the partitions created and a way in which to re-create one or more partitions if necessary. 有关详细信息,请参阅 Scripting ReplicationFor more information, see Scripting Replication.

  • 如果发布具有的参数化筛选器可生成带有非重叠分区的订阅,并且如果特定订阅丢失并需要重新创建,则您必须执行以下操作:删除曾订阅的分区,重新创建订阅,然后重新创建该分区。When a publication has parameterized filters that yield subscriptions with nonoverlapping partitions, and if a particular subscription is lost and needs to be re-created, you must do the following: remove the partition that was subscribed to, re-create the subscription, and then re-create the partition. 有关详细信息,请参阅 参数化行筛选器For more information, see Parameterized Row Filters. 生成发布创建脚本时,复制会为现有订阅服务器分区生成创建脚本。Replication generates creation scripts for existing Subscriber partitions when a publication creation script is generated. 有关详细信息,请参阅 Scripting ReplicationFor more information, see Scripting Replication.

使用 SQL Server Management StudioUsing SQL Server Management Studio

可在“发布属性 - <Publication>”对话框的“数据分区”页面上管理分区 。Manage partitions on the Data Partitions page of the Publication Properties - <Publication> dialog box. 有关访问此对话框的详细信息,请参阅 View and Modify Publication PropertiesFor more information about accessing this dialog box, see View and Modify Publication Properties. 在此页上,可以执行下列操作:创建和删除分区、允许订阅服务器启动快照的生成和传递、生成一个或多个分区的快照和清除快照。On this page you can: create and delete partitions; allow Subscribers to initiate snapshot generation and delivery; generate snapshots for one or more partitions; and clean up snapshots.

创建分区To create a partition

  1. 在“发布属性 - <Publication>”对话框的“数据分区”页面上,单击“添加” 。On the Data Partitions page of the Publication Properties - <Publication> dialog box, click Add.

  2. “添加数据分区” 对话框中,输入与要创建的分区相关联的 HOST_NAME() 和/或 SUSER_SNAME() 的值。In the Add Data Partition dialog box, enter a value for the HOST_NAME() and/or SUSER_SNAME() value associated with the partition you want to create.

  3. 还可以指定快照刷新计划:Optionally specify a schedule for refreshing snapshots:

    1. 选择 “安排此分区的快照代理在以下时间运行”Select Schedule the Snapshot Agent for this partition to run at the following time(s)

    2. 接受默认的快照刷新计划,或者单击 “更改” 以指定其他计划。Accept the default schedule for refreshing snapshots, or click Change to specify a different schedule.

  4. 单击“确定”。 Click OK.

删除分区To delete a partition

  1. “数据分区” 页上,在网格中选择分区。On the Data Partitions page, select a partition in the grid.

  2. 单击 “删除”Click Delete.

允许订阅服务器启动快照的生成和传递To allow Subscribers to initiate snapshot generation and delivery

  1. “数据分区” 页上,选择 “在新订阅服务器尝试同步时,根据需要自动定义分区并生成快照”On the Data Partitions page, select Automatically define a partition and generate a snapshot if needed when a new Subscriber tries to synchronize.

  2. 单击“确定”。 Click OK.

生成分区快照To generate a snapshot for a partition

  1. “数据分区” 页上,在网格中选择分区。On the Data Partitions page, select a partition in the grid.

  2. 单击 “立即生成所选快照”Click Generate the selected snapshots now.

清除分区快照To clean up a snapshot for a partition

  1. “数据分区” 页上,在网格中选择分区。On the Data Partitions page, select a partition in the grid.

  2. 单击 “清除现有快照”Click Clean up the existing snapshots.

使用 Transact-SQLUsing Transact-SQL

为了更好地管理带有参数化筛选器的发布,可使用复制存储过程以编程方式枚举现有的分区。To better manage a publication with parameterized filters, you can programmatically enumerate the existing partitions using replication stored procedures. 还可以创建和删除现有的分区。You can also create and delete existing partitions. 可以获取以下有关现有分区的信息:The following information on existing partitions can be obtained:

尽管在初始化新订阅时可以按需生成由两部分构成的快照的第二部分,但通过下面的过程可以控制此快照的生成方式,并在最方便的时候预生成该快照。While the second part of the two-part snapshot can be generated on-demand when a new subscription is initialized, the procedures below enable you to control how this snapshot is generated and to pre-generate this snapshot when it is most convenient. 有关详细信息,请参阅 Snapshots for Merge Publications with Parameterized FiltersFor more information, see Snapshots for Merge Publications with Parameterized Filters.

查看有关现有分区的信息To view information on existing partitions

  1. 在发布服务器上,对发布数据库执行 sp_helpmergepartition (Transact-SQL)At the Publisher on the publication database, execute sp_helpmergepartition (Transact-SQL). @publication 指定发布名称。Specify the name of the publication for @publication. (可选)指定 @suser_sname@host_name 以仅根据单个筛选条件返回信息。(Optional) Specify @suser_sname or @host_name to return only information based on a single filtering criterion.

定义新分区并生成新分区快照To define a new partition and generate a new partitioned snapshot

  1. 在发布服务器上,对发布数据库执行 sp_addmergepartition (Transact-SQL)At the Publisher on the publication database, execute sp_addmergepartition (Transact-SQL). @publication 指定发布名称,并为以下某个参数指定用于定义分区的参数化值:Specify the name of the publication for @publication, and the parameterized value that defines the partition for one of the following:

  2. 创建并初始化此新分区的参数化快照。Create and initialize the parameterized snapshot for this new partition. 有关详细信息,请参阅 为包含参数化筛选器的合并发布创建快照For more information, see Create a Snapshot for a Merge Publication with Parameterized Filters.

删除分区To delete a partition

  1. 在发布服务器上,对发布数据库执行 sp_dropmergepartition (Transact-SQL)At the Publisher on the publication database, execute sp_dropmergepartition (Transact-SQL). @publication 指定发布的名称,并为以下某个参数指定用于定义分区的参数化值:Specify the name of the publication for @publication and the parameterized value that defines the partition for one of the following:

    此操作还将删除快照作业以及该分区的任何快照文件。This also removes the snapshot job and any snapshot files for the partition.

使用复制管理对象 (RMO)Using Replication Management Objects (RMO)

若要更好地管理具有参数化筛选器的发布,可以通过使用复制管理对象 (RMO) 以编程方式创建新的订阅服务器分区,枚举现有的订阅服务器分区以及删除订阅服务器分区。To better manage a publication with parameterized filters, you can programmatically create new Subscriber partitions, enumerate the existing Subscriber partitions, and delete Subscriber partitions by using Replication Management Objects (RMO). 有关如何创建订阅服务器分区的信息,请参阅 为包含参数化筛选器的合并发布创建快照For information about how to create Subscriber partitions, see Create a Snapshot for a Merge Publication with Parameterized Filters. 可以获得有关现有分区的以下信息:The following information about existing partitions can be obtained:

  • 分区所基于的值和筛选函数。The value and filtering function upon which the partition is based.

  • 为订阅服务器生成参数化快照的作业的名称。The name of the job that generates a parameterized snapshot for the Subscriber.

  • 参数化快照作业上次运行的时间。The last time that a parameterized snapshot job ran.

查看有关现有分区的信息To view information on existing partitions

  1. 使用 ServerConnection 类创建与发布服务器的连接。Create a connection to the Publisher by using the ServerConnection class.

  2. 创建的 MergePublication 类的实例。Create an instance of the MergePublication class. 设置发布的 NameDatabaseName 属性,并将 ConnectionContext 属性设置为步骤 1 中创建的 ServerConnectionSet the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the ServerConnection created in step 1.

  3. 调用 LoadProperties 方法获取该对象的属性。Call the LoadProperties method to get the properties of the object. 如果此方法返回 false,则说明步骤 2 中的发布属性定义不正确,或者此发布不存在。If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. 调用 EnumMergePartitions 方法,然后将结果传递至 MergePartition 对象数组。Call the EnumMergePartitions method, and pass the result to an array of MergePartition objects.

  5. 对数组中的每个 MergePartition 对象,获取感兴趣的任何属性。For each MergePartition object in the array, get any properties of interest.

删除现有分区To delete existing partitions

  1. 使用 ServerConnection 类创建与发布服务器的连接。Create a connection to the Publisher by using the ServerConnection class.

  2. 创建的 MergePublication 类的实例。Create an instance of the MergePublication class. 设置发布的 NameDatabaseName 属性,并将 ConnectionContext 属性设置为步骤 1 中创建的 ServerConnectionSet the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the ServerConnection created in step 1.

  3. 调用 LoadProperties 方法获取该对象的属性。Call the LoadProperties method to get the properties of the object. 如果此方法返回 false,则说明步骤 2 中的发布属性定义不正确,或者此发布不存在。If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.

  4. 调用 EnumMergePartitions 方法,然后将结果传递至 MergePartition 对象数组。Call the EnumMergePartitions method, and pass the result to an array of MergePartition objects.

  5. 对数组中的每个 MergePartition 对象,确定是否应删除分区。For each MergePartition object in the array, determine whether the partition should be deleted. 此决定通常基于 DynamicFilterLogin 属性的值或 DynamicFilterHostName 属性的值。This decision is usually based on the value of the DynamicFilterLogin property or the DynamicFilterHostName property.

  6. 在步骤 2 中的 RemoveMergePartition 对象上调用 MergePublication 方法。Call the RemoveMergePartition method on the MergePublication object from step 2. 传递步骤 5 中的 MergePartition 对象。Pass the MergePartition object from step 5.

  7. 对已删除的每个分区重复步骤 6。Repeat step 6 for each partition that is deleted.

另请参阅See Also

Parameterized Row Filters Parameterized Row Filters
Snapshots for Merge Publications with Parameterized FiltersSnapshots for Merge Publications with Parameterized Filters