詳解DBA如何安排SQL Server數據庫自動監控

作者: Sai~ 來源: 博客園發布時間: 2011-01-24 11:50 閱讀: 2829 次推薦: 0 原文鏈接 [收藏]

摘要：SQL Server本身給廣大DBA提供了不少自動化監控的手段，只是苦于對基礎監控架構不熟悉，不夠了解而使很多DBA出現重復無效勞動。今天我們就講講如何安排SQL Server數據庫自動監控，減輕大家的工作負擔。

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

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

MSSQL數據庫郵件系列一(環境搭建)

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

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

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

　　1.服務器管理器功能添加功能 SMTP 服務器。

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

　　3.首先點擊域新建域（我新建了一個SMTP.QQ.COM的域，所以我要轉發郵件到QQ郵箱）。

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

　　4.在出站安全中-選擇基本身份驗證-添加你的郵箱登錄帳號（TSL加密根據你SMTP服務器的規則）來設置。

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

　　6.最后一步，把你的DatabaseMail帳號設置成匿名身份驗證,SMTP服務器選擇你的內網SMTP服務器地址，端口默認25，其他都不變動。

　　如果發送測試郵件通過說明沒問題了，搞不定可以搜搜資料吧，或者直接提問。那么我也就解決了第一個問題我們如何獲取通知，采用最基本的郵件方式。

　　詳細的監控信息：數據庫避免不了一些異常狀態，比如錯誤的腳本導致的異常，空間不夠，磁盤掛了，復制失敗了等。這里我先提提SQL Server事件。這個意味著SQL Server發生特定錯誤產生的事件，每個事件都有對應的數據庫、嚴重級別、錯誤號、錯誤文本。

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

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嚴重級別的事件。有了事件，必然的，需要一個操作員或者說是監控人員來對獲取事件來轉發郵件，我們可以看到SQL Server 代理中有操作員的選項卡，可以創建操作員：

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 的操作人員,對應郵件地址為10000的郵件地址，然后我們可能需要為所有的事件添加訂閱人員，也就是Terry Mao。

　　在視圖中，可以查詢到所有創建的警報：

SELECT * FROM msdb.dbo.sysalerts;

　　當然不可能一個去添加訂閱事件，可以創建類似功能的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

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