Error When Creating new Database on Clustered SQL Environment

Recently I got an Clustered SQL 2012 environment created with two nodes as I needed to deploy SharePoint 2013 on it. SharePoint Installation gone well but I got below error when I’m tying to create a new web application.

“Cannot use file ‘D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\WSS_Content_7777.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.”

tried though SQL Management Studio to create a new DB in order to verify and it gives the same error. so it should be from SQL cluster side for sure.

image

 

 

 

 

anyway, we love these kind of little damns because always we learns something though them. let’s find out what it is.

when I look at the error it was clearly mentioning something about Dependency. so yes obviously my cluster is brand new. didn’t fine tune well because was bit hurry on deployment of SharePoint.

So it’s all about Configuring Dependency from Windows cluster.

  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.

generally SQL prevents the creation of Databases on volumes which are not Dependency of SQL Server resources. So The point of having dependencies is to prevent situations where SQL Server comes online before the disk containing the data goes on line. Therefore the term “DEPENDANCY.”

Let’s see how we can add the dependencies to get this fixed. by the way you don’t have to do this once you got a new clustered environment created as The dependencies are automatically established when you create the SQL cluster. but if you substitute any disk resources don’t wait till the error remind you for it, you have to add the dependencies.here in my case it was the point, I added some disk resources and I forgot to re-add the dependencies Winking smile.

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

The Following Illustrate the Resource Settings of SPLAB which is my testing environment.

clip_image001

  • The Name Depends on the IP Address

clip_image002

  • IP Address does not depends on anything

clip_image003

  • If Analyses services are Installed, it depends on the Network Name

clip_image004

  • Cluster Disks has no Dependencies

clip_image005

  • SQL Server has Dependencies of Disks and Network Name

clip_image006

  • And Finally Agent depends on SQL Server

clip_image007

once all these added. you done !

Advertisements

One thought on “Error When Creating new Database on Clustered SQL Environment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s