首页 > 代码库 > SQL 邮件配置篇

SQL 邮件配置篇

在我们运维工作中,经常要对备份,ETL等作业进行监控,这时我们需要用到SQL SERVER自带的邮件服务器,其原理,我在这么里不多说,直接来实战,下面是我对服务器配置源码,分享给大家,希望对大家有帮助。

exec sp_configure show advanced options,1RECONFIGURE WITH OVERRIDE  goexec 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 name          FROM   msdb..sysmail_profile          WHERE  name = NETLErrorProfileLog)  BEGIN      EXEC 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=Nz中文邮件内容  中文邮件内容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_regread  NHKEY_LOCAL_MACHINE,--启用警报系统 【启用邮件配置文件】  NSOFTWARE\Microsoft\MSSQLServer\SQLServerAgent,  NDatabaseMailProfile ---邮件系统【选择数据库邮件】EXEC master.dbo.Xp_instance_regwrite  NHKEY_LOCAL_MACHINE,  NSOFTWARE\Microsoft\MSSQLServer\SQLServerAgent,  NUseDatabaseMail,  NREG_DWORD,  1EXEC master.dbo.Xp_instance_regwrite  NHKEY_LOCAL_MACHINE,--启用警报系统 【启用邮件配置文件】  NSOFTWARE\Microsoft\MSSQLServer\SQLServerAgent,  NDatabaseMailProfile,---邮件系统【选择数据库邮件】  NREG_SZ,  ETLErrorProfileLog ---邮件配置文件【邮件配置里面的SQLMailProfile】   -----------------------------------------------------------------------------------DECLARE @Sys_OperatorsName VARCHAR(100)SET @Sys_OperatorsName=BSMichealIF EXISTS (SELECT name           FROM   msdb.dbo.sysoperators           WHERE  name = @Sys_OperatorsName)  BEGIN      EXEC 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 1           FROM   msdb.dbo.sysjobs           WHERE  name = dad)  BEGIN      EXEC 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这个的,所以我查看了该用户所属权限,果然,角色分配不对,后将其调整,作业可以正常发送邮件了。

SQL 邮件配置篇