SQL Server Automation Operations series-about email notifications that's something (. NET Developer Benefits)

Requirements Description

In our production environment, in most cases, we need to have our own operation and maintenance system, including the detection of their own health status. In the event of an exception, an early warning is required, and the form of notification is generally communicated by e-mail.

e-mail as a very convenient way to implement early warning, in terms of timeliness and ease of use also has irreplaceable advantages.

Therefore, in this article will be detailed analysis of the SQL Server in the mail notification function and how to use.

This article realizes

1, through the SQL Server's own mail function to achieve operational and maintenance of early warning and detection

2. The disadvantage of using Database Mail component instead of traditional C # to send mail

3, to realize the job task running state detection

4, the use of PowerShell to implement job task scheduling detection

<1> Basic Configuration

First, let's configure the underlying service item for the Mail component under SQL Server. SQL Server since the 05 release, the mail feature does not need to open the provisioning Configuration Manager, it has its own components, to achieve the function of mail delivery.

If, not used, you can follow the steps below to configure it, the steps are simple.

Right-click to configure Database Mail

Then go directly to the next step and create a new account.

Then, the next step is complete, the steps are simple, there are several concepts need to be clear, for the SQL Server mail account is controlled by the rights, the purpose is to achieve different people using different mail accounts, such as large database management generally have several DBAs responsible for operations, sub-responsibility after the operation, the Send alert message also generated a distinction, always can not appear in the module any problems are sent to a person.

With you a dime relationship is not abnormal, every day to send you e-mail, is not very uncomfortable? .... This way of management is often violent!

To address these issues, SQL Server categorizes the accounts for the messages:

Divided into public accounts and private accounts.

Generally, if the management personnel are small, configure a public account can be, the problem is sent to the mailbox can be.

At this point, you have completed the configuration of the Database Mail module, the steps are simple. Here you can send a test message to test the connectivity of the message.

Tip:The SQL Server mail component needs to run as SQL Server age runs, so you need to make sure that the service is running.

Right-click on Database Mail, send a test e-mail, enter the address of the destination mailbox, and then tap Send.

At this point, your SQL Server has completed the basic configuration of the mail component, and then the rest of the work is how to use that component to do some of the work.

<2>c# Calling the Database Mail component for sending messages

Remember when you first graduated from the time, for sending mail this feature was an unusual obsession, a variety of research and various debugging.

Later in the end after the abolition of the Dickens, finally in a midnight dream back to see my dream of the test message to send a notification, think of a word description: Grass!

In general I remember the following namespaces that need to be referenced:using system.net; using System.Net.Mail;

The SmtpClient class provided by C # is then assembled into a mail entity, and then a Send () method, where the pain points lie in various coding specifications.

I believe there are many programs that apes still use this way.

Today, there is another flexible implementation that uses the mail components of a SQL Server database to send messages.

The call to the mail component provided in the first step above is actually implemented in SQL Server, which provides the system's own stored procedures. Here's how:

The stored procedure provides various parameters for sending messages, fully satisfying the various requirements for sending mail, such as: Subject, Content, attachments, CC, Secret cc .... Wait a minute.

Here's how to call this stored procedure:

-- stored procedure call outgoing mail EXEC msdb.dbo.sp_send_dbmail @profile_name =  ' [email protected] ' 

The above is an easy way to execute both the delivery of the message and the implementation.

Then all you need is C # to invoke the stored procedure.

The process of writing C # code to call stored procedures via ADO is not covered here, I believe this is the entry level of small white can also handle things.

Then, here is the analysis of the message components of the principle and performance issues. I believe this is a lot of people care, in fact, when the SQL Server mail is sent through a low-level job polling execution, so there is no need to worry about its execution order and performance issues.

and SQL Server also provides several system views to view the history of the State and the status of the current message's queue:

    -- Mail content    SELECT * from Msdb.dbo.sysmail_allitems

    -- Mail send log    SELECT * from Msdb.dbo.sysmail_event_log

and SQL Server provides the ability to resend the message, as well as other default parameters, which are set up to refer to this screen:

At this point, you have completed the ability to send mail using C #.

I believe that basically in C # will be paired with Microsoft's own SQL Server database, and after using it can be a small amount of code to achieve the function of mail delivery.

<3> Enable detection of job task run status

When we use SQL Server, in many cases we need to customize the job to implement some of the functionality, and most of the time is to take the morning or the non-business period to work.

The operation of the job results in the detection of a problem that needs to be tracked, such as the operation of the n job sometimes, only a few problems, and the uncertainty of the job occurred on that machine, so the importance of automation operations is self-evident.

For the above problem resolution, SQL Server provides a very simple configuration to implement.

(1) First, you need to define a few operators, in the final analysis is a few people on duty operation of this database

Above, I define a person, in fact, can define a number of people, several operations and maintenance personnel several ...

(2) Second, the need to define the alarm, in the final analysis is the resulting warning sent to the above several operations and maintenance personnel.

The severity option in this is a very important feature, and some simple problem warnings sometimes don't need to be paid attention to, or they don't need to be dealt with on a temporary basis.

But some problems need to be solved, such as server outage ....

We then associate this alert with an operator

Here, we have completed the detection configuration of the alert, and then we need to associate the task property value of the job agent.

With the above configuration, any of our custom job status can be automatically detected.

For example: A job Run batch success, a job run failed. Let's create a new custom job to test under:

Then set the warning

Then, when you run the job with an exception, you can automatically report to the appropriate OPS person.

Here we set up an OPS person, so this is only sent to one person.

Let's run it manually to test the effect.

Hey, sure enough, there's an alarm, it looks like a sweet look.

At this point, this feature has been configured to complete its own flexible implementation.

<4> using PowerShell to implement Job Task Scheduler detection

For the job's detail log, please refer to the following article:

SQL Server Automation Operations Series-Monitor run-batch job status (Power Shell)

Conclusion

Originally intended to use the Power shell script detection of the implementation of the function is also added, but the article has been a little bit of space, the follow-up to complete it. This article about the SQL Server mail function as a discussion, the other needs of their own flexibility to implement.

In fact, the job task described in this article on the detection of a few servers there is still a problem, but if more than one server, if there are several job exceptions on each server, every morning to open the mail more estimates will make your scalp numbness, and in the abnormal alarm, not give detailed error information, In fact, this is a very uncomfortable disadvantage.

So, for an elegant operation of automating operations, we will scan all of our detected server job runs each time, then summarize them to a single message, and then send them to a fixed ops person as important.

Does that sound like a little bit of excitement, next we're going to implement this feature. Interested children's shoes can be noticed in advance.

There is a wide range of SQL Server Automation operations and testing, many of which are based on daily experience, from manual to automated processes step-by-step.

If you read this blog, feel that you have something to gain, please do not skimp on your " recommendation ".

SQL Server Automation Operations series-about email notifications that's something (. NET Developer Benefits)