search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.22k stars 21.37k forks source link

Issue with SQL AG listener creation in a workgroup is not reflected in documentation #69173

Closed EitanBlumin closed 1 year ago

EitanBlumin commented 3 years ago

The following page is supposed to explain how to configure a domain-independent SQL AG in a workgroup:

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-clusterless-workgroup-configure#configure-a-load-balancer

In the step for configuring the load balancer, it references this page:

https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-load-balancer-portal-configure

However, that page is not properly written to take into account the creation of the load balancer in a workgroup. Specifically, step 5.2: Add the client access point:

  1. Add the client access point. The client access point is the network name that applications use to connect to the databases in an availability group. Create the client access point in Failover Cluster Manager.

    a. Expand the cluster name, and then click Roles.

    b. In the Roles pane, right-click the availability group name, and then select Add Resource > Client Access Point.

    c. In the Name box, create a name for this new listener. The name for the new listener is the network name that applications use to connect to databases in the SQL Server availability group.

    d. To finish creating the listener, click Next twice, and then click Finish. Do not bring the listener or resource online at this point.

Following those instructions leads one to encounter the error " Unable to determine if the computer '<CAPName>' exists in Domain '<WorkgroupName>'. The server is not operational .", which happens because of an issue with default values configured in the FCM GUI, as explained in the following technical article:

https://techcommunity.microsoft.com/t5/core-infrastructure-and-security/sql-server-workgroup-cluster-fcm-errors/ba-p/371387

That article explains that the FCM GUI tries to use default settings for the new listener IP which would not work in a WORKGROUP environment. It suggests a Powershell script as a workaround, to explicitly set the values as needed.

I suggest that something like the following Powershell code would be offered in the documentation, in order to easily resolve the expected issue when creating the listener in a workgroup:

$ListenerName           = "<MyListenerName>"
$IPResourceName         = "<MyIPResourceName>" # the IP Address resource name
$ListenerILBIP          = "<n.n.n.n>" # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
[int]$ListenerProbePort = <nnnnn>
$ListenerSubnet         = "255.255.255.255"
$ClusterAGRole          = "<MyClusterRoleName>"
$ClusterNetworkName     = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)

Import-Module FailoverClusters | Out-Null

Add-ClusterResource -Name $IPResourceName -ResourceType "IP Address" -Group $ClusterAGRole
Get-ClusterResource -Name $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="$ListenerSubnet";"Network"="$ClusterNetworkName";"EnableDhcp"=0}

Add-ClusterResource -Name $ListenerName -Group $ClusterAGRole -ResourceType "Network Name"
Get-ClusterResource -Name $ListenerName | Set-ClusterParameter -Multiple @{"DnsName" = "$ListenerName";"RegisterAllProvidersIP" = 1} 
Set-ClusterResourceDependency -Resource $ListenerName -Dependency "[$IPResourceName]" 
Start-ClusterResource -Name $ListenerName -Verbose 

Stop-ClusterResource -Name $ClusterAGRole -Verbose 
Set-ClusterResourceDependency -Resource $ClusterAGRole -Dependency "[$ListenerName]" 

Start-ClusterResource -Name $ClusterAGRole -Verbose

Thanks, Eitan

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

psmanhas commented 3 years ago

@EitanBlumin Thanks for the feedback! I have assigned the issue to content author to check and update the document as appropriate.We will update you accordingly.

psmanhas commented 3 years ago

@MashaMSFT Can you please check and add your comments on this doc update request as applicable

MashaMSFT commented 1 year ago

Hi @EitanBlumin , thanks so much for bringing this to our attention! I've processed a change to add the blog and PowerShell to the article and these changes should be live next week. I'll go ahead and close out this Git Issue but please feel free to comment should you need anything further.

Thanks again for reaching out and I hope you have a wonderful rest of your day! Masha from the SQL Docs team

please-close