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?

Improve this question
5
  • 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
    Commented Feb 20, 2018 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
    Commented Feb 20, 2018 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
    Commented Feb 20, 2018 at 11:49
  • 1
    Preffered and Possible owners are separate things: blogs.msdn.microsoft.com/alwaysonpro/2014/02/28/…
    – Shivam Kumar
    Commented Feb 20, 2018 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
    Commented Feb 20, 2018 at 12:07

1 Answer 1

Reset to default
0

A deeper dive into the MS's documentation helped us discover the Flexible Automatic Failover Policy (https://learn.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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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