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»»

AG + Failover Clustered Instance Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 12:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
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
Post #1522166
Posted Thursday, December 12, 2013 12:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:45 AM
Points: 11, Visits: 302
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
Post #1522172
Posted Thursday, December 12, 2013 12:44 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
Going with Synchronous

You can't have automatic failover in this type of configuration
Post #1522176
Posted Thursday, December 12, 2013 12:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:45 AM
Points: 11, Visits: 302
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
Post #1522182
Posted Thursday, December 12, 2013 1:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
See http://technet.microsoft.com/en-us/library/ms189134.aspx



  Post Attachments 
Capture.JPG (5 views, 28.45 KB)
Post #1522187
Posted Thursday, December 12, 2013 1:43 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 12:45 AM
Points: 11, Visits: 302
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[url=http://msdn.microsoft.com/en-us/library/bb510411%28v=sql.120%29.aspx][/url]
Post #1522193
Posted Thursday, December 12, 2013 4:00 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:49 PM
Points: 488, Visits: 1,286
Always on automatic failover from failover cluster instance is not supported.

Regards,
Raj

Strictlysql.blogspot.com
Post #1522221
Posted Thursday, December 12, 2013 5:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:49 PM
Points: 488, Visits: 1,286
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

Strictlysql.blogspot.com
Post #1522248
Posted Monday, December 16, 2013 8:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
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

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1523255
Posted Monday, December 16, 2013 11:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:44 AM
Points: 1,371, Visits: 2,597
The connection will drop because the secondary replica database will go into a resolving state
Post #1523531
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse