Updating SQL Server AlwaysOn IP in Windows Azure

When deploying SQL Server AlwaysOn Availability Groups into Windows Azure with the listener enabled the configuration can break if the public IP address of the cloud service the SQL VMs are deployed into changes.

Cloud Service IP

The IP address of the cloud service hosting your SQL VMs can change if you shut down all the SQL Nodes at the same time using the Portal or PowerShell (and not using the -StayProvisioned flag in Stop-AzureVM). If this happens you will no longer be able to connect to the SQL Server through the listener until it is updated with the new IP of the cloud service when the VMs are restarted. This is a very common scenario with Dev/Test and proof of concepts because you may spin up the SQL servers but need to shut them down when not in use.

To make this easier from a DevOps perspective I have written a PowerShell script and published it to the TechNet Script Center that will update the listener IP address to the current IP Address of the cloud service.

Using the Script

The script does use the Windows Azure PowerShell cmdlets for automation so ensure they are setup and configured first.

Install and configure the Windows Azure PowerShell Cmdlets

Next you will need to enable CredSSP authentication since the script automates the other SQL nodes from the primary.

Enable CredSSP on your client machine for delegation before executing the script.

Example:
enable-wsmancredssp -role client -delegatecomputer “*.cloudapp.net”

Note:
This command will fail if your client machine is connected to any networks defined as “Public network” in “Network and Sharing Center.” of if PowerShell remoting has not previously been enabled with Enable-PSRemoting.

Run GPEdit.msc You must also enable delegating of fresh credentials using group policy editor on your client machine. Computer Configuration -> Administrative Templates -> System -> Credentials Delegation and then change the state of “Allow Delegating Fresh Credentials with NTLM-only server authentication” to “Enabled.” Its default state will say, “Not configured.”

Enable CredSSP on the primary SQL Server Node

enable-wsmancredssp -role client -delegatecomputer "*.cloudapp.net"

Install the WinRM certificate for the primary SQL Node

Download the script from the TechNet Script center:

Install WinRM Certificate for Windows Azure VMs

Example that installs the WinRM certificate (this allows secure remote PowerShell to the SQL Server VM)

$subName = "my subscription"
$cloudService = "MySQLAOCloudService"
$vmName = "SQLAO-01"
 
.\InstallWinRMCertAzureVM.ps1 -SubscriptionName $subName -ServiceName $cloudService -Name $vmName

Update the SQL Always On Listener IP

Download the SQL AlwaysOn IP Update script from the TechNet script center:

Update SQL Server Always On Listener IP Address in Windows Azure

Example of using the script to update a SQL AlwaysOn Availability Group.
The UpdateSQLAlwaysOnVIP.ps1 script will get the current IP address of the cloud service. Log in to the first SQL Node (SQLAO-01), update the IP cluster resource IP address and restart the SQL Nodes.

$avgroup = "SQLAG"
$nodes = "SQLAO-01", "SQLAO-02", "SQLAO-03"
.\UpdateSQLAlwaysOnVIP.ps1 -SubscriptionName $subName -ServiceName $cloudService -AvailabilityGroupName $avgroup -Nodes $nodes

SQL Always On References

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>