SQL Server Cluster Resource Dependencies

Recently, I needed to create a database on the SQL Server 2008 R2 two node failover cluster to host another application under review. The failover cluster lab project is described in the following posts: SQL Failover Cluster – Part 1, Part 2, Part 3 and Part 4. In the process of creating the database, I got the following error message:

Cannot use file ‘D:\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\myDatabase.mdf’ 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.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

You have to love these little surprises. Here you are trying to knock out a quick little research project and BAM! You find yourself transported to a different planet where you will spend far more time than you have first, trying to make sense out of what Microsoft is trying to tell you and another “lifetime” trying to construct the query that you can “bing google” to get a clue to what is really going on.

OK, people have things to do and don’t need to be entertained. The answer is simple: It’s all in the “dependencies.”

1. SQL Server depends on the Network Name and a Disk Resource.
2. SQL Network Name depends on the IP Address.
3. SQL Agent depends on SQL Server.
4. The IP Address and the Disk Resource do not depend on anything. 

Translated, this means that you forgot to configure the disk dependency in Failover Cluster Manager. SQL Server prevents you from creating databases on volumes which aren’t a dependency of the SQL Server resource – point #1. The point of having dependencies is to prevent situations where SQL Server comes online before the disk containing the data goes on line. Hence the term “dependency.”

This functionality is similar to the situation with various Services that have a dependent relationship that must be established in order to function correctly. You set the relationships in the Service property.

To fix this, add the dependency:

1. Open Failover Cluster Manager
2. Select your clustered application
3. Right-click on your SQL Server resource then click Properties.
4. Click on the Dependencies tab.
5. Click the Insert button to add an additional row, then select the shared disk that contains your SQL databases.
6. Ensure that all resources are required (there is an AND next to subsequent resources) then click OK.

The dependencies are automatically established when you create the SQL cluster; however, if changes are made and you substitute another disk resource, then you will have to specify the new dependency.

The following illustrate the resource settings for the AdventureWorksFC failover cluster.

The Name is dependent on the IP Address

The IP Address has no dependencies.

The Cluster Disk has no dependencies. 

SL Server is dependent on the Cluster Name and the Disk Resource(s).

The SQL Agent is dependent on the SQL Server Engine.

 In retrospect, the most frustrating part of researching this problem is creating the query that you can use as a Internet search.