Introduction

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 filesON files.profile_id = items.profile_id
INNER JOIN [msdb].[dbo].[sysmail_profileaccount] AS filesaccsON filesaccs.profile_id = files.profile_id
INNER JOIN [msdb].dbo.sysmail_account AS accsON accs.account_id = filesaccs.account_id
INNER JOIN [msdb].[dbo].[sysmail_server] AS sersON sers.account_id = accs.account_id
WHERE1 = 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