Manual failover in a SQL Server cluster

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.

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.

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”: