Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Cluster Administrator Application for SQL Server DBAs


By:   |   Read Comments (5)   |   Related Tips: More > Clustering

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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

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

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.

 

Meanwhile this image shows how those resources are allocated:

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.

 

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.

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



Last Update:


signup button

next tip button



About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator and SQL Server MVP. He has been working with SQL Server since 1999.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

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

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


Thursday, December 09, 2010 - 1:34:27 PM - Jeelan Back To Top

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

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

Hi,

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


Learn more about SQL Server tools