1

I have 2 Microsoft SQL Server database servers DBServer1 and DBServer2 and 2 databases DB1 and DB2. Each of the databases is in a different availability group between the 2 servers. Therefore:

  • Availability Group 1 (AG1) contains a DB1 in a cluster between DBServer1 and DBServer2 and has listenerIP such as 192.168.80.1

  • Availability Group 2 (AG2) contains a DB2 in a cluster between DBServer1 and DBServer2 and has listenerIP such as 192.168.80.2

The database DB1 has some synonyms pointing to some tables of the listener of DB2. For example:

USE [DB1]
CREATE SYNONYM [dbo].[REMOTETABLE] FOR [192.168.80.2].[DB2].[dbo].[REMOTETABLE]

Then I try to perform a query to DB1 such as:

SELECT * 
FROM [DB1].[dbo].[REMOTETABLE]

However the synonym does not work as I would expect. When both of the databases are in the same server (either both in DB1 or both in DB2) a query works perfectly and returns the content of REMOTETABLE from DB2. However when one of the database is in DBServer1 and the other in DBServer2 the query fails with the following error:

The target database, 'DB2', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

I would like to understand why is this happening if the synonym is pointing to the listener. Also, is there any way to make synonyms works with listeners?

Thank you very much

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jorpilo
  • 46
  • 3
  • Your question is better suited for dba.stackexchange.com since it's about database administration rather than programming. – Dan Guzman Jan 07 '21 at 03:49
  • @DanGuzman Thank you for the comment, I was not aware of this website. However I already solved the question and I am going to leave the info here for anyone looking for it. – jorpilo Jan 07 '21 at 05:51

1 Answers1

0

Well I am going to auto-answer myself as I figured out the problem.

First, we need to configured a linked server in order to stablished the connection.

Second, the main issue was due to permissions. When a databased is transferred due to a failover the user loses the mapping permissions on the database (which is no longer accesible). Let's imagine we have a user, and the user have access to both DB1 and DB2. When DB1 and DB2 resides on the same server, DBServer1, the user has mappings to both DB1 and DB2 as they are accesible but it has NO mappings in DBServer2.

When DB2 is transferred to DBServer2 due to a Failover. The user only has mapping to DB1 in DBServer1 (as DB2 is in DBServer2 and is not accesible) and DB2 in DBServer2 (as DB1 is still in DBServer1 and is not accesible). This mismatching causes a user auth failure as it tries to authenticate with the same credentials.

This can be solved by configuring impersonation of users in the linkedServer configuration.

jorpilo
  • 46
  • 3