Windows Server 2016 no Active Directory Cluster + SQL Server 2017 Standard Basic AG + Azure Load Balancer as a Listener

• 83851263

  SSC Enthusiast

  Points: 148

  April 25, 2018 at 5:03 am

  #344851

  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

• mykhailo.karpenko

  SSC Rookie

  Points: 43

  April 27, 2018 at 7:12 am

  #1988443

  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