Create a Snapshot for a Merge Publication with Parameterized Filters

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

This topic describes how to create a snapshot for a merge publication with parameterized filters in SQL Server 2016 by using SQL Server Management Studio, Transact-SQL, or Replication Management Objects (RMO).

Recommendations

When generating a snapshot for a merge publication using parameterized filters, you must first generate a standard (schema) snapshot that contains all of the published data and Subscriber metadata for the subscription. For more information, see Create and Apply the Initial Snapshot. After you have created the schema snapshot, you can generate the snapshot that contains the Subscriber-specific partition of the published data.

If the filtering for one or more articles in the publication yields non-overlapping partitions that are unique for each subscription, metadata is cleaned up whenever the Merge Agent runs. This means that the partitioned snapshot expires more quickly. When using this option, you should consider allowing Subscribers to initiate snapshot generation and delivery. For more information about filtering options, see the "Setting 'partition options'" section of Snapshots for Merge Publications with Parameterized Filters.

Generate snapshots for 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. You can allow Subscribers to initiate snapshot generation and delivery and/or generate snapshots.

Generate a schema snapshot for the publication. By default, a schema snapshot is generated when you complete the New Publication Wizard; you can also generate a schema snapshot from SQL Server Management Studio.

To generate a schema snapshot

Connect to the Publisher in Management Studio, and then expand the server node.

Expand the Replication folder, and then expand the Publications folder.

Right-click the publication for which you want to create a snapshot, and then click View Snapshot Agent Status.

When the Snapshot Agent finishes generating the snapshot, a message will be displayed, such as "[100%] A snapshot of 17 article(s) was generated."

To allow Subscribers to initiate snapshot generation and delivery

On the Data Partitions page of the Publication Properties - <Publication> dialog box, select Automatically define a partition and generate a snapshot if needed when a new Subscriber tries to synchronize.

To create a publication that allows Subscribers to initiate snapshot generation and delivery

A value of true for @allow_subscriber_initiated_snapshot, which enables Subscribers to initiate the snapshot process.

(Optional) The number of dynamic snapshot processes that can run concurrently for @max_concurrent_dynamic_snapshots. If the maximum number of processes is running and a Subscriber attempts to generate a snapshot, the process is placed in a queue. By default there is no limit to the number of concurrent processes.

At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 1 for @publication and the Microsoft Windows credentials under which the Replication Snapshot Agent runs for @job_login and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the Microsoft SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication. For more information about generating an initial snapshot and defining a custom schedule for the Snapshot Agent, see Create and Apply the Initial Snapshot.

To create a publication and pre-generate or automatically refresh snapshots

At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 1 for @publication and the Windows credentials under which the Snapshot Agent runs for @job_login and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication. For more information about generating an initial snapshot and defining a custom schedule for the Snapshot Agent, see Create and Apply the Initial Snapshot.

At the publisher on the publication database, execute sp_adddynamicsnapshot_job (Transact-SQL). Specify the name of the publication from step 1 for @publication, the value of @suser_sname or @host_name from step 8, and a schedule for the job. This creates the job that generates the parameterized snapshot for the specified partition. For more information, see Specify Synchronization Schedules.

Note

This job runs using the same Windows account as the initial snapshot job defined in step 2. To remove the parameterized snapshot job and its related data partition, execute sp_dropdynamicsnapshot_job (Transact-SQL).

At the Publisher on the publication database, execute sp_helpmergepartition (Transact-SQL), specifying the value of @publication from step 1 and the value of @suser_sname or @host_name from step 8. Note the value of the dynamic_snapshot_jobid in the result set.

At the Distributor on the msdb database, execute sp_start_job (Transact-SQL), specifying the value obtained in step 9 for @job_id. This starts the parameterized snapshot job for the partition.

Repeat steps 8-11 to generate a partitioned snapshot for each subscription.

To create a publication and manually create snapshots for each partition

At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 1 for @publication and the Windows credentials under which the Snapshot Agent runs for @job_login and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the SQL Server login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication. For more information about generating an initial snapshot and defining a custom schedule for the Snapshot Agent, see Create and Apply the Initial Snapshot.

Start the snapshot job or run the Replication Snapshot Agent from the command prompt to generate the standard snapshot schema and other files. For more information, see Create and Apply the Initial Snapshot.

Run the Replication Snapshot Agent again from the command prompt to generate bulk copy (.bcp) files, specifying the location of the partitioned snapshot for -DynamicSnapshotLocation and one or both of the following properties that defines the partition:

Examples (Transact-SQL)

This example creates a merge publication with parameterized filters where Subscribers initiate the snapshot generation process. Values for @job_login and @job_password are passed in using scripting variables.

This example creates a publication using a parameterized filter where each Subscriber has its partition defined by executing sp_addmergepartition and the filtered snapshot job created by executing sp_adddynamicsnapshot_job passing the partitioning information. Values for @job_login and @job_password are passed in using scripting variables.

This example creates a publication using a parameterized filter where each Subscriber must have its data partition and filtered snapshot job created by supplying the partitioning information. A Subscriber supplies partitioning information using command-line parameters when manually running the replication agents. This example assumes that a subscription to the publication has also been created.

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=AdventureWorks2012
SET PubName=AdvWorksSalesPersonMerge
"C:\Program Files\Microsoft SQL Server\120\COM\SNAPSHOT.EXE" -Publication %PubName%
-Publisher %DistPub% -Distributor %DistPub% -PublisherDB %PubDB% -ReplicationType 2
-OutputVerboseLevel 1 -DistributorSecurityMode 1
PAUSE

You can use Replication Management Objects (RMO) to generate partitioned snapshots programmatically in the following ways:

Allow Subscribers to request snapshot generation and application the first time they synchronize.

Pre-generate snapshots for each partition.

Manually generate a snapshot for each Subscriber by running the Snapshot Agent.

Note

When filtering for an article yields non-overlapping partitions that are unique for each subscription (by specifying a value of NonOverlappingSingleSubscription for PartitionOption when creating a merge article), metadata is cleaned up whenever the Merge Agent runs. This means that the partitioned snapshot expires more quickly. When you use this option, you should consider allowing Subscribers to request snapshot generation. For more information, see the section Using the Appropriate Filtering Options in the topic Parameterized Row Filters.

Security Note

When possible, prompt users to enter security credentials at runtime. If you must store credentials, use the cryptographic services provided by the Microsoft Windows .NET Framework.

To create a publication that allows Subscribers to initiate snapshot generation and delivery

The maximum number of dynamic snapshot jobs to run for MaxConcurrentDynamicSnapshots. Because Subscriber initiated snapshot requests can occur at any time, this property limits the number of Snapshot Agent jobs that can run simultaneously when multiple Subscribers request their partitioned snapshot at the same time. When the maximum number of jobs are running, additional partitioned snapshot requests are queued until one of the running jobs is completed.

Use the MergeArticle property to add articles to the publication. Specify the FilterClause property for at least one article that defines the parameterized filter. (Optional) Create MergeJoinFilter objects that define join filters between articles. For more information, see Define an Article.

To create a publication and pregenerate or automatically refresh snapshots

Use the MergeArticle property to add articles to the publication. Specify the FilterClause property for at least one article that defines the parameterized filter, and create any MergeJoinFilter objects that define join filters between articles. For more information, see Define an Article.

To create a publication and manually create snapshots for each partition

Use the MergeArticle property to add articles to the publication Specify the FilterClause property for at least one article that defines the parameterized filter, and create any MergeJoinFilter objects that define join filters between articles. For more information, see Define an Article.