I'm wondering what is the best way to replicate all transactions (inserts, updates, and deltes) from one sql 2000 server to another, at as close to "real-time" as possible. We need to have a up to date server ready to go in case the main db server should fail.
I was looking at using a dts package to do it and have it just run every minute or so, and only insert any new records or updates, but I can't think how I'd do the deletes.
or
Another way I thought I could do it would be have a trigger that would fire off on all updates, inserts, or deletes and have it write the new information over, via a stored procedure that would activate a dts.
(but since it would be on another db system I'd still have to set up a package to run, because as far as I know you cannot reference a seperate computer in a sql statment. IE: myDB1.dbo.table1 I can't put something like this: server2.myDB1.dbo.table1)
One of the major problems is that I can't have the data replicate hours later, it has to be as close to "real-time" as possible.
How does everyone else out there do something like this?
Thanks for any input and direction
I was looking at using a dts package to do it and have it just run every minute or so, and only insert any new records or updates, but I can't think how I'd do the deletes.
or
Another way I thought I could do it would be have a trigger that would fire off on all updates, inserts, or deletes and have it write the new information over, via a stored procedure that would activate a dts.
(but since it would be on another db system I'd still have to set up a package to run, because as far as I know you cannot reference a seperate computer in a sql statment. IE: myDB1.dbo.table1 I can't put something like this: server2.myDB1.dbo.table1)
One of the major problems is that I can't have the data replicate hours later, it has to be as close to "real-time" as possible.
How does everyone else out there do something like this?
Thanks for any input and direction
