Database Mail
It is an enterprise solution for sending mails from SS db engine to SMTP servers.
It uses SMTP server to send mail.
SS 2000 supports SQL Mail which supports MAPI(Messaging Application Programming Interface.) profiles to send email instead of SMTP(Simple Mail Transfer Protocol) server.
We can send text message, query result, file as attachment.
Main components of db mail
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO
Architecture
MSDB Tables related to Database Mail
1. sysmail_profile : Consists of all the profiles information
2. sysmail_account : Consists of SMTP server accounts information.
3. sysmail_server : Consists of SMTP server details.
4. sysmail_allitems : Mail sent status. If the sent_status is 1 then success otherwise failed.
5. sysmail_log : To check the errors raised by Database Mail feature.
6. sysmail_configuration : Consists of system parameters details.
Steps to configure
1. Enable db mail feature at server level
sp_configure 'Database Mail XPs',1
reconfigure
2. Enable service broker in msdb database.
USE [master]
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO
3. Configure mail profile (Profile is a collection of Accounts)
4. Add SMTP account(s)
5. Make the profile as private or public
* Private profile can be used by
* sysadmin members and
* databasemailuserrole members of msdb
6. Set the parameters
7. Send the mail
Example
Go to Management --> Right Click on Database Mail --> Configure Database Mail
Next
Select the following option
Next
Enter profile name = SQLProfile
Click on Add
Select New Account if it was prompted. Otherwise enter the following details
Click OK
Next
Make the profile as public and default as follows
Testing Database Mail
> Right click on Database Mail --> Send Test Email and Enter the following
> Click Send Test E-Mail.
To verify the mail status
> Now verify from Email Box
Sending mail manually by calling sp_send_dbmail
use MSDB
go
sp_send_dbmail @profile_name='SQLProfile',
@recipients='info@optimizesql.com',
@subject='From SQL Server',
@body='Database Mail Testing...'
It is an enterprise solution for sending mails from SS db engine to SMTP servers.
It uses SMTP server to send mail.
SS 2000 supports SQL Mail which supports MAPI(Messaging Application Programming Interface.) profiles to send email instead of SMTP(Simple Mail Transfer Protocol) server.
We can send text message, query result, file as attachment.
Main components of db mail
- sp_send_dbmail
- msdb
- service broker
- DatabaseMail.exe (Present in Binn folder of respective instance)
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO
Architecture
MSDB Tables related to Database Mail
1. sysmail_profile : Consists of all the profiles information
2. sysmail_account : Consists of SMTP server accounts information.
3. sysmail_server : Consists of SMTP server details.
4. sysmail_allitems : Mail sent status. If the sent_status is 1 then success otherwise failed.
5. sysmail_log : To check the errors raised by Database Mail feature.
6. sysmail_configuration : Consists of system parameters details.
Steps to configure
1. Enable db mail feature at server level
sp_configure 'Database Mail XPs',1
reconfigure
2. Enable service broker in msdb database.
USE [master]
GO
ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT
GO
3. Configure mail profile (Profile is a collection of Accounts)
4. Add SMTP account(s)
5. Make the profile as private or public
* Private profile can be used by
* sysadmin members and
* databasemailuserrole members of msdb
6. Set the parameters
7. Send the mail
Example
Go to Management --> Right Click on Database Mail --> Configure Database Mail
Next
Select the following option
Next
Enter profile name = SQLProfile
Click on Add
Select New Account if it was prompted. Otherwise enter the following details
Click OK
Next
Make the profile as public and default as follows
Testing Database Mail
> Right click on Database Mail --> Send Test Email and Enter the following
> Click Send Test E-Mail.
To verify the mail status
> Now verify from Email Box
Sending mail manually by calling sp_send_dbmail
use MSDB
go
sp_send_dbmail @profile_name='SQLProfile',
@recipients='info@optimizesql.com',
@subject='From SQL Server',
@body='Database Mail Testing...'
USE [master]
1.1
Configuring SQL 2012 Database
Mail
Launch
Microsoft SQL Server Management Studio
from the Microsoft SQL Server 2012 program group.
Expand Management
container of the server and double click on Database Mail to launch Database Mail Configuration Wizard.
At the Welcome Screen press Next
Seselect the option Set up Database Mail by performing the following task and click Next button.
Enter the following details then click ok.
Click Public then change the Default Profile to Yes press Next
Leave the default parameters then press Next
Click Finish to complete the steps to configure Database Mail.
Click Close
to close out the database mail wizard.
Configuring SQL Server Agent to send mail using
Database Mail profile created in the previous steps :
- From SQL Server Management Console
right click on the SQL Server Agent icon and choose properties.
- On the properties page, choose Alert
System from the left pane to display the alert notification properties.
- Check the Enable mail profile box and choose Database Mail from the Mail system dropdown list box. Choose the
mail profile you create earlier from the Mail profile dropdown list box.
- In the To Line enter ‘mailaddress’
- Click on OK to save & close the SQL
Server Agent properties.
- Restart SQL Server Agent service for the changes to take effect.
Enabling and
configuring Database Mail in SQL Server using T-SQL
This walk through will execute a number of
stored procedures to accomplish the following tasks:
·
Enable the Database Mail
feature
· Create a profile for Database Mail
· Create an account for use with the profile
· Send a test email to the DBAs email address
· Enable the SQL Server Agent to use Database Mail
· Add the ‘DBAs’ as an operator for notifications
· Create a profile for Database Mail
· Create an account for use with the profile
· Send a test email to the DBAs email address
· Enable the SQL Server Agent to use Database Mail
· Add the ‘DBAs’ as an operator for notifications
Before running any of scripts below, I would
take a backup of your system databases (master, msdb), and ensure you have
sysadmin rights.
1) Enable the Database Mail XPs:
USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
2) Create a new mail profile:
USE msdb
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'admin',
@description = 'Profile for sending Automated DBA Notifications'
GO
3) Create an account for the notifications (changing the email
address, mail server, port as appropriate to your environment):
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLAlerts',
@description = 'Account for Automated DBA Notifications',
@email_address = 'sqlalerts@example.com',
@display_name = 'SQL Alerts',
@mailserver_name = 'smtp.example.com',
@port = 25
GO
4) Add the account to the profile:
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'admin',
@account_name = 'SQLAlerts',
@sequence_number = 1
GO
5) Enable the SQL Server Agent to use Database Mail profile by updating
the registry settings:
USE msdb
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'UseDatabaseMail',
N'REG_DWORD', 1
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'admin'
6) Restart the SQL Server Agent:
At this point, the SQL Server Agent needs to be restarted. If it isn’t, the Database Mail configuration changes will not be picked up, and the Database Mail process will not start / function correctly.
If Database Mail is being configured on a SQL Server cluster, you’ll need to perform this using the Cluster Administrator tool by selecting the appropriate cluster group, then restarting the SQL Server Agent resource for the appropriate instance:
Windows Server 2003:
C:WINDOWSClusterCluAdmin.exe
Windows Server 2008:
C:WindowsSystem32Cluadmin.msc
7) Once the SQL Server Agent has been restarted, try sending an email to test the configuration is working as expected:
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'admin',
@recipients = 'mail@example.com',
@Subject = 'Test Message generated from SQL Server Database Mail',
@Body = 'This is a test message from SQL Server Database Mail'
GO
8) Finally, setup an operator called ‘DBAs’ for the job notifications
(24×7 schedule in this case) for the email address you supplied earlier:
EXEC msdb.dbo.sp_add_operator @name=N'DBAs',
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235959,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235959,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235959,
@pager_days=127,
@email_address=N'sqlalerts@example.com',
@category_name=N'[Uncategorized]'
GO
Set-up job failure
notifications
To generate notifications when a job succeeds, fails, or completes, you can run a stored procedures like below on a job by job basis:
USE msdb
GO
EXEC msdb.dbo.sp_update_job @job_name='System databases - backups.Subplan_1',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'DBAs'
GO
Quick
troubleshooting queries for Database Mail
Check to see if
the service broker is enabled (should be 1):
SELECT
is_broker_enabled FROM sys.databases WHERE name = 'msdb'
Check to see if Database Mail is started in the msdb database:
EXECUTE
dbo.sysmail_help_status_sp
…and start Database Mail if necessary:
EXECUTE
dbo.sysmail_start_sp
Check the status of the mail queue:
sysmail_help_queue_sp
@queue_type = 'Mail'
Check the Database Mail event logs:
SELECT * FROM
sysmail_event_log
Check the mail queue for the status of all items (including sent mails):
SELECT * FROM
sysmail_allitems
select * from
msdb.dbo.sysmail_profile
1) Enable the Database Mail XPs:
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO
USE msdb
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'admin',
@description = 'Profile for sending Automated DBA Notifications'
GO
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLAlerts',
@description = 'Account for Automated DBA Notifications',
@email_address = 'sqlalerts@example.com',
@display_name = 'SQL Alerts',
@mailserver_name = 'smtp.example.com',
@port = 25
GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'admin',
@account_name = 'SQLAlerts',
@sequence_number = 1
GO
USE msdb
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'UseDatabaseMail',
N'REG_DWORD', 1
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'admin'
At this point, the SQL Server Agent needs to be restarted. If it isn’t, the Database Mail configuration changes will not be picked up, and the Database Mail process will not start / function correctly.
If Database Mail is being configured on a SQL Server cluster, you’ll need to perform this using the Cluster Administrator tool by selecting the appropriate cluster group, then restarting the SQL Server Agent resource for the appropriate instance:
Windows Server 2003:
C:WINDOWSClusterCluAdmin.exe
Windows Server 2008:
C:WindowsSystem32Cluadmin.msc
7) Once the SQL Server Agent has been restarted, try sending an email to test the configuration is working as expected:
EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'admin',
@recipients = 'mail@example.com',
@Subject = 'Test Message generated from SQL Server Database Mail',
@Body = 'This is a test message from SQL Server Database Mail'
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBAs',
@enabled=1,
@weekday_pager_start_time=0,
@weekday_pager_end_time=235959,
@saturday_pager_start_time=0,
@saturday_pager_end_time=235959,
@sunday_pager_start_time=0,
@sunday_pager_end_time=235959,
@pager_days=127,
@email_address=N'sqlalerts@example.com',
@category_name=N'[Uncategorized]'
GO
To generate notifications when a job succeeds, fails, or completes, you can run a stored procedures like below on a job by job basis:
GO
EXEC msdb.dbo.sp_update_job @job_name='System databases - backups.Subplan_1',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'DBAs'
GO
Check to see if Database Mail is started in the msdb database:
…and start Database Mail if necessary:
Check the status of the mail queue:
Check the Database Mail event logs:
Check the mail queue for the status of all items (including sent mails):
No comments:
Post a Comment