SQL Server Database Alarm improvements

consider the ability to turn on Database Mail for all SQL Server servers in the production environment, which is a database-delivered messaging feature that receives SQL Server event alarms in a timely manner by configuring access to the mail server.

A SQL Server alert is an engine-based event notification mechanism that sends an alert when an event occurs when an alarm is alerted.

at this stage, the following two types of SQL Server event alarms need to be collected:

• Severity-Event Severity level -To -Alarm
  
  

• Error Number-including operating system errors,IOerrors, memory errors,AlwaysOnState-related alarms.
  
  

Policy tuning

We consider integrating these alarms that need to be monitored into the Application log and access the Elk Log Analysis system.

We understand that the errors/messages generated by SQL Server and SQL Server applications are sent to the Windows Application log in the following situations:

• Sys.messages error with severity level 19 or higher

• Any RAISERROR statement that is invoked with the WITH LOG syntax

• All sys.messages errors modified or created using sp_altermessage

• All log events that use xp_logevent logging
  
  Referenced from Https://docs.microsoft.com/en-us/sql/ssms/agent/create-an-alert-using-severity-level "severity levels from 19 Through send a SQL Server message to the Microsoft Windows application log and trigger an alert. Events with severity levels less than would trigger alerts only if you had used sp_altermessage, RAISERROR with LOG, or Xp_logevent to force them to being written to the Windows application log. ”

For the severity and error number alarms mentioned above that we need to monitor, we need to make the following change adjustments:

• Severity-After discussion, do not log user-level errors below severity level 19, Level 19 and above fatal error is logged by default to the application log, we do not have to make any changes.

• Error number-errors that need to be monitored, depending on the query result of the attachment, the is_event_logged field of the partial error numbers is 0, indicating that there is no logging to the application log. You can modify this field by Sp_altermessage above to achieve the purpose of logging to the application log for 1.

SQL Server Database Alarm improvements