Testing Cluster Configuration:

Most people never like to test what they can done. Assuming that things have been done perfectly which results in a situtation of panic sometimes. So i thought that i could let you all know the steps to check after completing a cluster configuration.

1. Check if the Nodes are available and able to ping each other.
2. Check if you are able to reach the nodes from the client machines both with the names and IP address.
3. Try validating a failover and check if it failsover successfully. This can be done as given in the topic Validation Failover.
4. Check if all the disks are seen in the cluster group. This is the place most DBAs could go wrong. They might not have added the disks as dependencies in their configuration. To check and add disks as dependecies look on the topic Disk resources in cluster.
5. Verify error log and event viewer log.
6. Try connecting to the SQL Server management studio to check if it succedds.

Validating Failover:

1. Start Cluster Administrator.
2. In the left pane, expand Groups, right-click a cluster group, select the Move Group option, and finally select a node to move the resource group.
If the failover is functioning properly, the owner of the resources will change to the new node name.
3. Repeat step 2 to move the resource to all other nodes of the server cluster.

Disk resources in cluster:

1. Start Cluster Administrator.
2. Select the group that contains the disk resource you want to use with SQL Server Services.
3. Make sure the disk resource is owned by the node you are currently logged on to..
4. In the right pane, select the disk resource, right-click, select Change Group, and then select
the group that contains your SQL Server.
5. When prompted to confirm the move action, click Yes.
6. Once again, you will be prompted to confirm the disk resource move to the other group, Click Yes.
The disk will now be moved to the target group.
7. Take the SQL Server resource offline.
8. Right-click the SQL Server resource, and select Properties; alternatively,
double-click the resource. Select the Dependencies tab. Click Modify.
9. In the Modify Dependencies dialog box, select the new disk resource listed under Available
Resources. And move that resource to Dependencies;
10. The Dependencies tab will now show that the disk is added as a dependency of the
resource. Click Apply. Click OK.
11. Bring the offline resources online. SQL Server will be able to use the newly added disk.

Renaming a Failover Clustering Instance of SQL Server

Can you rename the SQL virtual name in a Cluster? The answer for this in SQL Server 2005 is abviously Yes. New to SQL Server 2005 is the ability to rename a clustered instance of SQL Server without having to uninstall and reinstall it.

Follow these steps:

1. Start Cluster Administrator.
2. Select the resource group that has the SQL Server resources.
3. Take the SQL Server service offline.
4. Right-click the SQL Server Network Name resource, and select Properties; alternatively, double-click the resource.
5. Select the Parameters tab of the resource’s properties page
6. Enter the new name of the failover clustering resource and click Apply.
7. Select the General tab. Change the name of the resource to include the new name you configured
in step 6. Click OK.
8. Bring the SQL Server resources online.
9. Ping the new name of the SQL Server failover clustering instance. If the name cannot be
resolved, you will have to flush your DNS cache by issuing these three commands in succession:
ipconfig /flushdns
ipconfig /registerdns
and nbtstat –RR.
10. Start SQL Server Management Studio, and connect with the new instance name. If this succeeds,
the change has been done successfully.
11. For a final verification, you can also run a SELECT @@SERVERNAME query, which should reflect the name change.

Note:
You can only rename the SQL Server virtual Name and not the instance name and this operation involves some down time.

Getting Information on SQL Server Clustering

SQL Server 2005 aruably gives the best resources to see if the instance is clustered or not. And if so, what are the properties of the cluster. The DMVs that are related to the Cluster can be used for this. These DMVs are very useful for a new DBA joining a company who can easily find out the environment and the disk details without needing for a proper document on place.

To check if the instance is clustered:

select SERVERPROPERTY('IsClustered')

If this returns 1 then your environment is clustered.

To check the computer name of the node owning the cluster

Select SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

The following DMVs would can be used for the cluster information.

sys.dm_os_cluster_nodes
This will return a row for each node in the failover cluster instance configuration. If the current instance is a failover clustered instance, it returns a list of nodes on which this failover cluster instance (formerly ‘virtual server’) has been defined. If the current server instance is not a failover clustered instance, it returns an empty rowset.

Sample Output:

SQLCLUSN1
SQLCLUSN2

sys.dm_io_cluster_shared_drives

This will return the information such as drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.

Sample Output:

D
L
T
X