Help for Migration to SQL Server 2008

SQL Server 2008 has launched the new Migration Solution Center.  On this site, the freely downloadable SQL Server Migration Assistants (SSMAs) which can help to move databases from Access, Oracle, and Sybase to SQL Server.  Check out these free tools from Microsoft.

Simple Sector Alignment

In my experience as a SQL Server consultant, about 5% of my customers have heard of Sector Alignment.  The fact that a properly aligned partition will out perform a non-aligned partition by 20% or more should make this the most important thing you read today.  Here it is as simple as I can make it.

First, find out what is your FAU (File Allocation Unit size) by running fsutil fsinfo ntfsinfo <drive letter like c:\> at a command prompt.  Read the line Bytes Per Cluster which is your FAU.  The FAU for my local workstations c: drive is 4096 bytes.

Next, and only if you are working on a SAN or RAID array, find the Stripe Size.  How to find this will vary by manufacturer or utility that you use.  For this example, we'll assume I have a Stripe Size of 32k.

Third is the Partition Offset.  This is found by running WMIC PARTITION LIST FULL.  The value that you look for is StartingOffset.  Mine is 32256 (the default) which is less than 32k.

Now divide the Partition Offset by the Stripe Size.  32256 / 4096 = 7.875.  To be properly aligned, the result should be a whole number.  Let's say I change my Partition offset to 64k or 65536 bytes.  65536 \ 4096 = 16.  Good so far.

Next, we need the FAU to be aligned with the Stripe Size.  Put another way, FAU / Stripe Size should be a whole number.  For me example, 4096 / 4096 = 1.  Good.

If you have trouble with all of the above, FAU = 64k, Starting Offset = 64K, and Stripe Size = 64K almost always works great for SQL Server. 

The biggest gotcha is that setting the Partition Offset must be done when you create the partition.  This means that if the partition is already in use it would need to be recreated which would require moving all data on the partition to another location and then moving it back once complete. You must also be using Windows Server 2003 SP1 or later.  The Partition Offset is made with the DISKPART utility.  Instructions are here: http://support.microsoft.com/default.aspx?scid=kb;en-us;923076&sd=rss&spid=3198.

I am now aligned and my IO system is smokin'!  I hope your results are the same.