SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL 2012 and AG's


SQL 2012 and AG's

Author
Message
Bobby Glover
Bobby Glover
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 751
Recent post from Brent Ozar. Is what he is saying correct.

In SQL 2012, if your primary drops offline or the cluster loses quorum, the readable replica databases drop offline. (This is hilarious because it’s right when you really want to be able to query the secondaries.) No way to control it – it’s totally automatic. In SQL 14, the secondaries remain online and readable when the primaries aren’t available.

Also are secondaries always just readable copies and therefore can not be used in a DR plan.
Andy Hyslop
Andy Hyslop
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1015 Visits: 3037
Bobby Glover (6/14/2013)
Recent post from Brent Ozar. Is what he is saying correct.

In SQL 2012, if your primary drops offline or the cluster loses quorum, the readable replica databases drop offline. (This is hilarious because it’s right when you really want to be able to query the secondaries.) No way to control it – it’s totally automatic. In SQL 14, the secondaries remain online and readable when the primaries aren’t available.

Also are secondaries always just readable copies and therefore can not be used in a DR plan.


To my knowledge yes if the cluster looses quorum the yes it will drop offline depending on your setup, a way around this is to give a quorum vote to a file on a network location this will enable a majority vote.

Secondaries can be readable copies (they don't have to be) but they certainly can be used in a DR plan, if you use synchronous commit then if the primary goes offline then the secondary will automatically fail over and become the primary, if you use asynchronous commit then the fail over has to be done manually.

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
Bobby Glover
Bobby Glover
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 751
Thanks, does this apply to when the Primary db falls offline too.
Lets say san administrator removed the disks that held the mdf and Ldf files. This would also force the sec, offline.

If this is the case that is very poor.
Bobby Glover
Bobby Glover
Old Hand
Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)Old Hand (335 reputation)

Group: General Forum Members
Points: 335 Visits: 751
Ok, if my secondaries go offline. Can I bring them online
manually and turn one into the Primary.
Steve-3_5_7_9
Steve-3_5_7_9
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1496 Visits: 1599
Bobby Glover (6/14/2013)
Thanks, does this apply to when the Primary db falls offline too.
Lets say san administrator removed the disks that held the mdf and Ldf files. This would also force the sec, offline.

If this is the case that is very poor.


If set to "automatic failover", synchronous commit, it should fail over to the secondary provided a quorum exists. You should consider created a file share witness for a two node cluster in order to retain a quorum.

If you don't have a quorum, then it will go "offline" which is how a physical cluster also works. A physical cluster goes "offline" when the quorum is lost.

Here are some MS articles regarding file share witness and HA.

http://blogs.technet.com/b/askpfeplat/archive/2012/06/27/clustering-what-exactly-is-a-file-share-witness-and-when-should-i-use-one.aspx

http://technet.microsoft.com/en-us/library/cc770620(v=ws.10).aspx

Steve



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search