Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Parameterized Row Filters in SQL Server Merge Replication


By:   |   Updated: 2012-09-05   |   Comments (4)   |   Related: More > Replication

Problem

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.

Solution

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'
GO
-- 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
GO
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] = 
    CONVERT(int, CASE WHEN ISNUMERIC(HOST_NAME())=1 
    THEN HOST_NAME() 
    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
GO

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

PUSH or PULL

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 Default


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.

PublisherData

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

SubscriberData

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
GO
Next Steps


Last Updated: 2012-09-05


get scripts

next tip button



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

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

 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

Azita -

 

Please see the sections called "Overriding the HOST_NAME() Value" from this link https://msdn.microsoft.com/en-us/library/ms152478.aspx 

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

Chad

 


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

 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?

Thanks

 

 


Thursday, September 06, 2012 - 2:28:08 AM - Chandu Ade Back To Top

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


Learn more about SQL Server tools