Problem solve Get help with specific problems with your technologies, process and projects.

Replicating a transactional table on nine sites

I have a transactional table called TableA which is available on nine independant sites. Some of those sites are disconnected most of the time and connect occasionally. Each site updates that table by inserting new rows. Rows are identified by an ID plus a siteID, so conflicts will not occure. Sometimes this table records rows in site A that are related to site B and visa versa. I want to establish a replication model to allow site A to replicate those rows related to site B to site B only and allow site B to replicate rows related to site A to site A only. The number of transactions in the tables varies from site to site, say in site A = 20000, in site B = 1000. I want to apply this replication between all nine sites, connected and disconnected. What is the best way to do it?

You can do this using just about any method of replication. Transactional as well as merge will work in both a connected and disconnected manner, as long as you do not have conflicts occuring between sites. The number of transactions does not matter either. As long as you are partitioning the tables based on the site, then you can handle moving the rows to where they belong. The biggest challenge is going to be in desiging your paritions such that your data is logically split. If you are moving data via static partitions, transactional would be a better choice. If you are dynamically splitting the data or you are partitioning multiple tables based upon a "master" table, then merge would be a better choice.

For More Information

Dig Deeper on SQL Server Replication

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.