October 30, 2007 at 9:29 am
Hi,
We have a client who have a SQL Server 2000 in Florida and another one in Montreal. We would like to copy data from Florida to Montreal (where the main office is located, so, a one-way replication). Problem is, in Florida, the internet connexion is unstable, low speed, no reliability. Both site are connected by VPN, when the internet works, but it speed is very slow. What is the best strategy to replicate inserted and updated rows from the Florida DB to the Montreal's ? It can be replicate once per day, during the night. I thought creating a DTS who takes all data for the day, export them in files and transfert them by FTP where another DTS would takes the files and insert the rows in the DB. But if rows, related to transactions happend several days ago, are modified in Florida, these updated will not be "replicated" that way. So how can I handle this situation? someone have an idea?
I really appreciate your help,
thanks a lot.
Dominic Gagne
October 30, 2007 at 9:54 am
The concept's the right one - but it sounds like you might not be pushing it "far enough". Meaning - you need to track what's inserted/updated, and what's deleted.
Inserted/updated can be done with a single "last update" field tracking the last time any modifications (including creation) was performed on the record. Deletes should be tracked by "logging" the primary keys of the records being deleted (again - with the datetime when this was done).
Since it's "not reliable" - I'd set up some form of hashing process to be sent over "regular email", a.k.a not VPN, so that Montreal can validate that the file received has everything in it that should be there. Meaning - create the file, create a hash value for the file once it's been created locally, and send that to the remote office so it can be validated on the other end.
You'd also need to concoct some way to validate that the updates do in fact occur.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2007 at 11:21 am
thanks a lot for the reply.
Like you said, I'll push it more, by adding a "row modified" flag to every table. Problem is, I'll have to modify every single StoredProc (about 200 of them) who insert and update records in tables, to set that flag every time a row has been added or modify (there will be no deletion).
It's going to be a lot of work, but if I don't have any alternative, I'll have to stick with it.
thanks again for your help
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply