February 6, 2008 at 6:50 am
I’m not sure if this is the proper forum for this, but I don’t know where to start. Here’s my dilemma:
I have two databases on two different LANs that I need to sync up. They are both transaction processing dbs running SQL 2K5 Enterprise. The issue is that I need to be able to update one of the databases with data entered on its counter part. I was hoping to perform this via SSIS (export to removable device and an import on the destination db) but I’m not sure if that’s the best solution or not.
The largest obstacle is that the destination database also needs to be able to enter data on its own that the source database will never be aware of. In addition to new data, there may also be updates from the source database to existing data. I’ve considered using a composite key (an identity seed and a field indicating which db the record came from) but that won’t work because the identity seed will get out of sync. I’ve also considered having the identity for the destination database start with a high number to distinguish it as being data entered on that system.
There are a lot of development issues with all of this and I’m just not sure of the best tool or method to use. Does anyone have any experience with this or can anyone give me a nudge in the right direction to go?
Any help would be greatly appreciated.
February 6, 2008 at 8:29 am
First, please post in the version appropriate area. This was in 2000, I'll move to 2005.
Second, I'm assuming there is no connectivity, so some manual method is needed to move the data. A removable device can work. However the issues you have do not change.
This is exactly the reason why GUID were created. you could use them instead of identities and you would not have issues. If you need identities for some reason, then you'd need to assign ranges to each server, say 1 to Server A and 1billion to ServerB as the starts and hope you never exceed the range.
February 6, 2008 at 10:47 am
Thanks much. Sorry about posting in the wrong version section. I'll check out out GUID and see if I can figure out if it will aid in my solution.
February 6, 2008 at 11:56 am
After some breif research on MSDN, it looks like this will work. Thanks, Steve.
February 6, 2008 at 8:36 pm
You are welcome and glad it will help.
One cool thing to think about as well is that clients can actually generate the GUID themselves and insert the values. They're unique, so that works and if you have the insert parent/get ID/insert child process, you don't need to get the ID. Have the client generate it first and just insert both the parents and children together.
February 7, 2008 at 5:33 am
I did see that in the MSDN documentation. It'll be very nice for saving a round trip. It wont matter for this particular implementation, but I have another one that has many clients that may increase performance. I'm going to look into implementing it there, but that particular db also has many foreign keys so I'm not sure if the change will actually improve the application performance because of the degredation of the in primary to a 16 byte binary number might have on relationships. We'll see in testing. And thanks again.
February 7, 2008 at 6:36 am
Just check Distributed Transaction (MS DTC) can help you. You can also look topic two-phase commit (2PC).
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply