Microsoft SQL Server Always On availability group monitoring

Track the performance, availability, and health of your Microsoft SQL Server Always On availability group replicas and databases by monitoring the key performance indicators including the active HADR threads, replica information, log compression cache hits, failover alerts, synchronization health, and database state. Plus, get instant alerts whenever there is a breach.

Once the agent is successfully installed and the credentials are provided for insight monitoring, the Microsoft SQL Server Always On availability groups running on the server will be auto-discovered and added for monitoring.

Prerequisites

Before setting up your Microsoft SQL Server Always On availability groups monitors, make sure you have the required conditions satisfied:

Ensure AppLogs is enabled. To enable, go to Admin > AppLogs > Settings > Collect application logs from servers using AppLogs > Yes.
Site24x7 Windows agent version should be 20.9.1 or above.
.NET version should be 3.0 or above.
SQL version should be 2008 or above.

Provide the required permissions and follow the steps as stated in this help document to add your Microsoft SQL Server monitor. After adding the monitor, all the Availability Groups running on the server will be auto-discovered and added for monitoring.

Microsoft SQL Server Always On availability groups monitor

To view your monitor, follow these steps:

After logging in to Site24x7, click Database > Microsoft SQL Server Always on Availability Group.
You will be directed to the monitor page to view the list of the Microsoft SQL Server Always On availability groups you have added to monitor.
Under the availability group, click Databases to view the list of databases in the selected instance.

Note

If a new availability group is added after providing credentials, trigger application discovery from the parent SQL Server monitor. (Go to Server > Server Monitor > Servers and click the associated SQL Server monitor. Hover over the hamburger icon beside the display name and click Discover Applications.)

Performance metrics

Monitoring the key performance metrics of your Microsoft SQL Server Always On availability group instance and your Microsoft SQL Server Always On availability group database is highly crucial. By closely tracking these parameters, you can proactively identify potential bottlenecks, troubleshoot issues, and optimize the overall efficiency of your Microsoft SQL Server Always On availability group environment.

Threshold configuration

After adding the Microsoft SQL Server Always On availability group monitor to your Site24x7 account, you can add a Threshold and Availability profile to set thresholds and receive alerts. You can set thresholds for your replica monitors and your Microsoft SQL Server Always On availability group databases and get alerted when there is a breach.

For example, for your Availability Group, you can set a threshold when there is a failover or when the synchronization health is unhealthy. And for your replica monitor, you can get alerted when the operational state is offline. For your databases, you can get alerted when the transactional delay is more than 300 milliseconds.

To set a Threshold and Availability profile:

Select the Microsoft SQL Server Always on Availability Group monitor, click the hamburger icon> Edit.
In the Edit Microsoft SQL Server Always on Availability Group page, you will find the Threshold and Availability tab under Configuration Profiles. Click the + icon to add a new threshold profile or click the pencil icon to edit an existing threshold profile.
Click Save.

When a particular threshold is breached, you will receive alerts, and the specific resource will be marked trouble or critical.

Reports

View the reports of your Microsoft SQL Server Always On availability group databases by clicking Reports > Microsoft SQL Server Always on Availability Group and selecting the report you want to view.

Security

The Site24x7 server monitoring agent never stores your data in the Site24x7 app servers. The username and password are encrypted in the agent for security reasons.

Licensing

Each Microsoft SQL Server Always On Availability Group instance monitor consumes one advanced monitor license.