Manually Configure SQL Server Resource Group Ownership in Cluster Administrator

By: Tim Ford   |   Updated: 2008-12-22   |   Comments   |   Related: > Clustering

Problem

We have a two node SQL Server cluster in our organization.  We are running an instance of Microsoft SQL Server on each node in the cluster.  I'm coming up to speed on the Cluster Administrator tool and I am looking for a process by which I can check the configuration settings that were put in place with the SQL Server installation, specifically the failover options for each SQL instance and ownership of the instances.

Solution

Many of the cluster-specific configuration settings implemented upon installation of Microsoft SQL Server 2005 are able to be modified post-install via the Cluster Administrator management tool integrated into the Windows Server operating system. 

Access to Cluster Administrator is obtained via START\Administrative Tools\Cluster Administrator from the Start Menu in Microsoft Windows.  A previous tip provided a high-level overview of this management control panel and offered insight into how to manually fail-over a SQL Server instance between nodes in the cluster.  Please review this tip Cluster Administrator Application for SQL Server DBAs if you are unfamiliar with the general layout and functionality of the Cluster Administrator.

In order to become acquainted with the layout of the sample cluster used in this discussion I would like to take a moment to lay out the naming conventions I am using.  Please also refer to the screen shot below for review.  It is the same assumptions and naming conventions used in the previous Cluster Administrator tip.

To proceed with answering your question, select a resource group to review, right click, and select properties:

The following dialog box will be displayed:

This dialog presents the name of the resource group, a description for the resource group if one has been associated, and the listing of preferred owners for the resource group in question.  A preferred owner is physical server/node given preference over ownership when nodes are brought down (either gracefully or by force) or during situations involving manual failover or failback scenarios.  This information is particularly critical in clusters where you may want to dedicate physical hardware to only certain instances of SQL Server, allowing the remaining nodes to handle high-availability functionality between themselves.  For example consider the following example:

You have a four node cluster with 3 instances of SQL Server 2005 installed.  The instances and physical node ownership is as follows:

As the DBA you've been given the following requirements for this cluster:

• A cost center in your organization has funded the purchase of hardware and licensing for SQL01 and Server01.  Only its databases can run on that server.
• Servers 02 and 03 and their associated SQL instances are enterprise-funded and should be able to accept failover of any other SQL instance in the cluster.
• Server04 is dedicated to accept failover of only the SQL01 instance of the critical financial databases running by primary default on Server01.

In order to accomplish these requirements you need to navigate as I've prefaced above.  You will assign the Preferred Ownership by selecting Modify on the General Tab of the Resource Group's properties menu.

In the screen shot above you'll note that the ownership has not been set.  By not doing so you're jeopardizing the automatic failover process' success.  In the example I laid out above your "Modify Preferred Owners" dialog should look like this for each of the resource groups:

Resource Group SQL01

Note that the primary owner is Server01, yet Server04 is allowed to own the resources for SQL01 in case Server01 encounters physical server failure or a reboot/shutdown request.

The resource groups for SQL02 and SQL03 are both allowed to run on one anothers primarily assigned physical node if necessary due to a failover request.  The ownership is therefore identical with the exception of priority.

Resource Group SQL02

Resource Group SQL03

As you can see it is a very simple process by which you construct the failover behavior in Cluster Administrator.  This process is identical for any resource group type and is not limited to simply the SQL Server resource group.  I suggest reviewing these settings after any SQL Server installation to verify that the physical node ownership was configured properly and also periodically reviewing the ownership to ensure that the customer requirements for high-availability are met in an accurate fashion.

Next Steps

• Additional Microsoft SQL Server clustering tips are available on MSSQLTips.com:
  • Introduction to the Microsoft Windows Cluster Administrator
  • Getting Started with SQL Server Clustering
  • SQL Server Clustering: Active Vs. Passive
  • SQL Server Clustering and Consolidation: Balancing Resources
• Stay tuned for additional tips to come on the following clustering subjects:
  • Installing Microsoft SQL Server 2005 in a Microsoft Clustered Server environment.

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