exclude Always On Read-only databases

Question

exclude Always On Read-only databases

Siten0308

SSCarpal Tunnel

Points: 4404
More actions
January 11, 2021 at 8:26 pm
Go to Answer
#3831135

Hello,
I am writing a tsql statement but every time i run it, it comes up with error:
The target database ('Database_Name') is in an availability group and is currently accessible for connections when the application intent is set to read only.
So i am trying to find a way to exclude Always On Read-Only databases, any idea on a sys table that i can do to exclude them?

thanks in advance
Michael L John

One Orange Chip

Points: 27043
More actions
January 12, 2021 at 2:08 pm
Answer
#3831463
This will filter for the local databases.
```
SELECT * 
FROM sys.databases 
WHERE replica_id IS NULL
```
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24703 More actions · Answer 1

without knowing what you are trying to do on the database, I am not sure what direction to point you in.

But a good starting point would probably be sys.databases. It has an "is_ReadOnly" flag that you could check. I am not certain if that applies to always on Read-only databases, but it is where I'd start looking. I'd start with a SELECT * FROM sys.databases WHERE name = <Always On Read-Only database name> OR name = <some other one where the command should be successful> and then compare the results of those 2 rows to determine which fields I can use to filter.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Michael L John One Orange Chip Points: 27043 More actions · Answer 2

Siten0308 wrote:

Hello,
I am writing a tsql statement but every time i run it, it comes up with error:
The target database ('Database_Name') is in an availability group and is currently accessible for connections when the application intent is set to read only.
So i am trying to find a way to exclude Always On Read-Only databases, any idea on a sys table that i can do to exclude them?
thanks in advance

It sounds like you are running a query against the secondary in an availability group, and it's not set up to allow that.

In other words, you are connecting to the wrong server.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Siten0308 SSCarpal Tunnel Points: 4404 More actions · Answer 3

ok thank you all for your input...

so this is what i wanted to accomplish, i want to run a inventory of sql databases, and I have a registered server that has all the database servers... so when i go to query it, i do run into the following errror message:

The target database ('Database_Name') is in an availability group and is currently accessible for connections when the application intent is set to read only.

and its just a simple

Select *

from sys.databases

and yes you all guessed it, some are secondary, HOWEVER secondary can also have local databases as well, which i want to know, however I cant when I receive that error message and it stops my query.

just wondering if there is a way to exclude those databases if the database is on Always on Secondary Read-Only?