Managing a Windows and SQL Server Cluster using the Failover Cluster Manager tool

By:   |   Updated: 2012-05-07   |   Comments (9)   |   Related: More > Clustering

Problem

From a DBA standpoint, I've installed my first Windows and SQL Server cluster on the 2008 platform. Now what?

Solution

Installing a cluster is just the beginning of a DBA's administrative duties. In this tip, I'll show you a few management tasks that may help the novice. Most tasks will be carried out using Failover Cluster Management which is the management console built into Windows Server 2008. You can access the Failover Cluster Management in a couple of different ways:

  • Go to Server Manager, Features, Failover Cluster Manager
ServerManager
  • Go to Control Panel, Administrative Tools, Failover Cluster Manager
StartBar

Once you open Failover Cluster Manager let's see what can be done inside of this console.

View Status of Services and Applications

Inside of Failover Cluster Manager, if you click on Services and Applications you are presented with a wealth of information.

In this example, I have a multiple instance cluster that consists of four instances on a two node active/passive cluster. After clicking Services and applications, you can see in the right pane all the instances, status, type, current owner (or current node that they exist on) and if they are set to auto start. In the right pane, if you click on a specific instance you can see more information regarding this instance at the bottom.

SA1

Likewise, if you drilldown from Services and applications and click on an instance name you will see even more information regarding this instance that includes server name, IP address, disk information, and the services that are clustered.

SA2

View Status of Nodes

Click on Nodes to view the status of the nodes in the cluster. Here you will be able to see the nodes that have been installed and the status of each node.

Nodes

View Status of Storage

Click on Storage to view the summary of each storage device. Here you will able to see a total storage summary that includes total capacity, available capacity, and each cluster disk and whether it's online or offline and the current owner.

Storage

View Status of Networks

Click on Networks to view the status of your cluster networks.

Network1

To view each individual network click on the network desired. Here you will be able to see subnet information, each NIC and the IP addresses associated with them, each NIC status and current owner.

Network2

Perform a Services or Applications Failover

To perform a manual failover, drilldown into services and applications and select an instance name. Right click the instance name, highlight "Move this service or application to another node, and finally click Move to node....

Failover1

In the Please confirm action box, read the message and select the option to Move SQL Server

Failover2

It will show Offline for a few seconds while it is performing the failover, but should soon say Online again and the owner should change.

Failover3

Setting Preferred Owners for Services and Applications

It is possible for you to set each service and application to have a preferred owner at startup meaning when the servers are brought online the service and application will start up under the correct owner.

Below are the steps to manage this option:

Drilldown into Services and Applications, right click an instance name and select Properties.

SAProp1

In the Service and Applications properties box, select the preferred owner you would like the instance to start up in. You can also select all of the owners and move them in order using the Up and Down buttons. (The owner at the top will have top priority)

In this example, I have chosen SQLCL01 and SQLCL02 as my preferred owners, but since SQLCL01 is at the top it will be the owner my instance connects to. You can also view what node this instance is connected to currently at the bottom of this dialog box.

SAProp2

To ensure the service or application automatically moves to the correct node, select Allow Failback under the Failover tab.

SAProp3
Next Steps
  • Another administrative task dealing with clusters is removing nodes which can be viewed in this tip
  • View all of the MSSQLTips Clustering tips here


Last Updated: 2012-05-07


get scripts

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

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
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, February 17, 2015 - 11:08:28 AM - Hailu D Back To Top

 

Hi Brady, I enjoy your article on  Managing a Windows and SQL Server Cluster using the Failover Cluster Manager tool.




Monday, August 04, 2014 - 2:42:18 PM - Raju Back To Top

The way you explained is awesome..thank you very much.


Tuesday, January 21, 2014 - 3:18:40 PM - Ankit Shah Back To Top

Great Artical Brady. Really helpful 

Kepp it up


Thursday, May 31, 2012 - 10:24:38 AM - Alex Wu Back To Top

Hello, I just installed a SQL Server 2008R2 failover cluster, but with the Managment Studio, I can't the status of the instance, sql server agent, actaully, Instance01 and sql server agent are running, but the status in Management Studio is "stopped" why?

Can SSMS manage a clustered SQL Server?


Monday, May 21, 2012 - 11:00:15 AM - Perry whittle Back To Top

Hi

Even so, recommending that the clustered SQL app be set to auto failback is not what I would call a tip ;-)

Again, I highly recommend you don't configure auto failback

Regards

Perry


Monday, May 21, 2012 - 9:12:31 AM - Jinbo Back To Top

I enjoy reading the article. Thanks.


Monday, May 07, 2012 - 3:27:19 PM - Chintak Chhapia Back To Top

Thanks for this good tip..


Monday, May 07, 2012 - 1:02:34 PM - Brady Back To Top

This tip was not written to tell someone "how" to setup their environment. It's simply a tip that offers some management insight for someone that doesn't have much experience working with a cluster.

Also, there are more small to medium sized organizations than large organizations where DBA's also act as the System Administrator. That's why this "could" be helpful for DBA's.

Thanks for the comments.


Monday, May 07, 2012 - 11:30:25 AM - Perry whittle Back To Top

I would highly recommend you DO NOT configure automatic immediate fail back, thi could cause the clustered SQL server instance to go offline unexpectedly and in a busy environment could make you very unpopular. Also, for a 2 node cluster leave the preferred owners as is, there's no need to touch it. 

It's worth noting that in most large organisations the DBA has absolutely nothing to do with configuring the base windows cluster, this is performed by a server administrator

 

Regards Perry



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools