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?