Setup SQL Disks

by

Yesterday, I wrote about how I setup SQL Servers Virtual Machines using PowerShell and PowerCLI.  Now that I’ve got the SQL Server imaged, it’s time to format the disks and prep the Page File. Let’s dig in.

#requires -RunAsAdministrator

#region Online & Enable RAW disks
Get-Disk | Where-Object { $_.OperationalStatus -eq "Offline" } | Set-Disk -IsOffline:$false
Get-Disk | Where-Object { $_.PartitionStyle -eq "RAW" } | ForEach-Object { Initialize-Disk -Number $_.Number -PartitionStyle GPT }
#endregion

#region Build Disk List
$DiskInfo  = @()
$DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]1; DriveLetter = "D"; Label = "Page File" } )
$DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]2; DriveLetter = "E"; Label = "Programs" } )
$DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]3; DriveLetter = "F"; Label = "TempDB" } )
$DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]4; DriveLetter = "G"; Label = "Data" } )
$DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]5; DriveLetter = "H"; Label = "Logs" } )
$DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]6; DriveLetter = "I"; Label = "Backups" } )

$FullFormat  = $false # indicates a "quick" format
$UseLargeFRS = $true  # see https://technet.microsoft.com/en-us/library/dn466522(v=ws.11).aspx
ForEach ( $Disk in $DiskInfo )
{
    # Set Disk to Read-Write
    if ( Get-Disk -Number $Disk.DiskNumber | Where-Object { $_.IsReadOnly } )
    {
        Write-Host "Setting $( $Disk.DiskNumber ) to Read-Write"
        Get-Disk -Number $Disk.DiskNumber | Set-Disk -IsReadOnly:$false
    }
    
    # Create Partition and then Format it
    Write-Host "Creating partition on Disk $( $Disk.DiskNumber ) with Letter $( $Disk.DriveLetter ):"
    New-Partition -DiskNumber $Disk.DiskNumber -UseMaximumSize -DriveLetter $Disk.DriveLetter | Format-Volume -FileSystem NTFS -AllocationUnitSize 64KB -Force -Confirm:$false -Full:$FullFormat -NewFileSystemLabel $Disk.Label
#    Write-Host "Formatting $( $Disk.DriveLetter ): [$( $Disk.Label )]"
#    Format-Volume -DriveLetter $Disk.DriveLetter -FileSystem NTFS -AllocationUnitSize 64KB -Force -Confirm:$false -Full:$FullFormat -NewFileSystemLabel $Disk.Label
    
    # Disable Indexing via WMI
    $WmiVolume = Get-WmiObject -Query "SELECT * FROM Win32_Volume WHERE DriveLetter = '$( $Disk.DriveLetter ):'"
    $WmiVolume.IndexingEnabled = $false
    $WmiVolume.Put()
}
#endregion

#region Set Page Files
$CompSys = Get-WmiObject -Class Win32_ComputerSystem -EnableAllPrivileges
# is the system set to use system managed page files
if ( $CompSys.AutomaticManagedPagefile )
{
    # if so, turn it off
    Write-Host "Disabling automatic page file management"
    $CompSys.AutomaticManagedPagefile = $false
    $CompSys.Put()
}
# Set the size to RAM + 257MB (per Microsoft Recommendations) and move it to the D:\ Drive
# as a safety-net I also keep 257MB on the C:\ Drive.
$MemoryInMB = [math]::Round( ( Get-WmiObject -Class Win32_ComputerSystem ).TotalPhysicalMemory / 1GB, 0) * 1024
$PageFileSettings = @()
$PageFileSettings += "C:\PageFile.sys", 257, 257 -join " "
$PageFileSettings += "D:\PageFile.sys", ( $MemoryInMB + 257 ), ( $MemoryInMB + 257 ) -join " "
ForEach ( $PageFileSetting in $PageFileSettings )
{
    $PageDetails = $PageFileSetting.Split(" ")
    Write-Host "Setting Page File at $( $PageDetails[0] ) with min size: $( $PageDetails[1] ) MB and max size $( $PageDetails[2] ) MB"
}
Set-ItemProperty -Path "HKLM:\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management\" -Name "PagingFiles" -Type multistring -Value $PageFileSettings
#endregion

Pretty simple:

  1. Initialize the Disks
  2. Check for Read-Only Flag & Format the Disks (NTFS with 64KB Blocks)
  3. Disable Automatic Indexing via WMI
  4. Statically set the Page File for the C: and D: Drives.

The final step is to install SQL Server. That’s pretty tricky and I need to invoke a few tricks (and scripts) to get it done the way I want.  That’ll have to wait for another post.

6 thoughts on “Setup SQL Disks”

      • Using this “ New-Partition -DiskNumber $Disk.DiskNumber -UseMaximumSize -DriveLetter $Disk.DriveLetter | Format-Volume -FileSystem NTFS -AllocationUnitSize 64KB -Force -Confirm:$false -Full:$FullFormat -NewFileSystemLabel $Disk.Label “ will format all drives to 64KB what if I want certain drives like D, E, and F to be 64KB but use the script .

        Reply

        • You could probably add another element to the disk array to control the disk allocation unit and then call that later.
          Like convert:
          $DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]1; DriveLetter = “D”; Label = “Page File” } )
          to
          $DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]1; DriveLetter = “D”; Label = “Page File”; AllocationUnitSize = 64KB } )

          Then in your above mention, you could swap out
          -AllocationUnitSize 64KB
          for
          -AllocationUnitSize $Disk.AllocationUnitSize

          That should work, but I haven’t tested it personally.

          Edit: the allocation size should be “KB” in the PowerShell custom object, not “K”

          Reply

          • Hi Kevin , After swapping
            $DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]1; DriveLetter = “D”; Label = “Page File” } )
            to
            $DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]1; DriveLetter = “D”; Label = “Page File”; AllocationUnitSize = 64K } )

            I get an error message of this

            64K : The term ’64K’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of
            the name, or if a path was included, verify that the path is correct and try again.
            At line:3 char:153
            + … veLetter = “E”; Label = “SCCM_SQL_MDF”; AllocationUnitSize = 64K } )
            + ~~~
            + CategoryInfo : ObjectNotFound: (64K:String) [], CommandNotFoundException
            + FullyQualifiedErrorId : CommandNotFoundException

          • Changed

            $DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]1; DriveLetter = “D”; Label = “Page File”; AllocationUnitSize = 64K } )

            to

            $DiskInfo += New-Object -TypeName PSObject -Property ( [ordered]@{ DiskNumber = [int]1; DriveLetter = “D”; Label = “Page File”; AllocationUnitSize = 64KB } )

            Error Messages I got .
            Creating partition on Disk 2 with Letter E:
            Format-Volume : Cannot process argument transformation on parameter ‘AllocationUnitSize’. Cannot convert value “64KB” to type
            “System.UInt32”. Error: “Input string was not in a correct format.”
            At line:12 char:148
            + … -FileSystem NTFS -AllocationUnitSize $Disk.AllocationUnitSize -Force …
            + ~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo : InvalidData: (:) [Format-Volume], ParameterBindingArgumentTransformationException
            + FullyQualifiedErrorId : ParameterArgumentTransformationError,Format-Volume

            Creating partition on Disk 3 with Letter F:
            Format-Volume : Cannot process argument transformation on parameter ‘AllocationUnitSize’. Cannot convert value “64KB” to type
            “System.UInt32”. Error: “Input string was not in a correct format.”
            At line:12 char:148
            + … -FileSystem NTFS -AllocationUnitSize $Disk.AllocationUnitSize -Force …
            + ~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo : InvalidData: (:) [Format-Volume], ParameterBindingArgumentTransformationException
            + FullyQualifiedErrorId : ParameterArgumentTransformationError,Format-Volume

            Creating partition on Disk 4 with Letter G:
            Format-Volume : The specified cluster size is invalid
            Activity ID: {c7461cb6-ff03-4ba6-b74a-428bc6156fbb}
            At line:12 char:97
            + … iveLetter | Format-Volume -FileSystem NTFS -AllocationUnitSize $Disk. …
            + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo : NotSpecified: (StorageWMI:ROOT/Microsoft/…age/MSFT_Volume) [Format-Volume], CimException
            + FullyQualifiedErrorId : StorageWMI 43000,Format-Volume

            Creating partition on Disk 5 with Letter H:
            Format-Volume : Cannot process argument transformation on parameter ‘AllocationUnitSize’. Cannot convert value “64KB” to type
            “System.UInt32”. Error: “Input string was not in a correct format.”
            At line:12 char:148
            + … -FileSystem NTFS -AllocationUnitSize $Disk.AllocationUnitSize -Force …
            + ~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo : InvalidData: (:) [Format-Volume], ParameterBindingArgumentTransformationException
            + FullyQualifiedErrorId : ParameterArgumentTransformationError,Format-Volume

            Creating partition on Disk 6 with Letter I:
            Format-Volume : The specified cluster size is invalid
            Activity ID: {7ff5c845-0a3d-4870-88c9-9e8838f1fe79}
            At line:12 char:97
            + … iveLetter | Format-Volume -FileSystem NTFS -AllocationUnitSize $Disk. …
            + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo : NotSpecified: (StorageWMI:ROOT/Microsoft/…age/MSFT_Volume) [Format-Volume], CimException
            + FullyQualifiedErrorId : StorageWMI 43000,Format-Volume

            Creating partition on Disk 7 with Letter J:
            Format-Volume : The specified cluster size is invalid
            Activity ID: {c28b93ce-ba5c-48e7-bff4-46b12d32f742}
            At line:12 char:97
            + … iveLetter | Format-Volume -FileSystem NTFS -AllocationUnitSize $Disk. …
            + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            + CategoryInfo : NotSpecified: (StorageWMI:ROOT/Microsoft/…age/MSFT_Volume) [Format-Volume], CimException
            + FullyQualifiedErrorId : StorageWMI 43000,Format-Volume

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.