SQL Server 郵箱告警配置

2021 年 1 月 28 日
筆記
SQL Server, 數據庫

配置數據庫郵件
* 手動啟用數據庫郵件功能
* 配置數據庫郵件
* 測試數據庫郵件
實現 JOB 任務運行狀態的檢測
* 定義操作員
* 新建死鎖警報
* 設置 SQL Server 代理
創建 Profile 及映射
* 添加 Profile
* 映射 Account 和 Profile
配置 Job 發送 Database Log Space 郵件
* 新建 Job
* 測試 Job

一、配置數據庫郵件

1.手動啟用數據庫郵件功能

需執行以下腳本：

exec sp_configure 'show advanced options',1
RECONFIGURE

exec sp_configure 'Database Mail XPs',1
RECONFIGURE With Override

如下圖所示：

2.配置數據庫郵件

連接上數據庫，Management →Database Mail → Configure Database Mail

彈出如下彈窗，點擊 Next

在選擇配置任務中，如果是新增選擇【Set up Database Mail for the first time , select the setup option】，如果是修改，可選擇【Manage Database Mail account and profiles】。

本例選擇【Set up Database Mail for the first time , select the setup option】，先擇後點擊 Next

配置SMTP賬戶（如果已有SMTP賬戶，可在下方列表中看到；如果沒有，點擊添加按鈕添加即可。）

新增數據庫郵件賬戶
　　a. 賬戶名：可根據實際情況新增
　　b. 電子郵件地址：開通POP3/IMAP/SMTP/Exchange/CardDAV/CalDAV服務的郵箱
　　c. 顯示名稱：可根據實際情況新增
　　d. 服務器名稱：如果電子郵件地址是QQ郵箱，smtp.qq.com。
　　e. 此服務器要求安全連接（SSL）,打勾。
　　f. 基本身份驗證：用戶名為電子郵箱地址；密碼為授權碼。

本例配置如下，配置完成後點擊 OK

錄入完成後，可看到 SMTP 賬戶列表

添加 Profile name（本例為 Monitor_Mail），完成後點擊 Next

接下來需要選擇安全公共配置，選中剛才配置的文件名，然後後面默認為配置文件，建議選【No】，可根據實際情況配置。

配置完成後點擊 Next

默認配置文件選【No】後，可根據實際情況修改系統參數。

本例使用默認配置，點擊 Next

配置完成，點擊 Finish

點擊 Close，關閉窗口。

3.測試數據庫郵件

在數據庫界面測試，如下圖：

在收件人處輸入收件人郵箱即可（可添加多個郵箱，郵箱地址之間用分號隔開）

添加完成後，點擊 Send Test E-Mail

出現如下彈窗，因為已成功收到數據庫測試郵件，故點擊 OK

收到郵件如下：

二、實現JOB任務運行狀態的檢測

使用SQL Server時，很多情況下都需要自定義Job進行部分功能的實現，而大部分時間是採取凌晨或者非業務期進行工作。

因而Job的運行結果的檢測便形成了一個需要跟蹤的問題，比如有時候N個Job的運行，只有幾個出現問題，並且不確定的此Job發生在那個機器上，所以自動化運維的重要性就不言而喻了。

對於上面問題的解決，SQL Server提供了很簡單的配置便可以實現。

1 . 定義操作員

SQL Server Agent → Operators → New Operator

說明：
　　a. 姓名：操作員的姓名，可以是別名。
　　b. 電子郵件名稱：郵箱的地址。
　　c. 尋呼值班計劃：可根據實際情況設置時間

配置完成後點擊 OK

展開操作員列表，可看到操作員已添加成功

2. 新建死鎖警報

接下來，我們設置死鎖（Dead Lock）告警, 如下所示，當然也可以使用UI界面設置。

補充說明：配置前先查詢當前警報等級信息：

select * from msdb.dbo.syscategories

查詢結果如下

select * from msdb.dbo.sysalerts

查詢結果如下

配置語句如下：

USE [msdb]
GO
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.syscategories WHERE name='DBA_MONITORING' AND category_class=2)
BEGIN
 
EXEC msdb.dbo.sp_add_category
    @class=N'ALERT',
    @type=N'NONE',
    @name=N'DBA_MONITORING' ;
 
END
GO
 
IF EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
    EXEC msdb.dbo.sp_delete_alert @name=N'SQL Server Dead Lock Detected';
END
GO
 
 
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysalerts WHERE name='SQL Server Dead Lock Detected')
BEGIN
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Dead Lock Detected', 
        @message_id=1205, 
        @severity=0, 
        @enabled=1, 
        @delay_between_responses=0, 
        @include_event_description_in=1, 
        @category_name=N'DBA_MONITORING', 
        @job_id=N'00000000-0000-0000-0000-000000000000'
END
GO
 
IF NOT EXISTS ( SELECT  *
                FROM    msdb.dbo.sysnotifications
                WHERE   alert_id = ( SELECT id
                                     FROM   msdb.dbo.sysalerts
                                     WHERE  name = 'SQL Server Dead Lock Detected'
                                   ) )
    BEGIN
 
        EXEC msdb.dbo.sp_add_notification @alert_name = N'SQL Server Dead Lock Detected',
            @operator_name = N'Manager', @notification_method = 1;
    END;
GO

如下圖

成功執行後，刷新 Alert 列表

刷新後，展開 Alert 列表可看到新建的告警

3. 設置SQL Server代理

SQL Server Agent → Properties

說明：
　　a. 選擇是否啟用配置文件，郵件系統選擇了數據庫郵件，郵件配置文件可選擇具體的配置。
　　b. 啟用防故障操作員

配置 Alert System 選項頁，配置完成後點擊 OK

補充：其他選項頁均保留默認設置，截圖如下

三、創建 Profile 及映射

1.添加 Profile

exec msdb..sysmail_add_profile_sp
@profile_name = 'dba_profile'-- profile 名稱
,@description  = 'dba mail profile'-- profile 描述
,@profile_id   = null

如圖

2.映射 Account 和 Profile

exec msdb..sysmail_add_profileaccount_sp  
@profile_name    = 'dba_profile'-- profile 名稱
,@account_name    = 'Monitor'-- account 名稱
,@sequence_number = 1-- account 在profile中順序

如圖

四、配置 Job 發送 Database Log Space 郵件

1.新建Job

SQL Server Agent → Job → New Job

General 選項頁配置如下

配置 Step 選項頁，點擊 New

New Job Step 配置如下圖

其中代碼段為：

EXEC sp_send_dbmail
    @profile_name = 'dba_profile',
    @recipients = '[email protected]', //實際配置時需將 [email protected] 更換成實際郵箱地址
    @subject = 'Data Log Space',@query='dbcc sqlperf(logspace)'