22. Automating Maintenance Routines

SQL Server Agent is a service that provides the ability to create automated routines with decision-based logic and schedule to run them one time only, on a recurring basis, when SQL Server Agent starts, or when a CPU idle condition occurs.

Schedules:

• one time
• when SQL Server Agent starts
• CPU idle
• recurring

Operator - individual or team who receives notification of job status or alert notified via e-mail, pager, and NET SEND. Pager and NET SEND are deprecated. Need to configure DB mail.

Jobs

• one or more actions (aka job steps)
• action (except T-SQL) runs under SQL Server Agent account or proxy which is linked to a credential
• action can have retries
• on success/failure
• schedule: specific/shared
• notifications

Alerts repond to events

msdb.dbo.sysalerts_performance-counters_view

MSDB:

• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

sysadmin gets all of these roles. Not all SQL Agent activities are covered by these roles.

T-SQL run as user (no proxy)

Credential -> proxy account -> schedule operator used to receive e-mail

EXEC sp_configure 'show advanced options',1;
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs',1;
GO
RECONFIGURE
GO

Can have a default profile which sends e-mail to one or more operators

• Normal - log errors
• Extended - error, warn, info
• Verbose - error, warn, info, success, internal

After configuring DB mail, create operator who receives e-mails in event of job failures. Then create job.

sp_start_job @job_name=N'...';

sp_start_job @job_name=N'...' @step_name=N'...';

Multiserver jobs:

• MSX Master Server
• TSX Target Server

Use regedit to set AllowDownloadedJobsToMatchProxyName MsxEncryotChannelOptions registry key:

• =1 encrypt, no certificate valid
• =2 encrypt plus SSL certified
• =0 off

connect server\instance
CREATE CREDENTIAL ...;
EXEC msdb.dbo.sp_add_proxy ...;
EXEC msdb.dbo.sp_grant_proxy_to_subsystem ...;

EXEC sp_delete_jobserver
EXEC sp_add_jobserver

Force poll TSX -> MSX to distributed list for latest jobs

• Defection (deleting) removes TSX.
• Synchronize Clocks MSX -> all TSX

SQL Server Agent is a scheduling engine for SQL Server that allows you to create powerful maintenance jobs, whith decision based logic on a variety of schedules. A job is a container for the tasks that should be performed, and each of these tasks is known as a step. Job step can have different accounts/subsystems.

Good security: proxy accounts -> credentials -> AD principal