Skip to main content
How can we help?
< All Topics
Print

SQL Server with databases not in availability group

If a server contains availability groups, and the server also contains databases that are not a part of any availability group, it can lead to unexpected behavior in case of a failover. It can also increase the license costs, as a full license is required for e.g. secondary failover nodes if they contain databases that are not in an availability group.

To query the Performance Store database for servers with availability groups that also contains databases that are not included in an availability group, use:

select s.*
from dbo.v_servers s
where s.availability_group_info in ('AG_ALLPRIMARY_NOTCLEAN', 'AG_ALLSECONDARY_NOTCLEAN', 'AG_MIXED_NOTCLEAN')
and s.ags_with_readable_secondary = 0

If a database is not a part of an availability group, it will become unavailable if the server is performing a failover.

Note, the alert is not thrown if the Availability Group contains readable secondaries, as usually in this scenario, the servers are not expected to be fully identical.

The availability_group_info column in the dbo.v_servers view can have the following values:

  • NONE: Server does not contain any availability groups
  • AG_ALLPRIMARY: All availability groups on the server are primary
  • AG_ALLSECONDARY: All availability groups on the server are secondary
  • AG_MIXED: Server contains both primary and secondary availability groups
  • AG_ALLPRIMARY_NOTCLEAN: All availability groups on the server are primary, and server also contains databases that are not a part of an availability group
  • AG_ALLSECONDARY_NOTCLEAN: All availability groups on the server are secondary, and server also contains databases that are not a part of an availability group
  • AG_MIXED_NOTCLEAN: Server contains both primary and secondary availability groups, and server also contains databases that are not a part of an availability group

Note, above information will not be collected for asynchronous availability group replicas.

Servers will not be in the “AG_ALLPRIMARY” or “AG_ALLSECONDARY” state if it contains databases that are not in an Availability Group. Sometimes this scenario is still correct if e.g. management databases are present on the servers, and these databases should not be included in the Availability Group. In such case, such databases can be added to the dbo.allow_as_not_clean table, whereas the server will change state to “AG_ALLPRIMARY“ or “AG_ALLSECONDARY”.

The alert will be visible on the Latest stats dashboard.

To exclude specific servers from this alert, see how to use the dbo.team_alert_exclude table in Alerting.

Table of Contents