SQL Server Agent and Automation

45.1 Components

Jobs: one or more steps (T-SQL, PowerShell, CmdExec, SSIS, replication)

Schedules: recurring, one-time, at Agent start, when CPU is idle Operators: notification recipients

Alerts: on error severity, error number, performance condition, or WMI event

Proxies and credentials: run steps under other security contexts

Database Mail: SMTP-based email from SQL Server

45.2 Creating a Job with T-SQL

USE msdb;
EXEC sp_add_job @job_name = N'Nightly_IndexMaintenance';
EXEC sp_add_jobstep @job_name = N'Nightly_IndexMaintenance', @step_name = N'Rebuild',
     @subsystem = N'TSQL', @database_name = N'ShopDB',
     @command = N'EXEC dbo.IndexOptimize @Databases = ''USER_DATABASES''', @retry_attempts = 2;
EXEC sp_add_schedule @schedule_name = N'Daily_2AM', @freq_type = 4, @freq_interval = 1, @active_start_time = 020000;
EXEC sp_attach_schedule @job_name = N'Nightly_IndexMaintenance', @schedule_name = N'Daily_2AM';
EXEC sp_add_jobserver @job_name = N'Nightly_IndexMaintenance';
EXEC sp_start_job N'Nightly_IndexMaintenance';

-- Job history
SELECT j.name, h.run_date, h.run_time, h.run_status, h.message
FROM msdb.dbo.sysjobhistory h JOIN msdb.dbo.sysjobs j ON j.job_id = h.job_id ORDER BY h.instance_id DESC;

45.3 Database Mail and Alerts

Create alerts for severities 16–25 and errors 823, 824, 825 (I/O corruption).

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA', @recipients = '[email protected]',
     @subject = 'Backup failed', @body = 'Check job history.';

EXEC msdb.dbo.sp_add_alert @name = N'Severity 024', @severity = 24, @include_event_description_in = 1;
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 024', @operator_name = N'DBA Team', @notification_method = 1;

45.4 Maintenance Plans vs Scripts

Maintenance Plans (GUI, SSIS-based) vs Ola Hallengren's Maintenance Solution (industry standard: DatabaseBackup, DatabaseIntegrityCheck, IndexOptimize).

Multi-server administration (master/target servers) and Central Management Servers.

45.5 PowerShell Automation

Install-Module dbatools
Get-DbaDatabase -SqlInstance SQL1 | Select Name, RecoveryModel, LastFullBackup
Backup-DbaDatabase -SqlInstance SQL1 -Database ShopDB -Path B:\Backup -CompressBackup
Test-DbaLastBackup -SqlInstance SQL1 -Destination SQLTEST
Copy-DbaLogin -Source SQL1 -Destination SQL2
Invoke-DbaQuery -SqlInstance SQL1 -Query "SELECT @@VERSION"