I have server A and server B. I want server A can see data and changes on specific article on server B . But server B just see its own data . And also I want to update server B's data on both servers and data remain consistent. I want to prohibit the server B to see server A data As simple word I want replication with one way insert on subscriber and two way update on publisher data on both servers. Is it possible ? If it is how? Thanks
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
If I understand correctly, you want an insert on A to propagate to B but an insert on B not to propagate to A. If a record that exists on both gets updated on either side, you want that update to propagate. If a record that only exists on B gets updated it stays only on B.
The simplest way to get bi-directional replication going is to use merge replication. It can handle conflicts automatically in is therefore a good choice. See my stairway series for an introduction to merge replication: http://www.sqlservercentral.com/stairway/72401/
The additional requirement of hiding inserts on one node is easy if you can find a common property of those records that all other records don't have. If you think of the typical sale data distribution problem, you have a main database with the complete data set. The PA sales person is only interested in PA data, but if someone updates PA data in the main database she wants to know about it.
Replication allows you to specify a horizontal filter that would include only 'PA' data in the table article. The article about the merge publisher in my series has a brief introduction to horizontal filters.
If you do not have such an indicator yet, you could add a surrogate indicator, something like