Alter a view that refers to a non-readable secondary database

Question

I have a database (DB1) that is not readable because it is secondary in an AG group.

I have another database (DB2) that is not replicated with the AG group. It contains some views that reference the first database. I need to periodically alter some views.

The error is:

Msg 976, Level 14, State 1, Procedure vw_xxx, Line 4 [Batch Start Line 9] The target database, 'DB1', 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 do not have these options:

1. Enable the readable secondary (lack of licenses)
2. AG failover (this is a DR site)
3. Add DB2 to the same AG (too large, I only want to replicate schema not data)

Are other options available?

I have 3 AG nodes. The DR is not readable because it lacks an active license to be readable. The ALTER VIEW occurs on DB2 at the DR site, referencing DB1 in that instance. I'm altering the views because I'm deploying changes with a dacpac in that DR site. I do not have licences to cover that DR site in an active way.

Answer 1

Can you create a linked server on each AG node that references the AG listener?

You can then alter your view to query DB1 via the linked server using 4-part naming, instead of querying DB1 using 3-part naming. Then you shouldn't encounter this error as the linked server will redirect to the AG primary.