SQL Server Agent Functions

SQL Server Agent

Question Difficulty = Easy

Question 1: What sorts of functionality does SQL Server Agent provide?

• SQL Server Agent is a Windows service that accompanies each instance of SQL Server on a machine for most editions of SQL Server.
• SQL Server Agent is primarily a job scheduler for executing T-SQL, SSIS, DOS, etc. scripts.
• SQL Server Agent is also responsible for defining Operators and Alerts.
• Operators can be associated with Jobs or Alerts, so that particular people (email addresses, pagers, NET SEND) are notified or distribution lists are notified if an issue occurs.
• Alerts can be setup for custom conditions or errors of a particular severity level.
• Additional information - Setting Up Alerts for All Jobs

 Question 2: Do all of the SQL Server 2005, 2008, 2008 R2 editions install the SQL Server Agent service by default?

• No - The SQL Server Express Edition does not have a SQL Server Agent Service.

Question 3: If SQL Server Express does not have a job scheduling interface what alternatives are available?

• Windows Task Scheduler.
• Third party solutions.
  • Additional information - Free Job Scheduling Tool for SQL Server Express and MSDE

 Question 4: True or False - Can a single Job have multiple Job Schedules?

• True.

 Question 5: Which database stores the SQL Server Agent objects?

• MSDB
  • Additional information - Accessing SQL Server Agent Data

Question Difficulty = Moderate

• Question 1: How many options are available to identify failed jobs?
  • Manually review the failed Jobs in Management Studio.
  • Setup an automated process to query the msdb.dbo.sysjobhistory system table to find the failures.
    • Additional information - Finding SQL Server Agent Job Failures

• Question 2: How many of the SQL Server Agent system tables can you name with their associated purpose?
  • sysjobactivity stores data about job activity
  • sysjobhistory stores data for all historical executions of all jobs
  • sysjobs stores data about each job such as the name
  • sysjobschedules stores job schedule information
  • sysjobservers stores server information related to a job
  • sysjobsteps stores specific job step information such as the type of code being issued, the actual code, etc.
  • sysjobstepslogs stores specific job step log information for each run if this is enabled.
    • Additional information - Accessing SQL Server Agent Data

• Question 3: How many of the SQL Server Agent system stored procedures can you name with their associated purpose?
  • sp_help_job
    • This stored procedure returns information about the job.
    • If no parameters are used information is returned for all jobs.
    • If a specific job_id is passed it gives you job information, job step information, schedule information and last run information.
  • sp_help_jobactivity
    • This stored procedure returns information about the status of the job run.
    • If no parameters are used information is returned for all jobs.
  • sp_help_jobcount
    • This stored procedure gives you a count of how many jobs a schedule is tied to.
    • This stored procedure requires either @schedule_id or @schedule_name to be passed as a parameter.
  • sp_help_jobhistory
    • This stored procedure returns all history information for all of the job runs.
    • If no parameters are used information is returned for all jobs.
    • If you also use parameter @mode = N'FULL' this provides additional information about each job step.
  • sp_help_jobs_in_schedule
    • This stored procedure gives you a list of the jobs that are tied to a schedule.
    • This requires either @schedule_id or @schedule_name to be passed as a parameter.
  • sp_help_jobschedule
    • This stored procedure provides jobs schedule information for a particular job.
    • This stored procedure requires either @job_id or @job_name to be passed.
  • sp_help_jobserver
    • This stored procedure provides information about a specific server tied to a job.
    • This stored procedure requires either @job_id or @job_name to be passed.
  • sp_help_jobstep
    • This stored procedure provides information about the job steps for a specific job.
    • This stored procedure requires either @job_id or @job_name to be passed.
  • sp_help_jobsteplog
    • This stored procedure returns information about a specific job step log.
    • This stored procedure requires either @job_id or @job_name to be passed.
  • sp_get_composite_job_info
    • This stored procedure returns combined data for all jobs in the system.
    • If no parameters are used info is returned for all jobs.
  • Additional information - Accessing SQL Server Agent Data

• Question 4: What resources are available to troubleshoot SQL Server Agent?
  • SQL Server Agent Log is a record of all entries written by the SQL Server Agent service.
    • Additional Information - SQL Server 2005 Exposed - Log File Viewer
  • Performance Monitor and Profiler can be setup to monitor the status of a particular job.
    • Additional Information - Collecting Performance Statistics for Scheduled Jobs

• Question 5: True or False. Besides the MSDB database, SQL Server Agent also has configuration parameter related data stored in the registry.
  • True.
    • Additional information - Missing SQL Server Agent History

Question Difficulty = Advanced

• Question 1: What is multi-server administration and when would you use it?
  • Job management paradigm with a master server and one or more target servers. The master server sends and receives jobs from the target servers with all of the job and job step related information stored on the master server. When the jobs complete on the target servers notification is sent to the master server so this server has the updated information. Multi-server administration is used in a enterprise environment where a consistent set of jobs need to run on numerous SQL Servers, this technology helps to consolidate the creation, execution and management of those jobs.

• Question 2: What is a SQL Server Agent Proxy? Can you name some of the sub-systems proxies? Why are the proxies of any significance?
  • A SQL Server Agent Proxy is an account that is setup to help secure a particular sub-system, so that if an login\user is trying to access the particular sub-system and does not have rights, the proxy will grant rights.
  • The SQL Server Agent Proxies include:
    • ActiveX Script
    • Operating System (CmdExec)
    • Replication Distributor
    • Replication Merge
    • Replication Queue Reader
    • Replication Snapshot
    • Replication Transaction-Log Reader
    • Analysis Services Command
    • Analysis Services Query
    • SSIS Package Execution
    • Unassigned Proxies
  • The SQL Server Agent Proxies offer a new level of granularity for SQL Server Agent that was not previously available.
  • Additional information - SQL Server Agent Proxies

• Question 3: What are the new SQL Server Agent Fixed Database Roles and what is the significance of each role?
  • SQLAgentUserRole - Ability to manage Jobs that they own
  • SQLAgentReaderRole - All of the SQLAgentUserRole rights and the ability to review multi-server jobs, their configurations and history
  • SQLAgentOperatorRole - All of the SQLAgentReaderRole rights and the ability to review operators, proxies and alerts, execute, stop or start all local jobs, delete the job history for any local job as well as enable or disable all local jobs and schedules
  • Additional information - <-- SQL Server Security Note --> SQL Server Agent Fixed Database Roles

Ovewrite Excel file using SSIS

Create an SSIS package as shown below:

 

Step A: Add an SQL task and execute a command "Truncate Table Sales", where Sales is the name of the Excel table.

Step B: Add an SQL task and executye a command to create a table with necessary columns, see example below:

CREATE TABLE Sales(
SalesID NUMBER,
 InvoiceNo NUMBER,
SalesAmount NUMBER,
OrderQty NUMBER,
CustomerID NUMBER,

SalesDate DATE
 )

A new table will be created in sheet 'Sales', then create an DFT task to load data into excel sheet Sales.