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

 

Manually Configure SQL Server Resource Group Ownership in Cluster Administrator


By:   |   Read Comments   |   Related Tips: More > Clustering

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


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.

Object Description
EC Cluster Name
SQL03 Physical Name of primary server participating in cluster
SQL04 Physical Name of secondary server participating in cluster
DEV01 Name of virtual server and associated resource group
DEV02 Name of other virtual server and associated resource group
DEVBACKUP Resource group associated with dedicated backup disk

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:

Physical Node Instance Name / Resource Group Notes
Server01 SQL01 Dedicated Instance / Node combination for critical financial databases
Server02 SQL02 SQL shared environment for 50 databases
Server03 SQL03 SQL instance for 20 databases
Server04   Passive node for accepting failover of SQL resources

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



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     



Learn more about SQL Server tools