none
Connecting to Always on Database RRS feed

  • Question

  • Hi,

    I have configued SQL 2016 always and have a DB replicating but have no idea how I attach to it using the listener

    Could someone please help

    Thanks

    Wednesday, June 12, 2019 2:55 PM

All replies

  • Hi,

    I have configued SQL 2016 always and have a DB replicating but have no idea how I attach to it using the listener

    Could someone please help

    Thanks

    Creating listener is easy if you have free IP address to use for listener or port if you need it.

    See setting up listener in This Blog


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, June 12, 2019 4:28 PM
    Moderator
  • Everything looks to be configured correctly but I'm struggling to understand which IP I connect to? Which should be the VIP?
    Wednesday, June 12, 2019 5:37 PM
  • Everything looks to be configured correctly but I'm struggling to understand which IP I connect to? Which should be the VIP?
    You should use Listener IP to connect this will direct you to primary replica. I would suggest use listener name instead of IP. You can also use SQL Server name to connect but since listener directs you to primary replica you do not have to bother which node is primary replica

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, June 13, 2019 6:54 AM
    Moderator
  • This is what I don't understand:

    In a standalone single SQL server with 2 instances I would just use SERVERNAME/INSTANCE

    I have now configured 2 node SQL alway-son cluster which are on different subnets

    Server 1 listener IP - 10.0.0.50

    Server 2 listener IP - 10.0.2.50

    How do I get to the database from the app? and also make sure it will failover?

    In a standalone env it would be a case of just using SERVERNAME/INSTANCENAME

    Thursday, June 13, 2019 7:54 AM
  • Do I need to introduce some kind of external load balancer when using Always-on with host that are in different subnets?
    Thursday, June 13, 2019 7:57 AM
  • Do I need to introduce some kind of external load balancer when using Always-on with host that are in different subnets?

    No you do not. Sorry but it seems like you just created multi subnet AG without reading about it before. It would be difficult to tell you everything here but I will try. Since you have multisubnet AG you need to add two IP's to listener. In the blog you would see while adding listener author has used two IP's. Each listener will correspond to subnet range in which it lies. 

    To connect database from app simply put listener name in connection string


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, June 13, 2019 10:07 AM
    Moderator
  • OK so the problem I am having is that the listener only responds locally. It will not respond to PING or allow an ODBC connection from a remote machine (both work fine locally)

    Even with firewall totally disabled

    This is an azure VM by the way

    Thanks

    Thursday, June 13, 2019 1:17 PM

  • This is an azure VM by the way

    Thanks

    This is the part you should have told me at the start. For AG on Azure VM you need to set up Azure load balancer. Please go through it and let me know

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, June 13, 2019 1:23 PM
    Moderator
  • So are you saying this MUST be used with a Azure Load Balancer, and will not work without one? As in an F5 or other load balancer will simply not work?

    Also is it normal that the VIP on the active node will not respond to PING from the passive node? Even though routing and firewall permits


    Friday, June 14, 2019 7:01 AM
  • So are you saying this MUST be used with a Azure Load Balancer, and will not work without one? As in an F5 or other load balancer will simply not work?

    Sorry If I am not clear this is NOT A MUST you can have AG on Azure VM working without Azure Load Balancer but during the outage the failover and redirection would not be seamless. I will like you to read This Githib Link why LB is required but not a must.

    In regular WSFC (Windows server failover cluster) on-prem setup, when AG listener is created, it will create a DNS record for AG listener with the IP(s) provided. This IP address has to map now to MAC address of the current Primary node in ARP tables of switches/routers in the network. The cluster does this by using Gratuitous ARP (GARP) where it will broadcast to the network the latest IP-to-MAC mapping whenever a new Primary is elected after failover. Here, the IP is listener’s and MAC is of current Primary. This GARP should force an update on ARP table entries for the switches/routers and to a user connection to the listener IP address seamlessly goes to the current Primary.
    GARP (even ARP) is not supported on any public clouds (Azure, GCP and AWS, I believe as well) due to security reasons. In short, any kind of broadcast is not supported on cloud setup.

    So, in public cloud’s network infrastructure, load balancers provide traffic routing. In short, the load balancers are setup with a frontend IP, corresponding to the listener, and a probe port is assigned where LB will periodically poll for status. The VM which responds successfully to probe on this port will be forwarded incoming traffic. At one time only one SQL VM (Primary) will respond for this TCP probe. There is also configuration made at WSFC level, where corresponding probe port is setup at cluster IP resource level, thereby ensuring that Primary node does respond to TCP probe requests on this port.

    Also is it normal that the VIP on the active node will not respond to PING from the passive node? Even though routing and firewall permits

    By VIP you mean IP address of listener ?


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, June 14, 2019 7:36 AM
    Moderator
  • I mean the listener on the SQL server itself.

    If I do an IPConfig on the passive node I do not see the listener IP which is as I expect

    If I then fail-over and run IPConfig again I now see the listener IP (Again, as expected)

    I can then PING the listener from the local node but not from the other node

    Is this as it should be? I'm thinking not

    Friday, June 14, 2019 8:40 AM
  • Hi,

    Please refer to Availability Group Listeners(https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/listeners-client-connectivity-application-failover?view=sql-server-2017).

    Hope this could help you.

    Thursday, June 20, 2019 9:28 AM