Check user database(s) not part of Availability Group

[Garayia]

Feature Request

Report any database(s) on AlwaysOn cluster that is not part of any Availability group.

New Check

I think this doesnt exists in dbachecks or dbatools but if I have missed something happy to be corrected.

What would you like to check?

Check for user database(s) on any nodes of Availability group cluster that are not part of an Availability group. This helps DBA to know of any databases that are sitting only on one node and that it can cause outage for the application if availability group were to failover.

What should be able to be excluded from being tested ?

We should be able to exclude database(s). This is required as we do have a general database by the name 'DBA' that doesnt need to be part of any availability group.

Additional information

Reason for requesting this check is we have over 1000 SQL Server instances and sometimes the team forgets to add the databases to Availability group and as a result when the failover occurs (during patching or for some other reasons) that particular database is not available and causes an outage.

This check also helps DBA in identifying database(s) that doesnt need to be in HADR should not be on AOAG cluster.

Below is a T-SQL for this check from here which I have slightly modified.

IF (SELECT SERVERPROPERTY('IsHadrEnabled')) = 1 BEGIN SELECT name FROM sys.sysdatabases where dbid>4 and name not in ('excludedDB', '') and name not in ( SELECT DB_NAME(drs.database_id) as name FROM ( (sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id ) JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id and drs.replica_id = ars.replica_id where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY' ) END