Cannot Create New Database in SQL Cluster

[Jason S. Global Moderator]

We have two SQL servers clustered using MS's Failover Cluster Manager. This is generally a static environment. We dont normally add/remove/modify databases.

 

This time around, however, i need to add a new database. In SQL, when i try to add a new db i get an error message:

 

 

After searching Google I found that the SQL service in FCM needed to have Dependencies set. From FCM, i highlight the SQL service, click Properties, then click the Dependencies tab. I added both our D: and L: drives (data, logs). I then restarted the cluster service. I still get the error.

 

 

Any other ideas? I'm pretty stuck here...

[sc302 Veteran]

I have nothing other than more google searches. 

http://www.sqlservercentral.com/Forums/Topic411395-149-1.aspx

"I was adding the dependy on the wrong group instead that on the SQL Server Group."

 

https://support.microsoft.com/en-us/kb/295732

1. Open the Cluster Administrator.
2. Make sure that all the physical disk resources that contain SQL Server databases are in the same group as the SQL Server resource.
3. Right-click the SQL Server resource, and then bring the resource into an Offline state by clicking Bring Offline.
4. Right-click the SQL Server resource, and then click Properties.
5. Click the Dependencies tab.
6. Click Modify to add the disk to the dependencies list for that resource.
7. Bring the SQL Server resource back online, and then put the SQL Server files on that shared cluster disk.

 

 

here is something else you can try:

Create a db, and restore it....(this is using a product call sql safe, but you get the idea).

Specify WITH MOVE during the restore operation to restore these files to a drive that the cluster is dependent upon.

If you wish to move the data and log files to a drive that is already configured as a cluster resource drive, you can use the WITH MOVE parameter in the restore command.

If you are performing the restore from the SQL Safe Management Console, you can simply type in the new path for these files in the ‘Restore database files as’ field labeled ‘Change Path.’ Hit Apply and you will see that the target files have been updated with the new path. This will add the WITH MOVE T-SQL statement to the restore job.

If you are using the command line or the SQL Safe Extended Stored Procedures to perform this restore, then you will want to use the –move or @move parameter, respectively, to change the destination for these files to a drive which the SQL Server instance can use.

[Jason S. Global Moderator]

i read that same linked article. "dependy"

 

i also did the exact same steps from the MS article.

 

in the last option, is it suggesting that i create a new DB in another location and then move it? Could i create it, move it to their respective locations and then attach?

[sc302 Veteran]

Well if you have dbs currently running in the environment, I don't see why you couldn't move/restore an existing db.   It is certainly is something else to try...it doesn't fix your issue, but it is a work around. 

 

 

It sounds like something may be misconfigured, but damned if I know from where I sit over here.

[Jason S. Global Moderator]

wow, this is crazy. i'm completely stumped.

 

i created a new db on another SQL server. I then moved the mdf and ldf to their correct locations on the clustered drives of the clustered SQL instance. After that, i tried the "attach" option from right-clicking on the SQL instance in SSMS. I got the same error message! I cant even attach the db even though they now exist.

 

nuts. i dont get it.

[Jason S. Global Moderator]

alright! i figured it out!

 

turns out i was missing a dependency, but i was looking in the wrong spot.

 

I used this link: http://level7techgroup.com/blog/?p=823

 

In FCM, select the SQL service under "Services and applications". I was trying to add the drive dependencies under the 'Name' field. This turned out to be incorrect. Instead, i needed to add the D: and L: drives to the Dependencies tab of the SQL Server service under 'Other Resources'.

 

 

What a pain. I sure hope this helps someone else!

[sc302 Veteran]

Like I said,  it looks perfect from where I sit. Lol.  It might help,  certainly a misconfiguration on the install.  And something that cannot be easily determined without a lot more information or direct access to your servers.