Manual failover in a SQL Server cluster

Microsoft SQL Server

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

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

SQL Server Failover Cluster Owner Node

Only on the active cluster node, the shared disks will be available:

SQL Server Failover Cluster Shared Disks

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

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

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

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.

6 comments » Write a comment

  1. 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.

  3. 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?

  4. Pingback: Manual failover in a SQL Server cluster | Jingyang Li

  5. 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

Leave a Reply

Required fields are marked *.