I'm trying to Create a SQL Server 2017 Standard Edition Basic Always On Availability Group ob Azure VM...
- Am I missing something?
- Does it possible to have Windows Server 2016 no Active Directory Cluster + SQL Server 2017 Standard Basic AG + Azure Load Balancer as a Listener?
Please read the details below.
Will be apreciated for any feedback.
What do I have:
2 Azure VMs with SQL Server 2017 Standard. Basic Availability Group created with an option WSFC.
SQL1 ip xx.xx.xx.10
SQL2 ip xx.xx.xx.11
Cluster IP xx.xx.xx.12
AlwaysOn Listener/ Azule Internal Load Balancer xx.xx.xx.15
Cluster has a Cloud Witness.
I used this article to create a cluster https://www.sqlpassion.at/archive/2016/01/11/how-to-create-a-sql-server-availability-group-without-an-active-directory-domain/
Please note, thee is no AD. Just 2 VM and etc/hosts + domain suffix file used.
Then I used manuals like this to have Azure Internal Load Balancer as Alway On Listener
Automatic Failover works fine.
But the listener is a bit tricky for me...
Able to ping/ connect to the cluster and Listener only from an active node within the same network.
Trying to connect from a Secondary Replica to a primary in differnt ways.
in case it is azure i'm not counting much on ping,
But I am able to ping ip xx.xx.xx.10 <-> ip xx.xx.xx.11 and connect to SQL Server Instance any time within the network.
I and able to ping and connect to the Cluster IP/Name and Listener Ip/Name from the VM hosting the Primary Replica.
I and NOT able to ping ot connect to the Cluster IP/Name and Listener Ip/Name from the Secondary Replica or any VM within ip xx.xx.xx.0 network.
Firewalls and NSG are temporarily off. Does not help. Does not look like a firewall issue.
I read a lot and did a research of how to troublesoot the issue.
Found similar issue topics with no clear answer why.
For an instance:
Tools I have used for cnnection validation.
sqlcmd -S xx.xx.xx.15 -E
sqlcmd -S xx.xx.xx.15 -E -M
telnet xx.xxx.xx.15 1433
I tried these parameters and Multisubnet Failover
Get-ClusterResource "MyListener"| Get-ClusterParameter
Get-ClusterResource "MyListener" | Get-ClusterParameter
Get-ClusterResource "MyListener" | Set-ClusterParameter -Name overrideaddressmatch -Value 1
$CAPName = 'MyListener'
Get-ClusterResource $CAPName | Set-ClusterParameter RegisterAllProvidersIP 0
Get-ClusterResource $CAPName | Get-ClusterParameter