Replication recommendation 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.
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.