SQL Server Performance Forum – Threads Archive
Merge Replication and Dynamic filters
Hi, I would like to setup a merge replication from a main database (publisher), to around 100 client databases (subscribers), that contain only subset information for each distinct client: each client has then its own database to view and modify its own data (Filtered on rows and columns). The client databases will initially be hosted on the same SQL server Instance. I don’t want to setup manually 100 publications with static filters, and more may come in the future: a pain to setup and maintain because I have to configure the tables, the columns and the row filters (and joins) each time. I would like then to setup one publication with a dynamic filter, to filter in a way or another on a specific client. Creating subscribers becomes then a piece of cake. The dynamic filter would apply based on a property specific to the client, but… HOW? Filtering on HOST_NAME() will not work because several subscribers are on the same server. Filtering on SUSER_SNAME() will not work because merge agent will always use the same user name for connecting to the publisher (using push subcription, all merge agents are on the same server), and I have not find out how it can be configured by merge agent: even if the merge agent jobs have different owner, it is always the SQL Server Agent login that is used to connect to the publisher (I am using windows authentication). I was thinking about using DB_NAME(), and have specific db name for each client DB, but DB_NAME() provides the name of the publisher DB, not the subscriber DB. etc … What could I use in this case to dynamically filter on client data without having to fall into heavy replication administration and setup. Thanks for your help, Best regardsI have found the way to filter on each subscriber by forcng a different host name for each subscriber:
In the job that launch the merge agent, I add a parameter -HostName [CustomizedName]
My publisher filters then on the host name.
]]>