SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Scheduled MS Cluster Failovers using Automation Server Objects

By Anthony Bressi,

Scheduled MS Cluster Failovers using Automation Server Objects

If you are working in an Active-Passive clustered SQL Server environment there are often times when you need to initiate a failover so that you can perform routine maintenance tasks on the Passive server - to apply service packs, install software, etc. In this article I outline a quick and easy way to perform scheduled failovers using the MS Cluster Service Automation Classes.

Microsoft's Cluster Automation Server Objects enable developers and DBA's to manage their MS Cluster server through Component Object Model (COM) objects using COM aware languages such as Visual Basic and C++ and aware scripting languages like VB Script. In this article we will use Visual Basic 6.0 to develop a short application that can be scheduled to failover a server cluster. This article assumes that you are familiar with creating and running projects in Visual Basic and therefore skips basics such as variable declaration in an effort to keep the sample code blocks short.

To develop code using the Cluster Automation Server objects you will need to download The Platform SDK if you do not already have it installed locally. At the time of this writing it can be downloaded from Microsoft at http://www.microsoft.com/msdownload/platformsdk/sdkupdate/psdk-full.htm.

To start, make a new "Standard.exe" project in Visual basic and add a reference in your Project references to "Microsoft Cluster Service Automation Classes.", msclus.dll.

Next, copy the code below and paste it into your project - the Form_Load method is fine. Modify the code to fit your environment:

	'Create your cluster object
	Set oCluster = New Cluster 

	'Open your connection, below change to name of your cluster
	oCluster.Open ("MyCluster")
	'Hook into Resource Groups collection 
	Set oClusGroups = oCluster.ResourceGroups 
	'Retrieve a specific group, change below to name of the group you want to work with 
	Set oClusGroup = oClusGroups.Item("My Cluster Group") 

Now you are hooked into your Cluster and a specific resource group. Next, determine your Active node:

	'Determine the Active node 
	sActiveNode = oClusGroup.OwnerNode.Name 

By knowing which node is Active in a simple Active/Passive, 2 server configuration you can easily determine which node is passive. We will skip that If Then statement and assume we assigned our passive node's name to a new string variable called sPassiveNode.

At this point, we have our cluster server objects all set up and know which server is active and which is passive. We are almost ready to failover. But before we failover it is a good idea to make sure that the secondary node is healthy:

	Set oSecondaryNode = oCluster.Nodes.Item(sPassiveNode) 
	Dim bOkay As Boolean 
	bOkay = True 
	'check each network interface
	For i = 1 To oSecondaryNode.NetInterfaces.Count 
		If Not oSecondaryNode.NetInterfaces.Item(i).State = ClusterNetInterfaceUp Then 
			bOkay = False 
		End If 

If everything is fine on the passive node we will initiate failover. The "Move" method used below moves a group and it's resources from the current active node to another node. If no other node is specified it chooses a "preferred" node based on it's own predefined criteria. Since we are only using 2 nodes in our example we leave out the optional cluster Node parameter, but still pass in our Timeout parameter of 180 seconds.

	'Move group over to the passive node
	If bOkay Then 
		varReturn = oClusGroup.Move(180) 
	End IF 
	'the move statement returns a variant that lets us know if the method timed out, if it didn't time out we assume success
 	If CBool(varReturn) Then 
		Debug.Print ("Failover timed out") 
	End If 


The intention of this article was to provide an introductory look into the Cluster Service Automation Classes. The code presented in this article is very basic and should be built upon with your own functionality such as logging and error catching as well as other general programming practices. Those general practices are outside the scope of this article. In our example we only failed over 1 group, if you want to failover several groups the failover code above will fit neatly into a function that can be called as needed. For more information on Windows clustering visit http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/ht ml/anch_winclustering.asp.

Anthony Bressi is owner of Agilist Technologies Inc. which specializes in software for SQL Server Database Administrators and SQL Server developers. Mr. Bressi has over 8 years of hands-on experience in the Microsoft SQL Server development environment.

Total article views: 4763 | Views in the last 30 days: 0
Related Articles

SQL Server Not Installed on Passive Node for 2008 Failover Cluster

SQL Server Not Installed on Passive Node for 2008 Failover Cluster


DTS packages failed in SQL 2005 Failover Cluster passive node

DTS packages failed in SQL 2005 Failover Cluster passive node


Combining AlwaysOn Groups With Failover Cluster Instances

This article discusses failover cluster instances and AlwaysOn groups



I am asked to impliment sql server 2008 failover clustering on windows server 2008


Creating A SQL Server 2008\2008 R2 Failover Cluster

Details how to install a SQL Server 2008\2008R2 Failover Cluster Instance