cross-database and cross-Availability Group queries and updates: which node do they go to with ApplicationIntent? 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....
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!