Connecting to AvailabilityGroup Listener

  • we have a new SQL site - and included Availability Groups in the install.

    set up is 2 servers - with 2 availability groups (AG1 and AG2) on each one.

    each of the availability groups has a Listener (AGL1 and AGL2)

    when running SSIS packages, we use the listeners to connect to the "right" node to enable updates to occur

    in SSMS, can connect to the listener as a "database" connection

    but would also like to be able to run a query across the groups


    when on the "secondary" node, execute a SQL update statement such as

    INSERT INTO [AGL1].[WarehouseDB].[dbo].TestTable...

    is this possible - i.e. the AGL "name" can be resolved (without using Linked servers)


  • No you would need to use a linked server as it needs to resolve the read-write replica which could be any node.


    If it was a select then that's a different story as you could just use 3 part naming, but for I/U/D queries which need to span different AG's you need to establish a connection to the listeners.

  • This was removed by the editor as SPAM

  • Okay to clarify what you seem to be saying:

    1. You have a 2 Node Availability group with a primary and a secondary (failover) server (the last is an assumption)
    2. You have a listener that points you to the current active server (typical setup)
    3. You want to write to the secondary node

    Item 3 is usually not possible as the secondary node is read only, and while you can query it you cannot write to it.  If that is not your step up I would need a better understanding of the relationship between these two servers to get a better handle on what you have set up so that I can better understand what you are attempting to do.

    Assuming the configuration I outlined is how things are set up and the secondary server is a failover backup that is synced to the primary server then again you can query the secondary (read-only) server all day but you cannot update/insert records to it. If you do need to access that secondary server from another server (such as the primary) -- for instance you need to get system data off of the secondary and record it on the primary server -- then you will (as was stated) need to set up a linked server but this is a bit tricky and requires a special users that has the appropriate rights while not having more rights than it needs -- a tricky balancing act and one we are still working through.

  • Dennis,

    many thx for the response.

    yes, points 1-3 are correct. DBs split into 2 AG groups on 2 servers - so on one server AG1 is primary and AG2 is secondary, while on other server AG1 is secondary and AG2 is primary...

    On each server, SSIS jobs run via SQL agent - with first step in each job checking to see if the current server is "PRIMARY" - and stopping if not. The SSIS jobs connect to DBs via listener so point to the current "PRIMARY" and enables required I/O. All good.


    we do have a few SQL Agent jobs that contain steps that are a mixture of SSIS packages and StoredProcedures - and these are the problem ones as the StoredProcedures execution might be trying to access a DB in AG2 which on current server might be secondary.

    (assume we could just go through and put the Stored Procs into SSIS jobs and use the listener.... but there is quite a few of them and would like a more elegant solution if possible)

  • Ant-Green,

    thx for the response.

    unfortunately no, not just reading from DBs in SProcs .... some do but most need a I/U/D ability

  • Sounds like you don’t want 2 AGs and you need to consolidate them into 1 AG.

    Or rearrange the DBs in the groups into their correct logical groupings.

  • Okay Mark your description is a bit confusing but here is what I understand so far and correct me if I am wrong:

    1. 4 Databases 1, 2, 3, 4
    2. 2 Servers X, Y
    3. 2 Availability Groups A, B
    4. Non-Failover Setup XA1-P/XA2-S and YB4-P/YB3-S -- where P = Primary and S = Secondary
    5. LSN1 > Primary (XA1 or XA2) and LSN2 > Primary (YB4 or YB3)
    6. SSIS Jobs will only run on the current Primary Database within both Availability Groups
    7. Some Stored Procedures try to Insert/Update to a Secondary (read-only) database for some unknown reason

    At this point your description breaks down because you refer to the 4 databases by only 2 names assuming you have the set up I have outlined (the only setup based on your description that makes sense to me).

    So is this setup you have?  If not please explain in more detail.

    Why do these Stored Procedures try to Insert/Update to a Secondary (read-only) database rather than to the primary one?

    Further and perhaps more importantly how are these Stored Procedures that do #7 being launched?

    The answers to these question in as much detail as you can provide will go a long way in helping us help you.

  • First - I see no value to having 2 AG's configured, especially since the databases across both AG's need to have access to each other.  Can you explain why the decision was made to separate out the databases into separate AG's?

    As for your agent job scenario - if you have job steps defined that need to access both AG's in a single job you are not going to be able to resolve these issues.  The general rule is to have the first job step check to see if this is the primary - if it isn't the primary then exit with success.  The assumption here is that all AG's are primary on that node - so the rest of the job steps can be run on that node.

    What that means is that you need to ensure that all job steps in that agent job are set to run for a single AG only.  Or - you move those stored procedure calls into the SSIS package so you can set the connection to point to the primary for each AG.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Well we run two AG groups although we do not cross-talk between them as one is for Preprod and the other is Prod. However, in our Prod AG we have 3 servers Primary, Secondary and Report Server (cannot failover to) these we need to have queryable from the Primary as we need to log system information particular to each server because we often use the Secondary server for Read Only tasks associated with the Primary server. This required setting up a pair of linked servers so that the Primary could query that information off the other two servers and record it into a table.

    The problem I see thus far with the OP's situation is that we are not fully clear on what they have set up due to the confusing nature of how it has been described. My last post was meant to help clarify what exactly is the setup so as to make sure we are all on the same page prior to that we are just shooting darts at a moving target in total darkness.

  • Thx for the replies - and apologies for delay in getting back to you.

    Dennis - yes this is (mainly) correct

    • 2 Servers X, Y
    • c 45 Databases
    • 2 Availability Groups A, B
    • c 32 databases in AvailabilityGroup A and c 12 in Availability Group B
    • Currently on Server X - Availability Group A is Primary and Availability Group B is Secondary

      Currently on Server Y - Availability Group A is Secondary and Availability Group B is Primary

      (this does enable us to "split the load" during some of the overnight processing)

    • SSIS Jobs are set up through SQLAgent to run on both servers - the first step in each / every job checks to see if running on Primary - and only continue if YES

    From the responses above, it appears we have 3 possible resolutions....

    1. Reorganize the databases into ONE AvailabilityGroup
    2. Use LinkedServers
    3. Migrate stored procedures to SSIS packages (and use the Listeners to resolve the connectivity issue)
  • Okay while that might be doable -- dang it hurts the head to think about what will actually happen.

    I mean, usually with an Availability Group, one is usually sychronizing them which means any changes to A get applied to B and any Changes to B get applied to A and any changes to A get applied to B -- ad infinitum. Aka a cascading endless loop of updates. Which is to say, you are setting up what appears to be basically a circular reference which as to what the final result will be is anyone's guess.

    Now if they are not sychronized I am not understanding your usage of Availability Groups and would ask you to explain.

    So far this appears to be a major miss usage of Availability Groups which I can almost confidently say will cause issues somewhere within the mix.

    Potential Options:

    #1: I cannot see how this would solve your current issue, please explain your thoughts.

    #2: I cannot see how using Linked Servers would help this situation, need more details on your thoughts on this.

    #3: This does not seem like a viable solution either, please explain your thoughts on this.

    Lastly perhaps you have not correctly portrayed what your true issue is, I am getting the feeling there is more to this than what as been fully outlined based on your potential solutions and other items that you have presented.

  • This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

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