By: Chad Churchwell | Comments (4) | Related: > 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.
3. Choose "Merge publication" as your publication type.
4. Choose your replication compatibility level, for this demo I chose "SQL 2008 or later".
5. Choose the table or "article" to add to the publication.
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.
7. Change any additional properties you deem necessary for your environment. My properties look like below.
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"
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.
10. Select your snapshot agent scheduling, I will just use the defaults for now.
11. Specify your security settings for the snapshot agent, I am using my SQL Service Account
12. Select "Create the publication".
13. Give the publication a name and select "finish" and your publication is now created.
Now if you right click the publication in SSMS and select "properties" and go to the "Subscription Options" tab you will see the following:
- 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.
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.
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".
4. Set the security for your merge agents, I am using Service Accounts for this.
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.
6. Choose your 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
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.
9. Choose "Create the subscription" and select "Finish" and your subscription will be created.
10. Right click the subscription and select Properties and you will see the values in place for dynamic row filtering.
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 GO
Next Steps
- BOL for SQL Server Replication
- Please read up on Parameterized Row Filtering
- Considerations for Snapshots for Merge Publications with Parameterized Filtering
- Consider implementing this feature if you have a need to dynamically send data to different locations based on a column value
About the author
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
