[PL] SQL Server – Migracja usługi Database Mail

Jakiś czas temu na forum portalu WSS.pl padło pytanie, czy można przenieść konfigurację usługi Database Mail na SQL Server 2005 / 2008 / 2008 R2. Odpowiedziałem wówczas, że pewnie można to zrobić skryptując dane z tabel w bazie msdb. Postanowiłem, że napiszę kod do takiego skryptowania. Oto on:

set nocount on;
print '-- *** Enabling DBMail ***

';
print 'EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE;
GO
EXEC sp_configure ''Database Mail XPs'', 1;
RECONFIGURE;
GO

';
print '-- *** Configuration ***';
select 'EXEC msdb.dbo.sysmail_configure_sp
  @parameter_name = ' + QUOTENAME(paramname, '''') + ',
  @parameter_value = ' + QUOTENAME(paramvalue, '''') + ',
  @description = ' + ISNULL(QUOTENAME(description, ''''),'NULL') + ';'
from msdb.dbo.sysmail_configuration;
print '-- *** Profiles ***';
select 'EXEC msdb.dbo.sysmail_add_profile_sp
  @profile_name = ' + QUOTENAME(name, '''') + ',
  @description = ' + ISNULL(QUOTENAME(description, ''''),'NULL') + ';'
from msdb.dbo.sysmail_profile;
print '-- *** Accounts ***';
select
  'EXEC msdb.dbo.sysmail_add_account_sp
  @account_name = ' + QUOTENAME(a.name, '''') + ',
  @description = ' +  ISNULL(QUOTENAME(a.description, ''''),'NULL') + ',
  @email_address = ' + QUOTENAME(a.email_address, '''') + ',
  @replyto_address = ' + ISNULL(QUOTENAME(a.replyto_address, ''''),'NULL') + ',
  @display_name = ' + ISNULL(QUOTENAME(a.display_name, ''''),'NULL') + ',
  @mailserver_name = ' + QUOTENAME(s.servername, '''') + ',
  @mailserver_type = ' + QUOTENAME(s.servertype, '''') + ',
  @port = ' + CONVERT(varchar(20), s.port) + ',
  @username = ''<ACCOUNT ' + a.name + ' - USERNAME,,>'',
  @password = ''<ACCOUNT ' + a.name + ' - PASSWORD,,>'',
  @use_default_credentials = ' + CONVERT(char(1), s.use_default_credentials) + ',
  @enable_ssl = ' + CONVERT(char(1), s.enable_ssl) + ',
  @account_id = ' + CONVERT(char(1), s.account_id) + ';'
from msdb.dbo.sysmail_account a
inner join msdb.dbo.sysmail_server s
on a.account_id = s.account_id;
print '-- *** Add accounts to profiles ***';
select
  'EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
  @profile_name = ' + QUOTENAME(p.name, '''') + ',
  @account_name = ' + QUOTENAME(a.name, '''') + ',
  @sequence_number = ' + CONVERT(varchar(20), pa.sequence_number) + ';'
from msdb.dbo.sysmail_profileaccount pa
inner join msdb.dbo.sysmail_account a
on pa.account_id = a.account_id
inner join msdb.dbo.sysmail_profile p
on pa.profile_id = p.profile_id
print '-- *** Grant access to the profile to the DBMailUsers role ***';
select
  'EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
  @profile_name = ' + QUOTENAME(pr.name, '''') + ',
  @principal_id = ' + CONVERT(varchar(20), p.principal_id) + ',
  @is_default = ' + CONVERT(char(1), s.is_default) + ';'
from msdb.dbo.sysmail_principalprofile s
inner join msdb.sys.database_principals p
on s.principal_sid = p.sid
inner join msdb.dbo.sysmail_profile pr
on s.profile_id = pr.profile_id;

Po kolei:

• na początek skryptuję wywołanie procedury sp_configure i włączenie samej usługi Database Mail,
• następnie skryptuję serię wywołań procedury msdb.dbo.sysmail_configure_sp, która ustawia opcje Database Mail’a,
• w kolejnym kroku skryptowane są kolejno: profile (wywołania msdb.dbo.sysmail_add_profile_sp), konta (msdb.dbo.sysmail_add_account_sp) oraz przypisania kont do profili (wywołania msdb.dbo.sysmail_add_profileaccount_sp),
• na sam koniec skryptowane jest dodawanie odpowiednich użytkowników do roli DBMailUsers (wywołania msdb.dbo.sysmail_add_principalprofile_sp).

Użycie kodu wygląda następująco:

1. Używając Management Studio uruchom kod na instancji, z której ustawienia Database Mail chcesz zeskryptować. Wynik zapisuj do tekstu.
2. Skopiuj wynik zapytania do nowego okna skryptu w Management Studio.
3. Zastąp niezbędne parametry (wciskając Ctrl+Shift+M i wypełniając kolumnę Value) odpowiednimi nazwami użytkowników oraz hasłami (danych wrażliwych, takich jak hasła, oczywiście nie trzymamy w skryptach).
4. Uruchom uzupełniony kod na instancji docelowej.
5. Sprawdź, czy działa wysyłanie wiadomości e-mail z wykorzystaniem usługi Database Mail.

Jak dotąd powyższy kod się sprawdzał. Od jakiegoś czasu używam go produkcyjnie. W razie problemów, proszę o kontakt.