June 3, 2003 at 9:09 am
Hello all ! This is my first post here, so I hope I won't make any mistake. I also apologize in advance for any spelling mistake, as English is not my first language.
My company is currently investigating using replication between 2 SQL Servers (2000) to provide Active/Passive Failover recovery (on this project, we can't afford going with a full cluster solution. 1 server (Srv_Prod) is the production server, and the other (Srv_Bck) is on standby. In case of a failure of Srv_Prod, the applications can use Srv_Bck to get their data. Since the data is important, it's imperative to have a fast replication occuring often (the volume of data is not very large).
The initial idea would be to use a replication based on the Transaction-Log model, with Snapshots being done every night, and Transaction logs every minute or so. The worst case for the failure/recovery would be the Srv_Prod server failing on Friday evening, as the 24/24 emergency team can do the server switch, but not debug (and/or reinstall) a crashed SQL Server. Which would mean about 60 hours to go on the Srv_Bck alone.
My question is : is it possible and relatively trouble-free, when the main SQL server goes back online (60 hours being a worst-case scenario), to upload the modified data back to it ? Will configuring the Suscriber on Queued Updating work ? Are there any specific problems I should be careful about ?
Thanks in advance !
June 3, 2003 at 9:38 am
Have you consider using log shipping instead of transactional replication?
June 4, 2003 at 12:17 am
Hi,
We have a similar scenario and have used queued Transactional Replication to the backup system - using all the default setting our latency is about 5 seconds.
Our plan, if the data server goes down, is to run off the backup and, when data server is repaired, to simply backup the database on the backup system - restore to the data server - then reset the replication process by doing another snapshot from data server.
Cheers, Peter
June 4, 2003 at 1:19 am
Thanks for the replies !
Racosta, I had a look into log shipping, and I'll probably test it, it may just be what we need.
Jonespm, thanks for sharing your experience. I'm glad to know this can work, and that the backup on 2nd server/restore on main procedure works well.
June 6, 2003 at 4:55 pm
Before you decide on either Replication as your failover, please keep the following in mind:
It doesn't replicate jobs, alerts, default values, constraints, triggers, primary key definitions and a host of other items. Identify solutions for all of those first.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply