Post reply

AG + Failover Clustered Instance

  • December 12, 2013 at 12:21 am

    #280104

    Hi all

    We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server. This is the setup:

    >> 3 Nodes (A, B, C) joined to the same cluster: WinCluster

    >> Nodes A + B share a clustered instance of SQL Server: SQLCluster

    >> Node C has a non-clustered instance of SQL Server installed

    >> AlwaysOn Availability Group created between SQLCluster & Node C

    >> Node C has been set to readable.

    This type of AG configuration will not allow automatic failover, correct? Is there any way around this?

    What would happen if:

    1. I Start a long select query on Node C

    2. While the above query is running, the SQL instance on SQLCluster fails over from Node A to Node B. Would the database on Node C go into a resolving state? Would it drop my connection created in part 1 ?

    Thanks

  • December 12, 2013 at 12:33 am

    #1672843

    Hi,

    Can you be more specific on "AlwaysOn Availability Group created between SQLCluster & Node C"

    Which commit mode did you use? Synchronous or Asynchronous?

    And if you used Synchronous, did you activate the automatic failover option?

    Regards

    Pieter

  • December 12, 2013 at 12:44 am

    #1672847

    Going with Synchronous

    You can't have automatic failover in this type of configuration

  • December 12, 2013 at 12:59 am

    #1672851

    Why can't you have automatic failover?

    If it's synchronous you have the option "automatic failover"

    See http://technet.microsoft.com/en-us/library/hh213151.aspx

  • December 12, 2013 at 1:08 am

    #1672854

    See http://technet.microsoft.com/en-us/library/ms189134.aspx

  • December 12, 2013 at 1:43 am

    #1672858

    When a failover occurs, you actually restarting the SQL Server Instance on your cluster, so you're losing connection with the primary

    As a result, your secondary DB will get a state NOT SYNCHRONIZED and current connection will be dropped.

    This issue is fixed in SQL 2014

    When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.

    Check the AlwaysOn Enhancements

  • December 12, 2013 at 4:00 am

    #1672880

    Always on automatic failover from failover cluster instance is not supported.

    Regards,
    Raj

    http://Strictlysql.blogspot.com

  • December 12, 2013 at 5:23 am

    #1672900

    Whether in synchronous or asynconrous commit node , if the Availability Replica is a part of Failover Cluster, Automatic failovers are not supported.

    Refer http://msdn.microsoft.com/en-us/library/hh923056.aspx - Page 12 - "Automatic Failovers and FCI "

    "When an FCI is joined to an availability group, the nodes within the FCI can fail over to each other automatically, but the availability replica on the FCI must manually fail over to or from another replica in the availability group. AlwaysOn Availability Groups does not support automatic failovers to and from an FCI. This should not be a cause for concern because you generally do not want automatic failovers to a disaster recovery site, because you do not want an automatic failover to happen simply because your primary and remote sites lose connectivity with each other. "

    Regards,
    Raj

    http://Strictlysql.blogspot.com

  • December 16, 2013 at 8:15 am

    #1673716

    SQLSACT (12/12/2013)

    Hi all

    We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server. This is the setup:

    >> 3 Nodes (A, B, C) joined to the same cluster: WinCluster

    >> Nodes A + B share a clustered instance of SQL Server: SQLCluster

    >> Node C has a non-clustered instance of SQL Server installed

    >> AlwaysOn Availability Group created between SQLCluster & Node C

    >> Node C has been set to readable.

    This type of AG configuration will not allow automatic failover, correct? Is there any way around this?

    What would happen if:

    1. I Start a long select query on Node C

    2. While the above query is running, the SQL instance on SQLCluster fails over from Node A to Node B. Would the database on Node C go into a resolving state? Would it drop my connection created in part 1 ?

    Thanks

    I think you connection will not drop because your connection string will be pointed to the AG group listener name.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • December 16, 2013 at 11:14 pm

    #1673947

    The connection will drop because the secondary replica database will go into a resolving state

  • December 16, 2013 at 11:46 pm

    #1673951

    SQLSACT (12/12/2013)

    Hi all

    We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server. This is the setup:

    >> 3 Nodes (A, B, C) joined to the same cluster: WinCluster

    >> Nodes A + B share a clustered instance of SQL Server: SQLCluster

    >> Node C has a non-clustered instance of SQL Server installed

    >> AlwaysOn Availability Group created between SQLCluster & Node C

    >> Node C has been set to readable.

    This type of AG configuration will not allow automatic failover, correct? Is there any way around this?

    What would happen if:

    1. I Start a long select query on Node C

    2. While the above query is running, the SQL instance on SQLCluster fails over from Node A to Node B. Would the database on Node C go into a resolving state? Would it drop my connection created in part 1 ?

    Thanks

    Hi,

    OK so your running your query on your read-only database on Node C and SQL Server fails over between Nodes A and B, well C does not go down - if it did it would mute the point of having it - so your query will continue to run. If how ever you were connected to your primary instance via your listener your connection would drop and the re connect and the query assuming is in an insert or update should start again / carry on.

    With your design, MS will not support automatic fail-over back to the primary instance ( your sql cluster) this will be a manual process from your single node DR box, it will however support the automatic fail over to the single instance.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • December 16, 2013 at 11:56 pm

    #1673952

    Ford Fairlane (12/16/2013)

    SQLSACT (12/12/2013)

    Hi all

    We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server. This is the setup:

    >> 3 Nodes (A, B, C) joined to the same cluster: WinCluster

    >> Nodes A + B share a clustered instance of SQL Server: SQLCluster

    >> Node C has a non-clustered instance of SQL Server installed

    >> AlwaysOn Availability Group created between SQLCluster & Node C

    >> Node C has been set to readable.

    This type of AG configuration will not allow automatic failover, correct? Is there any way around this?

    What would happen if:

    1. I Start a long select query on Node C

    2. While the above query is running, the SQL instance on SQLCluster fails over from Node A to Node B. Would the database on Node C go into a resolving state? Would it drop my connection created in part 1 ?

    Thanks

    Hi,

    OK so your running your query on your read-only database on Node C and SQL Server fails over between Nodes A and B, well C does not go down - if it did it would mute the point of having it - so your query will continue to run. If how ever you were connected to your primary instance via your listener your connection would drop and the re connect and the query assuming is in an insert or update should start again / carry on.

    With your design, MS will not support automatic fail-over back to the primary instance ( your sql cluster) this will be a manual process from your single node DR box, it will however support the automatic fail over to the single instance.

    I'll have to disagree here

    If SQL Server failover between Node A and B, Node C would go into a resolving state, however brief. This will cause a connection drop.

    Also, I disagree with the automatic failover to the single instance (Node C). It is well documented that automatic failover is not allowed.

  • December 17, 2013 at 12:23 am

    #1673959

    SQLSACT (12/16/2013)

    Ford Fairlane (12/16/2013)

    SQLSACT (12/12/2013)

    Hi all

    We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server. This is the setup:

    >> 3 Nodes (A, B, C) joined to the same cluster: WinCluster

    >> Nodes A + B share a clustered instance of SQL Server: SQLCluster

    >> Node C has a non-clustered instance of SQL Server installed

    >> AlwaysOn Availability Group created between SQLCluster & Node C

    >> Node C has been set to readable.

    This type of AG configuration will not allow automatic failover, correct? Is there any way around this?

    What would happen if:

    1. I Start a long select query on Node C

    2. While the above query is running, the SQL instance on SQLCluster fails over from Node A to Node B. Would the database on Node C go into a resolving state? Would it drop my connection created in part 1 ?

    Thanks

    Hi,

    OK so your running your query on your read-only database on Node C and SQL Server fails over between Nodes A and B, well C does not go down - if it did it would mute the point of having it - so your query will continue to run. If how ever you were connected to your primary instance via your listener your connection would drop and the re connect and the query assuming is in an insert or update should start again / carry on.

    With your design, MS will not support automatic fail-over back to the primary instance ( your sql cluster) this will be a manual process from your single node DR box, it will however support the automatic fail over to the single instance.

    I'll have to disagree here

    If SQL Server failover between Node A and B, Node C would go into a resolving state, however brief. This will cause a connection drop.

    Also, I disagree with the automatic failover to the single instance (Node C). It is well documented that automatic failover is not allowed.

    Node C in the question is the replica with synchronous commit - which allows it to be available for automatic fail over but you can not automatically fail to a sql cluster - if you go through the process you will see..

    I have a 3 node windows cluster as described above.

    SQLOC00-021\APP is a two node sql cluster Availability mode is Manual

    SQLDR01\APP_DR is a stand alone sql instance Availability mode is Automatic

    And as soon as I find the tech ed document around this I will post the link. But it works and tested.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

  • December 17, 2013 at 12:26 am

    #1673962

    Ford Fairlane (12/17/2013)

    SQLSACT (12/16/2013)

    Ford Fairlane (12/16/2013)

    SQLSACT (12/12/2013)

    Hi all

    We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server. This is the setup:

    >> 3 Nodes (A, B, C) joined to the same cluster: WinCluster

    >> Nodes A + B share a clustered instance of SQL Server: SQLCluster

    >> Node C has a non-clustered instance of SQL Server installed

    >> AlwaysOn Availability Group created between SQLCluster & Node C

    >> Node C has been set to readable.

    This type of AG configuration will not allow automatic failover, correct? Is there any way around this?

    What would happen if:

    1. I Start a long select query on Node C

    2. While the above query is running, the SQL instance on SQLCluster fails over from Node A to Node B. Would the database on Node C go into a resolving state? Would it drop my connection created in part 1 ?

    Thanks

    Hi,

    OK so your running your query on your read-only database on Node C and SQL Server fails over between Nodes A and B, well C does not go down - if it did it would mute the point of having it - so your query will continue to run. If how ever you were connected to your primary instance via your listener your connection would drop and the re connect and the query assuming is in an insert or update should start again / carry on.

    With your design, MS will not support automatic fail-over back to the primary instance ( your sql cluster) this will be a manual process from your single node DR box, it will however support the automatic fail over to the single instance.

    I'll have to disagree here

    If SQL Server failover between Node A and B, Node C would go into a resolving state, however brief. This will cause a connection drop.

    Also, I disagree with the automatic failover to the single instance (Node C). It is well documented that automatic failover is not allowed.

    Node C in the question is the replica with synchronous commit - which allows it to be available for automatic fail over but you can not automatically fail to a sql cluster - if you go through the process you will see..

    I have a 3 node windows cluster as described above.

    SQLOC00-021\APP is a two node sql cluster Availability mode is Manual

    SQLDR01\APP_DR is a stand alone sql instance Availability mode is Automatic

    And as soon as I find the tech ed document around this I will post the link. But it works and tested.

    An earlier reply in this thread......

    Refer http://msdn.microsoft.com/en-us/library/hh923056.aspx - Page 12 - "Automatic Failovers and FCI "

    "When an FCI is joined to an availability group, the nodes within the FCI can fail over to each other automatically, but the availability replica on the FCI must manually fail over to or from another replica in the availability group. AlwaysOn Availability Groups does not support automatic failovers to and from an FCI. This should not be a cause for concern because you generally do not want automatic failovers to a disaster recovery site, because you do not want an automatic failover to happen simply because your primary and remote sites lose connectivity with each other. "

    Regards,

    Raj

  • December 17, 2013 at 12:37 am

    #1673965

    SQLSACT (12/17/2013)

    Ford Fairlane (12/17/2013)

    SQLSACT (12/16/2013)

    Ford Fairlane (12/16/2013)

    SQLSACT (12/12/2013)

    Hi all

    We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server. This is the setup:

    >> 3 Nodes (A, B, C) joined to the same cluster: WinCluster

    >> Nodes A + B share a clustered instance of SQL Server: SQLCluster

    >> Node C has a non-clustered instance of SQL Server installed

    >> AlwaysOn Availability Group created between SQLCluster & Node C

    >> Node C has been set to readable.

    This type of AG configuration will not allow automatic failover, correct? Is there any way around this?

    What would happen if:

    1. I Start a long select query on Node C

    2. While the above query is running, the SQL instance on SQLCluster fails over from Node A to Node B. Would the database on Node C go into a resolving state? Would it drop my connection created in part 1 ?

    Thanks

    Hi,

    OK so your running your query on your read-only database on Node C and SQL Server fails over between Nodes A and B, well C does not go down - if it did it would mute the point of having it - so your query will continue to run. If how ever you were connected to your primary instance via your listener your connection would drop and the re connect and the query assuming is in an insert or update should start again / carry on.

    With your design, MS will not support automatic fail-over back to the primary instance ( your sql cluster) this will be a manual process from your single node DR box, it will however support the automatic fail over to the single instance.

    I'll have to disagree here

    If SQL Server failover between Node A and B, Node C would go into a resolving state, however brief. This will cause a connection drop.

    Also, I disagree with the automatic failover to the single instance (Node C). It is well documented that automatic failover is not allowed.

    Node C in the question is the replica with synchronous commit - which allows it to be available for automatic fail over but you can not automatically fail to a sql cluster - if you go through the process you will see..

    I have a 3 node windows cluster as described above.

    SQLOC00-021\APP is a two node sql cluster Availability mode is Manual

    SQLDR01\APP_DR is a stand alone sql instance Availability mode is Automatic

    And as soon as I find the tech ed document around this I will post the link. But it works and tested.

    An earlier reply in this thread......

    Refer http://msdn.microsoft.com/en-us/library/hh923056.aspx - Page 12 - "Automatic Failovers and FCI "

    "When an FCI is joined to an availability group, the nodes within the FCI can fail over to each other automatically, but the availability replica on the FCI must manually fail over to or from another replica in the availability group. AlwaysOn Availability Groups does not support automatic failovers to and from an FCI. This should not be a cause for concern because you generally do not want automatic failovers to a disaster recovery site, because you do not want an automatic failover to happen simply because your primary and remote sites lose connectivity with each other. "

    Regards,

    Raj

    And this is why your DR site doesn't have a vote...Generally you will set your Primary node and a file share will have votes so if you lose connectivity to the DR it wont cause a fail over.

    Hope this helps...

    Ford Fairlane
    Rock and Roll Detective

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

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