High Safety W/O Automatic Failover (Synchronous)

Question

High Safety W/O Automatic Failover (Synchronous)

Jonathan Marshall

SSCrazy

Points: 2633
More actions
July 8, 2011 at 1:24 pm

#241314

Trying to get a list of all the possible scenarios where there would have to be a forced failover besides a planned failover with (High Safety w/o Automatic Failover Synchronous)
Seems as if (High Safety w/o Automatic Failover Synchronous) isn't the best setup for disaster recovery or unplanned downtime.
With this in mind what could be put in place to make sure this mode of HA can be more proactive in event of an disaster.
Articles mention that if you cannot get to the principal server and manually failover to the mirrored database then you have no choice but to force the failover meaning you are going to lose some data.
Please let me know your thoughts and also scenarios.
What are the scenarios when forced failover would have to be used on a database mirroring session?
1). Principal instance has failed
2). Principal database(s) has become corrupt
Currently setup:
High Safety w/o Automatic Failover Synchronous
Also looking into combining log shipping with database mirroring.
Jonathan

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

High safety with automatic fail over would give you DR similar (not exactly)to that of clustering so why not use it ? Whats the exact DR scenario your worried about ?

The witness server will take care of elevating the mirror when it needs to do a DR

Jayanth Kurup[/url]

Jonathan Marshall SSCrazy Points: 2633 More actions · Answer 2

In the setup that I have there is not a Witness server.

So if one cannot get to the Principal server to initiate the failover then I believe that the only way to make the mirror the principal is to force it correct?

If it has to be forced then there is possible data loss.

Jonathan

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 3

http://msdn.microsoft.com/en-us/library/ms179344.aspx

Your right , without a witness server the mirror would not know what actions to perform in the event of a failure. In these cases its assumed the DBA would perform the required steps to promote the mirror to primary and then failover as required.

Jayanth Kurup[/url]

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

There will be no data loss since in high availability mode they transactions are first committed on the mirror before they are committed on the principal

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Jonathan Marshall (7/8/2011)
If it has to be forced then there is possible data loss.

You have to force it, but in synchronous mirroring (because transactions are committed on both sides before they are complete) there won't¹ be data loss

(1) Unless something really wacky happened or the synchronised state had been lost prior to the principal's failure. I think.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

GilaMonster (7/8/2011)
Jonathan Marshall (7/8/2011)
If it has to be forced then there is possible data loss.
You have to force it, but in synchronous mirroring (because transactions are committed on both sides before they are complete) there won't¹ be data loss
(1) Unless something really wacky happened or the synchronised state had been lost prior to the principal's failure. I think.

That is my understanding as well Gail.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Robert Davis One Orange Chip Points: 28027 More actions · Answer 7

GilaMonster (7/8/2011)
You have to force it, but in synchronous mirroring (because transactions are committed on both sides before they are complete) there won't¹ be data loss
(1) Unless something really wacky happened or the synchronised state had been lost prior to the principal's failure. I think.

The only time that there would be a potential for data loss when using synchronous mirroring is if the session was not in synchronized state. For example, if the mirror was disconnected at the time of failure or if the mirror had been disconnected and had reconnected but the partners were not yet synchronized again.

Also, you should be aware that when you force server allowing data loss, the potential for data loss doesn't occur the original principal comes back online and the mirroring session is restarted. You have an opportunity to bring the principal online and recover the data from it manually.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Jonathan Marshall SSCrazy Points: 2633 More actions · Answer 8

I see that's right. Since the mirror gets written to first then I would not have to worry about data loss.

So the only real disaster scenario with High Safety w/o Automatic Failover would be if the principal is unavailable and or disconnected.

This is when the Force service option has to be used to make the mirror into the principal??

Jonathan

Jonathan Marshall SSCrazy Points: 2633 More actions · Answer 9

Excellent information and guidance definitely helps clarify.

I just purchased that book to gather more information about mirroring.

thanks,

Jonathan