Getting little help and learning from each other always helps. When I don’t get to know of something and I need to explore something, I try to check with my SQL friends who can guide me in the right direction. This blog is a classic example of me trying to learn something and help the community. Recently I got an email from my blog reader as below:
My Name is Aysha and I have been a regular follower of you blog. Just to let you know that I am a hard-core developer, but also an accidental DBA. So please pardon me if this is a really simple issue for you. Also, I don’t have much knowledge about cluster. Here is the problem: I needed to create a new database on the SQL Server 2012 two node failover cluster to host database for new application under development. By following few internet articles, I added a new drive (M and N) into the SQL Group in the cluster, but I am unable to create database on the newly added drive.
Here is the command I was trying
CREATE DATABASE [TestDatabase] ON PRIMARY
(NAME = N'TestDatabase', FILENAME = N'M:\SQLDATA\TestDatabase.mdf' )
(NAME = N'TestDatabase_log', FILENAME = N'N:\SQLLOG\TestDatabase_log.ldf')
But here is the error I am receiving
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.
Do you think I did something wrong? I have restarted SQL multiple times, but still same error. Can you please help?
Here is my reply.
Thanks for contacting me and giving me all the details which I always expect from anyone who contacts me. Since you are new to cluster, you need to read a little more about the dependencies of cluster resource. Dependency decides the order in which they can come online. The child resource must come online before an attempt is made to bring parent resource online in a cluster.
- The IP Address and the Disk Resource do not depend on anything.
- SQL Network Name depends on the IP Address.
- SQL Server depends on the Network Name and a Disk Resource.
- SQL Agent depends on SQL Server.
Coming back to problem which you are facing (i.e. below error). Here is a breakup of error
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.
See that, dependency terms in error message?
So, all you need to do is add dependency of disk M and N which you have added post installation, to the SQL Server resource. SQL Server is preventing us from creating databases on drive which aren’t a dependency of the SQL Server resource. This is to make sure that the disk is online before the SQL is trying to access database file.
Here are the steps
- Open Failover Cluster Manager from Start Menu OR you can also go to Start > Run > Cluadmin
- Select the clustered application, i.e. the group which has SQL Server.
- Right-click on your “SQL Server” resource then choose Properties.
- Go to Dependencies tab.
- Click the Insert button to add an additional row, then select the shared disk (M) that is going to contain your SQL databases. In next row N also to be added.
- Ensure that all resources are required (there is an AND next to subsequent resources) then click OK.
That’s it. Depending on the version of SQL, you might need to take SQL Server resource offline and bring it back online by choosing from right click.
Hope this helps!
Later she responded back and informed me that she was able to fix the issue after following the steps.
Home work: Can you help me out with this – which SQL version does not need restart after modifying dependencies of the disk? If you already have a working SQL cluster, please test and let me know by commenting. This was a great learning for me too, and felt I would quiz you with some research.
Reference: Pinal Dave (https://blog.sqlauthority.com)