Dbmail and Automation

Dbmail and Automation

Introduction:

               DB Mail is an enterprise solution for sending mails from SQL Server db engine to SMTP servers. SQL Server db applications can communicate with users through Email system. DB Mail is introduced in 2005. It provides the features like scalabillity, security and reliability. DB mail can be used to notify the users or administrators regarding the events raised in SQL Server.

             Automation is one of another feature of SQL Server. we can schedule most of the features of SQL Server in order to reduce workload of administrator.  

1: What is Service Broker? How to Enable it in MSDB?

            It is to establish communication b/w the SQL Server engine and the DB mail engine we need a service broker. It submits the messages to the mail engine. (or) It is a buffering and queuing mechanism maintained by the service broker in SQL Server.

       Enable service broker is

                   ALTER DATABASE [MSDB] SET ENABLE_BROKER WITH NO_WAIT

                                GO

2: How to Enable db mail feature at server level?

             SP_CONFIGURE 'Database Mail XPs', 1

                  reconfigure

3: How to check whether db mail was configured or not?

               sp_configure --> Check Database mail XPs --> 1 or 0 ( If 1 - Enable, 0 - Disable)

4: What are the main components of DB mail and Define it?

             Components of DB mail are

1. sp_send_dbmail: It is asystem defined stored procedure which is used by SQL Server to send Email by using DB mail feature.
2. MSDB Database: It consists of all stored procedure, system tables and db role related to DB mail
3. Service Broker:
4. databasemail.exe: This file is present in Binn folder of respective instance. it is a db mail engine.

5: What are the steps to configure DB mail?

1. First you have to  Enable db mail feature at server level
2. After that Enable Service Broker in MDB db
3. then configure mail profile
4. Add SMTP account(s)
5. Make the profile as private(It can be used by Sysadmin and databasemailuserrole members of MSDB) or public
6. set the parameters
7. send the mail

6: How to verifying whether the mail was send successfully or not?

           Take a new query and execute the below command

                         SELECT * FROM sysmail_allitems

      after that check sent_status column value for your mail.

7: What are the MSDB tables related to DB mail?

1. sysmail_profile: It consists of all the profile information
2. sysmail_account: It consists of SMTP server accounts information
3. sysmail_allitems: It consists of mail sent status. If sent_status is 1 then mail is success otherwise failed.
4. sysmail_configuration: It consists of system parameter details.

8:What are the stored procedures related to DB mail?

1. EXEC msdb.dbo.sysmail_help_status_sp: To confirm the db mail activation is started
2. EXEC msdb.dbo.sysmail_start_sp: To start db mail process manually.
3. sysmail_stop_sp/sysmail_start_sp: To stop and start mail queue.
4. sysmail_delete_log_sp: stored procedures to permanently delete entries from the DB mail log.

Automation:

9: What is a JOB ?

     It is used to implement a series of tasks, which should be performed automatically. JOB can consists of one or more steps.

We can schedule the job.

• To run Continuously
• On demand
• On Schedule

10:  I have configured backup job to run daily at 12:00AM. what may be the scenarios when job fails?

1.  Agent service was not running
2. Network problem
3. Server is busy
4. Disk Full or No space available
5. MSDB is not online
6. DNS server was not running

11: What is Job Activity Monitor?

       It is graphical tool which displays all the jobs and their status from sysjobactivity table. with this tool.

• we can view properties of job, history, modify job settings, currently running jobs, failed jobs, last out come etc..
• For this go to SQL Server agent --> double click on job activity monitor.

12: What is Bulk copy program?

        BCP is used to import and export data from a table into file and vice versa.

13:What is mean by an Operator?

       Operators are people who will receive the messages from SQL Server db engine when job fails or completes.

14: What is mean by an Alert and what are the types of alerts?

         Alerts are important components of SQL agent which provides pro-active database management. Before the problem occurs can start troubleshooting with the help of alerts. when alert is fired it can call a job as well as submit the response to operator.

              SQL Server supports 3 types of Alerts.

1. Performance condition alerts (These alerts are fired depends on threshold values)
2. Event alerts ( These alerts are fired when a run time error occurs)
3. WMI alerts

15: Summary of Automation?

1.  Automation reduces task of administration and provides pro-active db management .
2. It is implemented by SQL Server Agent service and complete information is stored in MSDB database.
3. We can implement automation  using

• Jobs
• Alerts
• Operators