Implement monitoring and alerting using SQL Server Database Mail Service _mssql

Background

Now more and more enterprises, the company requirements for the database to achieve 7*24-hour database monitoring, the general use of the third party platform to achieve the message and mobile phone SMS monitoring reminders. A few days ago the company a new server, eager to deploy the program has not had time to build other related platforms, in order to better monitor the database, the temporary use of SQL Server with the mail services to achieve the database monitoring and early warning. The following is a brief introduction to the configuration process for later use.

Configure Mail

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

1. Set up mail server

Open the SMTP service, click on the Open, and then click the Build authorization code below, get authorization code is the database to send mail account password.

2. Configure Database Mail

Open MSSM---Management--"Database Mail-" Mail configuration Database Mail-"select as follows

Click Next and 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

You can then modify the Configure system parameters, such as the number of account retries, the blocked attachment file name extension, click Next, and the default configuration:

When you are finished, you will see the following four success states:

This configures the mail service so that you can right-click the "Database Mail" and click Test Mail. You can also code execution

Send mail

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = ' XXXX ',   --configuration file name
 @recipients = ' XXX@QQ.com ',--inbox email Address
 @subject = ' Hello ',     --mail subject
 @body = ' ... '-   -message body content

After execution, you can view the details by using the mail log below the error log. If you receive the message then you can proceed to the next monitoring of the database, here are two types of job-specific monitoring and alarm monitoring.

Job Custom Statement Monitoring

1. Create a job, the core is to add the specified data in the step when monitoring over the trigger notification. A raiseerror can be used here:

2. Configure notifications when the failure is sent by email.

2. Alert monitoring [code] What is an alert: MS SQL Server automatically records what happens in the Windows program log. The SQL Server Agent automatically monitors the program execution log that is logged by SQL Server and, if a defined action is found, alerts the response to the event.

The mechanism of the alert is as shown

]

Forget where the pictures are

mechanism Understanding :

To create different levels of alerts, you must write errors to the Windows time log. This is not difficult to understand. As we have already stated in the concept section of the alert, the SQL Server Agent reads the error message from the event log. When the SQL Server Agent reads the event log and finds a new error here, the entire database is searched for appropriate alerts. Once the SQL Server Agent discovers match alerts, the alert is activated immediately, informing the person concerned or reacting according to the job's settings.

The brief configuration is as follows, there are mainly three types of monitoring: Event Alert/performance Alert/WMI Alert, here is a description of an example

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

As the following illustration shows, you can set which operators to notify when an alert occurs. Click the new Operator button to complete the operator to add alert notification, select email

As shown in the following figure, you can set the way alerts are sent incorrectly, you can select [e-mail], [Pager], [Net send] checkbox (optional), and you can fill in the notification message at the same time you send an alert, and you can set the interval for the response.

Summarize

Because usually used to the use of Third-party platform so little Mail service, recently tried to find out 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 need is that the function for the permissions of the higher requirements, and sometimes need to adjust user rights and so on to send the message normally. It is a good experience to consider this approach when there is no third party to send an early warning platform.

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.