1

This is a simplified view of the scenario we are considering:

Node1
  AG1
    Database A (master readwrite)
  AG2
    Database B (secondary readonly)
Node2
  AG1
    Database A (secondary readonly)
  AG2
    Database B (master readwrite)

This is for SQL Server 2016 SP2+. I believe the behavior regarding cross-AG transactions is essentially the same in later editions, though in later editions the DTC is always used.

Now suppose a client application connects to the AG2 listener with ApplicationIntent=ReadOnly and Database=B. Then presumably they are connected to Node1. Now suppose that the app does a select ... join between database A and B. Is Node1 (readwrite for AG1) used for the database A data, or Node2? Similarly, suppose the client executes some update SQL against database A on that "readonly" connection; will this succeed (since database A is readwrite on that node) or not?

Also I presume the behavior is identical in this respect if the client app just connects directly to Node1 without using the listener.

Is there any way in the sql itself to namespace the reference to a db to force it to be from another node?

Are there additional performance costs to cross-AG transactions (in addition to whether they are entirely serviced in the same node or not)?

Does it matter if what is actually done from the client app is to exec a stored procedure (which might even be in another database C), and then the body of that SP does cross-AG operations?

Thanks....

  • Why don't you capture queries with extended events and see what happens? – Tony Hinkle Apr 28 at 18:28
1

Goodness, you have a lot of questions in here. Here goes:

Now suppose that the app does a select ... join between database A and B.

To be clear, let's say you use:

SELECT Stuff
FROM DatabaseB.dbo.Customers
JOIN DatabaseA.dbo.Sales 

Is Node1 (readwrite for AG1) used for the database A data, or Node2?

It would use Node1, the node you're connected to. It's just a plain ol' cross-database query.

Similarly, suppose the client executes some update SQL against database A on that "readonly" connection; will this succeed (since database A is readwrite on that node) or not?

If you connected with "ApplicationIntent=ReadOnly", you shouldn't be trying to execute updates.

Is there any way in the sql itself to namespace the reference to a db to force it to be from another node?

Sure, use views that point to linked servers, and the linked server name would be the AG listener name. That isn't necessarily performant, but you asked how you could do it from the SQL itself, and that would be one way.

Does it matter if what is actually done from the client app is to exec a stored procedure (which might even be in another database C) […]

One thing to keep in mind is that availability groups are independent of each other and do not necessarily have the same replicas in their group. This means your AG1 might not have the same primary and secondary replicas as AG2 or AG3. Keeping this in mind, if you absolutely must do cross database queries then all of those databases should reside in the same availability group.

I'll stop here - your best bet for additional questions would be to break this question up into components and ask them individually. They could each have several great answers. Hope that helps get you started!

  • Thanks... Taking your responses together i understand why "you shouldn't be trying to execute updates": based on the particular failover scenario, the readonly replicas of AG1 and AG2 might be in the same node, so even if sometimes an update in AG1 might succeed when connected with read intent to AG2, that would not be a sure bet. I will contemplate this more then try some individual separate questions. – user2180778 Apr 29 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.