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.
[white_box]To set up the virtual environment to experiment with my own SQL Server cluster, I followed Jonathan Kehayias superb blog series “Building a Completely Free Playground for SQL Server”. The first post in the series can be found here. The blog series uses Windows 2008 R2 and SQL Server 2008 R2, but with minor modifications the guide can be used to create a lab environment with Windows 2012 R2 and SQL Server 2014.[/white_box]
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:
Expand your cluster from the right menu, select “Roles” and you’ll see the owner node:
Only on the active cluster node, the shared disks will be available:
Also, only on the active node the SQL Server process will be running:
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”:
From there, select one of the passive nodes you want to move to and click “OK” (I have only one passive):
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
Hello,
There is any way to prevent down time?
I have a cluster where a I to move roles but the last one cut connections from application servers.
Thanks,
Paul
Simple and Fantastic Article. Thanks a lot.