Posts tagged “Storage Spaces

Configuring Storage Spaces for Azure SQL IaaS

One way to use SQL in Azure is to deploy SQL IaaS by installing SQL on an Azure VM. This post goes over provisioning disk system in Azure optimized for SQL. In this demo we’re using a DS14v2 size VM, but any VM size that supports 16 data disks is OK. To see the VM SKU’s that support 16 or more disks in a given Azure location we can use the PowerShell cmdlet:

Get-AzureRmVMSize -Location 'eastus' | 
    where MaxDataDiskCount -GE 16 | sort MaxDataDiskCount

and we’ll see a list like

We deployed a VM using the Microsoft Gallery image of the latest 2016 server version, using an unmanaged OS disk in a storage account configured as:

  • Premium
  • LRS
  • GPv2
  • Hot

Attach 16 disks:

Next we provision and attach 16 one TB unmanaged SSD disks (page blobs) using the following PowerShell code:

$VMName = 'myVMName'
$RGName = 'myResourceGroupName'
0..15 | foreach {
    $VM = Get-AzureRmVM -ResourceGroupName $RGName -Name $VMName
    $DataDiskName = "$VMName-DataDisk-$_"
    $OSDiskUri    = $VM.StorageProfile.OsDisk.Vhd.Uri
    $DataDiskUri  = "$($OSDiskUri | Split-Path)\$DataDiskName.vhd".Replace('\','/')
    $ParameterSet = @{
        VM           = $VM 
        Name         = $DataDiskName 
        Caching      = 'ReadWrite' 
        DiskSizeInGB = 1023 
        Lun          = $_ 
        VhdUri       = $DataDiskUri 
        CreateOption = 'Empty'
    $VM = Add-AzureRmVMDataDisk @ParameterSet
    Update-AzureRmVM -ResourceGroupName $RGName -VM $VM

Create Storage Pool:

Next we RDP to the VM and provision a storage pool. In Server Manager under File and Storage Service/Volumes/Storage Pools we should see the 16 disks under the default ‘Primordial’ pool. We can right click on that and create new pool. The important thing here is to select ‘Manual’ disk allocation for each of the 16 disks. This is important since this is an all-SSD pool. The default setting will cause the system to reserve all 16 disks for Journal and we won’t be able to create any virtual disks.

The same task can be performed via PowerShell as follows:

$PoolName = 'SQLPool'
$ParameterSet = @{
    FriendlyName = $PoolName 
    StorageSubSystemFriendlyName = (Get-StorageSubSystem).FriendlyName 
    PhysicalDisks = Get-PhysicalDisk –CanPool $True
New-StoragePool @ParameterSet
Get-PhysicalDisk -FriendlyName 'Msft Virtual Disk' | 
    Set-PhysicalDisk -Usage ManualSelect # otherwise all SSD disks will be reserved for journal

Create Virtual disks and volumes:

Finally we create virtual disks and volumes as follows:

This particular configuration uses 8 out of the 16 TB in the pool leaving 8 TB for future virtual disk growth. A Virtual disk can be expanded in Server Manager, to be followed by volume expansion in Disk Management tool.

The virtual disks in this configuration can survive a single disk failure being in a 2-way mirror. Although this is almost not a practical concern given that the 16 disks are triple redundant (each block of each disk is synchronously written to 3 physical underlying disks)

2 way-mirrored virtual disks also enhance read performance since read operations occur against one of the 2 disks in a mirrored space.

In the data/log/temp virtual disks, the Interleave size has been dropped to 64KB down from the default 256KB since SQL writes are 8-32KB. With 8 columns, this makes the data stripe size (64 * 8) 512KB


Using Powershell with Tiered Mirrored Storage Spaces

Windows Server 2012 R2 is full of new and enhanced features compared to Server 2008 R2. One of the new features is Storage Spaces. Basics of working with Storage Spaces:

  • Present raw disks to Windows via standard SAS controllers. No hardware RAID. Simply present JBOD to Windows to be used with Storage Spaces.
  • Boot/System disks must use traditional disks, not Storage Spaces. Typically use a pair of hardware mirrored disks for boot/system partitions.
  • The basic structure is: Storage pools contain physical disks, we create virtual disks within Storage Pools. A virtual disk can then be partitioned into volumes that can be formatted as a regular disk.
  • Initially all physical disks appear in the “primordial” pool. Newly added disks also appear in the primordial pool. Disks in the primordial pool are visible in the Computer Management => Disk Management tool and can be used directly.
  • Storage Spaces supports automatic tiering. Only 2 tiers are supported; typically SSD and HDD tiers. Tiering moves cold (less frequently accessed) data to the HDD tier, and hot (more frequently accessed) data to the SSD tier for better performance.
  • Tiering runs as a once-a-day scheduled task at 1 AM by default, and can be manually invoked.
  • When setting up tiered Storage Spaces, parity is not an option (can do simple or mirrored layout only). Also thin provisioning is not an option with tiering.
  • Storage Spaces supports thin and thick (fixed) provisioning.  Tiered Storage Spaces supports only thick (fixed) provisioning.
  • Storage Spaces supports write-back cache. The default is 1 GB for tiered vDisks, 32 MB for non-tiered vDisks, 100 GB maximum.
  • Recommended SSD to HDD ratio is 1:4
  • Storage Spaces supports 3 types of fault tolerance:
  1. Simple: this is like a stripe set with no parity: fastest but provides no fault tolerance
  2. Mirror: 2 way mirror requires minimum 2 disks disks and can survive a single disk failure. 3 way mirror requires minimum of 5 disks and can survive 2 simultaneous disk failures
  3. Parity: single parity requires minimum 3 disks and can survive a single disk failure. Dual parity requires minimum of 7 disks and can survive 2 simultaneous disk failures. Parity options are not available for tiered Storage Spaces.

Storage Spaces can be setup from the GUI: Server Manager => File and Storage Services => Volumes => Storage Pools.


Powershell provides more control compared to the GUI when configuring Storage Spaces. For example, you can set the write-back cache size when using Powershell but not from the GUI.

The following script sets up Tiered Mirrored Storage Spaces. Here’s how the disk system looked like in Computer Management before the script:


Here’s the script:


# Script to create Storage Spaces pool, virtual disks, volumes using custom settings
# Assumes physical disks in the default primordial pool
# Creates Mirrored Tiered virtual disks – need even number of SSD and even number of HDD available disks
# Sam Boutros
# 6/22/2014
# In this example I have 6x 256GB SSD disks + 2x 4TB SAS physical disks (not counting boot/system disks of course)
# I’d like to end up with # 3 mirrored and tiered vDisks of equal size using the maximum available space, with 25 GB write-back cache
# Customize the following settings to meet your specific hardware configuration
$PoolName = “Pool1”
$WBCache = 25 # GB (Default is 1 GB for Tiered disks – 32 MB for non-tiered)
$TieredMirroredvDisks = @(“HyperV1″,”HyperV2″,”HyperV3”) # List names of mirrored-tiered vDisks you like to create
$DriveLetters = @(“I”,”J”,”K”) # List drive letters you like to assign to the new volumes
$BlockSize = 32 # KB
# End Data Entery section
$Loc = Get-Location
$Date = Get-Date -format yyyyMMdd_hhmmsstt
$logfile = $Loc.path + “\CreateSS_” + $Date + “.txt”
function log($string, $color)
if ($Color -eq $null) {$color = “white”}
write-host $string -foregroundcolor $color
$temp = “: ” + $string
$string = Get-Date -format “yyyy.MM.dd hh:mm:ss tt”
$string += $temp
$string | out-file -Filepath $logfile -append
# Create new Storage Pool
$StorageSpaces = Get-StorageSubSystem -FriendlyName *Spaces*
$PhysicalDisks = Get-PhysicalDisk -CanPool $true | Sort Size | FT DeviceId, FriendlyName, CanPool, Size, HealthStatus, MediaType -AutoSize -ErrorAction SilentlyContinue
Log “Available physical disks:” green
log ($PhysicalDisks | Out-String)
if (!$PhysicalDisks) {
log “Error: no physical disks are available in the primordial pool..stopping” yellow
$PhysicalDisks = Get-PhysicalDisk -CanPool $true -ErrorAction SilentlyContinue
# Count SSD and HDD disk count and sizes, some error detection
$SSDBytes=0; $HDDBytes=0
for ($i=0; $i -le $PhysicalDisks.Count; $i++) {
if ($PhysicalDisks[$i].MediaType -eq “SSD”) {$SSD++; $SSDBytes+=$PhysicalDisks[$i].Size}
if ($PhysicalDisks[$i].MediaType -eq “HDD”) {$HDD++; $HDDBytes+=$PhysicalDisks[$i].Size}
$Disks = $HDD + $SSD
if ( $Disks -lt 4) { log “Error: Only $Disks disks are available. Need minimum 4 disks for mirrored-tiered storage spaces..stopping” yellow; break }
if ( $SSD -lt 2) { log “Error: Only $SSD SSD disks are available. Need minimum 2 SSD disks for mirrored-tiered storage spaces..stopping” yellow; break }
if ( $HDD -lt 2) { log “Error: Only $HDD HDD disks are available. Need minimum 2 HDD disks for mirrored-tiered storage spaces..stopping” yellow; break }
if ( $SSD % 2 -eq 0) {} else { log “Error: Found $SSD SSD disk(s). Need even number of SSD disks for mirrored storage spaces..stopping” yellow; break }
if ( $HDD % 2 -eq 0) {} else { log “Error: Found $HDD HDD disk(s). Need even number of HDD disks for mirrored storage spaces..stopping” yellow; break }
# Create new pool
log “Creating new Storage Pool ‘$PoolName’:” green
$Status = New-StoragePool -FriendlyName $PoolName -StorageSubSystemFriendlyName $StorageSpaces.FriendlyName -PhysicalDisks $PhysicalDisks -ErrorAction SilentlyContinue
log ($Status | Out-String)
if ($Status.OperationalStatus -eq “OK”) {log “Storage Pool creation succeeded” green} else { log “Storage Pool creation failed..stopping” yellow; break }
# Configure resiliency settings
Get-StoragePool $PoolName |Set-ResiliencySetting -Name Mirror -NumberofColumnsDefault 1 -NumberOfDataCopiesDefault 2
# Configure two tiers
Get-StoragePool $PoolName | New-StorageTier –FriendlyName SSDTier –MediaType SSD
Get-StoragePool $PoolName | New-StorageTier –FriendlyName HDDTier –MediaType HDD
$SSDSpace = Get-StorageTier -FriendlyName SSDTier
$HDDSpace = Get-StorageTier -FriendlyName HDDTier
# Create tiered/mirrored vDisks
$BlockSizeKB = $BlockSize * 1024
$WBCacheGB = $WBCache * 1024 * 1024 * 1024 # GB
$SSDSize = $SSDBytes/($TieredMirroredvDisks.Count*2) – ($WBCacheGB + (2*1024*1024*1024))
$HDDSize = $HDDBytes/($TieredMirroredvDisks.Count*2) – ($WBCacheGB + (2*1024*1024*1024))
$temp = 0
ForEach ($vDisk in $TieredMirroredvDisks) {
log “Attempting to create vDisk ‘$vDisk’..”
$Status = Get-StoragePool $PoolName | New-VirtualDisk -FriendlyName $vDisk -ResiliencySettingName Mirror –StorageTiers $SSDSpace, $HDDSpace -StorageTierSizes $SSDSize,$HDDSize -WriteCacheSize $WBCacheGB
log ($Status | Out-String)
$DriveLetter = $DriveLetters[$temp]
if ($Status.OperationalStatus -eq “OK”) {
log “vDisk ‘$vDisk’ creation succeeded” green
log “Initializing disk ‘$vDisk’..”
$InitDisk = $Status | Initialize-Disk -PartitionStyle GPT -PassThru # Initialize disk
log ($InitDisk | Out-String)
log “Creating new partition on disk ‘$vDisk’, drive letter ‘$DriveLetter’..”
$Partition = $InitDisk | New-Partition -UseMaximumSize -DriveLetter $DriveLetter # Create new partition
log ($Partition | Out-String)
log “Formatting new partition as volume ‘$vDisk’, drive letter ‘$DriveLetter’, NTFS, $BlockSize KB block size..”
$Format = $Partition | Format-Volume -FileSystem NTFS -NewFileSystemLabel $vDisk -AllocationUnitSize $BlockSizeKB -Confirm:$false # Format new partition
log ($Format | Out-String)
} else { log “vDisk ‘$vDisk’ creation failed..stopping” yellow; break }
Invoke-Expression “$env:windir\system32\Notepad.exe $logfile”

Here’s how vDisks look like after the script:


And here’s how the disks look like in Computer Management => Disk Management


For more information check this link.