Use the SQL Server database email service for monitoring and warning.

Use the SQL Server database email service for monitoring and warning.

Background

More and more enterprises and companies require 7x24 database monitoring. Generally, third-party platforms are used to monitor and remind emails and text messages. A few days ago, the company launched a new server, eager to deploy the program before it can build other related platforms. In order to better monitor the database, currently, SQL Server's built-in mail service is used to monitor and warn databases. The following briefly introduces the configuration process for future use.

Configure email

The core of the entire part is to configure the mail service, which requires an email account and the corresponding email server. The following uses QQ mail as an example to describe.

1. Set the email server

Enable the SMTP service, click Enable, and then click Generate authorization code below. The obtained authorization code is the password of the mail account sent by the database.

2. Configure database email

Open MSSM --- manage -- database mail -- mail configuration database mail-select the following

Click Next and configure according to step 123.

First, create an account as follows:

Use the created account as the configuration account

Next, you need to select the Security Public configuration, select the file name you Just configured, and the default configuration file is

Then, you can modify "configure system parameters", such as the number of account retries, the extension of the banned attachment file, and click Next. The default configuration is as follows:

The following four success statuses will be displayed after they are completed:

In this way, you can right-click Database mail and click test mail. You can also execute code.

Send email

EXEC msdb. dbo. sp_send_dbmail @ profile_name = 'xxxx', -- configuration file name @ recipients = 'xxx @ QQ.com ', -- recipient email address @ subject = 'your', -- email subject @ body = '... '-- Body of the email

After execution, you can view the details in the mail log under the error log. If you receive an email, you can monitor the database in the next step. Here we will introduce two types of monitoring: specified job monitoring and alarm monitoring.

Job custom statement monitoring

1. Create a job. The core is to add a notification to the step when the monitoring of specified data exceeds. Here we can use a raiseerror:

2. Configure the notification. If the notification fails, an email is sent.

 

2. Alarm monitoring [code] what is alarm: ms SQL SERVER automatically records events in Windows program logs. The SQL server agent automatically monitors program execution logs recorded by SQL SERVER. If a defined Action is found, an alert is triggered to respond to the event.

Alert mechanism

]

I forgot the figure.

Mechanism understanding:

To create alarms of different levels, you must write errors to Windows Time logs. This is not hard to understand. As we have already said in the alarm concept section, the SQL SERVER Agent reads error information from the event log. When the SQL SERVER Agent reads the event log and finds a new error, it searches for the entire database to find the corresponding alarm. Once the SQL SERVER Agent detects a Match alert, it immediately activates the alert to notify relevant personnel or respond accordingly based on the Job settings.

The following is a brief configuration. There are three main monitoring types: event alert, performance alert, and WMI alert. Here we will not describe them one by one.

You can select the alarm type and select monitoring content for the database and severity, for example, insufficient permissions. When the permission is insufficient, an alarm is triggered:

As shown in, you can set the operators to be notified when an alarm occurs. Click the Add operator button to add an alert notification to the operator. Select email.

 

As shown in, you can set the alarm error sending method. You can select the [E-mail], [Pager], and [Net send] check boxes (multiple options are allowed ), at the same time, you can fill in the notification message that is provided when an alert is sent, and set the response interval.

 

Summary

Because I am used to the use of third-party platforms, I seldom configure the mail service. Recently, I tried to find it convenient to use the service if the server network is smooth or the Intranet Service is complete, after all, it is Microsoft's own business that can quickly monitor many metrics. The main requirement is that this function requires a high level of permissions, and sometimes you need to adjust the user permissions to send emails normally. This method can be considered when there is no third-party warning platform, which is a good experience.

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.