Windows Storage Spaces

To look into Windows Storage Spaces on if and how to use it on a SQL Server database server I have added 5 SCSI disks to a virtual server running Windows Server 2016 on vmware Workstation.
Please notice that I am working with the local Windows Storage Spaces and not the networked Storage Spaces Direct (S2D). The physical disk on the host is a Samsung 970 Evo NVMe SSD also hosting the host OS Windows 10 Pro.

On these 5 disks I create a storage pool where I create 2 Virtual Disks of different size and with different allocation unit size. The allocation units are of different size with a SQL Server installation in mind.
Also the logical sector size in the storage pool is set to 4KB where the default is 512B. This is to see how easy it is to change this and maybe later experiment further with the impact of a larger logical sector size.

The storage pool is created with the CmdLet New-StoragePool:

New-StoragePool -FriendlyName Pool_00 -StorageSubSystemFriendlyName (Get-StorageSubSystem).FriendlyName -PhysicalDisks (Get-PhysicalDisk -CanPool $true) -LogicalSectorSizeDefault 4KB

The CmdLet return

FriendlyName OperationalStatus HealthStatus IsPrimordial IsReadOnly
------------ ----------------- ------------ ------------ ----------
Pool_00      OK                Healthy      False        False   

Then the first Virtual Disk is created with the CmdLet New-VirtualDisk:

New-VirtualDisk -StoragePoolFriendlyName Pool_00 -FriendlyName ProgramDisk -ResiliencySettingName Mirror -NumberOfDataCopies 3 -Size (42GB) -ProvisioningType Thin

This CmdLet return

FriendlyName ResiliencySettingName OperationalStatus HealthStatus IsManualAttach  Size
------------ --------------------- ----------------- ------------ --------------  ----
ProgramDisk  Mirror                OK                Healthy      False          42 GB

To create a partition on the Virtual Disk and format the volume with ReFS a small PowerShell script does it:

Get-VirtualDisk -FriendlyName ProgramDisk | Get-Disk |
Initialize-Disk -PartitionStyle GPT -PassThru |
New-Partition -DriveLetter F -UseMaximumSize |
Format-Volume -NewFileSystemLabel Program -FileSystem ReFS

This small script return

DriveLetter FileSystemLabel FileSystem DriveType HealthStatus OperationalStatus SizeRemaining     Size
----------- --------------- ---------- --------- ------------ ----------------- -------------     ----
F           Program         ReFS       Fixed     Healthy      OK                     40.93 GB 41.81 GB

When the disk is created a dialog about formatting the drive might be shown like this


To check the volume I use the command-line tool fsutil:

.\fsutil.exe fsinfo refsinfo F:

The tool return

REFS Volume Serial Number :       0xee841e1a841de63d
REFS Version   :                  3.1
Number Sectors :                  0x0000000000a74000
Total Clusters :                  0x0000000000a74000
Free Clusters  :                  0x0000000000a3bb69
Total Reserved :                  0x0000000000018510
Bytes Per Sector  :               4096
Bytes Per Physical Sector :       4096
Bytes Per Cluster :               4096
Checksum Type:                    CHECKSUM_TYPE_NONE

The second Virtual Disk is created with

New-VirtualDisk -StoragePoolFriendlyName Pool_00 -FriendlyName DataDisk -ResiliencySettingName Mirror -NumberOfDataCopies 3 -Size (123GB) -ProvisioningType Thin

A return similar to last time

FriendlyName ResiliencySettingName OperationalStatus HealthStatus IsManualAttach   Size
------------ --------------------- ----------------- ------------ --------------   ----
DataDisk     Mirror                OK                Healthy      False          123 GB

The volume is created and formatted with the small script:

Get-VirtualDisk -FriendlyName DataDisk | Get-Disk |
Initialize-Disk -PartitionStyle GPT -PassThru |
New-Partition -DriveLetter H -UseMaximumSize |
Format-Volume -NewFileSystemLabel Data -FileSystem ReFS -AllocationUnitSize 64KB

And the return is

DriveLetter FileSystemLabel FileSystem DriveType HealthStatus OperationalStatus SizeRemaining      Size
----------- --------------- ---------- --------- ------------ ----------------- -------------      ----
H           Data            ReFS       Fixed     Healthy      OK                    121.77 GB 122.81 GB

Check the volume

.\fsutil.exe fsinfo refsinfo H:

The tool return

REFS Volume Serial Number :       0x2620bb5220bb27a7
REFS Version   :                  3.1
Number Sectors :                  0x0000000001eb4000
Total Clusters :                  0x00000000001eb400
Free Clusters  :                  0x00000000001e710d
Total Reserved :                  0x00000000000027c8
Bytes Per Sector  :               4096
Bytes Per Physical Sector :       4096
Bytes Per Cluster :               65536
Checksum Type:                    CHECKSUM_TYPE_NONE

Discussion

This is just to look in the creation of a storage pool with different Virtual Disks in Windows Storage Spaces. Looking at performance and optimal configuration for SQL Server are other and later writings.

Please notice that Windows can't boot on a storage pool. This gives that you have to configure a more traditional storage for booting and running Windows. Here there is no difference between Windows Server and Windows desktop OS.

A quick thought though is that I think that I would avoid tiered storage. Just as in a SAN configuration for SQL Server.

vmxnet3 network adapter

What

When creating a virtual machine in VMware Workstation the default virtual network adapter is „e1000“. This adapter works fine in most cases but if you are working with heavy network load like iSCSI this adapter is not the right.
There are several virtual network adapters by VMware as described in the document „VMware Virtual Networking Concepts“ where the latest vmxnet paravirtualised network adapter „vmxnet3“ usually does the trick.

How

Stop the virtual machine. You can edit the vmx-file while the virtual machine is running, but the running configuration with the e1000 adaptor will be written to the vmx-file next time the virtual machine is shut down or restarted.

Edit the vmx-file which normally is in the folder to each virtual machine from
ethernet0.virtualDev = "e1000"
to
ethernet0.virtualDev = "vmxnet3"
If you have defined multiple network adapters you should consider to change the type on each adapter.

And then start the virtual machine.

Please notice that when changing the network adapter type on a virtual Windows machine then it will be handled as a new network adapter not a replace. This will among other thing have the effect that your network configurations will be default on the new network adapter.
This is why I would recommend setting the virtual network adapter type as one of the first things when configuring a virtual Windows machine.
I can't tell how other operating systems like freeBSD or Linux will act as I have not tried.

Why

As mentioned using the vmxnet3 paranirtualised network adapter will give you extra network performance. There might be an increased memory pressure on the virtual machine. Personally I haven't experienced severe penalty using the vmxnet3 adapter.

History

2019-03-06  Text moved from AzureAdmin blog.

2017-03-12  Blog entry created.

Rename TFS Collection databasefiles

During TFS (2017) restore on a new server the wizard might rename datafiles.

If the TFS Collection database is defined with multiple datafiles (mdf & ndf) where the files are name with a postfix like "*_<number>", e.g. "TFS_SQLAdmin_Collection_2.ndf".

Datafiles are renamed with a replacement of number last in name with a GUID. This put you in a unpleasant situation with unnecessary long file names.

To fix this you have to detatch the database, rename the files and attach the database with the new file names.