I am asked to investigate viability to use SQL Server replication technology for one of our upcoming project. In this project, basically there will be one central server located at corporate HQ (say Hub) and there will be multiple servers located at remote locations (say Nodes). All nodes will get data by using our application and Hub will get data directly injected into SQL Server. I am given 2 business requirements that need to be satisfied by this solution,

  1. Data to be synchronized between hub and nodes both ways.
  2. Data from one node NOT to appear on another node.

So based on those requirements, I chose to try Merge Replication.For test, I setup hub and 2 nodes. Hub is also acting as distributor. Then selected all objects of database and created subscription. And registered both nodes as subscribers. And this setup works... almost. The issue I am facing is, if I update data from node1 it appears in hub as well as node2 (and vise versa). That doesn't satisfy our 2nd requirement.

I am familiar with Replication but not master at it and I would really appreciate if someone can suggest if something is wrong with my approach. Or is merge replication not a good choice for my case?

I am using SQL Server 2014 standard editions for all 3 instances installed in Windows Server 2012R2.

  • This can be done in Merge Replication by using Parameterized Row Filters: docs.microsoft.com/en-us/sql/relational-databases/replication/… – Brandon Williams Aug 17 '17 at 15:41
  • @BrandonWilliams Yes. I was thinking to filter data. But since all tables will have identical structure and same PKs (we are using identity for most of columns), I will have to adjust part of database structure. And that is something I want to avoid if possible. Do I have any other choice? – JackLock Aug 17 '17 at 15:55
  • If data from one node is NOT to appear on another node, how do you know which data belongs to which node? – Brandon Williams Aug 17 '17 at 17:21

Not an ideal answer....but may be we will have to add a table column and use that to denote different locations. So, with merge replication in place, the node1 location bit as 0 and node2 location bit as 1....so it doesn't matter for hub.....however, for node1 can have further reporting proc code with that location filter. Same way for node2 process code also. And don't replicate this proc. Seems some coding effort. So, basically we are letting the data to replicate..... however limiting it to not come in reports by using filtered proc code. Thanks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.