Migrating SQL Server Database Mail Settings
Migrating SQL Server Database Mail profiles, accounts and settings can be a complex and time-consuming process. This article will provide an overview of what SQL Server Database Mail is, how to migrate SQL Server Database Mail profiles, and how to migrate SQL Server Database Mail accounts.
What is SQL Server Database Mail?
SQL Server Database Mail is a feature that allows you to send e-mail messages from within SQL Server. It includes several components, such as the Database Mail Configuration Wizard, which helps you set up the various components of Database Mail. There are also two stored procedures, sp_send_dbmail and sp_configure_dbmail, which are used to configure Database Mail and to send e-mail messages, respectively.
Scenario
Last weekend I was working on an on-premises to Azure migration. As per the plan, we migrated all databases and other required database objects from an on-premises server to an Azure server. But I faced difficulties while migrating DB mail profiles, accounts, and configuration. I found a couple of profiles and accounts on-premises servers and if we start manually recreating the same it will be time-consuming and error-prone. So I asked for support from a senior database administrator. He provided me with a script and show me how to do the task.
Solution
All we need to do is run this script in a new query window,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
USE msdb GO SET NOCOUNT ON GO SELECT 'exec msdb.dbo.sysmail_add_profile_sp ''' + name + ''' , ''' + isnull(description, name) + '''' FROM sysmail_profile SELECT 'EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = ''' + name + ''' , @description= ''' + description + ''' , @email_address = ''' + email_address + ''' , @display_name = ''' + display_name + ''' , @mailserver_name = ''smtp.xxxx.com'' , @port = 587 , @enable_ssl = 0 , @username = ''xxxxxx'' , @password = ''xxxxxx'' ' FROM sysmail_account SELECT 'exec msdb.dbo.sysmail_add_profileaccount_sp @profile_name = ''' + p.name + ''' , @account_name = ''' + a.name + ''' , @sequence_number = ''' + convert(VARCHAR(10), pa.sequence_number) + '''' FROM sysmail_profileaccount pa INNER JOIN sysmail_account a ON a.account_id = pa.account_id INNER JOIN sysmail_profile p ON p.profile_id = pa.profile_id |
Note: Below information, we have to provide before executing the script.
@mailserver_name = ”smtp.xxxx.com” , [Name of the smtp we have to mention here]
@port = 587 , [Port for smtp we have to mention here]
@enable_ssl = 0 , [SSL need to be use or not we have to mention here zero or one]
@username = ”xxxxxx” , [User name/email we have to mention here]
@password = ”xxxxxx” ‘ [Password for the User name/email we have to mention here]
Steps
To run a script on a Microsoft SQL Server using SSMS.
- Open Microsoft SQL Server Management Studio
- Select [New Query] from the toolbar
- Copy the ‘Example Query’
- Select the database to run the query against
- Paste the ‘Example Query’ into the query window
- Click the [Execute] button
Result
Creating profiles
Creating accounts
Adding profiles with accounts
