Post reply

SQL 2012 AG/FCI Failover

  • April 16, 2013 at 1:43 am

    #273602

    Hi All

    My Setup:

    >> 3 Servers (Node1-Node2-Node3) all part of the same Cluster "Cluster1"

    >> SQL installed as clustered on Node2 & Node3, producing "SQLCluster"

    >> SQL installed as nonclustered on Node1, producing "Node1\SQL"

    >> Availability Group has been set up between "SQLCluster" & "Node1\SQL"

    Quorum: What is the recommended Quorum configuration for my setup, I'm assuming Node majority.

    DTC: Should all the nodes be a possible owner of DTC? Should the DTC disk be presented to all the nodes?

    Failover: When configuring the failover mode, I am unable to change it to Automatic, is Automatic failover not supported when you combine an AG with an FCI or am I missing something?

    Thanks

  • April 16, 2013 at 2:08 am

    #1606729

    SQLSACT (4/16/2013)

    Hi All

    My Setup:

    >> 3 Servers (Node1-Node2-Node3) all part of the same Cluster "Cluster1"

    >> SQL installed as clustered on Node2 & Node3, producing "SQLCluster"

    >> SQL installed as nonclustered on Node1, producing "Node1\SQL"

    >> Availability Group has been set up between "SQLCluster" & "Node1\SQL"

    Looks ok to me

    SQLSACT (4/16/2013)

    Quorum: What is the recommended Quorum configuration for my setup, I'm assuming Node majority.

    Correct, for a 3 node cluster node majority is recommended.

    Do the servers span multiple sites at all?

    SQLSACT (4/16/2013)

    DTC: Should all the nodes be a possible owner of DTC? Should the DTC disk be presented to all the nodes?

    Yes and yes.

    SQLSACT (4/16/2013)

    Failover: When configuring the failover mode, I am unable to change it to Automatic, is Automatic failover not supported when you combine an AG with an FCI or am I missing something?

    Thanks

    This link details the following

  • FCIs do not support automatic failover by availability groups: FCIs do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can be configured for manual failover only.

    • -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 16, 2013 at 2:17 am

    #1606731

    Thanks

    Quorum: What is the recommended Quorum configuration for my setup, I'm assuming Node majority.

    Correct, for a 3 node cluster node majority is recommended.

    Do the servers span multiple sites at all?

    Nope - All Servers are in the same DC

    Would it make a difference if it was spanned across multiple sites?

    DTC: Should all the nodes be a possible owner of DTC? Should the DTC disk be presented to all the nodes?

    Yes and yes.

    Doesn't this take away from the seperate storage selling point of AG's combined with FCI?

    Failover: When configuring the failover mode, I am unable to change it to Automatic, is Automatic failover not supported when you combine an AG with an FCI or am I missing something?

    Thanks

    This link details the following

    FCIs do not support automatic failover by availability groups: FCIs do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can be configured for manual failover only.

    I'm no pro at HA but I can't seem to think of a logical reason why automatic failover is not supported when you have an FCI.

    Hmm, Gonna have to rethink my approach then

    So it's basically a tradeoff, You can have 2 nodes available to your primary but then you wont have automatic failover for your AG OR you can have a single node available to your primary and then have the safety of Automatic failover.

  • April 16, 2013 at 2:51 am

    #1606737

    SQLSACT (4/16/2013)

    Nope - All Servers are in the same DC

    DC?

    SQLSACT (4/16/2013)

    Would it make a difference if it was spanned across multiple sites?

    It's a consideration, yes.

    SQLSACT (4/16/2013)

    Doesn't this take away from the seperate storage selling point of AG's combined with FCI?

    No, the storage for the MSDTC is not part of the failover cluster instance.

    SQLSACT (4/16/2013)

    I'm no pro at HA but I can't seem to think of a logical reason why automatic failover is not supported when you have an FCI.

    What would happen if the AO group attempted automatic failover whilst the FCI was in failover mode?

    SQLSACT (4/16/2013)

    Hmm, Gonna have to rethink my approach then

    So it's basically a tradeoff, You can have 2 nodes available to your primary but then you wont have automatic failover for your AG OR you can have a single node available to your primary and then have the safety of Automatic failover.

    It is what it is, read the pre reqs and restrictions carefully to ensure you have no further surprises.

    Keep an eye out for my next major article around this exact subject, Combining AO with FCI 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 16, 2013 at 3:05 am

    #1606739

    Perry Whittle (4/16/2013)

    SQLSACT (4/16/2013)

    Nope - All Servers are in the same DC

    DC?

    Datacenter

    SQLSACT (4/16/2013)

    Doesn't this take away from the seperate storage selling point of AG's combined with FCI?

    No, the storage for the MSDTC is not part of the failover cluster instance.

    By this do you mean it's not part of the storage allocated to the clustered instance of SQL Server?

    Currently my DTC is part of the cluster but not part of the SQL group in the cluster, however, the DTC disk has only been presented to Node2 & Node3 - Node1 has it's own DTC disk, which I realize now is moot.

    My storage setup is as follows

    I: Drive presented to Node2 & Node3 - For SQL data and log files

    X: Drive Drive presented to Node2 & Node3 for DTC

    I: Drive presented only to Node1 - For SQL data and log files

    X: Drive presented only to Node1 for DTC

    Corrections?

  • April 16, 2013 at 3:31 am

    #1606743

    SQLSACT (4/16/2013)

    By this do you mean it's not part of the storage allocated to the clustered instance of SQL Server?

    Yep, MSDTC has absolutely nothing to do with SQL Server, SQL Server makes use of it for remote transactions but then so do other Windows services.

    SQLSACT (4/16/2013)

    Currently my DTC is part of the cluster but not part of the SQL group in the cluster, however, the DTC disk has only been presented to Node2 & Node3 - Node1 has it's own DTC disk, which I realize now is moot.

    With all nodes on one site it would make sense to have the DTC available on as many nodes as possible

    SQLSACT (4/16/2013)

    My storage setup is as follows

    I: Drive presented to Node2 & Node3 - For SQL data and log files

    X: Drive Drive presented to Node2 & Node3 for DTC

    I: Drive presented only to Node1 - For SQL data and log files

    X: Drive presented only to Node1 for DTC

    Corrections?

    For the instance, no, makes sense to have the same drive letter and datafile paths.

    DTC i would fail across all nodes, but it's your choice

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 16, 2013 at 3:39 am

    #1606745

    Perry Whittle (4/16/2013)

    SQLSACT (4/16/2013)

    By this do you mean it's not part of the storage allocated to the clustered instance of SQL Server?

    Yep, MSDTC has absolutely nothing to do with SQL Server, SQL Server makes use of it for remote transactions but then so do other Windows services.

    SQLSACT (4/16/2013)

    Currently my DTC is part of the cluster but not part of the SQL group in the cluster, however, the DTC disk has only been presented to Node2 & Node3 - Node1 has it's own DTC disk, which I realize now is moot.

    With all nodes on one site it would make sense to have the DTC available on as many nodes as possible

    SQLSACT (4/16/2013)

    My storage setup is as follows

    I: Drive presented to Node2 & Node3 - For SQL data and log files

    X: Drive Drive presented to Node2 & Node3 for DTC

    I: Drive presented only to Node1 - For SQL data and log files

    X: Drive presented only to Node1 for DTC

    Corrections?

    For the instance, no, makes sense to have the same drive letter and datafile paths.

    DTC i would fail across all nodes, but it's your choice

    Thanks

    DTC i would fail across all nodes, but it's your choice

    Because Node1 has SQL installed as nonclustered, is it safe to say that it's not absolutely necessay to share DTC across all 3 nodes. If I failover my AG to Node1, if DTC is needed when the AG is running on Node1, won't it just use the DTC service on that server?

    Thanks

  • April 16, 2013 at 4:30 am

    #1606757

    My current setup is just a test setup.

    Basically, what I am trying to do is way up is weigh up the Pros and Cons of moving to SQL 2012 AG's. We are definately moving to SQL Server 2012 but as far as changing our high availability, that's not decided as yet.

    Our current high availability setup is a 2 node Cluster in a Log Shipping setup with a 3rd server in a different cluster.

    I'm trying to establish if going to SQL 2012 HA will be beneficial in any way or if it will just be easier to stay with the current setup.

  • April 16, 2013 at 4:59 am

    #1606768

    SQLSACT (4/16/2013)

    Because Node1 has SQL installed as nonclustered, is it safe to say that it's not absolutely necessay to share DTC across all 3 nodes.

    yes you could argue that. But since all 3 nodes are in the same cluster on the same site the DTC resource would be even more highly available. It depends on your requirements for DTC, also under Windows 2008 you may now have multiple DTC resources whereas you couldn't under Windows 2003.

    SQLSACT (4/16/2013)

    If I failover my AG to Node1, if DTC is needed when the AG is running on Node1, won't it just use the DTC service on that server?

    Thanks

    Yes, but what about an inflight transaction that was in process at the time, that will not be recoverable by the local DTC.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 16, 2013 at 5:07 am

    #1606769

    Perry Whittle (4/16/2013)

    SQLSACT (4/16/2013)

    Because Node1 has SQL installed as nonclustered, is it safe to say that it's not absolutely necessay to share DTC across all 3 nodes.

    yes you could argue that. But since all 3 nodes are in the same cluster on the same site the DTC resource would be even more highly available. It depends on your requirements for DTC, also under Windows 2008 you may now have multiple DTC resources whereas you couldn't under Windows 2003.

    SQLSACT (4/16/2013)

    If I failover my AG to Node1, if DTC is needed when the AG is running on Node1, won't it just use the DTC service on that server?

    Thanks

    Yes, but what about an inflight transaction that was in process at the time, that will not be recoverable by the local DTC.

    Thanks

    Yes, but what about an inflight transaction that was in process at the time, that will not be recoverable by the local DTC

    Makes sense - Thanks

  • April 16, 2013 at 5:08 am

    #1606770

    SQLSACT (4/16/2013)

    Perry Whittle (4/16/2013)

    SQLSACT (4/16/2013)

    Because Node1 has SQL installed as nonclustered, is it safe to say that it's not absolutely necessay to share DTC across all 3 nodes.

    yes you could argue that. But since all 3 nodes are in the same cluster on the same site the DTC resource would be even more highly available. It depends on your requirements for DTC, also under Windows 2008 you may now have multiple DTC resources whereas you couldn't under Windows 2003.

    SQLSACT (4/16/2013)

    If I failover my AG to Node1, if DTC is needed when the AG is running on Node1, won't it just use the DTC service on that server?

    Thanks

    Yes, but what about an inflight transaction that was in process at the time, that will not be recoverable by the local DTC.

    Thanks

    Yes, but what about an inflight transaction that was in process at the time, that will not be recoverable by the local DTC

    Makes sense - Thanks

    Do inflight transactions exist if the failover of an AG - I thought it only existed when an instance of SQL Server is failed over to another node

  • April 16, 2013 at 5:35 am

    #1606774

    yes but i'm assuming you'll have other databases that are not part of the AO group or even other instances that may use DTC, if you don't then don't worry. As i said its your choice.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • April 16, 2013 at 5:39 am

    #1606776

    Thanks for your help

  • April 17, 2013 at 12:13 am

    #1607121

    No problem, don't forget to check the pre reqs and restrictions carefully, it can save you a whole lot of time later on.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • May 7, 2013 at 5:07 am

    #1612915

  • FCIs do not support automatic failover by availability groups: FCIs do not support automatic failover by availability groups, so any availability replica that is hosted by an FCI can be configured for manual failover only.

    • [/quote]

    Hi Perry

    I know this post is a few weeks old, I just wanted to run something by you.

    So I understand that when combining FCI with AG, automatic database failover is gone, but you still have automatic instance failover. Am I correct in this?

    When you have an AG with no FCI, you have automatic database failover but no instance failover. Am I correct in this?

    Are you aware of any way to achieve automatic failover of an instance and database using 2012 AlwaysOn functionality?

    Thanks

Viewing 15 posts - 1 through 15 (of 19 total)

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