Is My AlwaysOn Configuration Using BASIC Availability Groups?

SQL Server Administration
1

Hi experts, I need to determine just how my AlwaysOn configuration is set up. It's on the Enterprise Edition. I queried table sys.availability_groups but it doesn't provide any clues.

Any ideas? Can i see this thru the GUI tool?
Thanks

2

No - Enterprise Edition does not use basic availability groups. From SSMS - in the Object Explorer open up the folder 'Always On High Availability' to see what groups are setup and configured.

You can right-click on that folder to show the dashboard. You can right-click on a group and select properties to show the properties of that group. From that you can see the configured databases, the replicas and how they are configured and backup preferences.

1 Like
3

Thanks, Jeff. I thought you still had the option - in the Enterprise Edition - of using the Basic AGs.

4

I have configured ALwaysOn with automatic failover. In troubleshooting, this has come in handy:
Select * from sys.dm_hadr_availability_group_states; ( to show which instance is the Primary and whether healthy or not)

So, when automatic failover takes place, some series of stored procs gets executed I suppose. It would be helpful to configure an email alert to alert me that a failover has occurred.

5

No stored procedures are run when there's an availability group failover. The process is managed through SQL Server and Windows Failover Clustering. The listener IP address (if you have one) will change ownership at the cluster level to answer on the node which becomes the primary replica.

There are certain system messages/errors that are raised during AG events that you can create SQL Agent alerts for and send an email:

Note that you'll get multiple alerts for each database during a failover (multiple state changes on each node), so you may want to limit which messages you alert on. I used to get 50-60 emails when my 12 DB AG node failed over.

1 Like