1

What I'm trying to do is to gracefully shut down one of two nodes in our SQL Server Availiability Group, effectively draining all the connections.

Previously, we were just stopping the SQL Server instance on the node, but that results in a flurry of "The specified network name is no longer available" errors from client applications.

What is the kosher way of shutting down a node?

I'm no DBA here, so please, ask whatever questions you deem necessary.

1

You should failover to one of the other nodes first, before shutting down SQL.

This can be done either in SQL Management Studio (under the Always On High Availability node in the Object Explorer tree), or alternatively using the Windows Server Failover Cluster Manager program, which will be available in the Start Menu of all the Windows servers hosting the SQL instances.

Using SSMS is considered the better approach, as the WSFC utility won't know the synchronisation state of the other replicas.

To do a failover via TSQL instead, connect to the node you want to failover to (not from), and execute this, once for each listener group you have:

T-SQL : ALTER AVAILABILITY GROUP My_Group_Name FAILOVER;

In PowerShell you have this option: Switch-SqlAvailabilityGroup

(Source for code samples)

  • We are trying to shut down a secondary replica which we are using for read operations, so there's nowhere to failover to. – Anton Gogolev May 21 '18 at 7:34
  • Ah, I see - and I'm guessing that adding a third node is out of the question. Have you looked at removing the secondary node from the Read-Only Routing list? Might be worth giving a go. – Mike May 21 '18 at 7:51

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.