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.
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.
enable-wsmancredssp -role client -delegatecomputer “*.cloudapp.net”
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:
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:
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