Monitoring and alerting with Database Mail Service

Background

Now more and more enterprises, companies require the database to achieve the 7x24 hours of database monitoring, generally adopted is a third-party platform to achieve mail and mobile phone SMS monitoring reminders. A few days ago, the company has a new server, eager to deploy the program has not time to build other related platforms, in order to better monitor the database, temporarily with SQL Server's own mail service to achieve database monitoring and early warning. The following is a brief introduction to the configuration process for later use.

Configure Mail

The core of the whole part is to configure the mail service, which requires a mail account and the corresponding mail server. The following is the QQ mail as an example to explain.

1. Setting up a mail server

Turn on the SMTP service, click on, then click Generate Authorization code below, get the authorization code is the password of the database to send the mail account.

2. Configure Database Mail

Open mssm--"Management--" Database Mail-"Mail configuration Database Mail-" The following selection

Click Next and then follow step 123 to configure.

First create an account as follows:

Use the created account as the configuration account

Next you need to select a secure public configuration, select the file name that you just configured, and then default to the configuration file

Then, you can modify the "Configure system parameters", such as the number of account retries, prohibited attachment file extension, click Next, the default configuration can be:

After completion, the following four success statuses are displayed:

This configures the mail service to complete the right-click "Database Mail" hit test message. You can also code execution

--Send mail

EXEC msdb.dbo.sp_send_dbmail     @profile_name = ' XXXX ',         --configuration file name    @recipients = ' [email protected] ',  -- Email address    @subject = ' Hello ',--                 Email subject    @body = ' ... '            --message body content

After execution, you can view the details through the message log below the error log.

If you receive a message then you can do the next step for the monitoring of the database, here are two kinds of job is the designated monitoring and alarm monitoring.

Job Custom Statement Monitoring

1. Create the job, the core is to include in the step the monitoring of the specified data to trigger notification. Here you can use a raiseerror:

2. Configure the notification when the failure is to send an e-mail.

2. Alarm Monitoring

What is an alert: MS SQL Server automatically records what happens in the program log in Windows. SQL Server Agent automatically monitors the execution log of a program that is logged by SQL Server and, if a defined action is found, the alert that responds to the event is pulled.

Mechanism of the alert

Forget where the map is.

Mechanism Understanding:

To create different levels of alerts, you must write the error to the Windows time log. This is not difficult to understand. Because in the Alert Concepts section we have already said that SQL Server Agent is reading the error message from the event log. When SQL Server Agent reads the event log and finds a new error here, it searches the entire database for the appropriate alert. Once the SQL Server Agent discovers the match alert, the alert is immediately activated, notifying the person concerned or responding to the job's settings.

Briefly configured as follows, there are three main types of monitoring: Event Alert/performance Alert/WMI Alert, here is not a description of an example

You can select the alert type, select the monitoring content for the database and severity, such as insufficient permissions, and alert when insufficient permissions occur:

As shown, you can set which operators are notified when an alert occurs. Click the new Operator button to complete the operator to add an alert notification, select an email to

As shown, you can set the way the alarm is sent incorrectly, you can choose [e-mail], [Pager], [Net Send] check box (can be selected),

You can also fill in the notification message that is given at the same time as the alert is sent, and be able to set the time interval for the response.

Summarize

As usual accustomed to the use of third-party platform so rarely configured mail services, recently tried to find if the server network unobstructed or intranet service integrity, the use of the service is very convenient, and after all, Microsoft's own things for many indicators of monitoring is also relatively fast in place. The main requirement is that this feature has high requirements for permissions, and sometimes you need to adjust user permissions to send the message normally. This is a good experience when there is no third-party sending an alert platform to consider.

Monitoring and alerting with Database Mail Service