Failover cluster - problems connecting

  • GustiX

    SSC Journeyman

    Points: 75

    Hi,

    I am testing a failover cluster setup for the first time using virtual machines in azure and sql server 2019 in a totally isolated environment (fake domain etc.). After some youtube videos I managed to set everything up except that I can't connect to the SQL cluster (using the sql cluster name) except from the computer that is running the sql server at the time (i.e. the active node), e.g. if SQLFAILOVER1 is active, I can connect from SQLFAILOVER1 but not from SQLFAILOVER2 (or any other computer). All firewalls are down so it's not an firewall issue. Using netstat I can see that the active node is listening for connections on 1433 on the virtual ip address. All the dns and AD computer entries seem correct. I have tried telnet to test the connection to 1433 using the ip address and sql cluster name from a different computer but with no success. Any bright ideas?

    I include pictures of the setup, dns and cluster with red boxes around the things that mattercluster3cluster1cluster2

    Best regards,

    Agust

  • anthony.green

    SSC Guru

    Points: 112378

    Have all the health probes and load balancers etc been setup?  S2D synced etc?

    What documentation have you been following? Hopefully the below...

    https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-create-failover-cluster

     

    Also sometimes, having the firewall disabled can again cause problems, have you tried enabling it and punching the holes into it that's needed?

  • GustiX

    SSC Journeyman

    Points: 75

    I am using Azure virtual machines but only to emulate on prem setup. I am trying not to use anything azure specific, only general windows stuff. I created a domain controller VM which has the fake domain and DNS. I might try enabling the firewall again but I am not sure I know all the ports needed for "hole punching" 🙂

    One thing I came across was that perhaps I don't have enough NICs? I only have one network card in the nodes and I am using iSCSI disks. Most articles suggest you use a separate network card for the shared disks...

    • This reply was modified 1 month ago by  GustiX.
  • anthony.green

    SSC Guru

    Points: 112378

    Azure has its differences to on-prem in every way, so while you build a cluster one way on-prem the process is totally different in Azure.

    You can't just go and create a virtual name like you can on-prem without first creating the necessary load balancer so that the health rules and the right routing can take place.  This is the same for availability groups using Azure VM's.

  • GustiX

    SSC Journeyman

    Points: 75

    Not to doubt your claim but in what way is an Azure VM with Windows Server installed different from a physical computer on my desk? By that I mean if I only plan to use core Windows functionality like clustering, DNS & AD. I realize that the underlying hardware is of course different but should I not be able to set up a SQL cluster in Azure as if I were doing so on my desk with a couple of desktop machines?

    In my current setup, which consists of 3 virtual machines, all Windows 2019 Datacenter, one Domain Controller and 2 sql nodes, should I not be able to get an "old fashioned" SQL failover cluster to run without any azure components?

    br/Agust

  • anthony.green

    SSC Guru

    Points: 112378

    No, the concepts in Azure are totally different to on-prem, as such you need load balancers and health probes and disk sync tools on top of the "old fashioned" stuff to get clustering both the traditional fail over and always on availability groups to work in azure.

  • GustiX

    SSC Journeyman

    Points: 75

    Thank you for the tip Anthony. After a lot of digging and testing it boils down to the IP address assignment of the network adapters. The virtual sql cluster IP address (10.0.0.20) is assigned by Windows to the network adapter but somehow ignored/not accepted by Azure. If I edit the network adapter (assigned to node 1) manually in Azure I can get the connection to work, that is until it fails over. That is why I need to set up the load balancer stuff you mentioned... this was an interesting problem to chase 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply