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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy