Click here to monitor SSC
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
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 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
Say Hey Kid
Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)Say Hey Kid (706 reputation)

Group: General Forum Members
Points: 706 Visits: 3023
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
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 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
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 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
SSC Eights!
SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)SSC Eights! (982 reputation)

Group: General Forum Members
Points: 982 Visits: 1584
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