database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

Question

how can I find the list of databases I actually can connect to?

I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is showing as ONLINE

I cannot do this:

use my_database

and I cannot do this:

select * from my_database.sys.master_files

Msg 976, Level 14, State 1, Line 364 The target database, 'my_database', 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.

Answer 1

how can I find the list of databases I actually can connect to?

Msg 976, Level 14, State 1

the database is synchronizing and it is showing as ONLINE

You'll want to see if the replica, in a secondary role, is set to be readable. This requires the replica to be in a state where it can be read from (primary/secondary role, database is healthy, etc.).

To check whether the replica is set to be readable as a secondary, use sys.availability_replicas and check for secondary_role_allow_connections_desc for the current state. Note that READ_ONLY requires an additional connection string parameter and will result in a different error message than the one quoted above.

To check if the replica is healthy enough to query, check the state of the database via sys.dm_hadr_database_replica_states for values in synchronization_state_desc, database_state_desc, and is_suspended.

These values can be obtained either on the primary replica, or locally on the secondary replica. The secondary replica will only see itself, thus if you're attempting to map this topology wide, you'll want to only run the queries from the primary replica. Note that these values are online values and can change based on configuration, which is also an online change (can be changed any time without any downtime).

Answer 2

this did not work:

select db.state_desc, name, db_status=DATABASEPROPERTYEX(db.name,'status'),[Last Successful DBCC CHECKDB]=DATABASEPROPERTYEX(db.name,'LastGoodCheckDbTime') 
from sys.databases db
where name = 'my_database'

this worked:

SELECT name, HAS_DBACCESS(name) FROM sys.databases;