Share via


Leveraging Windows Server 2016 S2D with SQL Server 2016 to build hyper-converged clusters for the "Automated Datacenter"

<note: Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment >

Hello!

With the release of Windows Server 2016 and the "great/awesome/fabulous feature" storage spaces direct(S2D) that has the concept of leveraging local storage to build a "virtual-san", with different storage type from SAS/SSD/NVMe to generate different storage tiers/caching that can be both applicable on the cloud and on premises, this is game changer for SQL, both from a deployment perspective(automation) and cost vs performance !

How could one start to know a little bit more about this feature of Windows Server 2016?  Start Here.

Architecture:

1

Lab: The first steps towards building our SQL Server Always On Failover Cluster Instance with powershell!

In the cloud (Microsoft Azure):

Make sure we have our SQL Server VMs running in the same availability set to handle planned and unplanned maintenance and for the VMs not to run on the same rack/ network switch, meaning each VM will be assigned to an Update Domain (UD) split across Fault Domains (FD) :

2

 New-AzureRmAvailabilitySet -ResourceGroupName PauloCondecaInfraRG01 -Name SQLServerFCIAvailabilitySet01 -Location "westeurope"

After the Availability Set created, it is time to create our storage account with Premium Storage ..

 #PremiumStorageAccount
$newstorageaccountname = "paulo1337storage"
New-AzureRMStorageAccount -StorageAccountName $newstorageaccountname -Location westeurope -Type "Premium_LRS" 

Now, its time to install our Cluster Nodes on the top of this storage account:

3

Create Disks (Premium // SSD):

With Powershell Remoting, manage both servers from a management/jump box and run the bellow to add the machines to the data Domain, Windows Features :

 # On both servers:
 POWERCFG.EXE /S SCHEME_MIN
 Add-Computer -DomainName data.local -Credential paulo
 Restart-Computer -force 
 #Add Windows Features to our environment 
 Install-WindowsFeature –Name Failover-Clustering,File-Services –IncludeManagementTools 
 New-Cluster -Name CloudSQLServer1337 -Node CloudSQL1337N1,CloudSQL1337N2 -StaticAddress -NoStorage
 #Enable Storage Space Direct on the cluster
 Enable-ClusterS2D

At this stage, we have all the features enabled, lets add some SSD drives to the servers, note here, that the size of the disc will influence the performance of the disk itself.

A disk <128 GB of Size will translate to a maximum throughput of 100 MB/s while a disk > 512 GB will translate to a maximum throughput of 250 MB/s , more details about this P10/20/30 in /en-us/azure/storage/storage-premium-storage pstorage

After having a couple of disks created, it is time to start creating the VirtualDisks to be used by SQL Server

5 6

We can use this storage to install our SQL Server 2016 Failover Cluster Instance, independent of it being on premises or cloud!

Information about disk distribution by enclosure:

7

 

Doing a test with Diskspd, representative of what could be an OLTP Workload (75% reads + 25% writes) could easily reach a 250 MB/s throughput with a single file; with more parallel executions and different configurations, this numbers could sky-rocket but I would leave that for another time/article.

8

While this was running, I could see the network traffic between the nodes going on :) inline with the WriteActivity (500 Mbps~62MB/s): 9

And out of curiosity, in one of the workloads, I took one of the nodes offline, just to see the network activity to put the volume in 100% healthy state, and it peaked with 2GBit/s , basically 250MB/s that translate the maximum troughput of a P30 disk. 10

This post is just to basically create awareness about this new Windows Server feature and how we could leverage it to benefit SQL Server implementations, so naturally, there are details that are not covered in this post, such as workloads running that  leverage NVMe+RDMA+S2D for high throughput.

All the best!

Paulo Condeça