If you are using dynamic disks for some reason, and please avoid using them if you don’t have to, the partitions on it are likely to be misaligned for SQL server. I discovered this while trying to configure an AO Availability Group where one of the replicas were using local SSD drives configured with software RAID 1.
Problem
Since this was a new setup for me, I ran the old “wmic partition get BlockSize, StartingOffset, Name, Index” just to make sure everything was in order. To my astonishment, it was not: 
For some reason, the partition is using the old Win 2003 31,5 KB offset! To make it worse, I discovered this AFTER I had installed SQL server. Since dynamic disk and software raid basically sucks, information about this on the great interweb was sparse. But after some searching I found a cure, at least for volumes without RAID, at http://blogs.utexas.edu/alex/2013/04/04/windows-aligning-dynamic-disks/. (Link dead as of 2016.10)
Solution
Based on the above mentioned blog post, with my comments and changes for RAID. Be aware, this process may be destructive. This guide assumes that you, as I did, already have an active mirror with the wrong alignment. If you have fresh drives, just ignore the parts about breaking the mirror and moving data.
- Make sure you have a valid backup
- Be prepared to do a clean install if necessary
- Break the mirror
- Give both drives new drive-letters and restart the server to make sure no active application/service is using the drive
- Run diskpart, and select one of the drives that was part of the mirror. If you don’t know how to do this, STOP and ask someone to help you or read up on diskpart BEFORE you continue.
- Execute the following diskpart commands against the selected drive. This guide expects that you want one volume to fill the entire drive. If you don’t want this, think long and hard about why and consider changing your mind.
- clean
- online disk
- attributes disk clear readonly
- convert gpt
- select part 1
- delete part override
- create partition msr size 128
- convert dynamic
- create volume simple align=1024
- Screenshots
- Then, format the new partition with 64K allocation unit size
- Move the data from the other partition that was part of the mirror, and hope this will work
- Run the diskpart commands against the other disk. Be aware, this will delete the data, so make sure the move command was successful. And this time, skip the last create volume command.
- Select second disk
- clean
- online disk
- attributes disk clear readonly
- convert gpt
- select part 1
- delete part override
- create partition msr size 128
- convert dynamic
You should now have two dynamic disks, one with the data and one unallocated. Now, to add the mirror. I discovered that I was unable to add the second drive back as a mirror, the add mirror option was grayed out. I solved this by first shrinking the original by 50MB, and then creating the mirror. I didn’t test this extensively, but I would guess that 5 or 10 megabytes of free space would have been enough.
Hi,
When I tried this method to align dynamic disks on a Windows 2008 R2 server, I ran into an issues.
One additional partition was created after converting the GPT disk into GPT dynamic disk (the
Partition 2 in the following screen shot). In addition, I don’t understand why the ‘msr’ partition needs to be 128MB big.
… …
DISKPART> create partition msr size 128
DiskPart succeeded in creating the specified partition.
DISKPART> list part
Partition ### Type Size Offset
————- —————- ——- ——-
* Partition 2 Dynamic Reserved 1024 KB 1024 KB
Partition 1 Reserved 127 MB 2048 KB
Partition 3 Dynamic Data 29 GB 129 MB
… …
C:\Users\chixxisx>wmic partition get blocksize, startingoffset, name, index, size, type
BlockSize Index Name Size StartingOffset Type
512 0 Disk #0, Partition #0 77307314176 1048576 Installable File System
512 0 Disk #4, Partition #0 1048576 1048576 GPT: Logical Disk Manager Metadata
512 1 Disk #4, Partition #1 32076971520 135266304 GPT: Logical Disk Manager Data
512 0 Disk #1, Partition #0 38653624832 32256 Logical Disk Manager
Therefore, I cleaned the disk/partition configuration and repeat the process with the exception of creating a 1MB big msr partition.
After converting into dynamic disk, only two partitions are created. The new aligned dynamic partition has more space.
… …
DISKPART> create partition msr size 1
DiskPart succeeded in creating the specified partition.
DISKPART> list part
Partition ### Type Size Offset
————- —————- ——- ——-
* Partition 1 Reserved 1024 KB 1024 KB
DISKPART> convert dynamic
DiskPart successfully converted the selected disk to dynamic format.
DISKPART> list part
Partition ### Type Size Offset
————- —————- ——- ——-
* Partition 1 Dynamic Reserved 1024 KB 1024 KB
Partition 2 Dynamic Data 29 GB 2048 KB
… …
C:\Users\chixxisx>wmic partition get blocksize, startingoffset, name, index, size, type
BlockSize Index Name Size StartingOffset Type
512 0 Disk #0, Partition #0 77307314176 1048576 Installable File System
512 0 Disk #4, Partition #0 1048576 1048576 GPT: Logical Disk Manager Metadata
512 1 Disk #4, Partition #1 32210140672 2097152 GPT: Logical Disk Manager Data
512 0 Disk #1, Partition #0 38653624832 32256 Logical Disk Manager
The default size of the MSR partition is defined by Microsoft. For disks <16GiB, a 32MiB MSR should be used. For disks >=16GiB, a 128MiB MSR should be used. The extra partition that is created after converting the disk to dynamic contains the dynamic disk database. This behavior is as expected. The function of the MSR is to reserve space for software that previously used hidden sectors, as GPT drives doesn’t support hidden sectors.
See http://msdn.microsoft.com/en-us/windows/hardware/gg463525.aspx for more info.
Thanks for the information. It seems that we have to give up 128MiB in order to use GPT disks. (129MiB in fact as the MSR partition starts at 1024KiB.)However, when the disk is converted to GPT Dynamic disk, The MSR partition size is reduced to 127MiB as the first MiB becomes Dynamic Reserved partition. Is this still Okey for GPT disk or is this usage on purpose because Dynamic Reserved partition is one of possible usages of MSR?
Sorry my bad. Microsoft GPT FAQ does explain the usage of MSR partition and Dynamic disk database is one of the usages.