March 10, 2014 at 9:24 am
I have set up an always on availability group on a cluster known as SQLHA, using 4 sql boxes test1 ,2,3,4 All on VM's. test1 is the primary, test2 is the automatic failover synchronous non readable, test3 manual failover asynchronous nonreadable, test4 manual failover synchronous readable. All well and good everything seems to be ok. doing manual failovers with the dashboard I can see everything working as expected. If i connect to SQLHA it connects to test1 and data is updateable
I tried to simulate a service failure by pulling the power on test1, the sql failover works fine and if i connect to test2 or test4 i can read the data and on test 2 read and write.
however if i connect to sqlha i can't read the data as SQL HA now connects to test3 which is the asynchronous non readble copy. Is there a setting in the windows clustering i need to confugure so that it fails to the other members of the cluster in the same order? I hope I'm missing something as if I cant give the clustername to an applications connection string i cant guarantee that the automatic sql failover database will be visible.
March 10, 2014 at 11:59 am
Are you using an AG listener to connect to the AG databases?
If so, the most reliable method is to use SQL Native Client 11 or JBDC v4.
Specify MultiSubnetFailover=True in the client connection string and set the listener to use RegisterAllProvidersIP to 1.
If you can't get onto the latest SQL client, the only reliable way to get the listener to pick up the correct IP address is to set RegisterAllProvidersIP to 0 and reduce the TTL of the listener so that DNS cache will be shorter.
You can read about both here:
http://msdn.microsoft.com/en-us/library/hh205662(v=vs.110).aspx
March 11, 2014 at 5:44 am
I didnt have a listener but i do now, I also altered the advanced policies of the cluster exclude the two manual failover boxes from being hosts.
I am not sure of the imlications here, if the two auto failover boxes are taken off line I would need to manuall failover the cluster in SQL and also change the settings in the windows cluster manager to allow these manual boxes to be hosts. I will be testing various scenarios to see how this wil work
March 11, 2014 at 11:11 am
You shouldnt need to touch the cluster configuration, i'm assuming you mean that you have removed cluster votes from some of the nodes?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2014 at 2:16 am
the most obvious thing from my last post is that i need a new keyboard
March 12, 2014 at 2:23 am
Because I simulated a failure by closing down the test1 server, the sql HA cluster failed over to test2 as expected, the windows cluster with a dns record of SQLHA resolved to test3 so a client connecting to SQLHA was unable to read the database as this was the asynchronous non readable copy.
I altered the possible owners list in the cluster management console so that test1 and test2 are now the only ones ticked
Now if I simulate failure by switching off test1 the dns of sqlha points at test2 which also holds the live copy of the database.
March 12, 2014 at 3:41 am
Martin Stephenson (3/12/2014)
Because I simulated a failure by closing down the test1 server, the sql HA cluster failed over to test2 as expected
You don't have a sql a cluster you have an availability group, there is a difference.
Martin Stephenson (3/12/2014)
the windows cluster with a dns record of SQLHA resolved to test3 so a client connecting to SQLHA
If i read this right youre using the Cluster client access point is that correct?
This is for cluster management only and should not be used for client connectivity.
Martin Stephenson (3/12/2014)
I altered the possible owners list in the cluster management console so that test1 and test2 are now the only ones tickedNow if I simulate failure by switching off test1 the dns of sqlha points at test2 which also holds the live copy of the database.
You do not need to touch the cluster configuration.
Have you created a listener yet for your AO group, this is the virtual client access point you would use to gain access to the AO group databases.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2014 at 3:51 am
Martin Stephenson (3/10/2014)
test2 is the automatic failover synchronous non readable
The readable secondary is not important at this time, what are the connection settings when in the primary role for test2, test3 and test4
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2014 at 3:52 am
Yes I am scrambling my terminology - High availability uses clustering serivices but isnt a cluster apologies for mangling my terms.
So by using the listener as the client access point and not the cluster name (not realy a cluster) I avoid the problem with connecting after a failure ?
The windows servers can all act as hosts and will manage that at a server level and SQL will manage my connections through the listener.
March 12, 2014 at 3:56 am
Perry Whittle (3/12/2014)
Martin Stephenson (3/10/2014)
test2 is the automatic failover synchronous non readableThe readable secondary is not important at this time, what are the connection settings when in the primary role for test2, test3 and test4
Sorry if I appear dim but which connection settings ?
March 12, 2014 at 4:03 am
Martin Stephenson (3/12/2014)
Yes I am scrambling my terminology - High availability uses clustering serivices but isnt a cluster apologies for mangling my terms.So by using the listener as the client access point and not the cluster name (not realy a cluster) I avoid the problem with connecting after a failure ?
The windows servers can all act as hosts and will manage that at a server level and SQL will manage my connections through the listener.
As i said the cluster client access point is purely for managing the windows cluster itself, you should not use this as an access point for the AO group, create a listener, this will route the connections into the AO group accordingly.
The listener, when created, is created as a cluster group\application. It has 3 resources consisting of virtual IP, virtual name and AlwaysOn Listener cluster resource. When failing over an AO group, the listener tracks the Primary replica. So
if Test1 was the primary, you would see the listener cluster group owned by this node in Failover Cluster Manager. A failover of the AlwaysOn group to test3 would see the listener cluster group also failover to test3
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2014 at 4:25 am
Thanks, I have a listner and after shuting down the primary by using the listener as the access point I have access to the database. The database was fully accessible hrough the test02 server afte a succesful automatic failover.
Thank you I hadnt quite grasped the role of the listener fully. Most of the blogs i read to help me get the services set up and tested were really helpfull when it comes to the HA creation but few of them really talked about the listener role or perhaps i got to excited and didnt read to the end of the post.
March 12, 2014 at 5:02 am
March 12, 2014 at 5:13 am
Martin Stephenson (3/12/2014)
Perry Whittle (3/12/2014)
Martin Stephenson (3/10/2014)
test2 is the automatic failover synchronous non readableThe readable secondary is not important at this time, what are the connection settings when in the primary role for test2, test3 and test4
Sorry if I appear dim but which connection settings ?
Look at the properties of the AO group and on each replica there will be a setting for "Connections in primary role".
You may also use this query against the primary replica
select replica_server_name
, availability_mode_desc
, failover_mode_desc
, primary_role_allow_connections_desc
, secondary_role_allow_connections_desc
from sys.availability_replicas
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2014 at 5:15 am
All set to Allow all connections
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply