When you add new disks to a server and you intend to use them for SQL Server log and data files, there are some suggested best practices in preparing the disks. I suggest you read this post, then read the resources below before taking any action. Some of the configuration values in the Partitioning and Formatting steps below are not universally agreed upon despite strong evidence to support their validity. It is important that you understand the nature of your environment and come to your own conclusions regarding configuration before you proceed.
This post assumes that the disks have been installed or otherwise presented to the server and covers the 3 steps necessary to prepare them:
- Initializing
- Partitioning
- Formatting
The information in this post is generally applicable to any storage being used by Windows Server 2003. Check with your storage vendor for more information though. Recent changes in storage technology may mean that the specifics of these steps are not accurate for your environment.
Initializing
After the disks are installed or presented to the server, you must initialize them. This can easily be done through the Disk Management interface.
Click Start > Run, type DISKMGMT.MSC and hit Enter to bring up the Disk Management utility. On opening it may prompt you to configure these as dynamic disks. You don’t want that so cancel out of it.
In the bottom pane, you will see a list of disks on the machine starting with Disk 0. Underneath you will see disks that have been recognized but not initialized. Click on the Disk # of the disk you want to manage and choose Initialize. Do this for any disks you have added.
Partitioning
Next you have to create a partition on the disk. We are simply going to create a single primary partition using the DISKPART.EXE tool that comes with Windows Server 2003.
Click Start > Command Line to open up the command line. Then type diskpart to start a diskpart session.
At the diskpart prompt, type:
list disk
This will show all initialized disks. You’ll notice that the disk numbers here correspond to the disk numbers in the Disk Management utility. For the sake of example, we’ll assume that you added 2 new disks recognized as disk 3 and disk 4 under Disk Management and that you have initialilzed them. Type:
select disk 3
then hit enter and type:
create partition primary align=64
and hit enter again. The use of align=64 is perhaps a little controversial to some that are using advanced storage technology. There is strong enough evidence to support that this is an important configuration you should make regardless and I’m not going to jump into the debate. From Microsoft (see resources below):
…sector alignment, should be performed on the file system (NTFS) whenever a volume is created on a RAID device. Failure to do so can lead to significant performance degradation…
We will give Disk 3 a drive letter of Y and Disk 4 a drive letter of Z. Once the partitioning operation completes, assign a drive letter by typing:
assign letter=y
Now you can go through the same process for disk 4:
select disk 4
create partition primary align=64
assign letter=z
When you are done type exit to end your diskpart session.
Formatting
You can format through the Disk Management Utility if you want. I usually just do it through the command line just because I have the command line open after partitioning. The command is:
format y: /fs:ntfs /v:NewLogs /a:64k
Let’s break down the command. The y: specifies the drive to format by drive letter. /fs:ntfs species that the disk be formatted using the NTFS file system. /v:NewLogs gives the disk a lable of NewLogs. /a: 64k formats the disk to allocate data in 64k chunks. This can also be a misunderstood or controversial setting. Again, there is strong enough evidence to support using this configuration in addition to being a recommendation from Microsoft:
When formatting the partition that will be used for SQL Server data files, it is recommended that you use a 64-KB allocation unit size for data, logs, and tempdb.
After completing these 3 steps, your disks are properly prepared and ready to use. Explore the references below for more details.
References
- http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/pdpliobp.mspx
- http://support.microsoft.com/kb/929491
- http://sqlblog.com/blogs/kevin_kline/archive/2008/10/08/how-to-improve-application-and-database-performance-up-to-40-in-one-easy-step.aspx
- http://sqlblog.com/blogs/linchi_shea/archive/2007/02/01/performance-impact-of-disk-misalignment.aspx
- http://blogs.msdn.com/jimmymay/archive/2008/10/14/disk-partition-alignment-for-sql-server-slide-deck.aspx
- http://searchenterprisedesktop.techtarget.com/tip/0,289483,sid192_gci1241594,00.html