監控SQL Server資料庫異常鏡像狀態發警示郵件

標籤：database   mirroring   sp   

監控SQL Server資料庫異常鏡像狀態發警示郵件

 

在部署了資料庫鏡像之後，我們需要監控參與鏡像的主要資料庫和鏡像資料庫的狀態，如果狀態異常，發送警示郵件。那麼這個指令碼需要在主和鏡像伺服器上都運行。

 

目錄檢視sys.database_mirroring對SQL Server執行個體上的每個資料庫都包含一行（包括系統資料庫和未配置鏡像的資料庫），當然也包含所有鏡像資料庫的狀態資訊。我們可以查詢該目錄檢視，對於每個異常狀態的鏡像資料庫觸發警示郵件。筆者的環境配置的是非同步鏡像，依賴於手動容錯移轉。

 

前提條件：

1. 配置好資料庫郵件，有正確的Profile。

2. 有許可權發送郵件的有效Login，需要是msdb資料庫中DatabaseMailUserRole角色成員。

3. 一對用於監控的鏡像資料庫。

 

DECLARE@state VARCHAR(30)DECLARE@DbMirrored INTDECLARE@DbId INTDECLARE@String VARCHAR(100)DECLARE@databases TABLE (DBid INT, mirroring_state_desc VARCHAR(30))-- get status for mirrored databasesINSERT@databasesSELECTdatabase_id, mirroring_state_descFROMsys.database_mirroringWHEREmirroring_role_desc IN (‘PRINCIPAL‘,‘MIRROR‘)ANDmirroring_state_desc NOT IN (‘SYNCHRONIZED‘,‘SYNCHRONIZING‘)-- iterate through mirrored databases and send email alertWHILEEXISTS (SELECT TOP 1 DBid FROM @databases WHERE mirroring_state_desc IS NOT NULL)BEGINSELECT TOP 1 @DbId = DBid, @State = mirroring_state_descFROM @databasesSET @string = ‘Host: ‘[email protected]@servername+‘.‘+CAST(DB_NAME(@DbId) AS VARCHAR)+ ‘ - DB Mirroring is ‘[email protected] +‘ - notify DBA‘EXEC msdb.dbo.sp_send_dbmail ‘valid_mail_profile‘, ‘[email protected]‘, @body = @string, @subject = @stringDELETE FROM @databases WHERE DBid = @DbIdEND--also alert if there is no mirroring just in case there should be mirroring :)SELECT@DbMirrored = COUNT(*)FROMsys.database_mirroringWHEREmirroring_state IS NOT NULLIF@DbMirrored = 0BEGINSET @string = ‘Host: ‘[email protected]@servername+‘ - No databases are mirrored on this server - notify DBA‘EXEC msdb.dbo.sp_send_dbmail ‘valid_mail_profile‘, ‘[email protected]‘, @body = @string, @subject = @stringEND

 

依賴於手動容錯移轉。將以上指令碼放到主伺服器和鏡像伺服器上的作業裡，每5分鐘執行一次。

本文出自 “SQL Server Deep Dive” 部落格，請務必保留此出處http://ultrasql.blog.51cto.com/9591438/1906242

監控SQL Server資料庫異常鏡像狀態發警示郵件