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 Agent –>的警告中可以建立警告。一般來說我們關注大於等於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 Agent中 有 操作員的選項卡，可以建立操作員：

 

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 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

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