Identify Profiles Actually In-use to Make Database Mail Migration Efficient



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.

Fig #1

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).

msdb sends messages to an SMTP mail 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 as profile_name
        , as account_name
        , accs.email_address
        , sers.servername
        , COUNT (*) AS num_emails
        , MAX (sent_date) AS last_used
        [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
            , accs.email_address
            , sers.servername
--order by sent_date desc

The output of this script might look like the following dataset (Fig #3).

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: