Adding dependency to resource 1

[jmcarlson]

I have a clustered MS SQL 2005 server environment running on W2K3 server. I take a snapshot of live databases, and present them to a second clustered MS SQL 2005 environment. When the SQL tries to mount the database, I get the error below:

Job 'Refresh SNAP' : Step 1, 'Attach DBs' : Began Executing 2008-02-14 09:20:00

Msg 5184, Sev 16, State 2, Line 1 : Cannot use file '<file_name>' 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. [SQLSTATE 42000]

Where <file_name> is the name of the first file it tries to mount.

To create this dependency, I will need to temporarily off-line the SQL Server resource, add the dependency, then bring everything on-line. This will be a problem for us because we will need to create these snapshots at least once a day, and in some cases, multiple times a day. At those times, it will not be possible to off-line the SQL Server resource.

Is there a way to add a dependency to a resource without off-lining the resource?

 

[mrdenny]

Nope, the cluster resource has to be offline in order to change it's dependency information.

In order to do this to a clustered environment you'll probably need to use the native SQL Server tools to backup and restore the database. I would recommend a spin off of SQL Server Log shipping, where you do a single full backup and restore to the reporting system, then backup the log files from production and store them on a hard drive on the reporting system. Then when it's time to refresh the reporting system, restore those logs to reporting which will then bring your database up to current fairly quickly.

I've used this technique before, and on a fairly busy system it would talk about 30 minutes to roll forwards 24 hours worth of transaction logs.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[jmcarlson]

Denny,
Thank you for confirming what I had feared. We're going to try your suggestion, but I'm afraid that as our database grows, so will the time needed to perform this task.

Looks like we're going to need to do without a cluster for the time being.

Thank you

 

[mrdenny]

The nice thing about the log shipping, is that database size doesn't matter. You are only rolling forward the changes from a single day, not restoring the entire database each day. So unless the number of changes to the database goes up dramatically the amount of time to roll the logs forward won't change.

Do keep in mind that what ever day you do your database maintenance the logs will be very large as database maintenance is a logged operation, so the next morning that days worth of logs will be larger than the rest and will take longer to deploy.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog