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
Sql-server – Replication with two side update but one side insert
replicationsql server
Related Question
- Sql-server – Dealing with identity ranges for transactional replication
- Sql-server – Execute a script after transactional replication setup completes
- Sql-server – Merge Replication – Deleting from the subscriber database
- Sql-server – Identity management in Transaction replication with updatable subscription
- Postgresql – How to set up bidirectional replication with Postgresql
- Sql-server – How to force subscriber to have foreign key WITH CHECK
- Postgresql – Recovery with Postgresql logical replication
Best Answer
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
Repl_Ind BITcolumn and default it to 0 on the publisher, but change the default to 1 on the subscriber. (Or always specifically set it to 1 on the subscriber.) That way you could use the horizontal filter again.