Creating a lab environment on Azure IaaS to test SQL Server 2016 Basic Availability Group

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 Basic Availability Group.

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

Prepare a basic infrastructure (AD, vnet and so on)
Deploy a couple of SQL VMs via my optimized ARM template and configure Always On BAG with PowerShell

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 Basic Availability Groups cluster

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	SQLBAG01	SQLBAG02
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	BAG-Demo-RG	BAG-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	1	1
Data disks size	128	128

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

$sqlRgName = 'BAG-Demo-RG'              # SQL  RG 
$vnetRgName = 'GIVG-Demo'               # Vnet resource group name
$VNetName = 'adVnet'                    # Vnet name
$SubnetName = 'sqlSubnet'               # Subnet name
$ILBName = 'SQLBAG-ILB'                 # ILB name
$Location = 'West Europe'               # Azure location
$VMNames = 'sqlbag01','sqlbag02'        # Virtual machine names

$ILBIP = '10.0.1.230'                      # 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

# 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 = "BAG-Demo-RG"
$SaName = "sqlbagsawitness"
$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 SQLBAG01; 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 = 'SQLBAGLIVE-CLS'
$TempIpAddr = '10.0.1.160'
$ipAddr = '169.254.1.1'
$witnessSaName = 'sqlbaglivesawitness'
### To be updated!!! ###
$witnessSaKey = 'Insert your key here!!!'

$sqlNodes = 'SQLBAG01','SQLBAG02'

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 temporary IP
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

Now, it time to go on with BAG and related listener configuration!
From SQLBAG01, execute following PoSH:

$primaryReplicaName = 'SQLBAG01'
$secondaryReplicaName = 'SQLBAG02'
$domainFqdn = 'DEMO.LOCAL'

$sqlNodes = @()
$sqlNodes += [ordered]@{serverName = $primaryReplicaName; instanceName = 'DEFAULT';}
$sqlNodes += [ordered]@{serverName = $secondaryReplicaName; instanceName = 'DEFAULT';}

$sqlPort = 1433
$listenerPort = 1433
$endpointPort = 5022

$backupShare = "SQLBackup"
$backupFolder = "F:\SQLBackup"

$sqlSvcAcctn = "DEMO\sqlsvc"
$sqlSvcAcctnCreds = Get-Credential -UserName $sqlSvcAcctn -Message "Insert password for $sqlSvcAcctn"


$dbName = 'testdb'
$agConfig += [ordered]@{
    avgName = 'SQLBAG'; 
    listenerName = 'SQLBAG-LIS'; 
    ipAddress = '10.0.1.230';
    probePort = '59990';
}

# Load or install DBATools module on all servers
$scriptBlock = {
    try {
        Get-PackageProvider -Name "Nuget" -ErrorAction Stop
    }
    catch {
        Find-PackageProvider -Name "Nuget" | Install-PackageProvider -Force -Confirm:$false
    }
    try {
        Import-Module dbatools -ErrorAction Stop
    }
    catch {
        Install-Module dbatools -Confirm:$false -Force
    }
    try {
        Import-Module sqlserver -DisableNameChecking -ErrorAction Stop
    }
    catch {
        Install-Module sqlserver -Confirm:$false -Force -AllowClobber
    }
}

foreach($sqlNode in $sqlNodes) {
    Invoke-Command -ComputerName $sqlNode.serverName -ScriptBlock $scriptBlock
}

#Enable TCP protocol and HADR feature and replace service account on all instances
ForEach ($sqlNode in $sqlNodes) {

    if($sqlNode.instanceName -eq 'DEFAULT') {
        Enable-DbaAgHadr -SqlInstance $sqlNode.serverName -Confirm:$false
        Get-DbaSqlService $sqlNode.serverName -Type Engine,Agent | Update-DbaSqlServiceAccount -ServiceCredential $sqlSvcAcctnCreds
    } else {
        Enable-DbaAgHadr -SqlInstance "$($sqlNode.serverName)\$($sqlNode.InstanceName)" -Confirm:$false
        Get-DbaSqlService $sqlNode.serverName -Type Engine,Agent -Instance $sqlNode.instanceName | Update-DbaSqlServiceAccount -ServiceCredential $sqlSvcAcctnCreds
    }
}

#Create backup share
if(!(Test-Path $backupFolder)) {
    New-Item $backupFolder -ItemType directory    
}

net share $backupShare=$backupFolder "/grant:$sqlSvcAcctn,FULL"
icacls.exe "$backupFolder" /grant:r ("$sqlSvcAcctn" + ":(OI)(CI)F")


#Create database on every instance
Invoke-SqlCmd -Query "CREATE DATABASE $dbName" -ServerInstance "$($sqlNodes[0].serverName),$sqlPort" -Verbose
Backup-SqlDatabase -Database $dbName -BackupFile "$backupFolder\$dbName.bak" -ServerInstance "$($sqlNodes[0].serverName),$sqlPort" -Verbose
Backup-SqlDatabase -Database $dbName -BackupFile "$backupFolder\$dbName.log" -ServerInstance "$($sqlNodes[0].serverName),$sqlPort" -BackupAction Log -Verbose
Restore-SqlDatabase -Database $dbName -BackupFile "\\$($sqlNodes[0].serverName)\$backupShare\$dbName.bak" -ServerInstance "$($sqlNodes[1].serverName),$sqlPort" -NoRecovery -Verbose
Restore-SqlDatabase -Database $dbName -BackupFile "\\$($sqlNodes[0].serverName)\$backupShare\$dbName.log" -ServerInstance "$($sqlNodes[1].serverName),$sqlPort" -RestoreAction Log -NoRecovery -Verbose

#Mirroring endpoint creation

ForEach ($sqlNode in $sqlNodes) {
    
    $scriptBlock = {
        param($serverName, $instanceName, $sqlPort, $endpointPort)

        Import-Module SqlServer

        $endpoint = New-SqlHadrEndpoint "SqlHaEndpoint" `
                        -Port $endpointPort `
                        -Path "SQLSERVER:\SQL\$($serverName)\$($instanceName)"

        Set-SqlHADREndpoint `
            -InputObject $endpoint `
            -State "Started" -Verbose
    }
    
    Invoke-Command -ComputerName $sqlNode.serverName -ArgumentList @($sqlNode.serverName, $sqlNode.instanceName, $sqlPort, $endpointPort) -ScriptBlock $scriptBlock

    Invoke-SqlCmd -Query "CREATE LOGIN [$($sqlSvcAcctn)] FROM WINDOWS" -ServerInstance "$($sqlNode.serverName),$sqlPort" -Verbose
    Invoke-SqlCmd -Query "GRANT CONNECT ON ENDPOINT::[SqlHaEndpoint] TO [$($sqlSvcAcctn)]" -ServerInstance "$($sqlNode.serverName),$sqlPort" -Verbose
    Invoke-SqlCmd -Query "GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM];" -ServerInstance "$($sqlNode.serverName),$sqlPort" -Verbose
    Invoke-SqlCmd -Query "GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM];" -ServerInstance "$($sqlNode.serverName),$sqlPort" -Verbose
    Invoke-SqlCmd -Query "GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM];" -ServerInstance "$($sqlNode.serverName),$sqlPort" -Verbose
}

#Availability Replicas creation    
$AvailabilityReplicas = @()

ForEach ($sqlNode in $sqlNodes) {

    if($sqlNode.instanceName -eq "DEFAULT") {
        $Name = $sqlNode.serverName
    } else {
        $name = "$($sqlNode.serverName)\$($sqlNode.instanceName)"
    }

    $AvailabilityReplicas +=  New-SqlAvailabilityReplica `
                                -Name $Name `
                                -EndpointURL "TCP://$($sqlNode.serverName).$domainFqdn`:$endpointPort" `
                                -AvailabilityMode "SynchronousCommit" `
                                -FailoverMode "Automatic" `
                                -AsTemplate `
                                -Version 13 `
                                -ConnectionModeInPrimaryRole AllowAllConnections `
                                -ConnectionModeInSecondaryRole AllowNoConnections
}

#AVG Creation
New-SqlAvailabilityGroup `
    -Name $agConfig.avgName `
    -Path "SQLSERVER:\SQL\$($sqlNodes[0].serverName)\$($sqlNodes[0].instanceName)" `
    -AvailabilityReplica $AvailabilityReplicas `
    -Database $dbName `
    -BasicAvailabilityGroup

$scriptBlock = {
    param ($serverName, $instanceName, $avgName, $dbName)

    Import-Module SqlServer

    Join-SqlAvailabilityGroup `
        -Path "SQLSERVER:\SQL\$serverName\$instanceName" `
        -Name $avgName

    Add-SqlAvailabilityDatabase `
        -Path "SQLSERVER:\SQL\$serverName\$instanceName\AvailabilityGroups\$avgName" `
        -Database $dbName

}

Invoke-Command -ComputerName $sqlNodes[1].serverName `
                -ScriptBlock $scriptBlock `
                -ArgumentList @($sqlNodes[1].serverName, $sqlNodes[1].instanceName, $agConfig.avgName, $dbName)

                
#Client Access Point creation

$agClusterGroup = Get-ClusterGroup -Name $agConfig.avgName
#1. Add a network name named as the AG Listener
$cap = Add-ClusterResource -Name $agConfig.listenerName -ResourceType "Network Name" -Group $agClusterGroup
#2. Set parameters
$cap | Set-ClusterParameter -Multiple @{
    "DnsName" = $($agConfig.listenerName)
    "HostRecordTTL" = 300
    "RegisterAllProvidersIP" = 0
} -ErrorAction Stop
#3. Create two IP Addresses, assigning them the Azure ILB ip created on each vnet
$ClusterNetwork = Get-ClusterNetwork
$capIp = Add-ClusterResource -Name "$($agConfig.avgName) listener IP" -ResourceType "IP Address" -Group $agClusterGroup
$capIp | Set-ClusterParameter -Multiple @{
        "Address" = $($agConfig.ipAddress)
        "SubnetMask" = "255.255.255.255"
        "Network" = "$($ClusterNetwork.Name)"
        "EnableDHCP" = 0
        "ProbePort" = $($agConfig.probePort)
    } -ErrorAction Stop

#4. Add dependencies on IPs to Network Name
Set-ClusterResourceDependency -InputObject $cap -Dependency "[$capIp]"
#5. Add AG resource dependency on CAP network name
$agResource = Get-ClusterResource -Name $agConfig.avgName
$agResource | Stop-ClusterResource
Add-ClusterResourceDependency -InputObject $agResource -Resource $cap
$agResource | Start-ClusterResource
$cap|Start-ClusterResource

# Set AVG listener port
Set-SqlAvailabilityGroupListener `
    -Path "SQLSERVER:\SQL\$($sqlNodes[0].serverName)\$($sqlNodes[0].instanceName)\AvailabilityGroups\$($agConfig.avgName)\AvailabilityGroupListeners\$($agConfig.listenerName)" `
    -Port $listenerPort

Now you have you SQL 2016 Always On Basic Availability Groups lab environment to play with! ?
Enjoy yourselves!