February 18, 2008 at 9:30 am
Hi There!
We have a SQL 2K5 server which has a database that uses merge replication to exchange data with hand held devices.
The business want a Disaster Recovery solution.
Without spending a ton of money there doesn't seem to be a very elegant solution to this.
I found out immediately that you can't log ship or mirror the Distribution database.
The data on the handhelds is quite sensitive so in the event of a D.R. we really wouldn't want to lose any data.
The best solution so far seems to be to:
1. Log Ship or Mirror the Publication database to the D.R. server.
2. Take regular backups of Master, MSDB & Distribution databases and copy to the D.R. server.
In the event of having to failover to the D.R. server..
1. Rename the D.R. server to be the Production server.
2. Restore Master, MSDB & Distribution databases
Hopefully then everything will continue to work without having to re-initialise a snapshot (in which case we can lose data on the hand helds).
I'm going to try this shortly on some test servers, but i wondered if any of you had tried this already, can you see any potential problems with this or is there a better way to do it?
Many thanks.
Stu.
February 18, 2008 at 10:24 am
Wouldn't the data still be on the handhelds and any lost transactions would end up causing conflicts (potentially)?
I'm not a repl expert, but I think that what you've outlined is the best you can do. There might be some ways to look at saving off handheld data before restarting replication.
February 18, 2008 at 10:52 am
At the moment there isn't too much traffic from the handhelds.
They can update live via GPRS when required but quite often they get "docked" in the evening and data currently seems to merge mostly then.
So assuming someone wasn't right in the middle of a transaction when the Prod server dies, we "shouldn't" lose any data.
Whilst the prod server is down and they're waiting for the DR server to come online, the hand helds will just store the data until they can transmit again.
As long as everything carries on from where it left off we should be fine.
If we need to re-initalise then we lose all the data on the hand helds.
Hopefully I should be doing a proof of concept shortly, i'll update this record when I know if it works.
And if anyone has any better ideas, please let me know!
Ta!
Stu
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply