By: Tim Ford | Comments (5) | Related: > Clustering
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
- Be sure to get acquainted with the Cluster Administrator tool as a means to check the active nodes in your cluster and manually failover the cluster in needed.
- Check out these additional Microsoft SQL Server clustering tips available on MSSQLTips.com:
- Stay tuned for additional tips on Microsoft SQL Server clustering in the coming months.
About the author
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
