How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming)

Article
12/03/2008

When generating snapshots for publications using parameterized filters, you must first generate a standard, or schema, snapshot that contains all of the published data and the Subscriber metadata for the subscription. For more information, see How to: Create the Initial Snapshot (Replication Transact-SQL Programming). After you have created the schema snapshot, you can generate the snapshot that contains the Subscriber-specific partition of the published data.

Using stored procedures and the Snapshot Agent, you can:

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.
Security Note When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

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

At the Publisher on the publication database, execute sp_addmergepublication (Transact-SQL). Specify the following parameters:
- The name of the publication for @publication.
- 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 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 How to: Create the Initial Snapshot (Replication Transact-SQL Programming).

Security Note:
When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

Execute sp_addmergearticle (Transact-SQL) to add articles to the publication. This stored procedure must be executed once for each article in the publication. When using parameterized filters, you must specify a parameterized row filter for one or more articles using the @subset_filterclause parameter. For more information, see How to: Define and Modify a Parameterized Row Filter for a Merge Article (Replication Transact-SQL Programming).
If other articles will be filtered based on the parameterized row filter, execute sp_addmergefilter (Transact-SQL) to define the join or logical record relationships between articles. This stored procedure must be executed once for each relationship being defined. For more information, see How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).
When the Merge Agent requests the snapshot to initialize the Subscriber, the snapshot for the requesting subscription's partition is automatically generated.

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

Execute sp_addmergepublication (Transact-SQL) to create the publication. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

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 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 How to: Create the Initial Snapshot (Replication Transact-SQL Programming).

Security Note:
When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

Execute sp_addmergearticle (Transact-SQL) to add articles to the publication. This stored procedure must be executed once for each article in the publication. When using parameterized filters, you must specify a parameterized row filter for one article using the @subset_filterclause parameter. For more information, see How to: Define and Modify a Parameterized Row Filter for a Merge Article (Replication Transact-SQL Programming).
If other articles will be filtered based on the parameterized row filter, execute sp_addmergefilter (Transact-SQL) to define the join or logical record relationships between articles. This stored procedure must be executed once for each relationship being defined. For more information, see How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).
At the Publisher on the publication database, execute sp_helpmergepublication (Transact-SQL), specifying the value of @publication from step 1. Note the value of the snapshot_jobid in the result set.
Convert the value of the snapshot_jobid obtained in step 5 to uniqueidentifier.
At the Publisher on the msdb database, execute sp_start_job (Transact-SQL), specifying the converted value obtained in step 6 for @job_id.
At the Publisher on the publication database, execute sp_addmergepartition (Transact-SQL). Specify the name of the publication from step 1 for @publication and the value used to define the partition for @suser_sname if SUSER_SNAME (Transact-SQL) is used in the filter clause or for @host_name if HOST_NAME (Transact-SQL) is used in the filter clause.
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 How to: Specify Synchronization Schedules (Replication Transact-SQL Programming).

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

Execute sp_addmergepublication (Transact-SQL) to create the publication. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

Security Note:
When configuring a Publisher with a remote Distributor, the values supplied for all parameters, including job_login and job_password, are sent to the Distributor as plain text. You should encrypt the connection between the Publisher and its remote Distributor before executing this stored procedure. For more information, see Encrypting Connections to SQL Server.

Execute sp_addmergearticle (Transact-SQL) to add articles to the publication. This stored procedure must be executed once for each article in the publication. When using parameterized filters, you must specify a parameterized row filter for at least one article using the @subset_filterclause parameter. For more information, see How to: Define and Modify a Parameterized Row Filter for a Merge Article (Replication Transact-SQL Programming).
If other articles will be filtered based on the parameterized row filter, execute sp_addmergefilter (Transact-SQL) to define the join or logical record relationships between articles. This stored procedure must be executed once for each relationship being defined. For more information, see How to: Define and Modify a Join Filter Between Merge Articles (Replication Transact-SQL Programming).
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 How to: Create the Initial Snapshot (Replication Transact-SQL Programming).
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:
- -DynamicFilterHostName - the value if HOST_NAME (Transact-SQL) is used.
- -DynamicFilterLogin - the value if SUSER_SNAME (Transact-SQL) is used.
Repeat step 6 to generate a partitioned snapshot for each subscription.
Run the Merge Agent for each subscription to apply the initial partitioned snapshot at the Subscribers, specifying the following properties:
- -Hostname - the value used to define the partition if the actual value of HOST_NAME is being overridden.
- -DynamicSnapshotLocation - the location of the dynamic snapshot for this partition.

Note

For more information about programming replication agents, see Programming Replication Agent Executables.

Example

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.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks];

-- Enable AdventureWorks for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication with Subscriber requested snapshot
-- and using the default agent schedule. 
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks.', 
  @allow_subscriber_initiated_snapshot = N'true',
  @publication_compatibility_level = N'90RTM';

-- Create a new snapshot job for the publication, using the default schedule.
-- Pass credentials at runtime using sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains salesperson information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[EmployeeID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the agent job to generate the full snapshot for the publication.
-- The filtered data snapshot is generated automatically the first time 
-- the subscription is synchronized. 
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

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.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks];

-- Enable AdventureWorks for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication.  
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks.', 
  @allow_subscriber_initiated_snapshot = N'false';

-- Create a new snapshot job for the publication, using the 
-- default schedule. Pass credentials at runtime using 
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(Login), 
  @job_password = $(password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains customer information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[EmployeeID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

-- Start the snapshot agent job.
DECLARE @publication AS sysname;
SET @publication = N'AdvWorksSalesPersonMerge';

EXEC sp_startpublication_snapshot 
  @publication = @publication;
GO

PRINT '*** Waiting for the initial snapshot.';
GO

-- Create a temporary table to store the filtered data snapshot 
-- job information.
CREATE TABLE #temp (id int,
    job_name sysname,
    job_id uniqueidentifier,
    dynamic_filter_login sysname NULL,
    dynamic_filter_hostname sysname NULL,
    dynamic_snapshot_location nvarchar(255),
    frequency_type int, 
    frequency_interval int, 
    frequency_subday_type int,
    frequency_subday_interval int, 
    frequency_relative_interval int, 
    frequency_recurrence_factor int, 
    active_start_date int, 
    active_end_date int, 
    active_start_time int, 
    active_end_time int
)

-- Create each snapshot for a partition 
-- The initial snapshot must already be generated.
DECLARE @publication AS sysname;
DECLARE @jobname AS sysname
DECLARE @hostname AS sysname
SET @publication = N'AdvWorksSalesPersonMerge';
SET @hostname = N'adventure-works\Fernando';

WHILE NOT EXISTS(SELECT * FROM sysmergepublications 
    WHERE [name] = @publication 
    AND snapshot_ready = 1)
BEGIN
    WAITFOR DELAY '00:00:05'
END

-- Create a data partition by overriding HOST_NAME().
EXEC sp_addmergepartition 
  @publication = @publication,
  @host_name = @hostname;

-- Create the filtered data snapshot job, and use the returned 
-- information to start the job.
EXEC sp_adddynamicsnapshot_job 
  @publication = @publication,
  @host_name = @hostname;

INSERT INTO #temp (id, job_name, job_id, dynamic_filter_login,
    dynamic_filter_hostname, dynamic_snapshot_location,
    frequency_type, frequency_interval, frequency_subday_type,
    frequency_subday_interval, frequency_relative_interval, 
    frequency_recurrence_factor, active_start_date, active_end_date, 
    active_start_time,active_end_time)
EXEC sp_helpdynamicsnapshot_job;

SELECT @jobname = (SELECT DISTINCT job_name FROM #temp WHERE dynamic_filter_hostname = @hostname);

EXEC msdb..sp_start_job @job_name = @jobname;
DROP TABLE #temp;
GO

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.

-- To avoid storing the login and password in the script file, the value 
-- is passed into SQLCMD as a scripting variable. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationdb AS sysname;
DECLARE @publication AS sysname;
DECLARE @table1 AS sysname;
DECLARE @table2 AS sysname;
DECLARE @filter AS sysname;
DECLARE @schema_hr AS sysname;
DECLARE @schema_sales AS sysname;

SET @publicationdb = N'AdventureWorks';
SET @publication = N'AdvWorksSalesPersonMerge';
SET @table1 = N'Employee';
SET @table2 = N'SalesPerson';
SET @filter = N'SalesPerson_Employee';
SET @schema_hr = N'HumanResources';
SET @schema_sales = N'Sales';

USE [AdventureWorks];

-- Enable AdventureWorks for merge replication.
EXEC sp_replicationdboption
  @dbname = @publicationdb,
  @optname = N'merge publish',
  @value = N'true';  

-- Create new merge publication.  
EXEC sp_addmergepublication 
  @publication = @publication, 
  @description = N'Merge publication of AdventureWorks.', 
  @allow_subscriber_initiated_snapshot = N'false';

-- Create a new snapshot job for the publication, using the 
-- default schedule. Pass credentials at runtime using 
-- sqlcmd scripting variables.
EXEC sp_addpublication_snapshot 
  @publication = @publication, 
  @job_login = $(Login), 
  @job_password = $(Password);

-- Add an article for the Employee table, 
-- which is horizontally partitioned using 
-- a parameterized row filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table1, 
  @source_owner = @schema_hr, 
  @source_object = @table1, 
  @type = N'table', 
  @description = 'contains employee information', 
  @subset_filterclause = N'[LoginID] = HOST_NAME()';

-- Add an article for the SalesPerson table, 
-- which is partitioned based on a join filter.
EXEC sp_addmergearticle 
  @publication = @publication, 
  @article = @table2, 
  @source_owner = @schema_sales, 
  @source_object = @table2, 
  @type = N'table', 
  @description = 'contains customer information';

-- Add a join filter between the two articles.
EXEC sp_addmergefilter 
  @publication = @publication, 
  @article = @table1, 
  @filtername = @filter, 
  @join_articlename = @table2, 
  @join_filterclause = N'[Employee].[EmployeeID] = [SalesPerson].[SalesPersonID]', 
  @join_unique_key = 1, 
  @filter_type = 1;
GO

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=AdventureWorks 
SET PubName=AdvWorksSalesPersonMerge

"C:\Program Files\Microsoft SQL Server\90\COM\SNAPSHOT.EXE" -Publication %PubName%  
-Publisher %DistPub% -Distributor  %DistPub%  -PublisherDB %PubDB%  -ReplicationType 2  
-OutputVerboseLevel 1  -DistributorSecurityMode 1

PAUSE

REM Run the Snapshot agent from the command line, this time to generate 
REM the bulk copy (.bcp) data for each Subscriber partition.  
SET DistPub=%computername%
SET PubDB=AdventureWorks 
SET PubName=AdvWorksSalesPersonMerge
SET SnapshotDir=\\%DistPub%\repldata\unc\fernando

MD %SnapshotDir%

"C:\Program Files\Microsoft SQL Server\90\COM\SNAPSHOT.EXE" -Publication %PubName%  
-Publisher %DistPub%  -Distributor  %DistPub%  -PublisherDB %PubDB%  -ReplicationType 2  
-OutputVerboseLevel 1  -DistributorSecurityMode 1  -DynamicFilterHostName "adventure-works\Fernando"  
-DynamicSnapshotLocation %SnapshotDir%

PAUSE

REM Run the Merge Agent for each subscription to apply the partitioned 
REM snapshot for each Subscriber.  
SET Publisher = %computername%
SET Subscriber = %computername%
SET PubDB = AdventureWorks 
SET SubDB = AdventureWorksReplica 
SET PubName = AdvWorksSalesPersonMerge 
SET SnapshotDir=\\%DistPub%\repldata\unc\fernando

"C:\Program Files\Microsoft SQL Server\90\COM\REPLMERG.EXE" -Publisher  %Publisher%  
-Subscriber  %Subscriber%  -Distributor %Publisher%  -PublisherDB %PubDB%  
-SubscriberDB %SubDB% -Publication %PubName%  -PublisherSecurityMode 1  -OutputVerboseLevel 3  
-Output -SubscriberSecurityMode 1  -SubscriptionType 3 -DistributorSecurityMode 1  
-Hostname "adventure-works\Fernando"  -DynamicSnapshotLocation %SnapshotDir%

PAUSE

Share via

How to: Create a Snapshot for a Merge Publication with Parameterized Filters (Replication Transact-SQL Programming)

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

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

To create a publication and manually create snapshots for each partition

Example

See Also

Tasks

Concepts

Other Resources

Help and Information

Additional resources