0

We currently have a set up of two nodes A and B with AlwaysOn High Availability. When restarting node A, we can connect with the listener name 'xyz' to our SQL Server instance. In this scenario, B will shift to primary and A to secondary. Everything is transparent, and connecting to 'xyz' works fine.

Now I've tried to test the scenario by just switching off the SQL Server service on the primary node, rather than restarting the whole node. (Side note: as a consequence, the SQL Server Agent stops as well)

Manually stopping SQL Server instance

When doing so, I am NOT able to connect to 'xyz' (listener name) via the management studio. I can only connect to 'B' directly. When automatic failover tries to take place, I am left with the following:

enter image description here

Is this normal? Does failing over occur by simply checking the node's status rather than the SQL service of that node?

  • 1
    When you shutdown SQL Server WSFC does not see this as a scenario where failover is required and hence the node B remains secondary replica and does not transform to primary. And since listener always connects to primary replica so in your case it is not working – Shanky Feb 20 '18 at 11:01
  • 1
    Do you have node B listed as the possible owner in failover cluster? If not then the services will never failover to B which I suspect is happening over here in your case. – Shivam Kumar Feb 20 '18 at 11:42
  • Both nodes are listed as preferred owners. The critical events for the Failover Cluster Manager shows the following msg: The Cluster service failed to bring clustered role 'NA_AVG01' completely online or offline. One or more resources may be in a failed state. This may impact the availability of the clustered role. – test Feb 20 '18 at 11:49
  • 1
    Preffered and Possible owners are separate things: blogs.msdn.microsoft.com/alwaysonpro/2014/02/28/… – Shivam Kumar Feb 20 '18 at 11:51
  • also make sure password in service control manager is set correctly for service account or is not expired or changed which can create such issues – Shivam Kumar Feb 20 '18 at 12:07
0

A deeper dive into the MS's documentation helped us discover the Flexible Automatic Failover Policy (https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/flexible-automatic-failover-policy-availability-group)

A setting is available to adjust the scenario when the failover should kick in. The default setting is 3.

ALTER AVAILABILITY GROUP AG1 SET (FAILURE_CONDITION_LEVEL = 5);

enabled us to cater for more scenarious, including turning off manually the service. Level 5 encapsulates scenarios/conditions of lesser level values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.