cmatskas

Configuring SQL Server Always On Availability Groups in Azure - Part 2

Christos Matskas -

This is part 2 of a 3 part blog where I will explain how to setup Always On using primarily PowerShell and a little bit of GUI. In this section we discuss how to install and configure the Windows Server Failover Cluster in Azure to support the Always On functionality

Prerequisites

You have already completed Part 1 of the setup as described here.

Part 2 – WSFC configuration

You can simply copy paste the code below and execute it step by step in order to implement Part 2

## PREPARE THE ENVIRONMENT FOR THE CLUSTER DEPLOYMENT ##

##########################################################
#Step: 1
#Description: Start an Azure PowerShell session or start an elevated PowerShell session and run the following to import the Azure library:
#Run On: Local Machine
##########################################################
Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"

##########################################################
#Step: 2
#Description: Set the appropriate variables
#Run On: Local Machine
##########################################################
$affinityGroupName = "MyAffinityGroup"            
$availabilityGroupName = "MyAvailabilityGroup"  # The name used by the AG
$availabilitySetName = "MYSQLHADR"              # The name of the AG set
$clusterName = "TestCluster"				    # The windows cluster name that will support the AG
$dcServerName = "ad-server"                     # The Domain Controller/Active Directory server name
$dcServiceName = "my-ad"                        # The Azure Service for the DC/AD server
$domainName= "test"                             # The Active Directory Domain name
$FQDN = "test.net"                              # The fully qualified Domain name
$location = "West EU"                           # The datacenter location
$quorumServerName = "server0"                   # The name of the Quorum Cluster node - used for backups and filesharing
$sqlServiceName = "my-service-name"             # The service name for the Cluster and SQL AG
$sql1ServerName = "server1"                     # The name of the master SQL Server node
$sql2ServerName = "server2"                     # The name of the replica SQL Server node
$storageAccountName = "my-storage-account"      # Location for storing vhds - needs to be all lowercase and < 20 chars
$storageAccountContainer = "https://" + $storageAccountName + ".blob.core.windows.net/vhds/"
$subnetName = "Cluster"                         # The name of the Subnet that will be used by the Cluster/AG
$virtualNetworkName = "WestEU-VN"              
$vmAdminUser = "ClusterAdmin"                   # The domain admin user running the Cluster
$vmAdminPassword = "AVerySecurePasswordHere"    # The domain admin password
$vmSQLServiceAccount = "SQLSvcAdmin"            # The SQL Service domain account
$workingDir = "c:\Temp"                        # Location where all scripts and temp files will be placed. Can be any location

##########################################################
#Step: 3
#Description: Get your Azure settings file #(credentials, permissions etc)
#Details: This will open a browser window to allow you to log in to your Azure account. 
#Then, it will prompt you to save the settings file locally to your machine. Make sure you use an easy-to-find location
#Run On: Local Machine
##########################################################
Get-AzurePublishSettingsFile

##########################################################
#Step: 4
#Description: Run the following command. Ensure you #replace <publishsettingsfilepath> with the #location that you used on step 3
#Run On: Local Machine
##########################################################
Import-AzurePublishSettingsFile <publishsettingsfilepath>

##########################################################
#Step: 5
#Description: Select the subscription you will work with. 
#             Use the command below to enumerate all subscriptions available to your account:
#Run On: Local Machine
##########################################################
Get-AzureSubscription | Select SubscriptionName

##########################################################
#Step: 6
#Description: Set your Powershell session to the right subscription. Change the subscription name as necessary
#Run On: Local Machine
##########################################################
$subscriptionName = "My Subscription"
Select-AzureSubscription -SubscriptionName $subscriptionName

##########################################################
#Step: 7
#Description: Configure the default storage account to use with the subscription
#Run On: Local Machine
##########################################################
Set-AzureSubscription `
	-SubscriptionName $subscriptionName `
    -CurrentStorageAccountName $storageAccountName

$sqlImageName = (Get-AzureVMImage | where {$_.Label -like "BaseClusterNode"} | sort PublishedDate -Descending)[0].ImageName	
##########################################################
#Step: 8
#Description: Create the working directory
#Run On: Local Machine
##########################################################
if (!(Test-Path -path $workingDir)) `
 { New-Item -ItemType directory -Path $workingDir  }

Create the Cluster Subnet in Azure###

At this stage, we need to create the subnet that will be used by the WSFC cluster. The cluster will ne to operate in isolation from the rest of the network.

Follow the steps below to configure the subnet in the Azure portal:

  • On the Azure Portal go to 'Networks'
  • Select the Virtual Network where you wish the create the cluster
  • Go to 'Configure'
  • Under 'Virtual network address spaces' add a new Subnet
  • Click on 'Add Subnet' and create a new one as per below:
  • Name: TestCluster
  • Starting IP: 10.16.4.0 (or any other available subnet address range)
  • CIDR: /24 (251)
    Click on the Save button at the bottom
    Wait for the operation to complete

Create The Cluster Virtual Machines###

##########################################################
#Step: 1
#Description: Return to the Powershell session #started earlier              
#Run On: Local
##########################################################

##########################################################
#Step: 2
#Description: Create the Quorum Server
#Run On: Local
##########################################################
New-AzureVMConfig `
	-Name $quorumServerName `
	-InstanceSize Medium `
	-ImageName $sqlImageName `
	-MediaLocation "$storageAccountContainer$quorumServerName.vhd" `
    -AvailabilitySetName $availabilitySetName `
	-DiskLabel "OS" |
    Add-AzureProvisioningConfig `
    	-WindowsDomain `
    	-AdminUserName $vmAdminUser `
    	-Password $vmAdminPassword `
        -DisableAutomaticUpdates `
    	-Domain $domainName `
    	-JoinDomain $FQDN `
        -DomainUserName $vmAdminUser `
        -DomainPassword $vmAdminPassword |
        Set-AzureSubnet `
            -SubnetNames $subnetName |
            New-AzureVM `
                -ServiceName $sqlServiceName `
                –AffinityGroup $affinityGroupName `
                -VNetName $virtualNetworkName
				
##########################################################
#Step: 3
#Description: Create the Master SQL Server
#Details: The command below creates a new VM and #configures an endpoint to allow connections to SQL #Server
#Run On: Local
##########################################################
New-AzureVMConfig `
	-Name $sql1ServerName `
	-InstanceSize Large `
	-ImageName $sqlImageName `
	-MediaLocation "$storageAccountContainer$sql1ServerName.vhd" `
    -AvailabilitySetName $availabilitySetName `
	-HostCaching "ReadOnly" `
	-DiskLabel "OS" |
    Add-AzureProvisioningConfig `
    	-WindowsDomain `
    	-AdminUserName $vmAdminUser `
    	-Password $vmAdminPassword `
        -DisableAutomaticUpdates `
    	-Domain $domainName `
    	-JoinDomain $FQDN `
  	  -DomainUserName $vmAdminUser `
        -DomainPassword $vmAdminPassword |
        Set-AzureSubnet `
            -SubnetNames $subnetName |
            Add-AzureEndpoint `
            	-Name "SQL" `
                -Protocol "tcp" `
            	-PublicPort 1 `
                -LocalPort 1433 |
                New-AzureVM `
                	-ServiceName $sqlServiceName
					
##########################################################
#Step: 4
#Description: Create the Replica SQL Server
#Note: run this command for all other replicas and #ensure you change the appropriate variables
#Run On: Local
##########################################################
New-AzureVMConfig `
	-Name $sql2ServerName `
	-InstanceSize Large `
	-ImageName $sqlImageName `
	-MediaLocation "$storageAccountContainer$sql2ServerName.vhd" `
    -AvailabilitySetName $availabilitySetName `
	-HostCaching "ReadOnly" `
	-DiskLabel "OS" |
    Add-AzureProvisioningConfig `
    	-WindowsDomain `
    	-AdminUserName $vmAdminUser `
    	-Password $vmAdminPassword `
        -DisableAutomaticUpdates `
    	-Domain $domainName `
    	-JoinDomain $FQDN `
        -DomainUserName $vmAdminUser `
        -DomainPassword $vmAdminPassword |
        Set-AzureSubnet `
            -SubnetNames $subnetName |
            Add-AzureEndpoint `
            	-Name "SQL" `
                -Protocol "tcp" `
                -PublicPort 2 `
                -LocalPort 1433 |
                New-AzureVM `
                    -ServiceName $sqlServiceName

##########################################################
#Step: 5
#Description: Download the Quorum RDP file for #quick access
#Run On: Local
##########################################################
Get-AzureRemoteDesktopFile `
	-ServiceName $sqlServiceName `
	-Name $quorumServerName `
    -LocalPath "$workingDir$quorumServerName.rdp"
	
##########################################################
#Step: 6
#Description: Download the SQL Master RDP file for #quick access
#Run On: Local
##########################################################
Get-AzureRemoteDesktopFile `
	-ServiceName $sqlServiceName `
	-Name $sql1ServerName `
    -LocalPath "$workingDir$sql1ServerName.rdp"

##########################################################
#Step: 7
#Description: Download the SQL Replica RDP file for #quick access
#Run On: Local
##########################################################
Get-AzureRemoteDesktopFile `
	-ServiceName $sqlServiceName `
	-Name $sql2ServerName `
	-LocalPath "$workingDir$sql2ServerName.rdp"

Configure the windows server failover cluster (WSFC)###

Things are starting to get interesting here. Make sure you don't screw up your cluster setup or you may have to delete you VMs and repeate the previous step (sad bunny)

##########################################################
#Step: 1
#Description: Log in to the Quorum server using the <MachineName>\ClusterAdmin account
#             Note: replace the <machineName> with the name of the Quorum server
#Run On: Quorum
##########################################################

##########################################################
#Step: 2
#Description: Start an elevated PowerShell session
#Details: Install the Failover Cluster feature and add the domain admin 
#         to the local admins group
#Run On: Quorum
##########################################################
Import-Module ServerManager
Add-WindowsFeature Failover-Clustering
 
net localgroup administrators "test\ClusterAdmin" /Add
logoff

##########################################################
#Step: 3
#Description: Log in to the SQL Master server using the <MachineName>\ClusterAdmin account
#             Note: replace the <machineName> with the name of the master server
#Run On: Master
##########################################################
Import-Module ServerManager
Add-WindowsFeature Failover-Clustering
 
net localgroup administrators "test\ClusterAdmin" /Add

##########################################################
#Step: 4
#Description: Grant the domain admin sysadmin rights to SQL Server
#             Allow NT Authority to change the SQL Server Availability Group
#Run On: Master
##########################################################

# Start an elevated SQL Management Studio and connect with SQL Login (sa + AVerySecurePasswordHere)
# Right click on Object View and start up a SQL Powershell

Set-ExecutionPolicy -Execution RemoteSigned -Force
Import-Module -Name "sqlps" –DisableNameChecking

Invoke-SqlCmd -Query "CREATE LOGIN [test\ClusterAdmin] FROM WINDOWS" -ServerInstance "."
Invoke-SqlCmd -Query "EXEC sp_addsrvrolemember 'test\ClusterAdmin', 'sysadmin'" -ServerInstance "."
Invoke-SqlCmd -Query "CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS" -ServerInstance "."
Invoke-SqlCmd -Query "GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."
Invoke-SqlCmd -Query "GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."
Invoke-SqlCmd -Query "GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."

# Close the SQL Powershell

##########################################################
#Step: 5
#Description: Configure the firewall to allow SQL connections over TCP
#Run On: Master
##########################################################
netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP
logoff

##########################################################
#Step: 6
#Description: Log in to the Replica SQL server using the <MachineName>\ClusterAdmin account
#             Note: replace the <machineName> with the name of the replica server
#Run On: All Replicas
##########################################################
Import-Module ServerManager
Add-WindowsFeature Failover-Clustering
 
net localgroup administrators "test\ClusterAdmin" /Add

##########################################################
#Step: 7
#Description: Grant the domain admin sysadmin rights to SQL Server
#             Allow NT Authority to change the SQL Server Availability Group
#Run On: All Replicas
##########################################################

# Start an elevated SQL Management Studio and connect with SQL Login (sa + AVerySecurePasswordHere)
# Right click on Object View and start up a SQL Powershell

Set-ExecutionPolicy -Execution RemoteSigned -Force
Import-Module -Name "sqlps" –DisableNameChecking

Invoke-SqlCmd -Query "CREATE LOGIN [test\ClusterAdmin] FROM WINDOWS" -ServerInstance "."
Invoke-SqlCmd -Query "EXEC sp_addsrvrolemember 'test\ClusterAdmin', 'sysadmin'" -ServerInstance "."
Invoke-SqlCmd -Query "CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS" -ServerInstance "."
Invoke-SqlCmd -Query "GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."
Invoke-SqlCmd -Query "GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."
Invoke-SqlCmd -Query "GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM] AS SA" -ServerInstance "."

##########################################################
#Step: 8
#Description: Configure the firewall to allow SQL connections over TCP
#Run On: All Replicas
##########################################################
netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP
logoff

##########################################################
#Step: 9
#Description: Log in to the SQL Master server using the test\ClusterAdmin account
#Run On: Master
##########################################################

##########################################################
#Step: 10
#Description: Start an elevated PowerShell session, set the session variables and import the SQL module
#Run On: Master
##########################################################

$sql1ServerName = "server1"
$sql2ServerName = "server2"
$quorumServerName = "server0"
$acct1 = "TEST\SQLSvcAdmin"
$password = "AVerySecurePasswordHere"
$clusterName = "TestCluster"
$timeout = New-Object System.TimeSpan -ArgumentList 0, 0, 30
 
Set-ExecutionPolicy RemoteSigned -Force
Import-Module "sqlps" –DisableNameChecking

##########################################################
#Step: 11
#Description: Change the SQL Service account on the master server to use the Domain SQL Service account.
#              This will also restart the SQL Service on the server
#Run On: Master
##########################################################
$wmi1 = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $sql1ServerName
$wmi1.services | where {$_.Type -eq 'SqlServer'} | foreach {$_.SetServiceAccount($acct1,$password)}
$svc1 = Get-Service -ComputerName $sql1ServerName -Name 'MSSQLSERVER'
$svc1.Stop()
$svc1.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
$svc1.Start();
$svc1.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)

##########################################################
#Step: 12
#Description: Change the SQL Service account on the replica server to use the Domain SQL Service account.
#              This will also restart the SQL Service on the server
#Run On: Master for each replica
##########################################################
$wmi2 = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $sql2ServerName
$wmi2.services | where {$_.Type -eq 'SqlServer'} | foreach {$_.SetServiceAccount($acct1,$password)}
$svc2 = Get-Service -ComputerName $sql2ServerName -Name 'MSSQLSERVER'
$svc2.Stop()
$svc2.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Stopped,$timeout)
$svc2.Start();
$svc2.WaitForStatus([System.ServiceProcess.ServiceControllerStatus]::Running,$timeout)

##########################################################
#Step: 13
#Description: Run the following script to configure the cluster
#             Note: Ensure that you change the <pathToScript> to point to the right location
#Run On: Master
##########################################################
cd <pathToScript>
Add-WindowsFeature 'Failover-Clustering', 'RSAT-Clustering-PowerShell', 'RSAT-Clustering-CmdInterface', 'RSAT-Clustering-Mgmt'
Set-ExecutionPolicy Unrestricted -Force
.\CreateAzureFailoverCluster.ps1 -ClusterName "$clusterName" -ClusterNode "$sql1ServerName","$sql2ServerName","$quorumServerName" #additional nodes here

##########################################################
#Step: 14
#Description: Run the following patch to fix the bug with the Cluster Management console
#Details: more information regarding the bug can be found here: http://support.microsoft.com/kb/2803748
#Run On: Master (and any server where you plan to use the Management Console)
##########################################################
Windows8-RT-KB2803748-x64.msu

This completes step 2. You should now have a fully functional WSFC cluster. Congratulations!

Happy coding...