June 14, 2011 at 9:07 am
i'm looking for information on using SQL replication as a DR method, and specifically, how a failover works with Transactional replication. according to this article: http://support.microsoft.com/kb/822400
according to the article:
If a disaster occurs, you must manually switch servers by redirecting all the applications to the subscriber.
but this doesn't really outline the steps very well in how to perform an actual failover. :ermm:
i'd guess that a failover can be done in two different ways:
1) change the datasource of the clients to be the subscriber server (not the best option for us as we have a mix of different databases on the server that we wish to failover, changing datasources for a couple dozen different applications would be a PITA)
-or-
2) rename the subscriber server to be the same as the publisher (preferred as this would be transparent to the clients).
would this be correct?
and with either method, how do you get back to the original publisher/subscriber schema after the error is corrected (with the least amount of downtime)?
thanks in advance to clarify this!
June 14, 2011 at 10:44 am
Its not impossible to rename the subscriber but it is messy.
My preference would be to use a dns alias as the primary pointer and switch between the two.
You will have to manually clean up the subscriber side replication and be prepared for some downtime to swtich back once the publisher is back online.
June 15, 2011 at 12:06 am
thanks. a dns alias is a better idea.
what is the procedure to switch back to the publisher after the failover error is resolved? do you need to re-setup the replication from scratch again?
also, is there any way of configuring an automatic failover?
June 20, 2011 at 1:12 am
Use failover Server in the connection string of an application
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
June 21, 2011 at 8:30 am
Using transactional replication for DR is not easy. You'll likely run into issues with identity columns when you switch to the subscriber instance. Please see the following:
http://www.replicationanswers.com/DR_Using_TR_in_SQL2005.asp
June 27, 2011 at 2:50 am
To get your publisher back online, you will have to
1. drop existing subscription
2. restore subscriber database to the publisher server with don't keep replication settings option
3. setup publication
4. Recreate subscription
June 27, 2011 at 3:06 am
Automatic fail over is possible only with Clustering and database mirroring , replication will not meet any kind of DR requirement. Replication is only meant to scale out a database such as have a production database of your OLTP system and a have query able replicated database for you reporting needs .
If your interested in doing a manual fail over you have other options such as log shipping etc.
June 27, 2011 at 3:53 am
Jayanth_Kurup (6/27/2011)
Replication is only meant to scale out a database such as have a production database of your OLTP system and a have query able replicated database for you reporting needs .
I disagree. Admittedly, its not the most elegant solution in most cases and can be messy to failover, but the point is it can be done and is therefore an option depending on your needs.
Microsoft reference it along with all other DR methods. The MS article above also discusses it as a High Availability solution.
Description of disaster recovery options for Microsoft SQL Server
June 27, 2011 at 4:30 am
I admit that replication is not only for scalability however from the article you mentioned
The definition of high availability below hardly applies to replication I think.
A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.
My case being that , if your not looking for automatic fail over then there are simpler and more easier DR plans than Replication that can be considered.
June 27, 2011 at 4:45 am
Jayanth_Kurup (6/27/2011)
I admit that replication is not only for scalability however from the article you mentionedThe definition of high availability below hardly applies to replication I think.
A high-availability solution masks the effects of a hardware or software failure and maintains the availability of applications so that the perceived downtime for users is minimized.
My case being that , if your not looking for automatic fail over then there are simpler and more easier DR plans than Replication that can be considered.
Reading that definition to the letter neither does Log shipping and mirroring without a witness.
As always it depends on your requirements and what the company is willing to pay for. The DR server may also serve as a live reporting server perhaps?
Without asking questions you cannot say this technology doesnt fit OLDCHAPPY's requirements. He may have already assessed the alternatives. In my current position it would be obsurd to suggest using such a technology as cost isnt an issue, but in previous positions I was always looking for the best cost benefit and replication would work in certain situations.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply