April 25, 2018 at 5:03 am
Hello,
Please help.
I'm trying to Create a SQL Server 2017 Standard Edition Basic Always On Availability Group ob Azure VM...
Questions:
- 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
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sqlclassic/virtual-machines-windows-classic-ps-sql-int-listener?toc=%2Fazure%2Fvirtual-machines%2Fwindows%2Fclassic%2Ftoc.json
https://blogs.msdn.microsoft.com/igorpag/2016/01/25/configure-an-ilb-listener-for-sql-server-alwayson-availability-groups-in-azure-arm/#comment-2655
https://www.linkedin.com/pulse/set-up-internal-load-balancer-sql-server-availability-zheng-xu/
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:
https://social.msdn.microsoft.com/Forums/en-US/cd79c334-8106-4983-aeb0-3531675b485c/connect-to-sql-availability-group-listener-from-primary-server-but-not-from-secondary?forum=sqldisasterrecovery
https://serverfault.com/questions/583911/failover-cluster-client-access-point-only-responds-to-pings-on-the-owner-node
https://dba.stackexchange.com/questions/177374/sql-server-ag-listener-issues-in-an-aws-vpc/177389
https://support.microsoft.com/en-us/help/2792139/time-out-error-and-you-cannot-connect-to-a-sql-server-2012-alwayson-av
Tools I have used for cnnection validation.
SSMS
ping
psping.exe xx.xxx.xx.11:59999
psping.exe xx.xx.xx.15:1433
sqlcmd -S xx.xx.xx.15 -E
sqlcmd -S xx.xx.xx.15 -E -M
telnet xx.xxx.xx.15 1433
and these
https://blogs.msdn.microsoft.com/alwaysonpro/2017/02/22/troubleshooting-internal-load-balancer-listener-connectivity-in-azure/
https://support.microsoft.com/en-us/help/2792139/time-out-error-and-you-cannot-connect-to-a-sql-server-2012-alwayson-av
https://blogs.msdn.microsoft.com/alwaysonpro/2016/02/01/troubleshooting-availability-group-listener-in-azure/
https://docs.microsoft.com/en-us/azure/load-balancer/load-balancer-troubleshoot
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
Please advise...
Regards,
Mike
April 27, 2018 at 7:12 am
It woks!
In my case in was an extra NIC which I removed to make the proper NIC to be the only one.
Before that the command below was not able to accept -StaticAddress xx.xxx.xx.12
new-cluster -name Cluster –Node VM01,VM02 -StaticAddress xx.xxx.xx.12 -NoStorage –AdministrativeAccessPoint DNS
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply