sql-serversql-server-2016high-availabilityalwayson

How to address availability groups in SQL 2016


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.


Solution

  • 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

    1. Create a SQL Authentication account on AG2 DB that you will use to link the servers (if one doesn't already exist). It needs access to all objects you wish to query from AG1 DB.
    2. In SSMS under you AG1 server, navigate to Server Objects > Linked Servers
    3. Right click on the Linked Servers folder and select New Linked Server
    4. Enter the Server name under Linked Server
    5. Select the SQL Server radio button
    6. Click on the security page on the left
    7. Click the "Be made using this security context" and enter in the SQL Authentication account information. Feel free to use one of the other options if you want.
    8. Click OK

    Now, you can query that database.

    Linked Server

    Create Linked Server

    One of Many Tutorials