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,
- Data to be synchronized between hub and nodes both ways.
- 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.