December 1, 2005 at 2:01 pm
I have an environment that is transactionally intensive and is also 24x7. I am wanting to set up transactional replication between our Production server (SQL 2000) and our off-site, DRP (Production Disaster Recovery) server (also SQL 2000) . This is so that if the Production Server goes down, then our in-house application (which is set to automatically switch over to the DRP server) starts using the DRP server. I'm thinking that if I set up the Production server as the Publisher and the Distributor (with immediate replication); and then set up the DRP server as a Subscriber... this should suffice. However, one of the things that I want to know is, when the Production server comes back on-line... will those transactions that took place on the DRP server re-sync themselves with the Production server? If not, how do I go about ensuring that this happens? I know that if we were using SQL 2005, I could set the transactional replication as a peer-to-peer and this would happen. I just don't have a comfortable feeling of knowing that SQL 2000 will do this.
Any thoughts are greatly appreciated....
Thnx.... Scottye
December 2, 2005 at 6:48 am
There are many ways to achieve this, but in 'normal' (what ever that is 🙂 ) 2000 environments logshipping is probably the least invasive solution and best solution. It all really depends on how much data there is being shuffled around, the reliability of the network connectivity between the sites, and finally the recovery window needed. Traditionally transaction replication is a one way replication (yes it can be setup as two way but that is a different matter). Using replication for DR may additionally cause database upgrade issues as there are restrictions regarding schema changes when using replication.
So regarding your re-synch, in a one way configuration it wont, in two way it would. However setting up 2 way transactional may open up more issues than you really want to deal with. I would personally bank on manually restoring (or logshipping back) the original site if you are really in a disaster scenario - obviously it would be nice to have it automatic, but in that scenario I dont think I would trust it without getting my hands on it first (but that could just be being paranoid!)
Hope this helps some,
Gareth
December 2, 2005 at 8:42 am
A replication subscriber is not a hot spare ready to take over if the production system goes down.
The biggest issue is if you have any tables with identity fields, they won't be identity fields in the subscriber. (Not unless you go to a lot of trouble creating your own replication scripts with SET IDENTITY_INSERT commands).
Replication can impact the performance of the production system. And there are restrictions on schema changes after replication is set up (as mentioned above).
If you've got the money to buy a cluster system and SQL Enterprise, it's easy to solve your problem. If not, log shipping is the best way to go. SQL Enterprise will set up log shipping and monitor it automatically, but you can still do it manually in SQL Standard. You just need to run frequent transaction log backups of the production system, and schedule a job to copy the .TRN files to the standby system and use RESTORE ... WITH NORECOVERY or RESTORE ... WITH STANDBY to keep the standby database in sync.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply