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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply