Snippet Name: Check_Database_Readability_AG

Tags:

Created Date: 2025-06-16 13:12:40
Last Modified Date: 2025-05-31 10:31:50

Dependencies:

DBA Notes:

Sample Output:

Title: Check Database Readability AG
Author: user
i got the following error in SQL Server: The target database, 'DBA_ADMIN', is participating in an availability group and is currently not accessible for queries. How can i check if the database in quesiton is readable or not?

Author: ChatGPT
To check if a database in an Availability Group (AG) is readable in SQL Server, you can query the `sys.databases` and `sys.dm_hadr_database_replica_states` system views to get the status of the databases in the AG.

Here is a query to determine if the database is readable:

```sql
SELECT 
    d.name AS DatabaseName,
    d.state_desc AS State,
    d.is_read_only AS IsReadOnly,
    rs.synchronization_state_desc AS SynchronizationState,
    rs.is_primary_replica AS IsPrimaryReplica,
    rs.is_commit_participant AS IsCommitParticipant,
    rs.is_suspended AS IsSuspended,
    ags.database_id,
    ags.availability_group_id,
    ags.role_desc AS AvailabilityGroupRole
FROM 
    sys.databases d
LEFT JOIN 
    sys.dm_hadr_availability_group_states ags 
    ON d.database_id = ags.database_id
LEFT JOIN 
    sys.dm_hadr_database_replica_states rs 
    ON d.database_id = rs.database_id
WHERE 
    d.name = 'DBA_ADMIN';
```

### Explanation:
- `d.state_desc`: The state of the database (e.g., ONLINE, RECOVERING).
- `d.is_read_only`: Indicates if the database is set to read-only.
- `rs.synchronization_state_desc`: Describes the synchronization state (e.g., SYNCHRONIZED, NOT SYNCHRONIZING).
- `rs.is_primary_replica`: Indicates if the current replica is the primary replica.
- `ags.role_desc`: The role of the database within the Availability Group (e.g., PRIMARY, SECONDARY).

For readable secondary replicas, check the `availability_mode_desc` field from `sys.availability_replicas`:

```sql
SELECT 
    ag.name AS AGName,
    ar.replica_server_name,
    ar.availability_mode_desc AS AvailabilityMode,
    drs.is_readable_secondary
FROM 
    sys.availability_groups ag
JOIN 
    sys.availability_replicas ar 
    ON ag.group_id = ar.group_id
JOIN 
    sys.dm_hadr_database_replica_states drs 
    ON ar.replica_id = drs.replica_id
WHERE 
    drs.database_id = DB_ID('DBA_ADMIN');
```

If `drs.is_readable_secondary = 1`, the secondary replica is readable. If it's set to `0`, the replica is not configured for read access.

These queries should help you determine the read status of your `DBA_ADMIN` database within the Availability Group.

Author: user
sys.dm_hadr_availability_group_states does nto have a column named database_id

Author: ChatGPT
You are correct that `sys.dm_hadr_availability_group_states` does not have a `database_id` column. My apologies for the oversight.

To determine the read status of a database in an Availability Group, you should use `sys.dm_hadr_database_replica_states` along with `sys.databases` and `sys.availability_replicas`.

Here???s the corrected query:

```sql
SELECT 
    d.name AS DatabaseName,
    d.state_desc AS State,
    rs.synchronization_state_desc AS SynchronizationState,
    rs.is_primary_replica AS IsPrimaryReplica,
    ar.availability_mode_desc AS AvailabilityMode,
    drs.is_readable_secondary AS IsReadableSecondary
FROM 
    sys.databases d
JOIN 
    sys.dm_hadr_database_replica_states drs 
    ON d.database_id = drs.database_id
JOIN 
    sys.availability_replicas ar 
    ON drs.replica_id = ar.replica_id
WHERE 
    d.name = 'DBA_ADMIN';
```

### Explanation:
- `drs.synchronization_state_desc`: Describes the synchronization state of the database.
- `drs.is_primary_replica`: Indicates if this is the primary replica.
- `ar.availability_mode_desc`: The availability mode (e.g., SYNCHRONOUS_COMMIT, ASYNCHRONOUS_COMMIT).
- `drs.is_readable_secondary`: This value indicates whether the secondary replica is readable (1 for readable, 0 for not readable).

This query will help you determine whether the `DBA_ADMIN` database is readable on a secondary replica or not.