Cluster Administrator Application for SQL Server DBAs

By:   |   Comments (5)   |   Related: > Clustering

I've installed a SQL Server 2005 cluster and now I have to administer the darn thing.  I am comfortable within SQL Server and the management tools that wrap around it, but I've been told by the Server Team in our organization that I need to be able to use Cluster Administer to deal with failovers and some general monitoring issues such as being able to determine which server in the cluster "owns" the specific SQL instances.

Just today I was having a conversation with other SQL Server professionals about the fear of facing new challenges.  Our conversation was about taking the leap into technical writing, but it applies to your situation too.  I know that when I first faced the task of building my first Microsoft SQL Server 2005 cluster I had those concerns.  Having to use a new tool is never easy.  If you are indeed comfortable with SQL Server Management Studio you are probably comfortable with "reading Microsoft".  This is how I describe the ability to intuitively know where to find objects or information on a Microsoft-developed graphical user interface (GUI).  The Cluster Administrator tool "reads" like just about every other Microsoft management tool.  Let's take a look.

01 overview

First of all a note to those staff members of my company: all object names pertaining to any piece of equipment in our company, living or dead, have been changed to protect the innocent.  That being said, we'll review the main components of the Cluster Administrator interface.  Let's do so first by outlining the naming conventions used in the previous and remaining images from Cluster Administrator in this article:

  • DC = Cluster Name
  • SQL03, SQL04 = Server (aka Node) Names
  • BACKUP01, BACKUP02 = Backup Resource Groups
  • DB01, DB02 = Virtual Server Names
  • NODE02 = Instance Name for Second SQL Instance on the Cluster

On this particular cluster I am running two instances of Microsoft SQL Server - the default instance, and the NODE02 instance.  Each SQL instance has it's own mapped SAN volume for backups: BACKUP01 and BACKUP02, respectively.

My plan in this tip is to simply cruise through the tree view in Cluster Administrator on the left side of the tool and touch upon the highlights for each leaf level.  The first node, Groups, displays an entry for each resource group in the cluster.  According to Microsoft TechNet a Resource Group is "a collection of one or more resources that are managed and monitored as a single unit. Resource groups can be started and stopped independently of other groups (when a resource group is stopped, all resources within the group are stopped). In a server cluster, resource groups are indivisible units that are hosted on one node at any point in time. During failover, resource groups are transferred from one node to another."  In this case, we have the cluster resource group (DC), the backup resource groups (BACKUP01 and BACKUP02), and the SQL resource groups (DB01 and DB02).

Each resource group consists of one or more resources.  The following screen shot presents a listing of all the resource types available on the cluster.

06 Resource Types


Meanwhile this image shows how those resources are allocated:

05 Resources

What you will notice is that the information is repeatable throughout the Cluster Administrator tool.  To illustrate this we will move back up the tree view to the Groups node.  As you can see in the following two screen views for the BACKUP01 and DB01 resource groups, the information presented in the Resources node is simply segregated by resource group ownership.

03 Groups BackupNode1

04 Groups DBNode1


The final leaf levels in the tree view pertain to the individual nodes that make up the cluster.  The sub-levels for the node information include:

  • Active Groups - List all resource groups currently owned by the specific cluster node.
  • Active Resources - Listing of individual resources that comprise the resource groups owned by the node at the current point-in-time.
  • Network Interfaces - Information pertaining to the networking resources for the virtual server.

10 Node1 Active Groups

11 Node1 Active Resources cropped

12 Node1 Network Interfaces

Now that you have an understanding of the structure of the Cluster Administrator tool, I'd like to walk you through the steps of doing a manual failover.  Being a Database Administrator, this will perhaps be the main reason you fire-up Cluster Administrator other than to see which nodes own a given SQL instance at any given time.  The process is extremely simple.  You only need to right-click on the resource group you wish to fail over, in this case we'll use the BACKUP01 resource group, but this could very easily be a SQL instance's resource group as well.


Upon selecting Move Group from the menu, the resource will be taken offline.  This is followed by the ownership being transferred to the other virtual server (node) on the cluster.  Once that is accomplished, the resource group is brought back online.  The process usually takes between 3-10 seconds based up my experience with the various SQL Server clusters I've administered.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, June 20, 2012 - 5:03:38 PM - Mohammed Back To Top (18139)

Hi Tim,

Thanks for the article.

I have a requirement to add new MV'sstorage to the exisiting Windows 2003 cluster with SQL 2005 SP3 installed. This new storage will be used for back ups. Can you please let me know if you have any steps to accomplish this?.


Sunday, June 17, 2012 - 1:10:43 AM - Arghya Chatterjee Back To Top (18075)

Thnaks Tim for your valuable information with screenshot which makes things to understand easily.

Thursday, December 9, 2010 - 1:34:27 PM - Jeelan Back To Top (10436)

Hi this is jeelan an can u please send me the installation steps of clustering(screen shots) and as well as sen me the tips how to bring online the Quoram drive because i have completed almost installation of cluster but at the end my drive is in offline but it is not moving to online suggest me as  early as possible. 

Thursday, December 18, 2008 - 2:43:39 PM - timmer26 Back To Top (2422)

A failover will have all the effects of a SQL Service restart.  All active connections (and that would include those for the service account also) are killed.  When the resource group comes online on the new node SQL will go through recovery, rolling back uncommitted transactions in the transaction logs.  Therefore any Agent jobs running should also effectively cancel-out as part of the offline-move-online process.

Now, how would you be notified if this occurs?  What I do is have a job that automatically sends out a Database Mail notification when the cluster resources are moved.  A tip on configuring that process is forthcoming in January 2009.

Thursday, December 18, 2008 - 12:13:21 AM - kalisubbu Back To Top (2418)


What will happen to a running SQL job during failover from one node to another? Does it fail or it will continue?

get free sql tips
agree to terms