Post reply

Transactional Replication — Horizontal Filters

  • April 9, 2021 at 8:48 am

    #3867155

    One of the developers wants to introduce horizontal filters in a new transactional replication setup and has asked my opinion.

    I have, however, no practical experience of it on a productive server with daily loads. It works reasonably well on my local server. I have read warnings that it may require more resources, which is understandable.

    While I have experience of transactional replication, I have resisted until now the use of horizontal filters. It seems to me that if every line must be checked for the filter condition, this will be a performance problem. It would be good if unnecessary rows are prevented from being sent to the subscribers in question. The table being replicated will have almost 2 million rows to start off with and is expected to grow by in or around 100,000 rows per year. SQL Server 2016 is being used for all 3 servers (1x publisher, 2x subscribers).

    What is your experience of horizontal filters in transactional replication setups on production servers? Does it work well and efficiently? Did it require more resources (CPUs, RAM etc.)?

    There is this warning in Books Online:

    This note still leaves some questions: how small is reasonably small? Both subscribers can support the full data load but the point of the filtering is to avoid unnecessary rows in the relevant subscribers.

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/filter-published-data?view=sql-server-ver15

     

  • April 10, 2021 at 9:10 am

    #3867534

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply