In this blog post, I’ll demonstrate how to find the currently active node in a SQL Server cluster, and how to do a manual failover to another node.
Note that a failover will result in a short down time for your SQL Server environment. This is because a recovery (rollback and roll forward of transactions) will be performed on the new active node in the SQL Server cluster. In an environment with long running transactions, the time for the recovery may be substantial (how to check the latest recovery time). So don’t do failover in a production environment unless absolutely necessary. Preferably, use a cluster lab environment to test things out first.
In this demonstration, I am using Windows Server 2012 R2 and SQL Server 2014. I have two nodes in my cluster (WIN-2012SQL01 and WIN-2012SQL02), both running the default SQL Server instance. The cluster is named SQL2014CLU.
Finding the active node in a SQL Server cluster
From any of the nodes (active or passive) in the cluster, run the Failover Cluster Manager from Server Manager:
SQL Server Failover Cluster Failover Cluster Manager From Server Manager
Expand your cluster from the right menu, select “Roles” and you’ll see the owner node:
SQL Server Failover Cluster Owner Node
Only on the active cluster node, the shared disks will be available:
SQL Server Failover Cluster Shared Disks
Also, only on the active node the SQL Server process will be running:
SQL Server Failover Cluster SQL Service Running On Active Node
Manual Failover
Obviously, any event that takes the active node down will bring one of the passive nodes online. But if you, for instance, want to do service on a currently active node, the cluster roles can be switched from the Failover Cluster Manager. I the right menu, select “Roles”. Right-click on the cluster and select “Move” -> “Select Node”:
SQL Server Failover Cluster Failover Cluster Manager Select Node
From there, select one of the passive nodes you want to move to and click “OK” (I have only one passive):
SQL Server Failover Cluster Move Clustered Role
After a while (SQL Server will start up and perform a recovery on the new active node) the roles will be switched.
Hi Tomas,
Do you know the command line or PowerShell commands to do this same thing? I’d like to be able to automate it as a script.
No, I have never used a script for this.
/Tomas
Thanks for this article, very simple and helpful 🙂
I have three SQL 2014 clusters on one window 2012 cluster(active/passive), can I failover from the windows cluster level so that all three SQL clusters will also fail over as result of this?
Pingback: Manual failover in a SQL Server cluster | Jingyang Li