New Windows Storage

Sometimes one just has to add new storage to a Windows installation. And in some cases like adding physical storage there are a few simple steps like below.

Values like disk number you can get from the disk handling GUI in Windows (right-click Windows).

 

1) Initialize disk

Initialize-Disk -Number 2 -PartitionStyle GPT

2) Create partition

New-Partition -DiskNumber 2 -UseMaximumSize -DriveLetter S

3) Format volume

Format-Volume -DriveLetter S -FileSystem NTFS -NewFileSystemLabel Spare -Full:$false -UseLargeFRS

If you are adding more than 8 TB or know that the storage mostly is for a few large files then add the CmdLet parameter -AllocationUnitSize 64KB. Add the CmdLet parameter -UseLargeFRS to use large File Record Segment (FRS) when files are created, altered and deleted often.

 

If the storage unit is reused from another Windows installation, then the unit can be wiped like this

Clear-Disk -Number 2 -RemoveData

If the disk holds one or more OEM recovery partition then add the CmdLet parameter -RemoveOEM.

 

Share backup files

When I have to establish a database mirror or restore a database in another environment, I usually restore direct from the original backup file without a copy or move before. The restore is done on a UNC reference to the backup file.
This can not be done in the Management Studio GUI, but must be done by T-SQL as the GUI fails on a UNC reference to the backup file.
The UNC reference is on a share I create on the backup disk. I prefer to name the share "SQLBackup".
I have looked around for Best Practices on file shares and security. The best description I found is by Derek Melber (see Reference), but I have some issues...

It makes sense in general to control the details in access and rights i NTFS, and make a general access on the share. Derek suggests to grant access on the share to Authenticated Users in the domain and give Full Control rights. This should be done while you keep in mind that a Windows default right is that Everyone can Read.
When we are dealing with database backup files, that contain sensible data, I find this access a little too "broad" as is grant access to too many accounts.

Instead I have given specific Read access to the share, and depends on the general Read access to Everyone.
If Everyone is removed, and this I have seen in several organisations, you have to take care of the file access and rights also.

And again: Keep in mind that the backup files are to be handled as sensitive data.

Reference

Derek Melber: "Share Permissions".

NTFS Cluster Size

When formatting a partition for SQL Server data, it is recommended by storage vendors, MVPs and other that the allocation unit size should be the largest size possible. For NTFS this means 64 KB. Often the DBA is called in after the server is build, installed and configured, but I still want to confirm the allocation unit size. This can be done bu the command-line tool FSUTIL (%SystemRoot%\system32\fsutil.exe), and wrapping a generic command-line statement in PowerShell gives me the value itself to be viewed or used by the automation.
When using FSUTIL, the allocation unit size is typically called NTFS Cluster Size, and is named "Bytes Per Cluster". On newer Windows installations you need to execute FSUTIL with administrative privileges.

function Get-NtfsClusterSize {
param ( [char[]]$driveLetter = 'c' )

 $driveLetter | ForEach-Object {
  if ( Test-Path "$($_):" ) {
   $cs = New-Object PSObject
   $cs | Add-Member -MemberType NoteProperty Drive $_
   # Get 7th line of output from FSUTIL
   $cs | Add-Member -MemberType NoteProperty Size (fsutil fsinfo ntfsinfo "$($_):")[7].split()[-1]
   $cs
  }
 }
}

The function can be called with one drive letter as parameter value.
Get-NtfsClusterSize c

Or with several drive letters as parameter value.
Get-NtfsClusterSize c,h

The output values can be referred as object attribute values and on implicit drive reference.
$clusterSize = Get-NtfsClusterSize
$clusterSize.Drive
$clusterSize.Size

I find it a rather cumbersome way to get a simple value, but by using WMI it can be done much simpler.
$wql = "SELECT BlockSize,DriveLetter,Label FROM Win32_Volume WHERE FileSystem='NTFS'"
Get-WmiObject -Query $wql -ComputerName '.' | Select-Object DriveLetter,Label,BlockSize | Format-Table -AutoSize

Reference
SQLCAT: "Disk Partition Alignment Best Practices for SQL Server"
Brent Ozar: "Ten Things DBAs Need to Know About Storage"
Chad Miller: "Disk Alignment Partitioning: The Good, the Bad, the OK and the Not So Ugly"
VistaForums: "Getting the 'cluster size' of your hard disk?"
MSDN Library: "Win32_Volume Class"

Backlog
Get the NTFS Cluster Size of a Mount Point.