Thursday, May 30, 2013

Drive not showing in SQL Server Cluster?

I've had this happen a couple times, yesterday being the most recent, so this post will also serve as a reminder next time I run into this situation.

Situation:
Say you have a cluster and you add a new drive/LUN.  Your storage folks tell you it's available and it's been added in as a clustered resource.  All is good, right?  Nope.

Issue:
What you will notice is that if you try to backup to, restore from, or basically access that drive in any way from SSMS, it will not be listed as an available drive.  Why?

Resolution:
The last thing you need to be sure to do when you add a clustered resource is to make sure it's also added as a dependency to your SQL Server service:

  • Open your Cluster Manager
  • Expand Services and applications
  • Click on SQL Server (instance name)
  • Go to "Other Resources" section
  • Right-click on SQL Server (instance name) and click Properties
  • Go to the Dependencies tab
  • Insert the newly added drive as a dependency
Now if you go to access that drive from SSMS, for example, you will see it available.

Wednesday, May 29, 2013

Cannot drop the database because it is being used for replication.

In the event you want to move a database that is configured as a publisher for replication, a detach/attach will not work.  Rather, you can simply take the database offline, move the data file, adjust the properties and bring it back online.  I saw suggestions to backup/restore, but this is much quicker and more effective in my opinion.

Sample script:

ALTER DATABASE <database name> SET OFFLINE

-- move file to new location

ALTER DATABASE <database name> MODIFY FILE ( NAME = <logical file name>, FILENAME = '<path\file name>')

ALTER DATABASE <database name> SET ONLINE