Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL 2012 AG/FCI Failover Expand / Collapse
Author
Message
Posted Tuesday, April 16, 2013 1:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:34 AM
Points: 1,375, Visits: 2,659
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



Post #1442627
Posted Tuesday, April 16, 2013 2:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 6,162, Visits: 13,305
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"
    Post #1442638
    Posted Tuesday, April 16, 2013 2:17 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 3:34 AM
    Points: 1,375, Visits: 2,659
    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.

    Post #1442640
    Posted Tuesday, April 16, 2013 2:51 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 8:08 AM
    Points: 6,162, Visits: 13,305
    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"
    Post #1442649
    Posted Tuesday, April 16, 2013 3:05 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 3:34 AM
    Points: 1,375, Visits: 2,659
    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?

    Post #1442653
    Posted Tuesday, April 16, 2013 3:31 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 8:08 AM
    Points: 6,162, Visits: 13,305
    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"
    Post #1442658
    Posted Tuesday, April 16, 2013 3:39 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 3:34 AM
    Points: 1,375, Visits: 2,659
    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
    Post #1442661
    Posted Tuesday, April 16, 2013 4:30 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 3:34 AM
    Points: 1,375, Visits: 2,659
    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.
    Post #1442673
    Posted Tuesday, April 16, 2013 4:59 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 8:08 AM
    Points: 6,162, Visits: 13,305
    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"
    Post #1442685
    Posted Tuesday, April 16, 2013 5:07 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Today @ 3:34 AM
    Points: 1,375, Visits: 2,659
    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
    Post #1442686
    « Prev Topic | Next Topic »

    Add to briefcase 12»»

    Permissions Expand / Collapse