Week 6: Chapter 6 Agenda

fanaticalpumaMechanics

Nov 5, 2013 (3 years and 8 months ago)

114 views

Week 6: Chapter 6 Agenda


Automation of SQL Server tasks using:


SQL Server Agent


Scheduling


Scripting Technologies

Benefits of Automation

1.
Saves time


2.
Reduces chance of human error
(forgetting , running wrong task, …)


3.
Allows easy execution of tasks in non
-
peak times or at scheduled times

SQL Server Agent



SQL Server’s primary tool for automation
and scheduling


A service in the control panel of the SQL
Server2000 machine


Configured with Enterprise Manager in
Management object


Can automate many tasks using


SQL statements


Operating System Commands


Programming


Configuring SQL Server Agent

SQL Server Agent must be running at all
times. To do this:


Using Service Manager, configure SQL
Server Agent to auto start


Using Enterprise Manager, configure
SQL Server and SQL Server Agent
service to restart automatically if these
services stops unexpectedly

Configuring SQL Server Agent (ctd)


SQL Server Agent logon Account must be
mapped to sysadmin role


Map this account to administrators local
group


Use a windows domain user account
logon account


Use windows authentication mode for SQL
Server Agent to permit greater flexibility

SQL Server Agent Objects

SQL Server agent works with following
objects:


Jobs


series of tasks


Operators


names and contact info


Alerts


response to conditions
(normally for errors)

Jobs


A job is a set of instructions, schedules and
notifications


Each individual operation is called a step


Step types include Transact
-
SQL
commands, O/S commands, Scripts
(ActiveX)


Scripts can use VBScript, JavaScript, etc


Jobs can use conditional logic


Notification methods include email, email
pager and Windows NET SEND

Create a Job


Ensure that job is enabled (jobs are
enabled by default)


Specify the owner who is responsible for
performing the job (by default the owner
is the logon account creating the job)


Define whether the job is to execute on a
local server or on multiple remote servers


Job categories help to organize, filter and
manage many

jobs

Define job step using T
-
SQL




Identify database to be used




Include required variable and parameters




Can send a result set of a job step to an
output file (output files are often used in
troubleshooting to capture an error message
that may have occurred while the statement
is executing)

Define a job step using OS commands




Operating System commands are
identified by .exe, .bat, .cmd, .com files
extension




Identify a process exit code to indicate
that process was successful




Include the full path to the executable
application in the command text box.

Defining Job Steps




Can use ActiveX Script with languages such
as Microsoft VBScript, Microsoft Jscript etc.




Must identify scripting language in which the
job step is written




Write or Open Active Script (use the SQL
ActiveScriptHost object to print output to the
job step history or create object)


Job Flow Logic Example

JOB 1: Data Transfer

Job Step 1: Back Up Database

Type: Transact
-
SQL:

Retry attempts: 1

Job Step2: Transfer Data

Type: CmdExec;

Retry attempts : 2

Job Step3: Custom Application

Type: Active Scripting:

Retry attempts: 0

Write to

Windows

Application Log

Notify Operator

FAIL?

FAIL?

FAIL?

Y

Y

N

N

Y

Notify Operator

N

Job Scheduling


After job has been defined you can schedule
the job to be executed automatically


A schedule must be enabled to permit
automated execution


Can use Enterprise Manager (GUI) or
execute sp_add_jobschedule stored
procedure to define job schedule


Job schedules are stored in the
msdb.sysjobschedules system tables

Job Scheduling (ctd)


Can schedule jobs to start automatically:


When SQL Server Agent started


At a specific time (one time only)


On a recurring basis (daily, weekly, or
monthly)


When CPU is idle


Notification






Operators are notified of events that take
place on SQL Server


Operators don’t require a SQL Server login


Notifications are messages sent to the
operators


Notification methods can be:


Email


Email pager


Windows NET SEND command


Operators should be available for contact as
required

Operators


Use group email alias to notify more than one
individual to respond to notification


Test each notification method to ensure that
the operator is able to receive messages


Should specify a work schedule for each
operator to be notified by pager


Use NET SEND command to send messages
to network operators and servers running
Windows 2000 or Windows NT.


Operator Notification


Ensure operator is available to receive
notifications


Ensure messenger service is running on
computer of the operator to be notified by NET
SEND command


Review most recent notification steps to
determine date and time of last notification


Test individual notification methods outside of
SQL Server by verifying that you can send e
-
mail messages, page an operator or
successfully execute a NET SEND command.

Job History


Job history can be viewed from Enterprise
Manager and directly in the
msdb..sysjobhistory system table


Sysjobhistory table records:


Date and time each job step occurred


Success or failure of job step


The operator notified and notification
method


The duration of job step


Errors or messages from job step

Job History Size


By default, msdb database file properties are
set to auto growth, and the Truncate log on
checkpoint database option is turned on.


By default, maximum job history size is set
at 1,000 rows


By default, maximum job history size for
each job is set at 100 rows


Rows are removed from sysjobhistory
system table in a first
-
in, first
-
out (FIFO)
manner when size limit is reached

Execution Permissions



For T
-
SQL Jobs:



context of job owner or a specific user



Operating System and ActiveX Script jobs:



Members of Sysadmin role use the SQL
Server Agent login account



Job owners that are not members of the
sysadmin role use defined domain user
account called a proxy account