Creating a lab environment on Azure IaaS to test SQL Server 2016 Always On Failover Cluster Instance with Storage Spaces Direct

Published by Marco Obinu on 11 July 201811 July 2018

I needed a demo environment for my PASS Global Italian Virtual Group session on SQL Server HA on Azure IaaS, to explain some topics regarding SQL Server 2016 Always On FCI on top of Windows Server 2016 S2D technology in an hyperconverged configuration.

To create simple environment with 2-nodes cluster, you have to:

Prepare a basic infrastructure (AD, vnet and so on)
Deploy Azure Gallery template for SQL 2016 FCI with S2D OR deploy a SQL 2016 FCI with S2D via PowerShell

Regarding point 2, the Azure Gallery approach is the easiest way, but it didn’t work for me and I was too in a hurry to debug the DSC resource that gave me troubles. So I ended up with a longer approach that permits you to better understand what’s happening under the hood.

Prepare the basic infrastructure (AD, vnet and so on)

The basic infrastructure is composed by:

A virtual network
A new Active Directory forest with related domain controller
All the networking components we need to reach the DC from internet

You can provision these components by using the active-directory-new-domain template.
Simply click on the button, do a login on your subscription and fill the form with required parameters:Check required flags, and start deploying it!

After about 30 minutes, you’ll obtain the basic infrastructure you need to provision the SQL FCI.

Before going over, we need to create a new subnet dedicated to SQL Server, and a domain user which will be used as service account by the SQL Server template.

Edit adVnet virtual network generated by the AD template, and create a new subnet with following parameters:

Then, you need to connect to the domain controller via the public IP assigned to the load balancer.
Create a new domain user as showed in the following picture:

Now you’re ready to go on with SQL Server provisioning!

Provision a SQL Server 2016 Always On FCI on S2D

Proceed with ARM Template from Azure Gallery – the easiest way

Note: as already said before, this method didn’t work for me; the deployment crashed with an exception on the DSC resource which provisiong FCI. I was in a hurry, so I avoided troubleshooting on this way, and jumped on the PowerShell side.
If you want to deploy it in a safe way, or you’re just curious about PoSH, jump to the next section!

We’re now ready to deploy the sql-server-2016-fci-existing-vnet-and-ad template.
As did before, click on and fill the form with requested parameters:

And now you’re ready to go! Click on deploy, wait and then you can gain connectivity to the cluster from outside the vnet by using the DC as a jump box.

Proceed with my ARM Template + PowerShell – the Die Hard way

First of all, you need to deploy a couple of SQL Server VMs by using my optimized SQL Server template.
You need to deploy the template twice, clicking on and filling the following parameters (leaving others with default values):

Parameter name	Value for VM 1	Value for VM 2
Vm Name	SQLFCI01	SQLFCI02
Domain name	Demo.local	Demo.local
Admin username	demoAdmin	demoAdmin
Admin Password	Your strong password	Your strong password
Vnet name	adVnet	adVnet
Vnet Resource Group	FCI-S2D-Demo-RG	FCI-S2D-Demo-RG
SQL Server Version	SQL Server 2016 SP1 Standard on Windows Server 2016	SQL Server 2016 SP1 Standard on Windows Server 2016
Storage SKU	Premium_LRS	Premium_LRS
# of data disks	2	2
Data disks size	64	64

Once deployed, you should proceed following these steps on both VMs:

The template include a custom script extension which format data disks attached to the VMs, but to proceed with S2D you should work with empty drives. Open Disk Management console and delete F: and G: volumes:
From Add/Remove Programs, uninstall the current SQL Server Instance; it’s enough to remove all components from MSSQLSERVER instance (DB Engine and related components, SSAS, SSRS); you can leave shared components alone. After removal, reboot both VMs.

Now, from a client with AzureRM PowerShell module installed, execute following script to create an internal load balancer:

$vnetRgName = 'FCI-S2D-Demo-RG'               # Vnet resource group name
$VNetName = 'adVnet'                    # Vnet name
$SubnetName = 'sqlSubnet'               # Subnet name
$ILBName = 'SQLFCI-ILB'                 # ILB name
$Location = 'West Europe'               # Azure location
$VMNames = 'sqlfci01','sqlfci02'        # Virtual machine names

$ILBIP = '10.0.1.235'                      # AG listener IP address
[int]$ListenerPort = '1433'                 # AG listener port
[int]$ProbePort = '59990'                   # AG listener Probe port

$LBProbeNamePrefix = "$ILBName-PROBE-01"        # The Load balancer Probe Object Name              
$LBConfigRuleNamePrefix = "$ILBName-RULE-01"    # The Load Balancer Rule Object Name

$FrontEndConfigurationPrefix = "$ILBName-FECONFIG-01"  # Object name for the front-end configuration 
$BackEndConfigurationPrefix = "$ILBName-BECONFIG-00"   # Object name for the back-end configuration

# Connect to subscription
$Creds = Get-Credential
Login-AzureRmAccount -Credential $Creds

# Check for existing resource group or create it
$resourceGroup = Get-AzureRmResourceGroup -Name $sqlRgName -ErrorAction SilentlyContinue
if(!$resourceGroup)
{
    Write-Host "Resource group '$sqlRgName' does not exist. To create a new resource group, please enter a location.";
    if(!$Location) {
        $Location = Read-Host "resourceGroupLocation";
    }
    Write-Host "Creating resource group '$sqlRgName' in location '$Location'";
    New-AzureRmResourceGroup -Name $sqlRgName -Location $Location
}
else{
    Write-Host "Using existing resource group '$sqlRgName'";
}

# Load balancer creation with initial configuration
$VNet = Get-AzureRmVirtualNetwork -Name $VNetName -ResourceGroupName $vnetRgName 

$Subnet = Get-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $VNet `
                                                -Name $SubnetName 

# Frontend configuration
$FEConfig = New-AzureRMLoadBalancerFrontendIpConfig -Name $FrontEndConfigurationPrefix `
                                                    -PrivateIpAddress $ILBIP `
                                                    -Subnet $Subnet

# Backend configuration
$BEConfig = New-AzureRMLoadBalancerBackendAddressPoolConfig -Name $BackEndConfigurationPrefix

# Probe
$SQLHealthProbe = New-AzureRmLoadBalancerProbeConfig -Name $LBProbeNamePrefix `
                                                        -Protocol tcp `
                                                        -Port $ProbePort `
                                                        -IntervalInSeconds 15 `
                                                        -ProbeCount 2

# Rule
$ILBRule = New-AzureRmLoadBalancerRuleConfig -Name $LBConfigRuleNamePrefix `
                                                -FrontendIpConfiguration $FEConfig `
                                                -BackendAddressPool $BEConfig `
                                                -Probe $SQLHealthProbe `
                                                -Protocol tcp `
                                                -FrontendPort $ListenerPort `
                                                -BackendPort $ListenerPort `
                                                -LoadDistribution Default `
                                                -EnableFloatingIP 

# Creating ILB
$ILB= New-AzureRmLoadBalancer -Location $Location `
                                -Name $ILBName `
                                -ResourceGroupName $sqlRgName `
                                -FrontendIpConfiguration $FEConfig `
                                -BackendAddressPool $BEConfig `
                                -LoadBalancingRule $ILBRule `
                                -Probe $SQLHealthProbe 

# Backend pool
$bepool = Get-AzureRmLoadBalancerBackendAddressPoolConfig -Name $BackEndConfigurationPrefix `
                                                            -LoadBalancer $ILB 

# Assign VM NICs to backend pool
foreach($VMName in $VMNames)
{
    $VM = Get-AzureRmVM -ResourceGroupName $sqlRgName `
                        -Name $VMName 
    $NICName = ($VM.NetworkProfile.NetworkInterfaces[0].Id.Split('/') | Select-Object -last 1)
    $NIC = Get-AzureRmNetworkInterface -name $NICName `
                                        -ResourceGroupName $sqlRgName
    $NIC.IpConfigurations[0].LoadBalancerBackendAddressPools = $BEPool
    Set-AzureRmNetworkInterface -NetworkInterface $NIC
    start-AzureRmVM -ResourceGroupName $sqlRgName -Name $VM.Name 
}

After that, you need to create a storage account that will be used as Cloud Witness by WSFC. Again, from the client with AzureRM module, execute following script:

$ResourceGroupName = "FCI-S2D-Demo-RG"
$SaName = "sqlfcisawitness"
$Location = "West Europe"

# Connect to subscription
#$Creds = Get-Credential
#Login-AzureRmAccount -Credential $Creds

#Create a storage account for cluster witness
New-AzureRmStorageAccount -ResourceGroupName $ResourceGroupName `
                             -Name $SaName `
                             -SkuName "Standard_LRS" `
                             -Kind "Storage" `
                             -Location $Location

$sa = Get-AzureRmStorageAccount -Name $SaName -ResourceGroupName $ResourceGroupName

Write-Host "Storage account name: $($sa.StorageAccountName)"
$key1 = (Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroupName -AccountName $SaName).Value[0]
Write-Host "Storage account key1: $key1"

The above script will return the encryption key for the storage account; cut and paste it in the script you can find in the next box.

Now, you’re ready to move inside one of the two VMs, let’s say SQLFCI01; gain access via RDP (use the DC as a jump box), open a PS ISE administrative console and launch following script – remember to replace storage account encryption key!!:

$ClusterName = 'SQLFCI-CLS'
$TempIpAddr = '10.0.1.190'
$ipAddr = '169.254.1.1'
$witnessSaName = 'sqlfcisawitness'
### To be updated!!! ###
$witnessSaKey = 'Insert your key here!!'

$sqlNodes = 'SQLFCI01','SQLFCI02'

foreach($sqlNode in $sqlNodes) {
    Install-WindowsFeature Failover-Clustering `
        -IncludeAllSubFeature `
        -IncludeManagementTools `
        -ComputerName $sqlNode
}

Import-Module FailoverClusters

#==========================================================================
#        Configure Windows Firewall
#==========================================================================

$scriptBlock = {
    # SQL
    New-NetFirewallRule -Name "SQL Server" -DisplayName "SQL Server" -Protocol "tcp" -Direction Inbound -Action Allow -LocalPort 1433 -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server Browser Service" -DisplayName "SQL Server Browser Service" -Protocol "udp" -Direction Inbound -Action Allow -LocalPort 1434 -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server DAC" -DisplayName "SQL Server DAC" -Protocol "tcp" -Direction Inbound -Action Allow -LocalPort 1434 -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server Service Broker" -DisplayName "SQL Server Service Broker" -Protocol "tcp" -Direction Inbound -Action Allow -LocalPort 4022 -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server HA Endpoint" -displayName "SQL Server HA Endpoint" -Protocol "tcp" -Direction Inbound -Action Allow -LocalPort 5022 -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server Replication Sync (FTP)" -DisplayName "SQL Server Replication Sync (FTP)" -Protocol "tcp" -Direction Inbound -Action Allow -LocalPort 21 -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server Transact-SQL Debugger, Integration Services, MSDTC (RPC)" -DisplayName "SQL Server Transact-SQL Debugger, Integration Services, MSDTC (RPC)" -Protocol "tcp" -Direction Inbound -Action Allow -LocalPort 135 -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server service (sqlservr.exe) used for Remote Debugging, Dynamic ports, MSDTC" -DisplayName "SQL Server service (sqlservr.exe) used for Remote Debugging, Dynamic ports, MSDTC" -Program "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -Direction Inbound -Action Allow -Profile any -Enabled True
    New-NetFirewallRule -Name "SQL Server Integration Services (MsDtsSrvr.exe)" -DisplayName "SQL Server Integration Services (MsDtsSrvr.exe)" -Program "C:\Program Files\Microsoft SQL Server\130\DTS\Binn\MsDtsSrvr.exe" -Direction Inbound -Action Allow -Profile any -Enabled True

    # Cluster
    Get-NetFirewallRule -DisplayGroup "Failover Cluster Manager" | Enable-NetFirewallRule
    Get-NetFirewallRule -DisplayGroup "Failover Clusters" | Enable-NetFirewallRule
    Get-NetFirewallRule -DisplayGroup "SMB Witness" | Enable-NetFirewallRule

    # WMI
    Get-NetFirewallRule -DisplayGroup "Windows Management Instrumentation (WMI)" | Enable-NetFirewallRule
  
    # RDP
    Get-NetFirewallRule -DisplayGroup "Remote Desktop" | Enable-NetFirewallRule

    # Remote administration
    Get-NetFirewallRule -DisplayGroup "Windows remote management"  | Enable-NetFirewallRule

    # File and printer sharing
    Get-NetFirewallRule -DisplayGroup "File and Printer Sharing" | Enable-NetFirewallRule

    # DTC
    Get-NetFirewallRule -DisplayGroup "Distributed Transaction Coordinator" | Enable-NetFirewallRule
}
$sqlNodes | ForEach-Object { Invoke-Command -ScriptBlock $scriptBlock -ComputerName $_ }



#==========================================================================
#        Create WSFC
#==========================================================================

# Create WSFC with a temp IP from the subnet address range
Test-Cluster -Node $sqlNodes –Include "Inventory", "Network", "System Configuration"
New-Cluster -Name $clusterName -Node $sqlNodes -StaticAddress $TempIpAddr -NoStorage

# Replace IP with a Link Local one
$clusteripaddr = Get-ClusterGroup | get-clusterresource | where-object { $_.resourcetype.name -eq "ip address"}          

$clusteripaddr | set-clusterparameter -multiple @{
    "address" = "$ipaddr"
    "subnetmask" = "255.255.0.0"
    "enabledhcp" = 0
    "overrideaddressmatch" = 1
} -erroraction stop

Get-ClusterGroup | get-clusterresource | Stop-clusterResource
Get-ClusterGroup | get-clusterresource | Start-clusterResource 

# Change cluster quorum configuration to storage account
Set-ClusterQuorum -CloudWitness -AccountName $witnessSaName -AccessKey $witnessSaKey

# Enable storage spaces direct and create a new CSV
Enable-ClusterS2D
New-Volume -StoragePoolFriendlyName S2D* `
    -FriendlyName VDisk01 `
    -FileSystem CSVFS_REFS `
    -Size 120GB `
    -AllocationUnitSize 64KB `
    -ResiliencySettingName Mirror

Now, always from SQLFCI01 VM, open folder C:\SQLServerFull and launch SQL Server setup, choosing a Failover Cluster Instance setup:

Go on with a basic setup, we won’t apply any special setting since it’s a lab environment; feel free to adapt the setup to your business needs ?

Select DB Engine basic components, and keep default path as Instance Root Directory:

Deploy a default instance and provide a network name for SQL FCI:

Leave default settings for Cluster Resource Group and Cluster Disk Selection and jump to Cluster Network Configuration tab.

Now it’s time to configure IP for SQL FCI. Check IPv4, uncheck DHCP and assign the same IP you used as frontend pool IP for the load balancer provisioned before with PoSH (10.0.1.235):

Specify SQL service account previously created on AD:

In Data Directories tab, leave default settings for Cluster Shared Volume paths:

Complete setup come back to PowerShell ISE; from there, launch following script to assign correct configuration with probe port to SQL FCI IP resource:

#To be done after SQL Server FCI setup
$ILBIP = "10.0.1.235" 
$ProbePort = 59990

Import-Module FailoverClusters

$ClusterNetwork = Get-ClusterNetwork
$SqlClusterGroup = Get-ClusterGroup | Where-Object Name -Like '*SQL SERVER*'
$sqlClusterIpAddr = $SqlClusterGroup| get-clusterresource | where-object { $_.resourcetype.name -eq "ip address"}  

$sqlClusterIpAddr | Set-ClusterParameter -Multiple @{
    "Address"="$ILBIP";
    "ProbePort"=$ProbePort;
    "SubnetMask"="255.255.255.255";
    "Network"="$($ClusterNetwork.Name)";
    "EnableDhcp"=0
}

$SqlClusterGroup | Stop-ClusterGroup
$SqlClusterGroup | Start-ClusterGroup

Now it’s time to move to node 2!

Gain access via RDP to SQLFCI02 and, from C:\SQLServerFull, execute setup.exe.
Choose the option to add a node to an existing FCI:

Go through the wizard ‘till the tab where you have to specify passwords for service accounts:

Go on with the wizard and wait for setup to complete.

Now you have you SQL 2016 FCI with S2D lab environment to play with! ?
if you need any further information on intermediate steps, you can also have a look to the official walkthrough on Microsoft Docs.

Enjoy yourselves!