Moving SQL Datafiles

SQL Server Best Practices
SQL Server Best Practices
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

Continue reading on SirSQL.net.

54321
(0 votes. Average 0 of 5)
Leave a reply

Your email address will not be published. Required fields are marked *