SQL Server Agent and Its Components

SQL Server Agent and Its Components

1) What is SQL server Agent? What are its benefits?

SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, TSQL Statements, SSIS packages, Reports subscriptions etc.

2) What are various components of SQL Server Agent service?

• Jobs
• Schedules
• Operators
• Alerts

3) What is a SQL Server job?

A job is a specified series of actions that SQL Server Agent performs. We can use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can run on one local server or on multiple remote servers.

We can run jobs in several ways:

• According to one or more schedules.
• In response to one or more alerts.
• By executing the sp_start_job stored procedure.

Each action in a job is a job step. For example, a job step might consist of running a Transact-SQL statement, executing an SSIS package, or issuing a command to an Analysis Services server. Job steps are managed as part of a job

4) What is an Operator?

An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. In some enterprises, operator responsibilities are assigned to one individual. In enterprises with multiple servers, many individuals can share operator responsibilities. An operator does not contain security information, and does not define a security principal.

SQL Server can notify operators of alerts through one or more of the following:

• E-mail
• Pager (through e-mail)
• net send

5) What is a Schedule?

A schedule specifies when a job runs. More than one job can run on the same schedule, and more than one schedule can apply to the same job. A schedule can define the following conditions for the time when a job runs:

• Whenever SQL Server Agent starts.
• Whenever CPU utilization of the computer is at a level you have defined as idle.
• One time, at a specific date and time.
• On a recurring schedule.

6) What is an Alert?

An alert is an automatic response to a specific event. For example, an event can be a job that starts or system resources that reach a specific threshold. You define the conditions under which an alert occurs.

An alert can respond to one of the following conditions:

• SQL Server events
• SQL Server performance conditions
• Microsoft Windows Management Instrumentation (WMI) events on the computer where SQL Server Agent is running
• An alert can perform the following actions:
• Notify one or more operators
• Run a job

7) Which database contains information about the jobs and other components which are required for the execution of scheduled jobs by SQL Server Agent?

system database- msdb

8) Which tables can be Queries to get details about the Jobs and its steps?

Below are some examples of the tables in msdb database which contains information about the Jobs and its steps.

• sysjobs

Stores the information for each scheduled job to be executed by SQL Server Agent.

• sysjobsteps

Contains the information for each step in a job to be executed by SQL Server Agent.

9) Which table contains information about the SQL Server jobs scheudule?

• sysjobschedules

It contains schedule information for jobs to be executed by SQL Server Agent

10) Which tables will give you the Job history related information?

dbo.sysjobhistory

11) How can we grant permissions on the SQL Server agent jobs and which roles are available?

SQL Server contains 3 fixed database roles on the MSDB database, which gives administrators fine control over access to SQL Server Agent. The SQL Server Agent node in SSMS is visible only to users in one of these 3 roles (except sysadmins, who can see everything irrespective of role membership). Here is an explanation of the roles, in order from the most restrictive to least restrictive:

SQLAgentUserRole – Users in this role are granted view/edit/delete/execute access to only jobs owned by them. Users in this role cannot view any jobs owned by system administrators, or by users in the other two roles. Grant this role when you want users to only see jobs owned by them.

SQLAgentReaderRole – Users in this role get all the privileges of theSQLAgentUserRole, i.e. they get access to owned jobs. In addition to that, they can also view (but not modify or execute) all jobs on SQL Server Agent, irrespective of ownership. Grant this role when you want users to be able to view, but not execute, all jobs in the system, but modify/execute only jobs owned by them.

SQLAgentOperatorRole – Users in this role get all the privileges of the SQLAgentReaderRole. In addition to that, they can also execute, or enable/disable any job in the system. However, users in this role can modify only owned jobs. Grant this role for super users who can view/execute all jobs on the system.

12) Difference between the permissions of above three roles?

Action	SQLAgentUserRole	SQLAgentReaderRole	SQLAgent OperatorRole
Create/modify/delete	Only owned jobs	Only owned jobs	Only owned jobs
View List	Only owned jobs	All jobs	All jobs
Enable/Disable	Only owned jobs	Only owned jobs	All jobs
View Properties	Only owned jobs	All jobs	All jobs
Edit Properties	Only owned jobs	Only owned jobs	Only owned jobs
Start / Stop	Only owned jobs	Only owned jobs	All jobs
View job history	Only owned jobs	All jobs	All jobs
Delete job history	No	No	Only owned jobs
Change Ownership	No	No	No

13) Is SQL Mail option is available in SQL Server 2012 or not? If not what is the replacement of SQL mail?

SQL Mail has been removed in SQL Server 2012 version. It has been replaced by Database Mail option.

14) What is a Database Mail?

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, our database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.

15) What is the Database Mail Architecture?

Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail.exe). The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers. The external process inserts an item in the Status queue for the outcome of the send operation. Inserting the new entry in the status queue starts an internal stored procedure that updates the status of the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.

16) How to enable Database mail?

Database Mail is not active by default. To use Database Mail, We must explicitly enable Database Mail by using either the Database Mail Configuration Wizard, the sp_configure stored procedure, or by using the Surface Area Configuration facet of Policy-Based Management.

17) What is a Database Mail Account?

A Database Mail account contains the information that Microsoft SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server.

A Database Mail supports three methods of authentication to communicate with an SMTP server:

Windows Authentication: Database Mail uses the credentials of the SQL Server Database Engine Windows service account for authentication on the SMTP server.

Basic Authentication: Database Mail uses the username and password specified to authenticate on the SMTP server.

Anonymous Authentication: The SMTP server does not require any authentication. Database Mail will not use any credentials to authenticate on the SMTP server.

18) What is a Database Mail Profile?

A Database Mail profile is an ordered collection of related Database Mail accounts. Applications that send e-mail using Database Mail specify profiles, instead of using accounts directly. Separating information about the individual e-mail servers from the objects that the application uses improves flexibility and reliability: profiles provide automatic failover, so that if one e-mail server is unresponsive, Database Mail can automatically send mail to another e-mail server. Database administrators can add, remove, or reconfigure accounts without requiring changes to application code or job steps.

Profiles also help database administrators control access to e-mail. Membership in the DatabaseMailUserRole is required to send Database Mail. Profiles provide additional flexibility for administrators to control who sends mail and which accounts are used.

19) What is the difference between Public profile and Private Profile?

Public profiles are available for all members of the DatabaseMailUserRole database role in the msdb database. They allow all members of the DatabaseMailUserRole role to send e-mail using the profile.

Private profiles are defined for security principals in the msdb database. They allow only specified database users, roles, and members of the sysadmin fixed server role to send e-mail using the profile. By default, a profile is private, and allows access only to members of the sysadmin fixed server role. To use a private profile, sysadmin must grant users permission to use the profile. Additionally, EXECUTE permission on the sp_send_dbmail stored procedure is only granted to members of the DatabaseMailUserRole. A system administrator must add the user to the DatabaseMailUserRole database role for the user to send e-mail messages.

20) What is a SQL Server Agent Proxy Account?

SQL Server agent jobs run with the account assigned to SQL Agent service. In case if we have to perform some task which requires some elevated permissions we can use SQL Server Agent Proxy. Proxy is about having additional security. We can specify the job step for which we need a different security context to be using the security context of the specified proxy.

21) Can we have multiple schedules for a single job?

Yes, we can run a job with multiple schedules.

22) Is it possible to get and change all the schedules information of all the jobs in a single window? If yes then from where?

Yes, we can list all the schedules of all the jobs from below option.

Click on SQL Server Agent à Jobsà Right Click on Jobsà Click on Manage Schedules

23) Is it possible to run an SSIS package from a job step? How?

Yes, it is possible to run an SSIS package by selecting the Type as SQL Server Integration Services Package in the Step.

24) Can we run Operating system command in the SQL Server jobs?

Yes, it is possible to run it using Operating System Type in the job step.

25) What is Notification property in SQL Server job properties?

These settings can be used to setup the notification with regard to failure or success of the job.

E-mail

Select this option to send e-mail when the job completes. After selecting this option, choose the operator to notify and the condition that will trigger the notification: When the job succeeds; When the job fails; or When the job completes.

Page

Select this option to send e-mail to an operator’s pager when the job completes. After selecting this option, specify the operator to notify and the condition that will trigger the notification: When the job succeeds; When the job fails; or When the job completes.

Net send

Select this option to use net send to notify an operator when the job completes. After selecting this option, specify the operator to notify and the condition that will trigger the notification: When the job succeeds; When the job fails; or When the job completes.

Write to the Windows Application event log

Select this option to write an entry in the application event log when the job completes. After selecting this option, specify the condition that will cause the entry to be written: When the job succeeds; When the job fails; or When the job completes.

Automatically delete job

Select this option to delete the job when the job completes. After selecting this option, specify the condition that will trigger deletion of the job: When the job succeeds; When the job fails; or When the job completes.

26) What are maintenance jobs for a SQL Server database?

Jobs which runs Database maintenance tasks like”

Index Rebuild\Reorganise tasks

Update stats tasks

Database shrink activities

27) Sometimes there is no History available for the Failure or Success of a job? What could be the reason for the same?

By default SQL Server doesn’t hold a very long job history for your server. The defaults that SQL Server comes with are as follows:

That is the reason sometimes we are not able to get the job history details due to purging of the job history.

28) How can we change the Job History Retention?

Yes, We can change the retention of the job history. Right Click on SQL Server Agent à Click on Properties à History. There are options to set the maximum job history log size.

29) What could be the reason if your Database Mail working fine but No SQL Agent Alerts are working?

Database Mail is part of the SQL Server Service which means that the SQL Server Agent cannot automatically know that it’s present and active and which settings to use. Therefore although you may have set up your Operators and are able to select them within the Notifications tab of a scheduled task, you still have to tell the SQL Server Agent which email account and profile it can use.

Check on “Enable mail profile” option and mention the profile name which needs to be used under the Alert System tab of SQL Server agent properties and Restart the SQL Server agent service.

30) What is Fail Safe Operator? How can we enable this?

We can enable a fail-safe operator feature that will receive all emails in the event that SQL Server Agent cannot communicate with the system tables in the msdb database. This is accomplished by recording the information about the fail-safe operator in the registry. The fail-safe operator will also receive notifications if you have scheduled operators to only receive notifications during certain time periods and a notification occurs outside of that range. For example, if no operators are set to receive notifications on Sunday, the notification will automatically go to the fail-safe operator.

To enable a fail-safe operator, right-click SQL Server Agent and select Properties from the context menu. Then select the Alert System page as shown in the following image.

Select the Enable fail-safe operator checkbox and then choose the appropriate operator and notification method. Click OK to save the changes.

31) What is the reason when SQL Server Agent fails to start because of the error 15281 which is a very common error?

When you start to restart SQL Agent sometimes it will give following error.

SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, search for ‘Agent XPs’ in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

To resolve this issue we have to enable Agent XPs option at the SQL Server instance level using below script.

sp_configure 'show advanced options', 1;

GO

 RECONFIGURE;

 GO

 sp_configure 'Agent XPs', 1;

 GO

 RECONFIGURE

 GO