Monday, April 4, 2011

CREATE DATABASE failed with clustered Microsoft SQL Server 2008 instance

Trying to create a database on a clustered SQL 2008 instance? Getting 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.

If so, you forgot to configure the disk dependency in Failover Cluster Manager! SQL prevents you from creating databases on volumes which aren’t a dependency of the SQL Server resource. This is to prevent situations where SQL Server comes online before the disk containing the database which would lead to a “I can’t see the database!” error from SQL.

To remediate this, simply 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.

1 comment: