November 22, 2023 at 8:34 am
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
e.g.
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)
??
November 22, 2023 at 9:47 am
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.
November 22, 2023 at 10:31 am
This was removed by the editor as SPAM
November 23, 2023 at 5:22 pm
Okay to clarify what you seem to be saying:
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.
November 23, 2023 at 6:28 pm
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.
But...
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)
November 23, 2023 at 6:30 pm
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
November 23, 2023 at 7:31 pm
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.
November 24, 2023 at 12:13 am
Okay Mark your description is a bit confusing but here is what I understand so far and correct me if I am wrong:
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.
November 24, 2023 at 5:55 pm
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
November 25, 2023 at 11:34 pm
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.
November 26, 2023 at 8:29 am
Thx for the replies - and apologies for delay in getting back to you.
Dennis - yes this is (mainly) correct
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)
From the responses above, it appears we have 3 possible resolutions....
December 23, 2023 at 2:23 am
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.
December 24, 2023 at 7:57 pm
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