Manage Partitions for a Merge Publication with Parameterized Filters
Applies to:
SQL Server
This topic describes how to manage partitions for a merge publication with parameterized filters in SQL Server by using SQL Server Management Studio, Transact-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:
To manage partitions for a merge publication with parameterized filters, using:
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. For 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. For more information, see Scripting Replication.
Using SQL Server Management Studio
Manage partitions on the Data Partitions page of the Publication Properties - <Publication> dialog box. For 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
On the Data Partitions page of the Publication Properties - <Publication> dialog box, click Add.
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.
Optionally specify a schedule for refreshing snapshots:
Select Schedule the Snapshot Agent for this partition to run at the following time(s)
Accept the default schedule for refreshing snapshots, or click Change to specify a different schedule.
Select OK.
To delete a partition
On the Data Partitions page, select a partition in the grid.
Click Delete.
To allow Subscribers to initiate snapshot generation and delivery
On the Data Partitions page, select Automatically define a partition and generate a snapshot if needed when a new Subscriber tries to synchronize.
Select OK.
To generate a snapshot for a partition
On the Data Partitions page, select a partition in the grid.
Click Generate the selected snapshots now.
To clean up a snapshot for a partition
On the Data Partitions page, select a partition in the grid.
Click Clean up the existing snapshots.
Using 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:
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.
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 Create a Snapshot for a Merge Publication with Parameterized Filters.
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.
Using Replication Management Objects (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
Create a connection to the Publisher by using the ServerConnection class.
Create an instance of the MergePublication class. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the ServerConnection created in step 1.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.
Call the EnumMergePartitions method, and pass the result to an array of MergePartition objects.
For each MergePartition object in the array, get any properties of interest.
To delete existing partitions
Create a connection to the Publisher by using the ServerConnection class.
Create an instance of the MergePublication class. Set the Name and DatabaseName properties for the publication, and set the ConnectionContext property to the ServerConnection created in step 1.
Call the LoadProperties method to get the properties of the object. If this method returns false, either the publication properties in step 2 were defined incorrectly or the publication does not exist.
Call the EnumMergePartitions method, and pass the result to an array of MergePartition objects.
For each MergePartition object in the array, determine whether the partition should be deleted. This decision is usually based on the value of the DynamicFilterLogin property or the DynamicFilterHostName property.
Call the RemoveMergePartition method on the MergePublication object from step 2. Pass the MergePartition object from step 5.
Repeat step 6 for each partition that is deleted.
See Also
Parameterized Row Filters
Snapshots for Merge Publications with Parameterized Filters