How to: Manage Partitions for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming)
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. 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:
How a partition is filtered (using SUSER_SNAME (Transact-SQL) or HOST_NAME (Transact-SQL)).
The name of the job that generates a partitioned snapshot.
The last time that a partitioned snapshot job ran.
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. For more information, see Snapshots for Merge Publications with Parameterized Filters.
Note
When a publication has parameterized filters that yield subscriptions with nonoverlapping partitions, you must do the following if a particular subscription is lost and needs to be re-created: remove the partition that was subscribed to, re-create the subscription, and then re-create the partition. Replication generates creation scripts for existing Subscriber partitions when a publication creation script is generated. For more information, see Scripting Replication.
To view information on existing partitions
- At the Publisher on the publication database, execute sp_helpmergepartition (Transact-SQL). Specify the name of the publication for @publication. (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
At the Publisher on the publication database, execute sp_addmergepartition (Transact-SQL). Specify the name of the publication for @publication, and the parameterized value that defines the partition for one of the following:
@suser_sname - when the parameterized filter is defined by the value returned by SUSER_SNAME (Transact-SQL).
@host_name - when the parameterized filter is defined by the value returned by HOST_NAME (Transact-SQL).
Create and initialize the parameterized snapshot for this new partition. For more information, see How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming).
To delete a partition
At the Publisher on the publication database, execute sp_dropmergepartition (Transact-SQL). Specify the name of the publication for @publication and the parameterized value that defines the partition for one of the following:
@suser_sname - when the parameterized filter is defined by the value returned by SUSER_SNAME (Transact-SQL).
@host_name - when the parameterized filter is defined by the value returned by HOST_NAME (Transact-SQL).
This also removes the snapshot job and any snapshot files for the partition.