在SQL Server 2005中使用和管理数据库邮件
SQL Server 2005中，出现了一种新的邮件子系统──数据库邮件。数据库邮件与早期的的SQL邮件相比较有了很大的改进。现在你可以建立多个账号和个人资料以满足你不同的应用电子邮件需求。本文中我们将主要讨论建立和治理数据库邮件。
什么是数据库邮件？
数据库邮件是一个邮件队列系统。电子邮件消息存储在msdb数据库里的队列中等待被处理。当一个电子邮件消息到达队列时，将会触发一个外部过程来把队列中的这个消息送到合适的邮件服务器端。邮件一旦被发送，一个装有邮递状态的电子邮件消息就会被邮递回SQL Server。
打开数据库邮件功能项
数据库邮件在模块之外是不可用的。由于SQL Server安全模型缺省把所有选项都关闭了，假如你要想使用它的话就需要使能数据库邮件。你可以使用表面区域配置工具，或者用下面的T-SQL来使能数据库邮件：
sp_configure 'show advanced options', 1;GORECONFIGURE;GOsp_configure 'Database Mail XPs', 1;GORECONFIGUREGO |
建立账号
数据库邮件账号鉴别SQL Server 2005应该如何和SMTP服务器进行通信。这个账号具体说明了电子邮件如何被格式化和发送的。单一的账号将会鉴别单一的SMTP服务器和认证方法。数据库邮件所使用的账号不和SQL Server登录账号相对应。
当建立一个账号时你需要鉴别足够的信息以便使SQL Server 2005能够和SMTP服务器通讯，假如需要的话还要认证。可以参考Books Online获取定义账号的完整选项列表。你可以使用数据库邮件配置向导来建立账号，向导可以在SQL Server Management Studio里的“治理”文件夹“数据库邮件”下找到，或者使用存储过程（SP）“sysmail_add_account_sp”。这里有一个脚本使用了上面提到的存储过程来创建一个和不需要认证的SMTP服务器相通信的账号。
EXECUTE msdb.dbo.sysmail_add_account_sp@account_name = 'Database Administration Account',@description = 'Mail account for used by DBA staff',@email_address = 'ProdServer01@databasejournal.com',@display_name = 'ProdServer01 DBA Mail',@replyto_address = 'Greg.Larsen@databasejournal.com',@mailserver_name = 'mailserver.databasejournal.com'; |
这个账号名为“数据库治理账号”，邮件地址为ProdServer01@databasejournal.com。使用数据库邮件的优点之一就是这个邮件地址不必再是你邮件系统中的一个有效的邮件账号。除此之外，你可以把回复地址和你的数据库邮件账号联系起来。在上面的例子中，我定义了Greg.Larsen@databasejournal.com作为回复地址。所以现在，当某人从我的自动邮件通知处理得到一封邮件时，他们可以回复，这个回复邮件能直接发送到我这里。
有了数据库邮件，你就可以根据你的需要尽可能多地建立邮件账号。一会儿，我将讨论你需要建立多个数据库邮件账号的原因。
建立个人资料并和账号相联系
在能够发送数据库邮件到一个在账号中鉴别过的SMTP服务器之前，你需要把账号和个人资料联系起来，把访问权授予个人资料。数据库邮件个人资料用来提高邮件安全性。有两种个人资料，公开的和私有的。公开的个人资料任何人都可见，它把访问权授予msdb数据库，是msdb数据库中DatabaseMailUserRole中的成员，而私有的个人资料只可以被授权访问私有个人资料的特定用户使用。一个邮件个人资料能够和一个或多个账号相联系。你可以使用数据库邮件配置向导来治理个人资料，或者使用一些和下面类似的T-SQL命令来建立邮件个人资料：
-- Create a Database Mail profile EXECUTE msdb.dbo.sysmail_add_profile_sp@profile_name = 'Database Administration Profile',@description = 'Mail Profile for use by DBA processes'; |
这里我只是创建了一个名为“数据库治理个人资料”的个人资料。现在你仅仅有了一个刚创建的个人资料，并不意味着你能够使用它来发送邮件。你仍然需要把那个个人资料和至少一个数据库邮件账号及msdb数据库里的一个用户联系起来。为了做到这些我可以执行下面的语句：
-- Add the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp@profile_name = 'Database Administration Profile',@account_name = 'Database Administration Account',@sequence_number =1 ;-- Grant access to the profile EXECUTE msdb.dbo.sysmail_add_principalprofile_sp@profile_name = 'Database Administration Profile',@principal_name = 'ProdServer01', |
第一个EXECUTE语句把我的个人资料和一个账号相联系。第二个EXECUTE语句把我的个人资料和‘ProdServer01’msdb数据库用户相联系。这个用户在他们能够发送邮件之前必须是DatabaseMailUserRole中的成员。既然我把个人资料和一个特定用户联系起来，这个个人资料就是一个私有个人资料。要创建一个公共个人资料你需要把个人资料和“公共”数据库角色相联系。
发送数据库邮件
SQL Server提供了存储过程“sp_send_dbmail”来发送邮件。下面的句法用来调用这个存储过程：
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ][ , [ @recipients = ] 'recipients [ ; ...n ]' ][ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ][ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ][ , [ @subject = ] 'subject' ][ , [ @body = ] 'body' ][ , [ @body_format = ] 'body_format' ][ , [ @importance = ] 'importance' ][ , [ @sensitivity = ] 'sensitivity' ][ , [ @file_attachments = ] 'attachment [ ; ...n ]' ][ , [ @query = ] 'query' ][ , [ @execute_query_database = ] 'execute_query_database' ][ , [ @attach_query_result_as_file = ] attach_query_result_as_file ][ , [ @query_attachment_filename = ] query_attachment_filename ][ , [ @query_result_header = ] query_result_header ][ , [ @query_result_width = ] query_result_width ][ , [ @query_result_separator = ] 'query_result_separator' ][ , [ @exclude_query_output = ] exclude_query_output ][ , [ @append_query_error = ] append_query_error ][ , [ @query_no_truncate = ] query_no_truncate ][ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ] |
正如你所见，这个存储过程支持多个不同的参数。想获取这些参考中每个参数的信息，请参考Books Online。
下面是我如何使用上面创建的私有个人资料来给我发送一封有关自动数据库重索引过程方面的简单的邮件消息的例子。
EXEC msdb.dbo.sp_send_dbmail@profile_name = 'Database Administration Profile',@recipients = 'greg.larsen@databasejournal.com',@body = 'Reindex database process completed successfully',@subject = 'Reindex Database' ; |
由于数据库邮件是以队列方式工作的，当我运行上面的命令时将得到一个“邮件已进入队列”的通知邮件。当执行sp_send_dbmail存储过程时邮件并不是立即被发送，而是存储在msdb数据库中的邮件队列里。存储过程的执行触发一个外部邮件过程（DatabaseMail90.exe）的运行。这个可执行程序读入队列中的邮件，把它发送到合适的邮件服务器上。
账号和个人资料的其他用途
有很多不同的方式来利用数据库邮件答应你建立的多个账号和个人资料。
拥有多个账号的一个明显的优势就是来配置你的数据库邮件个人资料以防其中的一个SMTP服务器出现故障。当添加账号到数据库邮件个人资料时你可以给它们一个序列号。当发送一个新的电子邮件消息时，数据库邮件总是试着先用最低序列号发送。一旦那个账号发送邮件失败的话，数据库邮件就会使用次低的序列号。数据库邮件按这种方式工作直到邮件成功地发送出去或者所有的账号都发送失败。
拥有多个账号和个人资料的另一个有价值的选择是支持拥有不同电子邮件地址邮递的邮件消息。假如你有多个应用需要发送邮件，现在每个应用都可以有它自己的电子邮件地址。这样做有助于自动电子邮件的接收，仅仅看一下电子邮件地址就可区分什么过程发送了这个邮件。
最后，假如你使用私有个人资料的话，你可以把这些个人资料和不同安全原则联系起来。这样做使你能控制哪些msdb用户被答应使用特定的数据库邮件个人资料。
监控数据库邮件
SQL Server在msdb数据库里提供了6种不同的系统视图来监控和返回数据库邮件信息。这些视图可以用来检索msdb数据库里有关所有数据库邮件的状态或者一个特定的电子邮件消息等信息。这些视图用来鉴别哪些邮件被处理了，或者一些邮件消息为什么没有被正确邮递到需求的邮件服务器等方面是非常有用的。这些视图是监控和解决数据库邮件问题的很好的工具。想得到有关这些视图的更多信息，请参考Books Online。
sysmail_allitems – 这个视图返回一个包含了每个被数据库邮件处理过的邮件报文的一行记录的集合。
sysmail_event_log – 这个视图在数据库邮件试图处理一个邮件报文时返回一行Windows或者SQL Server错误信息。
sysmail_faileditems – 这个视图为每个出错的电子邮件报文返回一条记录。
sysmail_mailattachments – 这个视图包含每个发送附件的一行。
sysmail_sentitems – 这个视图包含每个成功发送的电子邮件的一条记录。
sysmail_unsentitems – 这个视图包含每个当前队列待发送或者在发送过程中的邮件的一条
记录。
在MSDB数据库中维护信息
既然所有的电子邮件消息都存储在msdb数据库中,你需要考虑如何治理这些信息.根据你的电子邮件保留政策,你应该定期清理那些不再需要的电子邮件消息. SQL Server 2005提供了两种不同的存储过程来把邮件记录从msdb数据库中移除.
sysmail_delete_mailitems_sp – 这个存储过程从msdb内部数据库邮件表中永久性删除电子邮件消息
sysmail_delete_log_sp – 这个存储过程删除数据库邮件日志消息
Review Books Online for more information on these SP’s.
参考Books Online获取关于这些存储过程的更具体的信息.
下面的T-SQL语句将会删除一个月之前发送的所有邮件:
DECLARE @delete_date datetimeSET @delete_date = dateadd(MM,-1,getdate())EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before=@delete_date |
总结：
在SQL Server 2005中，数据库邮件比起以前版本的SQL Server提供的SQL邮件是一个全新的改进.数据库邮件提供了一个有很多特征的邮件子系统来增强安全性并且使用T-SQL来治理正在发送的邮件.有了SQL Server 2005中的数据库邮件,你不再需要使用CDOSYS来建立七拼八凑的解决方案, CDOSYS使用旧版的SQL邮件不支持的函数来发送邮件。假如你正在寻找一种从T-SQL上支持电子邮件的方式,则必须要看一下数据库邮件。
