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

Posted in SQL, AlwaysOn, High Availability, WSFC, Failover Cluster, Windows Server

One of the coolest features in Azure is the fact that you can create Always On Availability Groups and configure a Listeners in order to provide high availability and redundancy to your back-end SQL Server solution. If you want to know more about AlwaysOn and what it can do for you, you can go here.

Up until recently, Always On was a bit “hacky” on Azure as we had to use workarounds, especially for the Listener part, which is one of the most important feature as far as applications are concerned. However, the latest patches and updates to Windows Server 2012 and SQL Server 2012 allow us to fully implement Always On with a Listener without having to come up with hacks.

This is part 1 of a 3 part blog where I will explain how to setup Always On using solely PowerShell (OK - 95% PowerShell, 5% GUI and a pinch of SQL here and there). The configuration is split in 3 distinct operations:

Prerequisites

  • You have a Domain Controller in place
  • You are setting up a brand new set of VMs for the cluster
  • You have a database somewhere to use for testing

Note – I assume at this stage that this is a brand new SQL Server cluster you are trying to install. If you already have a set of VMs running SQL server on Azure, you will need to follow a different procedure to this one.

Part 1 – Active Directory

Before we install the WSFC and SQL Server, we need to ensure that we have the right domain accounts with the right permissions to run the necessary services and generate the appropriate objects. You will have noticed that I said domain. That’s correct! In order to set up AlwaysOn you need a domain.

If you just signed up for a subscription, you will have noticed that it came with an Active Directory Service. Alternatively, you can roll out your own Domain Controller and this is what I will use for this tutorial. A DC has already been deployed and AD is fully functional at this stage.
Disclaimer - I assume that you can use the AD Service provided by Azure, but I haven’t tried it yet.

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

##########################################################
#Step: 1
#Description: Log in to the Domain Controller using the domain Admin account
#Run On: Domain Controller / AD server

##########################################################
#Step: 2
#Description: Start an elevated Powershell session and set the variables
#Run On: Domain Controller / AD server
##########################################################
$vmAdminUser = "ClusterAdmin"
$vmAdminPassword = "AVerySecurePasswordHere"
$vmSQLServiceAccount = "SQLSvcAdmin"
$domainName= "test"
$domainNameExt = "net"
$fqdn= $domainName + "." + $domainNameExt
Import-Module ActiveDirectory

##########################################################
#Step: 3
#Description: Create the Domain Admin account for the cluster
# NOTE: If the passwords used by the accounts below are different, 
# you need to run the following command for each different password:
#      $securePwd1 = ConvertTo-SecureString "Password" -AsPlainText –Force 
#Run On: Domain Controller / AD server
##########################################################
$securePwd = ConvertTo-SecureString $vmAdminPassword -AsPlainText –Force
New-ADUser `
    -Name $vmAdminUser `
    -AccountPassword  $securePwd `
    -PasswordNeverExpires $true `
    -ChangePasswordAtLogon $false `
    -Enabled $true

##########################################################
#Step: 4
#Description: Create the SQL Service domain account for the cluster
#            NOTE: Remember to change the password if different
#Run On: Domain Controller / AD server
##########################################################
#securePwd = ConvertTo-SecureString $myOtherPassword -AsPlainText –Force
New-ADUser `
	-Name $vmSQLServiceAccount `
    -AccountPassword  $securePwd `
    -PasswordNeverExpires $true `
    -ChangePasswordAtLogon $false `
    -Enabled $true

##########################################################
#Step: 5
#Description: Give the domain admin permission to create objects. 
#             This is necessary as otherwise the Cluster will fail to operate
#Run On: Domain Controller / AD server
##########################################################
Cd ad:
$sid = new-object System.Security.Principal.SecurityIdentifier (Get-ADUser "$vmAdminUser").SID
$guid = new-object Guid bf967a86-0de6-11d0-a285-00aa003049e2
$ace1 = new-object System.DirectoryServices.ActiveDirectoryAccessRule $sid,"CreateChild","Allow",$guid,"All"
$corp = Get-ADObject -Identity "DC=test,DC=net"
$acl = Get-Acl $corp
$acl.AddAccessRule($ace1)
Set-Acl -Path "DC=test,DC=net" -AclObject $acl

##########################################################
#Step: 6
#Description: Add the ClusterAdmin to the Domain Admin security group 
#             This is necessary as otherwise the Cluster will fail to operate
#Run On: Domain Controller / AD server
##########################################################
Add-ADGroupMember -Identity 'Domain Admins' -Members $vmAdminUser

Step 7

The domain SQL Service account needs to have permission to create SPNs. SPNs are necessary to allow the SQL Service accounts to authenticate against Kerberos. if the SQL Service account cannot create SPNs for each SQL Server, then the servers wont be able to communicate with each other and authentication will fall back to NTLM. More information on SPNs can be found here

Follow the steps below to add the right permission to the SQL Service Account

  • Log in to the Domain Controller / AD server
  • Start => Run => Adsiedit.msc
  • In the ADSI Edit snap-in, expand Domain 'Your Domain', expand DC=RootDomainName, expand CN=Users, right-click CN= SQLSvcAdmin, and then click Properties.
  • In the CN= AccountName Properties dialog box, click the Security tab.
    On the Security tab, click Advanced.
  • In the Advanced Security Settings dialog box, select one (any) of "SELF"'s row
  • Click Edit, Open Permission Entry dialog box.
  • Make sure Pricipal is "SELF", Type is "Allow" and "Applied to" is "This Object Only", in Properties section, select the properties below:
    • Read servicePrincipalName
    • Write servicePrincipalName
  • Click ‘Apply’ then ‘OK’ and close the management console

You have now successfully completed Part 1 !

Happy coding...

  • Share this post on