08 February, 2019

Prerequisite to Config Database Mail

Step 1: Verify SQL Server Agent Service is Running:

Messages in Database Mail are sent by the SQL Server Agent. If the Agent is not running, the messages will be accumulated in the msdb database and sent when the Agent service gets started again.
Use the master..xp_servicecontrol system stored procedure to check the status of the SQL Server Agent process. If the Agent is stopped, you will want to start it in order to send messages through Database Mail.
USE master
Go
EXEC xp_servicecontrol N'QueryState', N'SQLServerAGENT';
Go



Step 2: Verify Service Broker is Enabled in the MSDB database.

If Database Mail is stopped or if Service Broker message delivery is not activated in the msdb database, Database Mail queues messages in the database but cannot deliver the messages. In this case, the Service Broker messages remain in the Service Broker Mail queue. 
-- To check Service Broker is enabled or not in MSDB.
USE Master
Go
SELECT  name,is_broker_enabled
FROM    sys.databases
WHERE   database_id = DB_ID(N'msdb');
Go
-- To enable Service Broker for the msdb database:
USE master
Go
ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
Go



Step3: Collect fully qualified name, port information, Email address and authentication information
for your SMTP server.
Check SMTP Server port  is opened from database mail configured server. If not we need to open it..

How to validate : open cmd and type telnet <smtpservername> <portnumber>



Step 4: Enable the Database Mail feature at server level:

Database Mail Stored procedures are disabled on new installations of SQL Server which makes Database Mail not active by default. Users must explicitly enable these Database Mail stored procedures.
USE master
Go
EXEC sp_configure 'show advanced options', 1
Go
RECONFIGURE
Go
EXEC sp_configure 'Database Mail XPs', 1
Go
RECONFIGURE
Go
EXEC sp_configure 'show advanced options', 0
Go
RECONFIGURE
Go

No comments:

Post a Comment