Post reply

How do query an AG listener on Azure VM SQL Server instance for primary replica?

  • July 21, 2017 at 2:00 pm

    #402343

    Hello!

    I built a two node cluster on Azure VMs.  Each node is in a separate data center (management choice).  I used a cloud witness.  I then installed SQL Server 2016 and implemented high availability with a primary and secondary replica.

    Every is working fine.  Synchronization works.  Awesome!  Now, I want to query the data for my reports.  I set up the secondary replica to be readonly.

    I am not finding any documentation on how to query the databases by way of the listener.  I know I can create a dsn straight to the vm and to the database.  However, that was the poing in implementing AG was to the listener to query from the primary replica no matter which node was primary at the time.  What is the server name for a listenera?  Is it cluster name_listener?  Is it AG group name_listener?

    Any one know what the connection string looks like or how to connect to the databases using an AG listener?

    Thanks.

    Things will work out.  Get back up, change some parameters and recode.

  • July 24, 2017 at 3:41 am

    #1952312

    I'm assuming here that you want to query a readable secondary in an AlwaysOn Availability Group, and that:

    1) your Availability Group is already configured with a readable replica.
    2) your connection strings already specify ApplicationIntent=ReadOnly

    If not, you need to sort those out before continuing.

    The bit everybody (including me) usually forgets to set up for querying a readable secondary is the read-only routing lists.

    "For each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL. This URL takes effect only when the local replica is running under the secondary role. The read-only routing URL must be specified on a replica-by-replica basis, as needed. Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. Typically, every readable secondary replica is assigned a read-only routing URL."

    Have you done that?

  • July 24, 2017 at 2:41 pm

    #1952467

    Hello,

    I have set up the Availability group with a readable replica.  I have not read about read-only routing URL.  I need to review that.

    Thanks. 

    Things will work out.  Get back up, change some parameters and recode.

Viewing 3 posts - 1 through 2 (of 2 total)

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