Getting help and learning from each other is always beneficial. As a SQL Server enthusiast, I often rely on my SQL friends to guide me in the right direction when I come across something unfamiliar. Recently, I received an email from one of my blog readers, Aysha, who is a hard-core developer but also an accidental DBA. She was facing an issue while trying to create a new database on a SQL Server 2012 two-node failover cluster. Aysha had added a new drive to the SQL Group in the cluster but was unable to create a database on the newly added drive.

Aysha shared the command she was using to create the database:

CREATE DATABASE [TestDatabase] ON PRIMARY 
( NAME = N'TestDatabase' , FILENAME = N'M:\SQLDATA\TestDatabase.mdf' ) 
LOG ON 
( NAME = N'TestDatabase_log' , FILENAME = N'N:\SQLLOG\TestDatabase_log.ldf' )

However, she encountered the following error:

Msg 5184, Level 16, State 2, Line 1
Cannot use file ‘M:\SQLDATA\Testdatabase.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.
Msg 1802, Level 16, State 1, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

In my reply to Aysha, I explained that she needed to understand the dependencies of cluster resources. Dependencies determine the order in which resources can come online in a cluster. The child resource must come online before the parent resource in a cluster. In the case of SQL Server, the IP Address and Disk Resource do not depend on anything, but the SQL Network Name depends on the IP Address, and SQL Server depends on the Network Name and a Disk Resource. SQL Agent depends on SQL Server.

The error Aysha encountered indicated that the disk resources she added post-installation were not dependencies of the SQL Server resource. SQL Server prevents the creation of databases on drives that are not dependencies of the SQL Server resource to ensure that the disk is online before SQL Server attempts to access the database files.

I provided Aysha with the following steps to resolve her issue:

1. Open Failover Cluster Manager from the Start Menu or by going to Start > Run > Cluadmin.
2. Select the clustered application, i.e., the group that contains SQL Server.
3. Right-click on the “SQL Server” resource and choose Properties.
4. Go to the Dependencies tab.
5. Click the Insert button to add an additional row and select the shared disk (M) that will contain the SQL databases.
6. Add another row for disk N.
7. Ensure that all resources are required (there is an “AND” next to subsequent resources) and click OK.

After following these steps, Aysha was able to fix the issue. She later informed me that she had successfully created the database on the newly added drive.

As a homework assignment, I asked my readers to help me identify which version of SQL Server does not require a restart after modifying the dependencies of a disk. If you have a working SQL cluster, please test it and let me know by commenting on this post. I believe this will be a great opportunity for us to learn together and share our findings.

Thank you for reading and happy SQL Server clustering!