Feeds:
Posts
Comments

Archive for the ‘Sql Clustering’ Category

Quick one today:

Every time we we build a new cluster in our lab environment, we start with running cluster validation report before starting Sql Server installation. Once in a while, we need to go back to refer older validation reports.

All cluster validation reports are saved, by default, at the location below on the active node where the validation was executed from:

C:\Windows\Cluster\Reports

ValidationReport

 

Hope this helps,
_Sqltimes

 

Advertisements

Read Full Post »

Interesting one today:

In our lab environemnt, one of the SQL cluster environment ran into this error.

 

 

Error Message:

Clustered role 'Cluster Group' has exceeded its failover threshold.  
It has exhausted the configured number of failover attempts within 
the failover period of time allotted to it and will be left in a 
failed state.  
No additional attempts will be made to bring the role online or fail 
it over to another node in the cluster.  Please check the events 
associated with the failure.  After the issues causing the failure are 
resolved the role can be brought online manually or the cluster may 
attempt to bring it online again after the restart delay period.

The Cluster service failed to bring clustered role 'Cluster Group' 
completely online or offline. One or more resources may be in a 
failed state. This may impact the availability of the clustered role.

Resolution:

Errors like this are more common in Lab environments than in production environment. In any case, if you encounter the same error in production environment, then take extra caution before you follow these steps.

Possible Root Cause:

In lab sometimes, as part of some other effort, we inadvertently end up failing over the cluster several times within a short period of time. There is a setting in Cluster that measures the failover count.

  • If that count hits a particular threshold, it flags the Resource Group as ‘Failed’ state
  • And creates an entry in the the Cluster Events, that Cluster Resource Group failed after reaching the threshold (see the error message : Clustered role ‘Cluster Group’ has exceeded its failover threshold)

Resolution Steps:

According to this MSDN post, we could alter that failover count threshold to allow the resource group to come back up in a healthy state.

 

Step 1:

Go to Failover Cluster Manager >> Roles >> right click on the Resource Group and to go Properties:

  • Change the Maximum failures in a specified period to a larger number to account for the repeated failovers in recent hour.

ClusterFailoverThreshold_1

 

 

Step 2:

Go to Failover Cluster Manager >> Roles >>

In the bottom portion of the window where we the individual resources are listed, right click on the Resource that is in failed state and go Properties:

  • Increase the Maximum restarts in the specified period setting to a larger number to account for recent restarts.

 

ClusterFailoverThreshold_2

 

NOTE: This is not a standard solution for production environments.

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

Earlier, when performing some cluster maintenance work, this error popped-up on the screen.

The specified disk or volume is managed by the Microsoft Failover cluster 
component. The disk must be in the cluster maintenance mode and the 
cluster resource status must be online to perform this operation

When attempting to format a new LUN to 64K allocation unit, this error popped up. Since the LUN/Drive is already added to the cluster, new format changes could not be made.

Resolution:

As the verbose error message suggests, assign this particular disk into “Maintenance Mode”, then perform formatting steps.

Go to Failover Cluster Manager and go to Storage > Disks; Identify the particular disk and right click and go to More Actions >> Turn On Maintenance Mode.

Cluster_LUN_Error

Once the disk is in maintenance mode, you’ll see under Status column as Online (Maintenance Mode).

Now we are free to format the disk. Computer Management >> Storage >> Disk Management go to the individual disk and right click and Format.

FormatClusterLUN

Now formatting works and once completed, go back to Failover Cluster Manager and set the disk back out of Maintenance Mode.

 

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

During one of the Sql Cluster set up in our lab environment, we ran into this interesting (and common) error message.

The Sql Server failover cluster instance name '' already exists as a clustered resource. 
Specify a different failover cluster name.
Sql Cluster Set up Error

Sql Cluster Set up Error

Resolution

Turns out, the sysadmins created a Resource Group with the same name and allocated all the cluster disks to it. So, when I attempt to create a new Resource Group (for Sql Server resources), it throws this error.

 

SqlCluster Install Resolution

SqlCluster Install Resolution

 

Solution: Once the dummy resource group was removed, the cluster set up wizard progressed without any errors.

The moral of the story is, when we are about to create a resource group, make sure a resource group by the same name does not already exist.

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

Earlier this month, during Sql Server Cluster set up on a new set of VMs, ran into this interesting warning message.

Network binding Order generated a warning.

The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configurations to change the binding order.
Cluster Setup Network Binding Order

Cluster Setup Network Binding Order

Upon further investigation, it became clear that the NIC that connects to the Domain network is not given highest priority (as needed) for Sql Cluster.

Resolution

In Clustered environments, it is recommended to have the network interfaces properly ordered for maximum efficiency.

Go to “Network connections” and open Advanced Settings. See the image below:

Network Connection - Advanced Settings

Network Connection – Advanced Settings

In the resultant window, under Adapters and Bindings tab, make sure the network interfaces are ordered according to the recommendation. Domain network needs to be on the top, then Heartbeat Network and Remote Access Connections. See the image below, for the recommended order.

Network Binding Proper Order

Network Binding Proper Order

After saving the new order, go back to “Install Failover Cluster Rules” and re-run the checks. This blog has more info, if interested about the rest of cluster set up.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

On one of our production machines, we recently added a new LUN to a SQL cluster. A task like this is a team effort. Sysadmins perform some steps and DBA carry out the remaining. In this article, the main focus is on covering the steps after the LUN is added to the OS & Sql Cluster by the sysadmins.  For context, we’ll start with high level steps before going into details.

Sysadmins steps

  1. Add new storage to the machine/OS as an available storage
  2. Format the available drive with appropriate settings (cluster size) and add it as a new drive
  3. Make drive available to the Cluster using “Add Disk” screen in FailOver Cluster Management tool.

DBAs steps

  1. Add available storage to Sql Cluster
  2. Configure dependency (and check the report before & after)
  3. Add data file on the new cluster storage drive

Here we’ll cover the DBA steps in detail:

Some of these steps were covered under a different recent article as part of dealing with an error message, but here we’ll cover it as a task by itself (which it is).

Add New Storage

Once sysadmins have made the new storage as an ‘available storage’ to OS Cluster, it needs to be added as a new storage location to the SQL Cluster.

In FailOver cluster manager, go to Sql Server Resource Group for this SQL Cluster and right click for detailed options and choose “Add Storage” (see image below)

sqlcluster_addnewstorage_to_os_cluster

Once successful, go to Storage\Disks under in FailOver Cluster Manager to confirm the availability. See image below:

sqlcluster_addnewdrive

Configure Dependency

Adding the storage is an important step, and equally important step is adding the new drive to Sql Cluster Dependency Chain. Dependency Chain informs Sql Sever “how to act”, when any resource in the Cluster becomes unavailable. Some resources automatically trigger cluster failover to other node; some resources do not. This decision is made based on the configurations in Dependency Chain.

Example:

Critical: Data drive/LUN that has database files is critical for optimal availability of the Sql Cluster. So, if it becomes unavailable, failing over to other available nodes is imperative to keep the cluster available.

Non-Critical: In some scenarios, Sql Server Agent is not considered as Critical. So if it stops for some reason, Cluster will make multiple attempts to start it on the same node, but may not necessarily cause failover.

This is a business decision. All these “response actions” will be configured in Cluster settings.

Now, check the dependency report (before); We can see that new drive exists in Cluster, but is not yet added to the Dependency Chain.

SqlCluster_DependencyReport.PNG

To Configure Dependency Chain, go to the Sql Server Resource Group under Roles in FailOver Cluster Manager. See the image below for clarity:

Then go to the bottom section for this Resource Group, where all the individual resources that are part of this Resource Group are displayed.

Under “Other Resources“, right click on Sql Server Resource and choose properties.

do As show

sqlcluster_addnewstorage_add_to_dependency

In the “Sql Server Properties” window, we can see the existing resources already added to dependency chain logic.

SqlCluster_Dependency_Before.PNG

Now, go to the end of the logic list and choose “AND” for condition and pick the new Cluster Storage to be included. See image below for clarity:

SqlCluster_Dependency_After.PNG

After saving the settings, regenerate the Dependency Chain report. Now, we’ll see the new drive as part of the logic.

sqlcluster_dependencyreport_after

Add Database Data File to New Cluster Storage

Now, that the new drive is ready, we could easily add a new data file to the new location.

--
-- Add data file to new storage location
--
USE [master]
GO
ALTER DATABASE [SampleDB]
ADD FILE
	(
		  NAME 			= 	N'SampleDB_Data3'
		, FILENAME 		= 	N'U:\MSSQL\Data\SampleDB_Data3.NDF'
		, SIZE 			= 	3500 GB
		, FILEGROWTH 	= 	100 GB
		, MAXSIZE 		= 	3900 GB
	)
TO FILEGROUP [PRIMARY]
GO
Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »