SQL AG + FCI

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  January 20, 2015 at 12:05 am

  #296793

  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 step 1 ?

  Thanks

• Perry Whittle

  SSC Guru

  Points: 234012

  More actions

  January 20, 2015 at 4:36 am

  #1771361

  SQLSACT (1/20/2015)

  ---

  Hi all

  We are looking at implementing AlwaysOn AG combined with a Failover Clustered Instance of SQL Server.

  First question is why?

  You're taking the decision to implement an AlwaysOn group that will remove the shared storage dependency and then adding it straight back again by including an FCI.

  SQLSACT (1/20/2015)

  ---

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

  That is correct there is no automatic failover, the workaround is to remove the FCI and set a synchronous automatic partner 😉

  SQLSACT (1/20/2015)

  ---

  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 step 1 ?

  Thanks

  If a failover occurred the query would be terminated since the sql server instance will restart. Is the secondary a synchronous partner or asynchronous partner?

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

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