The Problem

A Sharepoint database (LOCAL_CONTENT_DB) running on a clustered SQL 2005 EE Instance (on Windows 2003 EE) had experienced growth from 10GB to 95GB in a few weeks and there was no more disk for it to grow any further.

I needed to add some disk to the cluster, get it in the cluster so that SQL could see it and then move data around so that there was extra capacity for this database and others.

 

Existing Disk Layout

• F: – SQL Data 200GB
• G: – SQL Logs 100GB
• H: – SQL Backups 200GB
• I: – SQL System & TempDB 75GB

 

Proposed Disk Layout

• F: – SQL Data 200GB
• G: – SQL Logs 100GB
• H: – SQL Data2 200GB (changed usage)
• I: – SQL System & TempDB 75GB
• J: – SQL Backups – 500GB (new)

Deploying The Changes

Attaching the disk

Fortunately we had some capacity on the SAN and a friendly SAN engineer who quickly carved up a new LUN for me and presented it to the clustered nodes. I logged on to each node, opened up the Disk Management console (diskmgmt.msc) and rescanned the disks (Action -> Rescan Disks). Once the scan was complete I scrolled down through the list and verified I could see the newly presented LUN (and made a note of the disk number)

 

Aligning the disk & creating a partition

With the disk number in hand, on one of the nodes, I opened up a Command Prompt window (so want to call this a DOS window) and opened diskpart.

Why not just format the disk? You might ask. Well there’s the disk offset issue in Windows 2003 (thankfully eliminated in Windows 2008) to be aware of which would cause extra writes and impact performance (read all about this at http://support.microsoft.com/kb/929491).

With diskpart open I partitioned the disk and assigned a drive letter (note disk 15 was the newly attached LUN)

select disk 15
create partition primary align = 64 (this is based upon recommended value from the storage provider)
assign letter = J

This done I exited out of diskpart and went back to Disk Management. A refresh and this now showed J good to go. Right click and a quick format later and the disk was ready to be added to the cluster.

 

Clustering the disk

Before working with the disk we need to get it in the cluster. Rather than just add it to the SQL cluster group I wanted to be sure that there were no issues with reading or writing to the disk from either host. To accomplish this I opened cluster administrator and created a new cluster group called temp.

In the cluster group I added a new disk resource for drive J, allowed both nodes to own the resource and brought the disk online.

To check I was able to read and write to the disk I just created a new file and saved it. Then failed over the disk to the other node, opened it there, appended to the file and saved it again. There were no issues so I knew we were good to go with getting this disk attached to SQL. To get the disk ready I set up my basic folder structure ready for the SQL files.

 

Presenting the disk to SQL

Now that I knew the disk was fully accessible and writable from both hosts it was time to actually present it to the SQL Instance. This, unfortunately, required a short period of downtime as the disk needed to be added as a dependency for SQL Server startup.

To do this I performed the following steps

• opened the Sharepoint cluster group in cluster admin and took offline the SQL Server Agent and SQL Server resources
• went to the temp cluster group and took “drive J” offline
• right clicked on “Drive J” and selected “Change Group” from the context menu
• selected the Sharepoint cluster group as the new group and confirmed
• went to the Sharepoint cluster group and brought “Drive J” online
• right clicked on the SQL Server resource, went to properties, dependencies, modified and added “Drive J” as a dependent resource
• brought the SQL Server & SQL Server Agent resources online
• checked that SQL was able to access and write to the new disk by performing a simple quick backup of the master database (and deleted this immediately afterwards)
• performed full backups of all the databases on the instance to the new drive

 

 

Moving the database

With the disk in place, and full backups taken it was time to move the database. In SQL 2000 this would have been done using sp_detachdb & sp_attachdb, but in SQL 2005 and later there is a much more elegant solution whereby you can set the database offline, copy the relevant file and then use a modify database statement to reference the new location before bringing the database back online again. Here are the steps I followed to do this:

• Set the database offline 
  • ALTER DATABASE LOCAL_CONTENT_DB SET OFFLINE;
• Copy file F:MSSQLDataLocal_Content_DB.mdf to H:MSSQLDataLocal_Content_DB.mdf Please note that this was a copy, not a move. Moving the file increases the risk of something happening to that file and you not being able to recovery it.
• Once the copy was complete change the reference for the mdf 
  • ALTER DATABASE LOCAL_CONTENT_DB
    MODIFY FILE
    (
    NAME = Local_Content_DB_Data
    , FILENAME = ‘H:MSSQLDataLocal_Content_DB.mdf’
    );
• Bring the database back online
  •  ALTER DATABASE LOCAL_CONTENT_DB SET ONLINE;

Final steps

With the database back online I ran a couple of queries against it, just to confirm it was accessible completely and then, just because I am a careful chap I also executed a dbcc checkdb (which also came back clean). Finally I had the application admin check that everything looked good (which it was).

Once I knew that everything was good I changed the backup location to point to the new disk and deleted the old mdf from the F drive, tada, done.