詳解DBA如何安排SQL Server數(shù)據(jù)庫自動監(jiān)控

作者：Sai~ 2011-01-20 08:35:23

SQL Server本身給廣大DBA提供了不少自動化監(jiān)控的手段，只是苦于對基礎(chǔ)監(jiān)控架構(gòu)不熟悉，不夠了解而使很多DBA出現(xiàn)重復(fù)無效勞動。今天我們就講講如何安排SQL Server數(shù)據(jù)庫自動監(jiān)控，減輕大家的工作負(fù)擔(dān)。

作為DBA，不僅僅是幫助開發(fā)人員寫優(yōu)秀的查詢腳本、高效的數(shù)據(jù)存儲架構(gòu)。其實更多時候都是在運維，大部分工作時間都不算飽和的,可是為什么有些DBA如此忙碌、狼狽不抗甚至等到客戶或者乙方公司抱怨呢，那很多原因就是對SQL Server 的基礎(chǔ)監(jiān)控架構(gòu)不熟悉，不夠了解，不能自動化安排監(jiān)控任務(wù)。

獲取通知：SQL Server為我們提供的諸如SQL Mail，Database Mail 郵件方式的信息發(fā)送方式，因此我們利用郵件來進行最基礎(chǔ)的獲取通知異常等情況。我在之前的文章提過Databasemail，大家可以做為參考

MSSQL數(shù)據(jù)庫郵件系列一(環(huán)境搭建)

http://www.cnblogs.com/bhtfg538/archive/2009/09/27/1575329.html

MSSQL數(shù)據(jù)庫郵件系列二(SSMS和TSQL)

http://www.cnblogs.com/bhtfg538/archive/2009/09/28/1576061.html

我提到了基本的郵件配置，但是也許我們的內(nèi)網(wǎng)數(shù)據(jù)庫是沒有不能訪問Internet的，如何發(fā)送到我們的監(jiān)控郵箱呢。有朋友可能嘗試會插入一個Mail表，然后在另外一臺能否訪問Internet的內(nèi)網(wǎng)機器讀取該表來發(fā)送郵件，其實我們可以通過簡單的SMTP中繼來實現(xiàn)，我們知道的SMTP其實是個推協(xié)議，一般是發(fā)送到提供商的STMP服務(wù)器，其實我們也能通過發(fā)送到自己的smtp服務(wù)器然后轉(zhuǎn)發(fā)到指定DNS響應(yīng)的SMTP服務(wù)器。

我測試是Windows Server 2008的機器,我們來安裝SMTP服務(wù)器。首先打開

1.服務(wù)器管理器 –> 功能 –> 添加功能 –> SMTP 服務(wù)器。

2.安裝成功以后，在管理工具 –> IIS (6.0）管理器打開設(shè)置。

3.首先點擊域 –> 新建域 –>

（我新建了一個SMTP.QQ.COM的域，所以我要轉(zhuǎn)發(fā)郵件到QQ郵箱）。

我們看到QQ提供的SMTP.QQ.COM使用的標(biāo)準(zhǔn)的25端口。所以打開WF.MSC 去添加25端口的出站規(guī)則，同時打開入站規(guī)則的25（讓DB服務(wù)器能夠中繼到該SMTP服務(wù)器），不關(guān)閉選項卡。

4.在“出站安全”中->選擇“基本身份驗證”->添加你的郵箱登錄帳號 –>（TSL加密根據(jù)你SMTP服務(wù)器的規(guī)則）來設(shè)置。

5.選中“SMTP Virtual Server #1” –> 右鍵屬性 –> 切換到 “訪問”選項卡 –>在“中繼限制”中單擊“中繼” 添加你對應(yīng)機器的訪問列表。

6.***一步，把你的DatabaseMail帳號設(shè)置成匿名身份驗證,SMTP服務(wù)器選擇你的內(nèi)網(wǎng)SMTP服務(wù)器地址，端口默認(rèn)25，其他都不變動。

如果發(fā)送測試郵件通過說明沒問題了，搞不定可以搜搜資料吧，或者直接提問。那么我也就解決了***個問題“我們?nèi)绾潍@取通知”，采用最基本的郵件方式。

詳細(xì)的監(jiān)控信息：數(shù)據(jù)庫避免不了一些異常狀態(tài)，比如錯誤的腳本導(dǎo)致的異常，空間不夠，磁盤掛了，復(fù)制失敗了等。這里我先提提SQL Server事件。這個意味著SQL Server發(fā)生特定錯誤產(chǎn)生的事件，每個事件都有對應(yīng)的數(shù)據(jù)庫、嚴(yán)重級別、錯誤號、錯誤文本。

打開SSMS->在SQL Server 代理 –>的警告中可以創(chuàng)建警告。一般來說我們關(guān)注大于等于13嚴(yán)重級別的事件。為乙方公司提供服務(wù)時快速的部署也很重要，因此我整理了一些腳本：

 
 
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

上面提供了 13-25嚴(yán)重級別的事件。有了事件，必然的，需要一個操作員或者說是監(jiān)控人員來對獲取事件來轉(zhuǎn)發(fā)郵件，我們可以看到SQL Server 代理中有操作員的選項卡，可以創(chuàng)建操作員：

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

我添加了一個 “Terry Mao” 的操作人員,對應(yīng)郵件地址為10000的郵件地址，然后我們可能需要為所有的事件添加訂閱人員，也就是Terry Mao。

在視圖中，可以查詢到所有創(chuàng)建的警報：

SELECT * FROM msdb.dbo.sysalerts;

當(dāng)然不可能一個去添加訂閱事件，可以創(chuàng)建類似功能的sp：

CREATE PROC dbo.sp_add_all_notification  
    @Operator sysname  
AS 
BEGIN 
DECLARE @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 = 0  
BEGIN 
    EXEC msdb.dbo.sp_add_notification @alert_name = @Name,  
@operator_name = @Operator,  
@notification_method = 1;  
    FETCH NEXT FROM Cur INTO @Name;  
END 
 
CLOSE Cur;  
DEALLOCATE Cur;  
RETURN 0;  
END

這樣就會把所有的事件全部給監(jiān)控起來，其實我就解決了過濾的監(jiān)控列表，但是我們可能會對運行中的SQL Server 比如長時間執(zhí)行的語句，存儲過程進行監(jiān)控來調(diào)優(yōu)。比如，我們給另外一家公司做DBA顧問，就立馬需要獲取有問題的查詢，這個我會在接下來的文章詳細(xì)說明。其中包括Profile的監(jiān)控，SQL Server WMI的監(jiān)控等。

原文鏈接：http://www.cnblogs.com/bhtfg538/archive/2011/01/19/1939190.html

【編輯推薦】

SQL Server 2000刪除實戰(zhàn)演習(xí)
SQL Server存儲過程的命名標(biāo)準(zhǔn)如何進行？
卸載SQL Server 2005組件的正確順序
對SQL Server字符串?dāng)?shù)據(jù)類型的具體描述
SQL Server數(shù)據(jù)類型的種類與應(yīng)用

責(zé)任編輯：彭凡來源：博客園

SQL Server