Parameterized Row Filters in SQL Server Merge Replication

By:   |   Comments (4)   |   Related: > Replication


I have been tasked with taking a table of sales data and replicating it to multiple locations with each Sales Territory's data going to a different location. At first I thought I would have to create a filtered publication for each SalesTerrritoryKey and hard code an ID into the filtered publication. This seems like it would be hard to manage with so many publications to maintain.


After further research, I learned merge replication has a feature called Parameterized Row Filtering that can be used to create a single publication and filter rows by either using SUSER_SNAME() or HOST_NAME() and even better you can override the value of HOST_NAME(). This seems like it would be a great fit for what I was trying to accomplish.

How does it work?

The way it works is it allows you to use a WHERE clause to select the appropriate data to be published rather than having to specify a literal value. Behind the scenes this creates "partitions" of your data that replication manages and these partitions can be overlapping (goes to multiple subscribers) or nonoverlapping (goes only to one subscriber). Nonoverlapping is less intrusive as replication does not need to maintain the meta data associated with each partition at each subscriber.

How to create a publication with Dynamic Row Filtering (GUI)

In this section I will set up a merge publication using the AdventureWorksDW database for the table FactResellerSales using the GUI.

1. Ensure your distributor is configured and your publisher is registered as a publisher with you distributor.

2. On the publisher, right click "Local Publications" and select "New Publication" and select the database your table is in.

New Publication Wizard

3. Choose "Merge publication" as your publication type.

Choose Publication Type

4. Choose your replication compatibility level, for this demo I chose "SQL 2008 or later".

Choose Compatibility Level

5. Choose the table or "article" to add to the publication.

Choose Article

6. After selecting your table, select the drop down "Article Properties" and choose "Set Properties of Highlighted Table Article". Pay attention to the "Partition options". This is where you can choose overlapping or nonoverlapping. I selected nonoverlapping.

Initial Article Properties

7. Change any additional properties you deem necessary for your environment. My properties look like below.

Modified Article Properties

8. Since merge replication uses UNIQUEIDENTIFIER to manage rows like transactional uses PK's, you are told a new column will be added if you don't already have a UNIQUEIDENTIFIER column in your table. By default the column added will be named "rowguid" of type "uniqueidentifier"

ROWGUID Column Added

9. On the next screen select "Add Filter" and it takes you to the following screen, this is where will add you dynamic row filtering options. Keep in mind, HOST_NAME() returns a nchar so usually there is some conversion that has to take place. I chose not to apply functions on the left side of my predicate as that would bypass any indexes I choose to apply in the future to assist this filter. You will also see that section 3 is already set to "A row will only go to one subscription" due to the fact I chose my article properties as nonoverlapping.

Dynamic Filter

10. Select your snapshot agent scheduling, I will just use the defaults for now.

Snapshot Agent Scheduling

11. Specify your security settings for the snapshot agent, I am using my SQL Service Account

Snapshot Agent Security

12. Select "Create the publication".

Create Publication

13. Give the publication a name and select "finish" and your publication is now created.

Publication Name

Publication Completed

Now if you right click the publication in SSMS and select "properties" and go to the "Subscription Options" tab you will see the following:

Publication Properties
  • The "Data Partitions" section is where you can pre-define partitions and create dynamic snapshots for that partition
  • Subscribers are validated by HOST_NAME() which you can override in this case with a SalesTerritoryKey
  • Partition are pre-computed which is a optimization for replication

Here is a picture where I predefined a SalesTerritoryKey of 4 and generated a dynamic snapshot.

Dynamic Snapshot

How to create a Publication with Dynamic Row Filtering (TSQL)

This is the code you can use to create dynamic row filtering using TSQL. Pay close attention to @validate_subscriber_info and @dynamic_filters on sp_addmergepublication as well as @subset_filterclause in sp_addmergearticle.

-- Enabling the replication database for merge replication
use master
exec sp_replicationdboption 
  @dbname = N'AdventureworksDW', 
  @optname = N'merge publish', 
  @value = N'true'
-- Adding the merge publication
use [AdventureworksDW]
exec sp_addmergepublication 
  @publication = N'SalesDataByTerritory',
  @description = N'Merge publication of database ''AdventureworksDW'' 
      from Publisher ''EMU-SQL1''.', 
  @sync_mode = N'native', 
  @retention = 14, 
  @allow_push = N'true', 
  @allow_pull = N'true', 
  @allow_anonymous = N'false', 
  @enabled_for_internet = N'false', 
  @snapshot_in_defaultfolder = N'true', 
  @compress_snapshot = N'false', 
  @ftp_port = 21, 
  @ftp_subdirectory = N'ftp', 
  @ftp_login = N'anonymous', 
  @allow_subscription_copy = N'false', 
  @add_to_active_directory = N'false', 
  @dynamic_filters = N'true', 
  @conflict_retention = 14, 
  @keep_partition_changes = N'false', 
  @allow_synctoalternate = N'false', 
  @validate_subscriber_info = N'HOST_NAME()', 
  @max_concurrent_merge = 0, 
  @max_concurrent_dynamic_snapshots = 0, 
  @use_partition_groups = N'true', 
  @publication_compatibility_level = N'100RTM', 
  @replicate_ddl = 1, 
  @allow_subscriber_initiated_snapshot = N'false', 
  @allow_web_synchronization = N'false', 
  @allow_partition_realignment = N'true', 
  @retention_period_unit = N'days', 
  @conflict_logging = N'both', 
  @automatic_reinitialization_policy = 0
exec sp_addpublication_snapshot 
  @publication = N'SalesDataByTerritory', 
  @frequency_type = 4, 
  @frequency_interval = 14, 
  @frequency_relative_interval = 1, 
  @frequency_recurrence_factor = 0, 
  @frequency_subday = 1, 
  @frequency_subday_interval = 5, 
  @active_start_time_of_day = 500, 
  @active_end_time_of_day = 235959, 
  @active_start_date = 0, 
  @active_end_date = 0, 
  @job_login = null, 
  @job_password = null, 
  @publisher_security_mode = 1
-- Adding the merge articles
use [AdventureworksDW]
exec sp_addmergearticle 
  @publication = N'SalesDataByTerritory', 
  @article = N'FactResellerSales', 
  @source_owner = N'dbo', 
  @source_object = N'FactResellerSales', 
  @type = N'table', 
  @description = N'', 
  @creation_script = N'', 
  @pre_creation_cmd = N'drop', 
  @schema_option = 0x000000010C034FD1, 
  @identityrangemanagementoption = N'none', 
  @destination_owner = N'dbo', 
  @force_reinit_subscription = 1, 
  @column_tracking = N'false', 
  @subset_filterclause = N'[SalesTerritoryKey] = 
    ELSE [SalesTerritoryKey] END)',
  @vertical_partition = N'false', 
  @verify_resolver_signature = 1, 
  @allow_interactive_resolver = N'false', 
  @fast_multicol_updateproc = N'true', 
  @check_permissions = 0, 
  @subscriber_upload_options = 0, 
  @delete_tracking = N'true', 
  @compensate_for_errors = N'false', 
  @stream_blob_columns = N'true', 
  @partition_options = 3

How to create a Subscription with Dynamic Row Filtering (GUI)

Now that we have our publication created, we need to add subscriptions, this is how certain territory's data will make it to certain subscribers to meet our original requirement.

1. Right Click our publication "SalesDataByTerritory" and select "New Subscription". From there choose the publication you want to create a subscription to.

New Subscription

2. Choose whether you want PUSH or PULL subscriptions. As a quick note, PUSH means the merge agent will run on the distributor and PULL means the merge agent will sun on the subscriber. Both have their purpose and trade offs, but this determination does not constitute a performance based decision. For more information on PUSH vs PULL see Subscribe To Publications


3. Choose your subscriber and subscriber database. For this example I created a database on another server called "SalesTerritory_4".

Choose Subscriber

4. Set the security for your merge agents, I am using Service Accounts for this.

Merge Agent Security

5. Set your synchronization schedule, for demo purposes I am choosing on demand only but in real life you would either want continuous or every XX minutes and this can be defined in the same interface as scheduling an SQL Agent Job as these agents are just SQL Agent jobs behind the scenes.

Choose Synchronization Schedule

6. Choose your initialization method.

Choose Initialization Method

7. Now choose your subscription type. With merge replication, subscriptions can be either "Server" or "Client". Client will work for most cases as Server is intended for republishers where you can assign a weighting for conflict resolution. Client defaults to "First to Publisher wins" as a priority for conflict resolution. For more information on this please see Subscribe To Publications

Choose Subscription Type

8. Choose your HOST_NAME() value, remember this is what we are overriding. It defaults to the HOST_NAME() of the subscriber but in our case we are wanting to push SalesTerritoryKey = 4 data to this subscription. Below is a screenshot of the default and the modified view of this screen.


HOST_NAME Modified

9. Choose "Create the subscription" and select "Finish" and your subscription will be created.

Create Subscription

10. Right click the subscription and select Properties and you will see the values in place for dynamic row filtering.

Create Subscription

11. Generate a new snapshot and start your merge agent and once completed you should see only data for SalesTerritoryKey=4 in your subscription database. Here is a screenshot of the data in the publication database and as you can see there are many different SalesTerritoryKey values.


Here is a screenshot of the data in the subscriber database and as you can see there is only 1 SalesTerritoryKey value.


How to create a Subscription with Dynamic Row Filtering (TSQL)

Here is the code to create this subscription using TSQL and this would be run on your publisher. If we had a PULL subscription there is some code you would have to run on the publisher as well as some to run on the subscriber. Notice the @hostname parameter being passed to sp_addmergesubscription, that defines the data set that will go to this subscriber.

-- Adding the merge subscriptions
use [AdventureworksDW]
exec sp_addmergesubscription 
  @publication = N'SalesDataByTerritory', 
  @subscriber = N'EMU-SQL3', 
  @subscriber_db = N'SalesTerritory_4', 
  @subscription_type = N'Push', 
  @sync_type = N'Automatic',
  @subscriber_type = N'Local', 
  @subscription_priority = 0,
  @description = N'', 
  @use_interactive_resolver = N'False', 
  @hostname = N'4'
--Created Merge Agent job
exec sp_addmergepushsubscription_agent 
  @publication = N'SalesDataByTerritory', 
  @subscriber = N'EMU-SQL3', 
  @subscriber_db = N'SalesTerritory_4', 
  @job_login = null, 
  @job_password = null, 
  @subscriber_security_mode = 1, 
  @publisher_security_mode = 1, 
  @frequency_type = 4, 
  @frequency_interval = 1, 
  @frequency_relative_interval = 1, 
  @frequency_recurrence_factor = 0, 
  @frequency_subday = 8, 
  @frequency_subday_interval = 1, 
  @active_start_time_of_day = 0, 
  @active_end_time_of_day = 235959, 
  @active_start_date = 0, 
  @active_end_date = 0
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, October 23, 2017 - 2:33:57 PM - Heather Back To Top (68700)

 Hi,  Running SQL 2014 on server and 2014 Express on subscribers.  In our replication we recently started seeing a lot of these errors randomly.  The only way to address is to reinitialize without upload.  Can you provide any info on how me might fix this issue, any thing specific i can look for.   or any help  in general is appreciated.  Ran about 5 years on SQL 2008 and ever had this error.  Increasing query time out does nothing.  Retention period is 60 days, would prefer to keep that and i'm unsure how to imporve indexes on published tables and which ones, we have many tables.


Error messages:

·          The merge process failed to enumerate changes in articles with parameterized row filters. If this failure continues, increase the query timeout for this process, reduce the retention period for the publication, and improve indexes on published tables. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200925)
Get help: http://help/MSSQL_REPL-2147200925

·          Query timeout expired (Source: MSSQLServer, Error number: 0)
Get help: http://help/0

·          The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0


Tuesday, April 12, 2016 - 8:41:04 AM - Chad Churchwell Back To Top (41201)

Azita -


Please see the sections called "Overriding the HOST_NAME() Value" from this link 

I am thinking you will need to convert the value.  Please give that a test



Tuesday, April 12, 2016 - 2:07:14 AM - Azita Assmar Back To Top (41198)

 Thanks for your very helpful post. I used your post to implement merge replication in my environment and receive error:

Environment: Merge Replication with 2 subscribers - SQL Server 2008 R2

After subscribing to publication, the synchronize agent starts and works fine for the first subscriber but doesn't start on a subscriber that its HOSTNAME() is set to  @hostname = N'-8070450532247928832'. The agent shows "Starting Agent" in Replication Monitor and generates error after 10 minutes of inactivity.

I setup verbose logging and received this error:
A syntax error occurred near parameter '-8070450532247928832'.

As an experiment, I just removed the minus sign and used @hostname = N'8070450532247928832' and the agent worked fine. 

Any idea why minus sign in @hostname generates error?




Thursday, September 6, 2012 - 2:28:08 AM - Chandu Ade Back To Top (19404)

This is exactly the same task on which we are working.... This is good one. We tried this using script, batch programing and also using Management Studio. We implemented this on SQL2008 R2 as a publisher and 2008 R2 Express edition as subscriber, where there is no SQL Agent running. But it works fine

get free sql tips
agree to terms