SQL Server monitoring series-1

As a DBA, it not only helps developers Write Excellent query scripts, but also efficient data storage architecture. In fact, most of the time is in operation and maintenance, and most of the work time is not saturated, but why are some DBAs so busy, so difficult to resist or even wait until the customers or Party B complain, many of the reasons are that you are not familiar with the basic monitoring architecture of SQL Server, are not familiar enough, and cannot arrange monitoring tasks automatically.

Get notification: SQL Server provides us with message sending methods such as SQL mail and database mail. Therefore, we use emails to obtain the most basic notification exceptions. I mentioned databasemail in my previous article. You can use it as a reference.

MSSQL database mail Series 1 (Environment setup)

MSSQL database mail Series 2 (SSMs and tsql)

I mentioned the basic email configuration, but maybe our intranet database is not accessible to the Internet. How can we send it to our monitoring mailbox. A friend may try to insert a mail table and then read the table to send emails on another Intranet machine that can access the Internet. In fact, we can implement this through simple SMTP relay, we know that SMTP is actually a push protocol, which is generally sent to the stmp server of the provider. In fact, we can also send it to our SMTP server and then forward it to the SMTP server with the specified DNS response.

I tested Windows Server 2008. We will install the SMTP server. Open

1. Server Manager-> function-> Add function-> SMTP server.

2. After the installation is successful, open the settings in the management tools> IIS (6.0) manager.

3. Click "Domain"> "new domain"> (I created a new SMTP. QQ. com domain, so I want to forward the email to my QQ mailbox ).

We can see the standard port 25 used by SMTP. QQ. com provided by QQ. Therefore, open WF. MSC to add the outbound rule of port 25, and open 25 of the inbound rule (so that the DB server can relay to the SMTP server) without closing the tab.

4. In "Outbound Security"-> select "basic identity authentication"-> Add your email Logon account-> (TSL encryption is set according to the rules of your SMTP server.

5. select "SMTP virtual server #1"-> right-click Properties-> switch to the "access" tab-> click "relay" in "relay restrictions" to add an access list for your machine.

6. In the last step, set your databasemail account to anonymous authentication. The SMTP server selects your intranet SMTP server address and the default port is 25.

If it is okay to send the test email, you can search for the information or directly ask a question. Then I solved the first problem, "How do we get notifications", using the most basic mail method.

Detailed monitoring information: the database cannot avoid exceptions, such as exceptions caused by wrong scripts, insufficient space, disk crashes, and replication failures. Here I will first mention the SQL Server event. This means that the SQL Server generates an event with a specific error. Each event has a corresponding database, severity level, error number, and error text.

Enable SSMs> SQL Server proxy> to create a warning. Generally, we focus on events with severity levels greater than or equal to 13. It is also important to quickly deploy services for Party B. Therefore, I have compiled some scripts:

USE [msdb]

GO

/****** Object:  Alert [SQL_EVENT_13]    Script Date: 12/08/2010 14:44:53 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_13', 

        @message_id=0, 

        @severity=13, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

USE [msdb]

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_14', 

        @message_id=0, 

        @severity=14, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_15', 

        @message_id=0, 

        @severity=15, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_16', 

        @message_id=0, 

        @severity=16, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_17', 

        @message_id=0, 

        @severity=17, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_18', 

        @message_id=0, 

        @severity=18, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_19', 

        @message_id=0, 

        @severity=19, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_20', 

        @message_id=0, 

        @severity=20, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_21', 

        @message_id=0, 

        @severity=21, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_22', 

        @message_id=0, 

        @severity=22, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_23', 

        @message_id=0, 

        @severity=23, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_24', 

        @message_id=0, 

        @severity=24, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/****** Object:  Alert [SQL_EVENT_17]    Script Date: 12/08/2010 14:45:03 ******/

EXEC msdb.dbo.sp_add_alert @name=N'SQL_EVENT_25', 

        @message_id=0, 

        @severity=25, 

        @enabled=1, 

        @delay_between_responses=1800, 

        @include_event_description_in=1, 

        @category_name=N'[Uncategorized]', 

        @job_id=N'00000000-0000-0000-0000-000000000000'

GO

The above provides 13-25 serious events. With the event, it is inevitable that an operator or monitoring personnel is required to forward the email to obtain the event. We can see that the SQL Server Agent contains the operator tab and you can create an operator:

 

USE [msdb]

GO

/****** Object:  Operator [Terry Mao]    Script Date: 01/19/2011 11:37:34 ******/

EXEC msdb.dbo.sp_add_operator @name=N'Terry Mao', 

        @enabled=1, 

        @weekday_pager_start_time=90000, 

        @weekday_pager_end_time=180000, 

        @saturday_pager_start_time=90000, 

        @saturday_pager_end_time=180000, 

        @sunday_pager_start_time=90000, 

        @sunday_pager_end_time=180000, 

        @pager_days=0, 

        @email_address=N'10000@qq.com', 

        @category_name=N'[Uncategorized]'

GO

I added a "Terry Mao" operator, corresponding to the email address of 10000, and then we may need to add a subscriber for all events, that is, Terry Mao.

You can query all created alarms in the View:

SELECT * FROM msdb.dbo.sysalerts;

Of course, it is impossible to add a subscription event. You can create an SP with similar functions:

CREATE PROC dbo.sp_add_all_notification    @Operator sysnameASBEGINDECLARE @Name sysname,@OperatorID int;SELECT @OperatorID = id FROM msdb.dbo.sysoperators WHERE name = @Operator;IF @OperatorID IS NULL    RETURN -1;    DECLARE Cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT [name] FROM 

msdb.dbo.sysalerts alters WHERE NOT EXISTS

(SELECT 1 FROM msdb.dbo.sysnotifications notify 

WHERE notify.operator_id = @OperatorID AND notify.alert_id = alters.id);OPEN Cur;FETCH NEXT FROM Cur INTO @Name;WHILE @@FETCH_STATUS = 0BEGIN    EXEC msdb.dbo.sp_add_notification @alert_name = @Name,

@operator_name = @Operator,

@notification_method = 1;    FETCH NEXT FROM Cur INTO @Name;ENDCLOSE Cur;DEALLOCATE Cur;RETURN 0;END

In this way, all the events will be monitored. In fact, I solved the filtered monitoring list, but we may execute SQL Server statements for a long time, the storage process is monitored for optimization. For example, if we serve as a DBA consultant for another company, we need to immediately obtain the problematic query. I will describe it in the next article. This includes profile monitoring and SQL Server WMI monitoring.