Task Automation

1. SQL Server Agent

SQL Server Agent is the central service for automating administrative tasks. Every SQL Server instance has one. Anything you need to do more than a couple of times can be automated here.

Components

Component	Description
SQL Server service (MSSQLSERVER)	The database engine itself
Windows Application Log	Logs system events; SQL Server Agent reads this
SQL Server Agent service	Runs automated tasks, reacts to events

What SQL Server Agent Can Automate

Jobs — contain steps, schedules, warnings and notifications
Alerts — monitor events and can send notifications or run tasks
Operators — groups or individuals who can receive notifications
Multi-server environments — managing multiple SQL instances from one master instance (via Master & Target servers)

2. Operators

An operator is a group or individual who can receive notifications (via email or pager).

Via SSMSVia T-SQL

Object Explorer → SQL Server Agent → right-click Operators → New Operator...

Set: - Name + email address - Pager schedule (workdays + time window) - Notifications: for which alerts

USE [msdb];
GO

EXEC msdb.dbo.sp_add_operator
    @name                    = N'Marcel',
    @enabled                 = 1,
    @weekday_pager_start_time = 80000,
    @weekday_pager_end_time  = 180000,
    @pager_days              = 62,
    @email_address           = N'marcel@ap.be';
GO

3. Alerts

An alert reacts to an event in the Windows Application Log or a performance condition.

SQL Server Agent reads the Windows Application Log and compares it against defined alerts
On a match → the alert is triggered

Alerts react to: - Potential problems (transaction log full, disk space, ...) - System errors - User-defined errors

Via SSMSVia T-SQL

Object Explorer → SQL Server Agent → right-click Alerts → New Alert...

Tabs: - General — name, type, object, counter, condition - Response — run a job and/or notify an operator - Options — include email/pager, delay between responses

USE [msdb];
GO

EXEC msdb.dbo.sp_add_alert
    @name                       = N'TEMPDB Growing',
    @enabled                    = 1,
    @delay_between_responses    = 120,
    @include_event_description_in = 1,
    @performance_condition      = N'Databases|Data File(s) Size (KB)|Employees|>|0';
GO

EXEC msdb.dbo.sp_add_notification
    @alert_name       = N'TEMPDB Growing',
    @operator_name    = N'Marcel Kiekeboe',
    @notification_method = 1;
GO

4. Jobs

A job is a series of steps that SQL Server Agent runs.

Creating a Job (via SSMS)

Object Explorer → SQL Server Agent → right-click Jobs → New Job...

Tabs:

Tab	Contents
General	Name, owner, category, description
Steps	Add steps — each step has a type (T-SQL, CmdExec, ...), database and command; on success/failure, continue to the next step or stop
Schedules	When the job runs (once, recurring, at startup)
Alerts	Link alerts to the job
Notifications	Notify an operator + an action on completion
Targets	Local server or target servers (multi-server)

Job Step Types

Type	Use
Transact-SQL script (T-SQL)	Run SQL statements
Operating System (CmdExec)	Run OS commands
Start an application	Run an external application

Schedule Options

Once, at a specific time
Recurring (daily, weekly, hourly, every minute, ...)
At SQL Server Agent startup

5. Job History Log

Task activity is stored in sysjobhistory in the msdb system database.

Field	Description
Date and time	When the step ran
Success/Failure	Whether the step succeeded
Operators	Who was notified
Duration	How long the step took
Error messages	Any error description

Max. 1000 rows in the log
Max. 100 rows per job

Via SSMS: right-click the job → View History → Log File Viewer

6. Automating Administrative Tasks

Tasks that are often performed and can therefore be automated via SQL Server Agent jobs:

Taking backups of databases & the transaction log
Transferring data
Rebuilding indexes
Checking data integrity
Shrinking databases

7. Database Mail

Database Mail lets SQL Server send emails itself via SMTP — handy for notifying operators on job failures or alerts.

Characteristics: - Messages can include query results and files as attachments - Uses SMTP (Simple Mail Transfer Protocol) - Requires membership in the DatabaseMailUserRole database role in msdb - Keeps a list of forbidden file extensions (configurable) - Keeps copies of messages and attachments in msdb

Configuration (via SSMS)

Object Explorer → Management → right-click Database Mail → Configure Database Mail

Wizard steps: 1. Create an SMTP account (name, email address, display name, SMTP server + port, authentication) 2. Create a mail profile and link SMTP account(s) 3. Set profile security (public/private) 4. Configure system parameters (retry attempts, max file size, ...)

Testing Database Mail

Object Explorer → Management → right-click Database Mail → Send Test E-Mail...

8. Multi-Server Environments

Through SQL Server Agent, tasks can be managed across multiple SQL instances:

Master server — manages and distributes jobs
Target servers — receive and run the jobs

This lets a DBA roll out jobs to all target servers from one central instance.