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


SQL 2012 AG/FCI Failover


SQL 2012 AG/FCI Failover

Author
Message
SQLSACT
SQLSACT
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2572 Visits: 2936
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
Perry Whittle
Perry Whittle
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26591 Visits: 17335
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" ;-)
    SQLSACT
    SQLSACT
    SSCrazy
    SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

    Group: General Forum Members
    Points: 2572 Visits: 2936
    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.
    Perry Whittle
    Perry Whittle
    One Orange Chip
    One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

    Group: General Forum Members
    Points: 26591 Visits: 17335
    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" ;-)
    SQLSACT
    SQLSACT
    SSCrazy
    SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

    Group: General Forum Members
    Points: 2572 Visits: 2936
    Perry Whittle (4/16/2013)
    [quote]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?
    Perry Whittle
    Perry Whittle
    One Orange Chip
    One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

    Group: General Forum Members
    Points: 26591 Visits: 17335
    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" ;-)
    SQLSACT
    SQLSACT
    SSCrazy
    SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

    Group: General Forum Members
    Points: 2572 Visits: 2936
    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
    SQLSACT
    SQLSACT
    SSCrazy
    SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

    Group: General Forum Members
    Points: 2572 Visits: 2936
    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.
    Perry Whittle
    Perry Whittle
    One Orange Chip
    One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

    Group: General Forum Members
    Points: 26591 Visits: 17335
    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" ;-)
    SQLSACT
    SQLSACT
    SSCrazy
    SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

    Group: General Forum Members
    Points: 2572 Visits: 2936
    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
    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