Common issues faced while configuring AlwaysOn listener on Azure VM cluster

We have MSDN article [Tutorial: Listener Configuration for AlwaysOn Availability Groups (https://msdn.microsoft.com/en-us/library/dn425027.aspx) which provides detailed steps to configure AlwaysOn listener on Azure VM cluster.
This article provides steps that can be followed to resolve commonly faced issues.

Important consideration: All SQL Server virtual machines on Azure should be in the SAME cloud service for AlwaysOn configuration with listener. This can be verified from Azure portal. For example, in below screen shot Node1 and Node 2 are in same cloud service, as required. This requirement is applicable when all virtual machines are in same Azure region.

Issue-1

Executing the below command might not return/display any progress or results at the end of execution, but the endpoints are not created. We might also see the below warnings in some cases.

PS C:\>Get-AzurePublishSettingsFile
PS C:\>Import-AzurePublishSettingsFile -PublishSettingsFile C:\temp\Newcredentials.publishsettings

PS C:\># Define variables
PS C:\>$AGNodes = "SQLVM2","SQLVM3" # all availability group nodes should be included, separated by commas
PS C:\>$ServiceName = "testservices" # the name of the cloud service that contains the availability group nodes
PS C:\>$EndpointName = "HDEAL25_EP" # name of the endpoint
PS C:\>$EndpointPort = "14333" # public port to use for the endpoint
PS C:\>
PS C:\># Configure a load balanced endpoint for each node in $AGNodes, with direct server return enabled
PS C:\>ForEach ($node in $AGNodes)
PS C:\>{
PS C:\>Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name $EndpointName -Protocol "TCP" -PublicPort $EndpointPort -LocalPort $EndpointPort -LBSetName "$EndpointName-LB" -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
PS C:\>}

Error /Warning:

WARNING: No deployment found in service: 'testservices'.
WARNING: No deployment found in service: 'testservices'.

Possible causes:

•   This can occur if the windows account which is used for downloading the “publishsettings” file might have multiple subscriptions

•   The command cannot find the Virtual Machines in the default subscription for the windows account and the configuration fails.

How to find if the account has multiple subscriptions?

Get-AzureSubscription | Select SubscriptionName,IsDefault # Get list of subscriptions for the account
Get-AzureSubscription -current | Select SubscriptionName # Find the currently active account for this connection
Get-AzureSubscription -default | Select SubscriptionName # Find the Default subscription for this connection
Get-AzureVM #Get the list of Virtual machines & its services

Resolution

•   Select the required subscription and verify the Virtual Machines under that subscription. Finally continue with the required deployment/configurations.

Select-AzureSubscription "Subscription_Name_Here"

•   We can also change the default subscription using the below command.

Select-AzureSubscription "Subscription_Name_Here" –Default

 

Issue-2

•   In one of the scenario we followed the steps (8^th point of 1^st step) mentioned in the article and executed the below commands to create the load-balanced VM endpoints.

PS C:\> Get-AzurePublishSettingsFile
PS C:\> Import-AzurePublishSettingsFile -PublishSettingsFile C:\temp\credentials.publishsettings
VERBOSE: Setting: azure-Prod_admin as the default and current subscription. To view other subscriptions use Get-AzureSubscription

1) # Define variables
2) $AGNodes = "Node1","Node2" # all availability group nodes should be included, separated by commas
3) $ServiceName = "SQLProd.cloudapp.net" # the name of the cloud service that contains the availability group nodes
4) $EndpointName = "MySQLEndpoint" # name of the endpoint
5) $EndpointPort = "59636" # public port to use for the endpoint
6)
7) # Configure a load balanced endpoint for each node in $AGNodes, with direct server return enabled
8) ForEach ($node in $AGNodes)
9) {
10) Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name $EndpointName -Protocol "TCP" -PublicPort $EndpointPort -LocalPort $EndpointPort -LBSetName "$EndpointName-LB" -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
11) }

Get-AzureVM : BadRequest: The hosted service name is invalid.
At line:3 char:5
+ Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Get-AzureVM], CloudException
+ FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.ServiceManagement.IaaS.GetAzureVMCommand
Get-AzureVM : BadRequest: The hosted service name is invalid.
At line:3 char:5
+ Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Get-AzureVM], CloudException
+ FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.ServiceManagement.IaaS.GetAzureVMCommand
PS C:\>

Note:- Line number added for explanation & more clarity

•   It also provides the line number which shows the point of failure, the variable $ServiceNamein the error output.

Error:

Get-AzureVM : BadRequest: The hosted service name is invalid.
At line:3 char:5
+ Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Get-AzureVM], CloudException
+ FullyQualifiedErrorId : Microsoft.WindowsAzure.Commands.ServiceManagement.IaaS.GetAzureVMCommand

 

•   We noticed that the value provided for cloud service was in the format of FQDN (Fully qualified domain name) of the service name/DNS name, where as it was expecting only the name of the service which is “SQLProd” in our case.

•   The command executed successfully once we modified the value provided for variable $ServiceName

Resolution - Amended command which executed successfully:

PS C:\> Get-AzurePublishSettingsFile

Technorati Tags: AlwaysOn

PS C:\> Import-AzurePublishSettingsFile -PublishSettingsFile C:\temp\credentials.publishsettings
VERBOSE: Setting: azure-Prod_admin as the default and current subscription. To view other subscriptions use Get-AzureSubscription
# Define variables
$AGNodes = "Node1","Node2" # all availability group nodes should be included, separated by commas
$ServiceName = "SQLProd" # the name of the cloud service that contains the availability group nodes
$EndpointName = "MySQLEndpoint" # name of the endpoint
$EndpointPort = "59636" # public port to use for the endpoint

# Configure a load balanced endpoint for each node in $AGNodes, with direct server return enabled
ForEach ($node in $AGNodes)
{
Get-AzureVM -ServiceName $ServiceName -Name $node | Add-AzureEndpoint -Name $EndpointName -Protocol "TCP" -PublicPort $EndpointPort -LocalPort $EndpointPort -LBSetName "$EndpointName-LB" -ProbePort 59999 -ProbeProtocol "TCP" -DirectServerReturn $true | Update-AzureVM
}

 

Summary: To conclude we should specify ONLY the name of Cloud service to which the node belongs and not the FQDN.

 

Issue-3

•   We usually tend to make mistake at point #8 for "STEP-4 Create the availability group listener" and we get the below error:

PS C:\> # Define variables
PS C:\> $ClusterNetworkName = "WinAOCluster" # the cluster network name
PS C:\> $IPResourceName = "AGListenerProd_10.0.0.55" # the IP Address resource name
PS C:\> $CloudServiceIP = "135.100.100.100" # IP address of your cloud service
PS C:\>
PS C:\> Import-Module FailoverClusters
PS C:\> Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";SubnetMask="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}

Error:
Set-ClusterParameter : Unable to save property changes for 'AGListenerProd_10.0.0.55'.
The cluster network was not found
At line:1 char:39
+ Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address" ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [Set-ClusterParameter], ClusterCmdletException
+ FullyQualifiedErrorId : Set-ClusterParameter,Microsoft.FailoverClusters.PowerShell.SetClusterParameterCommand
PS C:\>
PS C:\>

Possible Cause:

•   The error says it failed for the value supplied for "$IPResourceName" but the mistake is the previous line "$ClusterNetworkName"
•   The description for this parameter is "the cluster network name". This is not the name of the Windows Cluster.
•   We need to provide the name of the network used in the windows cluster. Ex “Cluster Network 1”

How to find if the “Network Name” for this configuration

1.  Connect to the Failover Cluster Manager and expand the NETWORKS section, we will see the network used for the current cluster subnet.

 

2.  Use the PowerShell command:

PS C:\ > Get-ClusterNetwork

Name State
----------------- -----
Cluster Network 1 Up

Resolution - Amended command which executed successfully:

PS C:\> # Define variables
PS C:\> $ClusterNetworkName = "Cluster Network 1"# the cluster network name
PS C:\> $IPResourceName = "AGListenerProd_10.0.0.55" # the IP Address resource name
PS C:\> $CloudServiceIP = "23.100.95.15"# IP address of your cloud service
PS C:\> Import-Module FailoverClusters
PS C:\> Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$CloudServiceIP";"ProbePort"="59999";SubnetMask="255.255.255.255";"Network"="$ClusterNetworkName";"OverrideAddressMatch"=1;"EnableDhcp"=0}
WARNING: The properties were stored, but not all changes will take effect until AGListenerProd_10.0.0.55 is taken offline and then online again.
PS C:\>

 

 

Written by:
Raghavendra Srinivasan – Support Engineer, SQL Server Support

Reviewed by:

Vijay Rodrigues – Escalation Engineer, SQL Server Support