October 14, 2008 at 5:16 am
hi guys
Here's my issue. We have three databases in different locations, and three versions of an app that connect to their local database.
There are a couple of tables in the three databases that I'd like to keep consistent.
The situation - and this was a solution implemented before I arrived - is that they all use a local table but add records to it with a specific ID based on their location. So LocationX adds records with ID between 1-10,000,000; LocationY adds records with ID between 10,000,001 to 20,000,000; LocationX adds records with ID between 20,000,001 and 30,000,000.
My task is to set up some kind of replication, so that if LocationX adds/updates records, these changes will be "automatically" populated to the LocationZ table.
Latency isn't an issue as only a few records are added/changed every week, and currently changes are updated manually (!) on request.
I'm thinking Merge Replication, but I've never done this before so would appreciate any input. I'm happy to figure out the "how to" myself, but any guidance to a general strategy would be great.
I wish the app had been designed to have TableX, TableY, and TableZ rather than the way it is as I'm sure that would make this more straight forward, but I can't change that at the moment.
TIA
October 14, 2008 at 5:23 am
Merge replication would require you to add a GUID and a timestamp column to the table. The GUID ensures uniqueness and the timestamp (which is not actually a date or a time) allows replication to know if a record has been modified. If your application can handle the additional columns, you could probably use merge replication.
You may want to look into peer-to-peer replication:
http://www.microsoft.com/technet/prodtechnol/sql/2005/p2ptranrepl.mspx
It is probably the best fit for your situation.
October 14, 2008 at 5:58 am
Thanks Michael - yeah I missed that out of my post but peer-to-peer transactional replication was my other thought - that's a great article there and, going by that and what you've said, looks like my most likely solution
Thanks again 🙂
October 14, 2008 at 6:06 am
Typically, the problem with updatable peer-to-peer replication is it ends up with a pretty high latency period. Since latency is not a problem for you, it should work fine. You already have identity ranges defined, so it should be pretty easy for you to set up.
Replication gets pretty messy as soon as you go outside the basic defaults. Follow the MS documentation and try not to get too creative or you will find yourself in a maintenance nightmare pretty quickly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply