Use this Knowledge Script to monitor the AlwaysOn availability group health status, replica synchronization status, replica failover readiness, and operational status.
This script raises an event if the availability group status is not healthy, replica databases are not synchronized properly, there is a potential data loss during a failover, or operational state is offline/failed.
Availability Groups
The default interval for this script is Every hour.
Set the following parameters as needed:
Description |
How to Set It |
---|---|
General Settings |
|
Job Failure Notification |
|
Raise event if job fails unexpectedly? |
Select Yes to raise an event if the SQLServer_AvailabilityGroupHealth job fails unexpectedly. The default is Yes. |
Event severity when job fails unexpectedly |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the job fails unexpectedly. The default is 5. |
Raise event if SQL Server login fails? |
Select Yes to raise an event if login to SQL sever fails. The default is Yes. |
Event severity when SQL Server login fails |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the login to SQL server fails. The default is 15. |
Additional Settings |
|
Event Details |
|
Event detail format |
Select the format in which you want to display the event detail. You can select from HTML Table or Plain Text. The default is HTML Table. |
Authentication |
Select the authentication method that you want to use to access SQL Server. You can either select Windows Authentication or SQL Server Authentication. The default is Windows Authentication. |
User name |
Specify the Windows or SQL Server user name that you want to use to access SQL Server. You can specify multiple users separated by a comma. The default is none. For more information on specifying user name, see Specifying the User Name in the Knowledge Script in the Management Guide. For more information on specifying user name, see Section 2.7.3, Specifying the User Name in the Knowledge Script. |
Monitor SQL Server Availability Group Health |
|
Include results in data details? |
Select Yes to include the results in the data detail message. By default, query results are not included. |
Event Notification |
|
Raise event if availability group state is not healthy? |
Select Yes to raise an event if the availability group status is not healthy. The default is Yes. |
Event severity when availability group state is not healthy |
Set the event severity level, from 1 to 40, to indicate the importance of an event in which the availability group status is not healthy. The default is 5. |
Raise event if availability replica is not synchronized? |
Select Yes to raise an event if the availability group replica is not synchronized properly. The default is Yes. |
Event severity when availability replica is not synchronized |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the availability group replica is not synchronized. The default is 5. |
Raise event if availability replica failed over with potential data loss? |
Select Yes to raise an event if the availability group replica failed over with potential data loss. The default is Yes. |
Event severity when the availability replica failed over with potential data loss |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the availability group replica failed over with potential data loss. The default is 5. |
Raise event if operational state of the availability replica is offline/failed? |
Select Yes to raise an event if the operational state of the availability group replica is offline or failed. The default is Yes. |
Event severity when operational state of the availability replica is offline/failed |
Set the event severity level, from 1 to 40, to indicate the importance of an event that is raised when the operational state of the availability group replica is offline or failed. The default is 5. |
Data Collection |
|
Collect data for availability group health status? |
Select Yes to collect data for the availability group health status for charts and reports. The default is unselected. |
Collect data for availability replica synchronization state? |
Select Yes to collect data for the availability group replica synchronization status for charts and reports. The default is unselected. |
Collect data for availability replica failed over with potential data loss? |
Select Yes to collect data for the potential data loss when the availability group replica failed over, for charts and reports. The default is unselected. |
Collect data for availability group replica operational state? |
Select Yes to collect data for the availability group replica operational state for charts and reports. The default is unselected. |