SQL Server alert management, real-time monitoring of database trends, in a planned way

Original: SQL Server Alert Management, real-time monitoring of database trends, in a planned way

Work for so many years, whether it is around the classmate or colleague, found that as long as the programmer has a common problem---lazy. Lazy to no one is willing to work overtime, especially "duty" overtime. Even if we are not willing to work overtime, but many times the project to go online or on the line after the error ah what, someone must look at this time on the birth of a new work, called "7x24." As the name suggests is that this kind of post real-time people have to watch, this is really a headache for things. Although it is inevitable that the project will be on the line, but we can minimize the amount of work (PS: Because it is really too tired, don't ask me how to know). So how do we reduce the workload? This is what we need to introduce in this blog post, the SQL Server alert management.

vWrite in front

Some of this blog post is based on the Microsoft SQL Server translation plus your own understanding.

vAlert Concepts

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. SQL Server primarily monitors the following types of things:

• SQL Server Events
• Server/Database performance conditions
• Windows Management Instrument Event (hereinafter referred to as: WMI)

The alert corresponds to the above 3 events, and the corresponding event is automatically triggered when the event occurs. The alert is done in the 3 parts of the name, the time the alert was triggered, or the performance condition, SQL Server Agent response event, or performance condition. A basic alert contains the following:

Error number

There are about 3,000 possible errors in SQL Server (common SQL Server errors), each with its own number. In many cases, program development can produce unpredictable errors, in which case we need to create a new custom error number and generate an alert for this event

Error level

Inside the program, any error is priority, and every error in SQL Server has an associated severity level that indicates the severity of the error and can produce different alerts at different levels. (This is very understood, take the atomic Bomb Mouse (fuss) things we do not do)

Performance counters

The corresponding alerts that database users can generate from performance counters that have a good effect on the various performance of the user database.

vAlert mechanismvCreate an alert

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.

Below we will create an event alert/performance Alert/WMI alert in turn

Create an Event alert

① Open the SQL Server Management Studio window, locate the alert node,

② Click on new alert (create alert)

Note:The basic properties of an alert can be set in a window. For example, the name of the alert can be set in the [Name] text box, the type of alert can be set from the [Type] drop-down list box, and the SQL Server Event alert option is selected in the figure. In the Database Name drop-down box, you can select the databases that perform the alert (by default, all databases), and you can use [error number] To specify the error numbers for the alert, and use [Severity] to select predefined alerts. If you select a severity level between 19~25, a SQL Server message is sent to the Windows Application log and an alert is triggered.

③ Select the [Response] tab and, as shown, you can set which operators are notified when an alert occurs. Stand-alone [New Operator] button to complete the operator to add alert notifications

④ Select the [Options] tab, as shown, you can set the way the alert error is sent, you can select the [e-mail], [Pager], [Net Send] check box (optional), you can also fill in the alarm sent at the same time the notification message, and the response interval can be set.

⑤ Complete the property setting, the stand-alone [OK] button completes the creation of the alert

Create a Performance alert

On the Performance Alert creation page, you can complete the following settings:

• In the [Object] drop-down box, you can set the object that the performance alert targets, like: Select the [Sql Server database] option to set the databases that the performance alert targets
• To set the count of performance counters by the [Counter] drop-down box
• Setting the database instance against which the performance counter is set through the [Instance] drop-down box
• Setting the conditions for performance counter triggering via the [Alert if counter] drop-down box

Next, just like the steps to create an event alert, set the [Response] tab and the [Options] tab and complete the setup, the stand-alone [OK] button completes the creation of the alert

Create a WMI alert

WMI Alerts help users manage both local and remote computers. WMI provides a continuous and consistent way for daily administration through programming and scripting languages, where users can accomplish the following tasks with WMI alerts:

• In the [Object] drop-down box, you can set the object that the performance alert targets, like: Select the [Sql Server database] option to set the databases that the performance alert targets
• To start a computer remotely or to start a process on a remote computer
• Set a process to run at a specific date and time
• Get a list of installed programs for a local or remote computer
• Querying the Windsows event log for a local or remote computer

WMI alert creation is similar to event Alert/performance alert creation by selecting the WMI Event alert option in the [Type] drop-down list of the [New Alert] window, as shown in

Using the default value of the [Namespace] text box \\.\root\microsoft\sqlserver\serverevents\mssqlserver, enter the following statement in the [Query] text box:

SELECT *  from  WHEREDatabase='Test'

vBlog Summary

Here, SQL Server Alert management is complete.

Here, with the platform of the blog Park, to those who struggle in the forefront of the front-line procedural apes to give the highest respect.

SQL Server alert management, real-time monitoring of database trends, in a planned way