How can i select information from a database which is in an availability group in SQL. Eg. I have 2 databases which are in 2 separate availability groups on the same server, I am in AG1 DB1 and trying to access a table in AG2 DB2. I know i can just use the database name if they are on the same server but if they fail over this query will fail.
I have looked around but cant see. Ideally I would think it would be AG2.DB2.DBO.table but this doesnt seem to work.
You need to add a linked server to the AG you are querying from. First, you'll need a SQL Authentication account on AG2 which has at least read access to the objects you wish to query from AG1--unless you want the user's credentials used. You'll use this account to create the linked server. Once you have done this, you will query it like you have stated above with server.database.schema.table
Now, you can query that database.