June 29, 2011 at 8:41 am
Hi,
I was hoping someone might be able to advise me how best to replicate specific data between sql servers located around the world.
Assuming there was a SQL server in HK, US and UK, what I'm wanting to achieve is for each server to replicate it's local data to the other two so all three has the data locally with edit rights on the data available to all.
For example:
If I had a table called "Pages" on each server with columns "ID", "Region", "Title", "Contents". The HK server's data would have "HK" in the region column, UK would have "UK" etc.
So, I'd really like the UK server to be able to publish "UK" rows to US and HK and for them to be able to edit these rows but UK would be the publisher.
Likewise, I'd like the HK server to publish "HK" rows to UK and US with them able to edit data but with HK being the publisher. US server would be the same again.
I had hoped to be able to use merge publications with filters for the region value to control the exact rows being published but I can't seem to get it to behave.
Would be interested to hear other people's thoughts on this with possible solutions etc.
Thanks for your help
Coll
June 30, 2011 at 3:38 am
In principle this sounds like peer to peer transactional replication where all servers are publishers.
You could also do this in merge and have a central publisher.
June 30, 2011 at 4:20 am
If you have SQL Server Enterprise Edition I would pick Peer-to-Peer.
However, have you been successful in creating basic Transactional Replication when data flow only from publisher to subscribers? As you have single table that will be source of data for one publisher and two subscribers you will have to setup filters on the data you will publish.
Have you considered adding table for each region Pages_UK, Pages_HK etc.In UK-region Pages_UK will be publishing and receiving updates from HK and US subscribers. Pages_HK and Pages_US will subscribe to matching publication (Publication could be setup as Tran Replication with Updatable Subscriptions with Queued Updating).
To read the data you could create a view combining all three tables. Updating would be more fun though.
Anyway, if you can I would go with P2P.
June 30, 2011 at 4:33 am
Thanks guys,
I agree peer to peer seems to be what I was looking for. I'll have a play and see if I can get it working how I need.
BTW Max thanks for suggestion of three separate tables but that table was just a simple example to try and explain my situation. My reality is far more complicated... I wish that really was all I needed to replicate 😉
Thanks again for you advice.... I may be back with more questions
Coll
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply