According to Microsoft, Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results and can also include files from any resource on your network. This article is going to discuss how to identify mostly used profiles/accounts to make side-by-side migration to a new instance easy.
Microsoft lists four benefits for Database Mail: reliability, scalability, security, and supportability. I personally going to add one more: convenience. Database Mail allows you to send emails using T-SQL, i.e. without leaving the database. Those could be simple text-only email notifications or full HTML emails with attachments. To setup Database Mail, you need an SMTP server, essentially an authorized email relay. Once you have it set up and running, you can take advantage of the msdb.dbo.sp_send_dbmail stored procedure (Fig #1) and all its parameters.
Of course under the hood, Database Mail makes the heavy lifting of pushing the request to a mail queue, which activates the DatabaseMail90.exe program that relays the message to the SMTP server (Fig #2).
A Migration Problem
Let’s assume that you have Database Mail setup and working, but after few years you were tasked with a side-by-side migration to a new server. Let’s also assume that you would need to migrate from SQL Server 2017 to SQL Server 2019. In that case you can’t use a convenient database backup and restore option. While a msdb database backup and restore is the easiest way to migrate, unfortunately, this is not an option when the server versions are different.
How do you migrate Database Mail and how do you identify what is needed to migrate?
A Scripted Solution
For the most part, you would need to recreate Database Mail profiles and accounts. Here is a perfect MigrateDbMailSettings.sql solution from FrankGill that does that. At the same time, the following script will help you to identify Database Mail profiles and accounts that are most utilized, and hence worth migrating to a new server.
SELECT TOP 5 files.name as profile_name , accs.name as account_name , accs.email_address , sers.servername , COUNT (*) AS num_emails , MAX (sent_date) AS last_used FROM [msdb].[dbo].[sysmail_mailitems] AS items INNER JOIN [msdb].[dbo].[sysmail_profile] AS files ON files.profile_id = items.profile_id INNER JOIN [msdb].[dbo].[sysmail_profileaccount] AS filesaccs ON filesaccs.profile_id = files.profile_id INNER JOIN [msdb].dbo.sysmail_account AS accs ON accs.account_id = filesaccs.account_id INNER JOIN [msdb].[dbo].[sysmail_server] AS sers ON sers.account_id = accs.account_id WHERE 1 = 1 AND sent_status = 1 AND DATEDIFF (YEAR, GETDATE (), sent_date) BETWEEN 0 AND 2 -- last 2 years GROUP BY files.name , accs.name , accs.email_address , sers.servername --order by sent_date desc ORDER BY COUNT (*) DESC;
The output of this script might look like the following dataset (Fig #3).
You can now use this information to gather information about the profiles that need to be migrated.
This article is partially based on the following resources: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail