Manage Partitions for a Merge Publication with Parameterized Filters

  • Article

This topic describes how to manage partitions for a merge publication with parameterized filters in SQL Server 2012 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:

    Recommendations

  • To manage partitions for a merge publication with parameterized filters, using:

    SQL Server Management Studio

    Transact-SQL

    Replication Management Objects (RMO)

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.

Arrow icon used with Back to Top link [Top]

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

  1. On the Data Partitions page of the Publication Properties - <Publication> dialog box, click Add.

  2. 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.

Arrow icon used with Back to Top link [Top]

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:

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

  1. 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:

  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

  • 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:

    This also removes the snapshot job and any snapshot files for the partition.

Arrow icon used with Back to Top link [Top]

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

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. 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.

  3. 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.

  4. Call the EnumMergePartitions method, and pass the result to an array of MergePartition objects.

  5. For each MergePartition object in the array, get any properties of interest.

To delete existing partitions

  1. Create a connection to the Publisher by using the ServerConnection class.

  2. 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.

  3. 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.

  4. Call the EnumMergePartitions method, and pass the result to an array of MergePartition objects.

  5. 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.

  6. Call the RemoveMergePartition method on the MergePublication object from step 2. Pass the MergePartition object from step 5.

  7. Repeat step 6 for each partition that is deleted.

Arrow icon used with Back to Top link [Top]

See Also

Concepts

Parameterized Row Filters

Snapshots for Merge Publications with Parameterized Filters