Filter a SQL replication

Posted on

Question :

I’m trying to replicate a SQL database but I want to apply a filter based on date.
The database has more than 6000 tables and all of them have a datetime column (stamp) with the same name “usrdate”.
Is it possible to apply a filter to be applied to all columns?
The goal is to have multiple databases in the replication site with data between periods.


Answer :

Assuming you mean Microsoft SQL Server, yes you can apply a Filter on the Publication using that usrdate column on each Article you’re replicating, but you’ll need to add the Filter for each replicated article. Also there are some limitations (mentioned in the previously linked docs) and using Publication Filters aren’t applied like regular SQL predicates so they may be less performant than doing it in SQL yourself.

You may be best off creating a series of Views on top of the Tables you want to replicate that apply the date filter as a predicate in SQL, which you probably can more easily script out than trying to add the Publication Filter to each Article.

Leave a Reply

Your email address will not be published. Required fields are marked *