Archive for the ‘Sql Clustering’ Category

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)


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


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.


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.


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


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


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:


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


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]
		  NAME 			= 	N'SampleDB_Data3'
		, FILENAME 		= 	N'U:\MSSQL\Data\SampleDB_Data3.NDF'
		, SIZE 			= 	3500 GB
		, FILEGROWTH 	= 	100 GB
		, MAXSIZE 		= 	3900 GB
Hope this helps,

Read Full Post »

Interesting one today:

MSDTC is one of the popular software components that is on all Windows systems. It is one of the Windows  Operating System components that Sql Server relies on it to perform some crucial tasks (when needed).

What does it do?

MSDTC, Microsoft Distributed Transaction Coordinator, is essentially, as name suggests, a coordinator/manager to handle transactions that are distributed over multiple machines. Let’s say we start a transaction, where one of the steps includes querying data from a different Sql Server instance on a different physical machine; MSDTC comes into action with these specific tasks that need transaction coordination across different physical machines. It executes the section of code that is supposed to run on remote machines and brings back the results to local Sql instance. In this process, if any issue were to occur, on the remote machine that results in rollback, MSDTC makes sure the original transaction on this machine also rolls-back safely.

How does it do?

MSDTC comes with necessary Operating System controls and memory structures to carry out these operations independent of the Sql Instances, while keeping integrity of the transaction across the multiple physical Sql machines a.k.a. the complete two-phase distributed commit protocol and the recovery of distributed transactions.

Where does Sql Server use it?

The key point here is that these need to be Sql Instances on different physical machines. Queries that request data across different instances on the same physical box do not go through MSDTC.

MSDTC is used by query activities like

  • Linked Servers
  • RPC (Remote Procedure Calls)
  • Ones with
  • etc…

So, every time we run SQL queries that utilize above techniques, they rely on MSDTC to carry out operation while maintaining transaction integrity.

Who else uses it?

MSDTC is an Operating System resource that is used by applications other than Sql Server, to perform any distributed transaction activities; Like eXtended Architecture applications.

Is MSDTC required?

MSDTC is not required for Sql Server installation or operation. If you are only going to use Database Engine, then it is not required or used. If your Sql uses any of the above mentioned query techniques (Linked Server, OPENQUERY, etc), or SSIS or Workstation Components then MSDTC is required.

If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances.

What about Sql Cluster?

Same rules as above apply to Sql Clusters as well with one additional rule. If you have two instances on the same machine (that are clustered across different physical machines), then you’ll need MSDTC. Since the Cluster could failover to remote machine at anytime.

Let’s take an example:

Let’s say Instance1 is on physical machines A & B, with B as active node. Instance2 is on machines B & C, with B as active node. A query going from Instance1 to Instance2 will need MSDTC (even if both the instances are active on the same physical machine B at that given point in time.).

This is because, there is no guarantee that they will remain on the same physical machine at any given time; They might failover to other machines, resulting in instances being on physically different machines. So MSDTC is required (when distributed operations are performed).

Also the recent Sql Server versions do not required MSDTC during Sql Server installations.

Other points in a Clustered Environment

We could have multiple instances of MSDTC as different clustered resource (along with default MSDTC resource).

In scenario with multiple MSDTC, we could configure each Sql Cluster resource to have a dedicated MSDTC instance. If such mapping does not exist, it automatically falls back to use the default MSDTC resource.

Hope this helps,

Read Full Post »

Quick one today:

In a previous post, we covered one of the techniques used to generate text version of cluster Log files using command prompt. Today, we’ll cover another technique, a more common one going forward; Using PowerShell.


In Windows Server 2012, looks like the cluster.exe command prompt interface is not installed by default, when you install FailOver Cluster.



So, we’ll use PowerShell cmdlets to generate these cluster logs.

#  Generate cluster log from SampleCluster and save in temp folder.
Get-ClusterLog -Cluster SampleCluster -Destination "c:\temp\"

When you run in PowerShell window, the response looks something like this:


Hope this helps,

Read Full Post »

Quick one today:

When we install Sql Server software on a Windows machine, sometimes, we need to install Service Packs (SP); Either SP1 or SP2 or SP3, etc. As service packs are cumulative, it helps to keep it down to a two step process. In some scenarios, this two step process is not posible due to version conflicts that do not allow RTM Sql Server versions on later versions of OS.

SlipStream allows us to combine these two steps into one. Combine RTM version with ServicePacks (SP) and install them together.


  • Reduce or remove need for multiple restarts
  • Reduce install duration times
  • Avoid version conflicts mentioned above (RTM Sql Server versions may not work on later versions of OS)

Preparatory Steps:

  • Install any prerequisites like .Net 3.5
  • Install Windows Installer 4.5
  • Download and extract SP file to a local drive a.k.a. C:\Temp\SP1\
  • Run the Service Pack (SP) first to install Setup & Support files. This prevents any surprises when actual install is performed.

SlipStream Install:

  • Open Command prompt as Administrator
  • Go to the location where install DVD is located.
  • Use this command to run install in SlipStream mode.
    • Setup.exe /PCUSource=C:\Temp\SP1
  • After a second or two, the Sql Installer Wizard opens
    • Walk through the normal install steps.
  • When you get to the “Ready to Install” screen, it indicates that SlipStream is engaged during this install (see in the image below).
SlipStream SqlCluster Install

SlipStream SqlCluster Install

  • Perform restart if you need to.

Please note that this is just one of the techniques. On Microsoft Support, they have other options detailed with troubleshooting techniques.

Hope this helps,

Read Full Post »

Quick one today:

In a clustered environment, sometimes there is a need to query Cluster Name, Node Name, Instance Name individually; The following script uses SERVERPROPERTY to retrieve that information:

SELECT    SERVERPROPERTY('MachineName')					AS [Cluster_Instance_Name]
		, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [NodeName]
		, SERVERPROPERTY('IsClustered')					AS [Is_Clustered]
		, SERVERPROPERTY('ServerName')					AS [MachineName_InstanceName]
		, CASE
			WHEN ISNULL(SERVERPROPERTY('InstanceName'), 0) = 0
				THEN 'Default Instance'
			ELSE SERVERPROPERTY('InstanceName')			END AS [InstanceName]


Buit-In Function & DMV:

To continue the point further; to retrieve all the nodes in the clustered environment, query the built-in function called  :  fn_virtualservernodes(). Looks like Microsoft recommends using this DMV instead : sys.dm_os_cluster_nodes.


Hope this helps,

Read Full Post »

Quick one today:

Surprisingly, we’ve been doing a lot of Sql Server Cluster installations in the last few weeks/months. This is a handy chart, helpful to showcase the steps at a VERY high level.

Each step has several sub-steps, which we’ll go into detail in other future posts, but here we just document the high level steps as a guidance or memory bookmark for quick reference.

High Level Steps:

  1. Install Failover Cluster Software through ‘Features’ addition
  2. Include Automation tools within FailOver Clustering
  3. Configure Cluster at OS level on nodes
    1. Validate cluster
  4. Configure MS DTC, if needed: Configure network configuration for MSDTC (Inbound/Outbound):
    1. Component Services >> Computers >> My Computer >> Distributed Transaction Coordinator >> ClusteredDTCs >>DC1REPLS02DTC >> properties >> Security (tab) >> Check Inbound & Outbound
  5. On first node, install Sql Server as Cluster Option
  6. On other nodes, install Sql as “add node”
  7. MS DTC resource mapping for Sql Resource Group
    1. Component Services >> Computers >> My Computer >> Properties >> MSDTC (tab) >> Assign MSDTC for Sql Resource Group from the drop down.

Post Installation Steps:

  1. Add disk dependency
  2. Test Failover with different variables

We’ll go into reasons why we do these steps in future posts.

Hope this helps,

Read Full Post »

Quick one today:

Earlier today, we needed to add a new drive to existing Sql Server cluster. New LUN was created and added to the cluster, but when we tried to add a new data file, this error occured:

Msg 5184, Level 16, State 2, Line 1
Cannot use file 'I:\MSSQL\Data\SsampleDB2.ndf' for clustered server. 
Only formatted files on which the cluster resource of the server has a dependency can be used. 
Either the disk resource containing the file is not present in the cluster group or the 
cluster resource of the Sql Server does not have a dependency on it.

Msg 5009, Level 16, State 8, Line 1
One or more files listed in the statement could not be found or could not be initialized.

The error message is verbose and helpful. It points to the exact things we could check to resolve it.

  1. Either the disk resource containing the file is not present in the cluster group OR
  2. Cluster resource of the Sql Server does not have a dependency on it

The first one did not apply here as the drive was already added to the Cluster through Failover Cluster Manager. So, lets look at the second step.

Dependency chain is an important aspect of Sql Server Clustering. It tells Sql Server what to do in case of failure. A resource group has several resources in it. All the resources in a single resource group act as a single group. They failover as a one unit; Thus providing high availability on the available nodes.

Keep these questions in mind, when you think about different resources in a cluster.

  • What resources belong to a Resource Group?
  • What to do when each of them fail?
    • Do we try to re-start the resource (or NOT)?
    • How many attempts to bring the resource back online?
    • Do we wait a few minutes and then attempt restarting?
    • Does failure of one resource trigger failover of the entire Resource Group?
    • Do we just failover that single resource?  (for this to happen, that resource needs to be in its own Resource Group

All these points are answered during the configuration of resources to a cluster.

For this situation, we need to add this new drive to the dependency chain for Sql Server Resource Group.

Resolution Steps:

Step 1:

First, go to Failover Cluster Manager. Under the relevant cluster name, go to Roles; On the right-hand window, highlight the Resource Group that has Sql Server resources. In our situation, it is called: Sql Server (MSSQLSERVER). See below:

Step 1: Sql Cluster Add NewDrive

Step 1: Sql Cluster Add NewDrive

Step 2:

When Sql Server Resource Group is highlighted, go to the bottom window, where details are displayed. It has two tabs.

  1. Summary
  2. Resources

Go to Resources tab; Here you might see a few sections.

  1. Storage:  All the drives added to this cluster Resource Group are listed here. Make sure the new drive can be seen in the list.
  2. Server Name : These are the logical Cluster Name and IP addresses
  3. Other Resources : Depending on the resources added, we see Sql Server, Sql Server Agent, MSDTC, etc

Right click on Sql Server, and go to properties.

Step 2: Sql Cluster Add NewDrive

Step 2: Sql Cluster Add NewDrive

Step 3:

In Properties window, go to Dependencies tab. Here we configure the Dependency Chain for Sql Server Resource in the Resource Group called Sql Server(MSSQLSERVER). Each and every data drive is critical for Sql Server to function properly. So, we use AND condition to specify that each and every drive is needed.

Click on Insert button, add condition as AND & pick the new Cluster Disk in the drop down under the Resource side.

Sql Cluster Add NewDrive 3

Sql Cluster Add NewDrive 3

Click ok and Voila !! The new drive is added to the “Dependency Chain”. For more information about the Cluster response to failure, review the options under the Policies tab in the same Properties window.

Now we could add a new NDF file to the new drive location.

Hope this helps,

Read Full Post »