Transcript SQL_Server_Agent_All_the_Knobs_You_Need_to_Knowx

SQL Server Agent
All the Knobs You Need to Know
Taiob M Ali
About Me
10 Years as DBA
MCSA SQL Server 2012
Certified MongoDB DBA
11 Years in Merchant Marine
Education Naval Science and
Computer Science
Agenda
Agent Security
Operators
Schedule when CPU Idle Alerts
Job History
Proxies
Security-Service Account
Should not run as Local System Account
Run under domain account but not a member
of local admin group
Give privilege to “Log on as a service”
How to do it?
Security-Users
MSDB database roles
SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole
Details
Agenda
Agent Security
Schedule when CPU Idle
Job History
Operators
Alerts
Proxies
Job Schedule
Skip a single run- do not disable job
An extra run- add an one time schedule
One time job –Delete automatically.
Changing standard schedules
Job Schedule- Idle CPU
Schedule a job when CPU is idle
Create a definition of idle CPU
Chose from “Schedule Type”
Job Schedule- Idle CPU
Does not work out of the gate
CPU Idle definition control schedule
Need custom solution
Interval
in
Seconds
Running every 20 Secs—CPU IDLE Definition control job schedule
Hour
Interval
Running every hour—Custom Solution; You control job schedule
CPU Idle
condition
meet Test Run
every 20 sec
FIRE JOB
Job
Run
Disable Job
Control Job
run every
one hour
CPU Idle
condition
meet Test Run
every 20 sec
FIRE JOB
Enable Job
Job Run
only if
Enabled
DEMO– Schedule Job When CPU Idle
Agenda
Agent Security
Schedule when CPU Idle
Job History
Operators
Alerts
Proxies
Job History
…History is
truncated
If bigger than 3998 char get truncated
Why does history get
truncated?
Custom Solution
More Options
Per job category
Per job
Different threshold for success/failure
Default value
Details: bit.ly/purgehistory
Driving table
Create two store procedures
One will drive
DBA_sysjobhistory_driver
One will do the purge
DBA_sp_jobhistory_row_limiter
Schedule a job
Fail Safe 30 days
Agenda
Agent Security
Schedule when CPU Idle
Job History
Operators
Alerts
Proxies
DEMO-Operators
Built in report
20 most frequently executed jobs
20 most frequently failing jobs
20 slowest jobs based
Job Steps Execution History
Agenda
Agent Security
Schedule when CPU Idle
Job History
Operators
Alerts
Proxies
Alert
Powerful feature for monitoring
SQL Server Event
Performance Condition
WMI Event-added in 2005
Service broker must be enabled for MSDB
 Pager and net send options will be removed from SQL Server
Agent in a future version of Microsoft SQL Server
DEMO ALERT
Agenda
Agent Security
Schedule when CPU Idle
Job History
Operators
Alerts
Proxies
Proxies-when to Use
Jobs are interacting with other network
components or machines.
.bat file
PowerShell script
Proxies-How to set up
Create a privileged account
Create a credential
Create a new proxy
Use proxy in job step
Demo Proxy
how you can be the first to get notified when data corruption
DEMO Proxies
Resource







Security Considerations - SQL Server Installation
SQL Server Agent Fixed Database Roles
Script to create alerts by Glenn Berry
Custom Category article by Tim Ford
Scripts-custom job history retention
Deadlock alert blog
How to read Deadlock Graph
@SqlWorldWide
linkedin.com/in/taiobali
sqlworldwide.com
[email protected]
I am Thankful to







Robert Padilla
Mike Hillwig
Adam Machanic
Jeremy Lowell
Suneel Mundlapudi
Ayman El-Ghazali
Kenneth Fisher