SQL 郵件配置篇

在我們運維工作中，經常要對備份，ETL等作業進行監控，這時我們需要用到SQL SERVER自帶的郵件服務器，其原理，我在這么里不多說，直接來實戰，下面是我對服務器配置源碼，分享給大家，希望對大家有幫助。

exec sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE  
go
exec sp_configure 'database mail xps',1 
RECONFIGURE WITH OVERRIDE  
go--2.創建郵件帳戶信息 
EXEC msdb..Sysmail_add_account_sp@ACCOUNT_NAME ='OCTMamiETL',-- 郵件帳戶名稱    @EMAIL_ADDRESS ='OCTMamiETL@163.com',-- 發件人郵件地址     @DISPLAY_NAME ='系統管理員',-- 發件人姓名  @REPLYTO_ADDRESS =NULL,@DESCRIPTION = NULL,@MAILSERVER_NAME = 'SMTP.163.COM',-- 郵件服務器地址    @MAILSERVER_TYPE = 'SMTP',-- 郵件協議      @PORT =25,-- 郵件服務器端口  @USERNAME = 'OCTMamiETL@163.com',-- 用戶名  @PASSWORD = 'ABC123',-- 密碼   @USE_DEFAULT_CREDENTIALS =0,@ENABLE_SSL =0,@ACCOUNT_ID = NULLGO--3.數據庫配置文件
IF EXISTS(SELECT nameFROM   msdb..sysmail_profileWHERE  name = N'ETLErrorProfileLog')BEGINEXEC msdb..Sysmail_delete_profile_sp @profile_name='ETLErrorProfileLog'ENDEXEC msdb..Sysmail_add_profile_sp@profile_name = 'ETLErrorProfileLog',-- profile 名稱  @description = '數據庫郵件配置文件',-- profile 描述    @profile_id = NULLgo--4.用戶和郵件配置文件相關聯
EXEC msdb..Sysmail_add_profileaccount_sp@profile_name = 'ETLErrorProfileLog',-- profile 名稱   @account_name = 'OCTMamiETL',-- account 名稱     @sequence_number = 1 -- account 在 profile 中順序
--5.發送文本測試郵件
EXEC msdb..Sp_send_dbmail@profile_name='ETLErrorProfileLog',@recipients='OCTMamiETL@163.com',--收件人@subject='Test title this is test ',@body=N'z中文郵件內容  中文郵件內容'go-----------------------------------------------------------------------------------
/*功能說明：啟用警報系統里面的郵件配置參數說明: xp_instance_regwrite 修改注冊表
*/
EXEC msdb.dbo.Sp_set_sqlagent_properties @email_save_in_sent_folder=1EXEC master.dbo.Sp_mssetalertinfo @pagersendsubjectonly = 0 --啟用警報系統 【在通知消息中包含電子郵件的正文】EXEC master.dbo.Xp_instance_regreadN'HKEY_LOCAL_MACHINE',--啟用警報系統 【啟用郵件配置文件】N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',N'DatabaseMailProfile' ---郵件系統【選擇數據庫郵件】EXEC master.dbo.Xp_instance_regwriteN'HKEY_LOCAL_MACHINE',N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',N'UseDatabaseMail',N'REG_DWORD',EXEC master.dbo.Xp_instance_regwriteN'HKEY_LOCAL_MACHINE',--啟用警報系統 【啟用郵件配置文件】N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',N'DatabaseMailProfile',---郵件系統【選擇數據庫郵件】N'REG_SZ','ETLErrorProfileLog' ---郵件配置文件【郵件配置里面的SQLMailProfile】   -----------------------------------------------------------------------------------
DECLARE @Sys_OperatorsName VARCHAR(100)SET @Sys_OperatorsName='BSMicheal'IF EXISTS (SELECT nameFROM   msdb.dbo.sysoperatorsWHERE  name = @Sys_OperatorsName)BEGINEXEC msdb..Sp_delete_operator @name = @Sys_OperatorsName -- 操作員ENDEXEC msdb.dbo.Sp_add_operator@name = @Sys_OperatorsName,@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 = 127,@email_address = 'OCTMamiETL@163.com',-----僅可以對 SQL Mail 使用電子郵件別名。必須對數據庫郵件使用電子郵件地址。@pager_address = N'',@netsend_address = N''/*功能說明： 在Job中添加操作員的操作參數說明:  @notify_level_email指定何時將該作業的項放入 Microsoft Windows 應用程序日志。eventlog_level 的數據類型為 int 【0 從不 1 成功時 2 失敗時 3 始終】         修改說明：Create by LY on 2011-010-10
*/
IF EXISTS (SELECT 1FROM   msdb.dbo.sysjobsWHERE  name = 'dad')BEGINEXEC msdb.dbo.Sp_update_job@job_name = 'dad',---對應的作業名稱@notify_level_email = 2,@notify_level_netsend = 2,@notify_level_page = 2,@notify_email_operator_name = 'BSMicheal' ---對應的操作員END;

?

當然，在配置過程中會遇到很多奇葩問題，我在這暫時不窮舉了，總之一點，明白配置步驟，問題都不大，下面我說一下我覺得比較重要一個方面，權限控制，很容易被人忽略，因為我們數據庫服務器不是本人配置，在配置的時候權限分配的不是到位，所以配置這個郵件服務功能有點曲折。

當我們調用作業用操作員發送的時候，發現怎么都發送不出去，后來在調試郵件中我發現了這行信息：

我就考慮到是不是該用戶的權限不夠，按照我正常的運維經驗來看的話，配置SQL SERVER的時候這個用戶所屬的角色應該包含SYSADMIN這個的，所以我查看了該用戶所屬權限，果然，角色分配不對，后將其調整，作業可以正常發送郵件了。