如何使用T-SQL語句配置SQL數據庫郵件功能

平常我們大都是使用GUI圖形界面的方式來配置數據庫郵件，但是如果需要一口氣在幾十台SQL服務器上配置數據庫郵件呢？
我們可以先使用T-SQL語句制作好一份配置數據庫郵件的腳本；然後把相關的郵件信息和配置文件信息填入腳本中；最後將將腳本拷貝到每台SQL服務器上執行，即可快速完成批量的郵件配置工作。

【開啟數據庫郵件功能】

1. 檢查數據庫當前實例是否有使用過郵件功能。
2. 如果沒有，需要先開啟數據庫郵件功能。
3. 檢查是否已開啟Service Broker，如果返回值為1則開啟，0為未開啟。

--啟動數據庫郵件XPs--
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
 
--查詢是否開啟Service Broker--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
 
--開啟Service Broker--
ALTER DATABASE msdb SET ENABLE_BROKER
 

【詳細步驟】

創建郵件賬號

1. SMTP服務器名稱（mailserver_name）：賬戶用來發送電子郵件的SMTP服務器的名稱或IP地址，根據實際的SMTP服務器配置信息填寫。
2. 端口號（port）：大多數的SMTP服務器使用的端口號為25，如果無法連通，請咨詢管理員相關的SMTP服務器所提供的端口號。
3. 身份驗證(use_default_credentials): 如果設置值為1則表示使用數據庫引擎服務憑據的Windows身份；如果設置為0則表示開啟基本身份驗證，需要額外添加相對應的賬號和密碼。（username和password）
4. 啟用SSL 通訊加密（enable_ssl）：開啟SSL通訊加密值為1，關閉值為0。

--創建郵件賬戶--
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = '數據庫郵件賬戶:Test',
@description = '相關說明:Test for Notifications',
@email_address = '[email protected]',
@display_name = '顯示名稱:Test',
@mailserver_name = '郵件服務器名稱：smtp.exanple.com',
@port = 25,
@username = '郵件賬號:[email protected]',
@password = '密碼',
@use_default_credentials = 0,
@enable_ssl = 0,
@account_id = 1
GO
 

創建配置文件

--創建新郵件配置--
USE msdb
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = '配置文件名稱：Profile1',
@description = '相關說明:Test1 for Notifications'
GO
 

向配置文件中添加賬戶

1. 填入的配置文件名稱（profile_name）和郵件賬號名（account_name），必須和前兩個步驟中設置的信息相同。
2. 優先級（sequence_number）：如果有多個郵件賬號添加到同一個配置文件中，需要分別設置此處的故障轉移優先級別。

--將賬戶添加到配置文件中：
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = '配置文件名稱:Profile1',
@account_name = '郵件賬戶名:Test',
@sequence_number = 1
GO

設置用戶使用配置文件的權限

1. 此處的配置文件名稱同樣與之前創建的保持一致。
2. 公共配置文件（principal_name）：表示任何一個用戶都能夠使用這個配置文件發送郵件（默認值為public）
3. 默認配置文件（is_default）：表示在不指定任何配置文件時，默認使用這個配置文件來發送郵件。（開啟為1，關閉為0）

--授予任意用戶使用數據庫郵件配置文件的權限--
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = '配置文件名稱:Profile1',
    @principal_name = 'public',
@is_default = 1 ;
 

發送測試郵件

1. 可以使用圖形界面發送測試郵件。

2. 或者使用腳本發送。注意修改相應的配置文件名稱和接收測試郵件的地址。
--發送測試郵件--
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'Profile1',
@recipients = '[email protected]',
@Subject = 'Test Message generated from SQL Server Database Mail',
@Body = 'This is a test message from SQL Server Database Mail'
GO

【相關查詢】

1. 在MSDB系統數據庫中查看相關數據庫郵件信息，便於檢查排錯。
--查詢是否開啟Service Broker--
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
 
--檢查數據庫郵件在msdb中的狀態
EXECUTE dbo.sysmail_help_status_sp
 
--啟動數據庫郵件--
EXECUTE dbo.sysmail_start_sp
 
--檢查郵件隊列的狀態--
sysmail_help_queue_sp @queue_type = 'Mail'
 
--檢查數據庫郵件事件日志--
SELECT * FROM sysmail_event_log
 
--檢查所有項目的狀態郵件隊列--
SELECT * FROM sysmail_allitems
 

【刪除郵件配置】

--刪除配置--
DECLARE
    @account_name sysname,
@profile_name sysname;
 
SELECT
    @account_name = '[email protected]',
    @profile_name = 'Profile1';
 
-- 從郵件配置文件中刪除數據庫郵件帳號
EXEC msdb.dbo.sysmail_delete_profileaccount_sp
    @profile_name = 'Profile1',
    @account_name = '數據庫郵件賬戶:Test';
 
-- 刪除數據庫郵件帳號
EXEC msdb.dbo.sysmail_delete_account_sp
    @account_name = '數據庫郵件賬戶:Test';
 
-- 刪除數據庫郵件配置文件
EXEC msdb.dbo.sysmail_delete_profile_sp
    @profile_name = 'Profile1';
 
-- 禁用數據庫郵件功能
EXEC sys.sp_configure 'Database Mail XPs', 0;
 
RECONFIGURE;
 
EXEC sys.sp_configure N'show advanced options', 0
 
RECONFIGURE;