CHAPTER 22
Automating Maintenance
Routines
Automation is a critical part of database administration because it reduces the total
cost of ownership (TCO) of the enterprise by allowing repeatable tasks to be carried out
with little or no human intervention. SQL Server provides a rich set of functionality for
automating routine DBA activity, including a scheduling engine, decision-tree logic, and
a comprehensive security model. In this chapter, we discuss how you can harness SQL
Server Agent to reduce the maintenance burden on your time. We also look at how you
can reduce effort by using multiserver jobs, which allow you to operate a consistent set
of routines across the enterprise.
SQL Server Agent
SQL Server Agent is a service that provides the ability to create automated routines with
decision-based logic and schedule them to run one time only, on a reoccurring basis,
when the SQL Server Agent service starts or when a CPU idle condition occurs.
SQL Server Agent also controls alerts, which allow you to respond to a wide range of
conditions, including errors, performance conditions, or WMI (Windows Management
Instrumentation) events. Responses can include sending e-mails or running tasks.
After introducing you to the concepts surrounding SQL Server Agent, the following
sections discuss the SQL Server Agent security model, how to create and manage jobs,
and how to create alerts.

SQL Server Agent Concepts
SQL Server Agent is implemented using jobs, which orchestrate the tasks that are run;
schedules, which define when the tasks run; alerts, which can respond to events that
occur within SQL Server; and operators, which are users (usually DBAs) who are notified
of occurrences, such as job status or alerts that have been triggered. The following
sections introduce you to each of these concepts.
Schedules
A schedule defines the time or condition that triggers a job to start running. A schedule
can be defined as follows:
One time: Allows you to specify a specific date and time.
Start automatically when SQL Server Agent starts: Useful if a set
of tasks should run when the instance starts, assuming that the
SQL Server Agent service is configured to start automatically.
Start when CPU becomes idle: Useful if you have resource-­
intensive jobs that you do not wish to impact user activity.
Recurring: Allows you to define a complex schedule, with start
and end dates, that can reoccur daily, weekly, or monthly. If you
schedule a job to run weekly, then you can also define multiple
days on which it should run. If you define the schedule as daily,
you can opt to have the trigger occur once daily, on an hourly
basis, every minute, or even as frequently as every 10 seconds.
If the schedule is reoccurring based on second, minute, or hour,
then it is possible to define start and stop times within a day. This
means that you can schedule a job to run every minute, between
18:00 and 20:00, for example.
Tip  A recurring daily schedule is actually used to define a schedule that runs
daily, hourly, every minute, or every second.

You can create individual schedules for each job, or you can choose to define a
schedule and use this to trigger multiple jobs that you need to run at the same time—for
example, when you have multiple maintenance jobs you want to run when the CPU
is idle. In this case, you use the same schedule for all of these jobs. Another example
is when you have multiple ETL running against different databases. If you have a
small ETL window, you may want all of these jobs to run at the same time. Here again,
you can define a single schedule and use it for all of the ETL jobs. This approach can
reduce administration; if, for example, the ETL window moves, you can change a single
schedule rather than many schedules.
Operators
An operator is an individual or team that is configured to receive a notification of job
status or when an alert is triggered. You can confine operators to be notified via e-mail,
NET SEND, or the pager. It is worth noting, however, that the pager and NET SEND options
are deprecated and you should avoid using them.
If you choose to configure operators so they are notified through e-mail, then you
must also configure Database Mail, discussed later in this chapter, specifying the address
and port of the SMTP Replay server that delivers the messages. If you configure operators
to be notified via NET SEND, then the SQL Server Agent Windows service is dependent on
the NET SEND service, as well as the SQL Server service, in order to start. If you configure
operators to be notified by pager, then you must use Database Mail to relay the messages
to the e-mail to pager service.
Caution  You increase your operational risk by introducing reliance on the NET
SEND service.
When using pager alerts, you can configure each operator with days and times that
they are on duty. You can configure this in 24/7 organizations that run support shifts
or “follow the sun” support models for operational support, which see shifts being
passed to support teams in different global regions. This functionality also allows you
to configure each operator with different shift patterns on weekdays, Saturdays, and
Sundays.

Jobs
A job is comprised of a series of actions that you should perform. Each action is known
as a job step. You can configure each job step to perform an action within one of the
following categories:
•
SSIS packages
•
T-SQL commands
•
PowerShell scripts
•
Operating system commands
•
Replication Distributor tasks
•
Replication Merge Agent tasks
•
Replication Queue Reader Agent tasks
•
Replication Snapshot Agent tasks
•
Replication Transaction Log Reader tasks
•
Analysis Services commands
•
Analysis Services queries
You can configure each job step, with the exception of T-SQL commands, to run
under the context of the service account running the SQL Server Agent service or to run
under a proxy account, which is linked to a credential. You can also configure each step
to retry a specific number of times, with an interval between each retry.
Additionally, you can configure On Success and On Failure actions individually for
each job step. This allows DBAs to implement decision-based logic and error handling,
as outlined in Figure 22-1.

You can run each job on a schedule that you can create specifically for the job that
you are configuring, or share between multiple jobs, which should all run on the same
schedule.
You can also configure notifications for each job. A notification alerts an operator
to the success or failure of a job, but you can also configure it to write entries to the
Windows Application Event Log or even delete the job.
Step 1
Step 2
Step 3
Step 4
(Error handler)
Quit Job
reporting
success
On Success
On Success
On Success
On Failure
On Failure
Quit Job
reporting
failure
Quit Job
reporting
failure
On Failure
On Failure
On Success
Quit Job
reporting
failure
Quit Job
reporting
success
Step 5
(Error handler)
Figure 22-1.  Decision-tree logic

Alerts
Alerts respond to events that occur in SQL Server and have been written to the Windows
application event log. Alerts can respond to the following categories of activity:
•
SQL Server events
•
SQL Server performance conditions
•
WMI events
When you create an alert against a SQL Server events category, you can configure it
to respond to a specific error message or to a specific error severity level that occurs. You
can also filter alerts so that they only fire if the error or warning contains specific text.
They can also be filtered by the specific database in which they occur.
When you create alerts against the SQL Server performance conditions category,
they are configured so they are triggered if a counter falls below, becomes equal to,
or rises above a specified value. When configuring such an alert, you need to select
the performance object that is essentially the category of performance condition, the
counter within that performance object, and the instance of the counter that you wish
to alert against. So, for example, to trigger an alert in the event that the Percentage Log
Used for the Chapter22 database rises above 70%, you would select the Databases object,
the Percent Log Used counter, and the Chapter22 instance and configure the alert to be
triggered if this counter rises above 70. A complete list of performance objects and their
associated performance counters can be revealed by running the query in Listing 22-1.
Listing 22-1.  Listing Performance Objects and Counters
SELECT
          object_name
        , counter_name
FROM msdb.dbo.sysalerts_performance_counters_view
ORDER BY object_name
SQL Server Agent Security
You control access to SQL Server Agent via database roles, and you can run job steps under
the context of the SQL Server Agent service account or by using separate proxy accounts
that map to credentials. Both of these concepts are explored in the following sections.

SQL Server Agent Database Roles
Other than members of the sysadmin server role, who have full access to SQL Server
Agent, access can be granted to SQL Server Agent using fixed database roles within the
MSDB database. The following roles are provided:
•
SQLAgentUserRole
•
SQLAgentReaderRole
•
SQLAgentOperatorRole
The permissions provided by the roles are detailed in Table 22-1. Members of
the sysadmin role are granted all permissions to SQL Server Agent. This includes
permissions that are not provided by any of the SQL Server Agent roles, such as editing
multiserver job properties. Actions that are not possible through SQL Server Agent role
membership can only be actioned by members of the sysadmin role.
Table 22-1.  SQL Server Agent Permissions Matrix
Permission
SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole
CREATE/ALTER/DROP
operator
No
No
No
CREATE/ALTER/DROP
local job
Yes (Owned only)
Yes (Owned only)
Yes (Owned only)
CREATE/ALTER/DROP
multiserver job
No
No
No
CREATE/ALTER/DROP
schedule
Yes (Owned only)
Yes (Owned only)
Yes (Owned only)
CREATE/ALTER/DROP proxy
No
No
No
CREATE/ALTER/DROP alerts
No
No
No
View list of operators
Yes
Yes
Yes
View list of local jobs
Yes (Owned only)
Yes
Yes
View list of multiserver jobs
No
Yes
Yes
View list of schedules
Yes (Owned only)
Yes
Yes
View list of proxies
Yes
Yes
Yes
(continued)

Table 22-1.  (continued)
Permission
SQLAgentUserRole SQLAgentReaderRole SQLAgentOperatorRole
View list of alerts
No
No
No
Enable/disable operators
No
No
No
Enable/disable local jobs
Yes (Owned only)
Yes (Owned only)
Yes
Enable/disable multiserver
jobs
No
No
No
Enable/disable schedules
Yes (Owned only)
Yes (Owned only)
Yes
Enable/disable alerts
No
No
No
View operator properties
No
No
Yes
View local job properties
Yes (Owned only)
Yes
Yes
View multiserver job
properties
No
Yes
Yes
View schedule properties
Yes (Owned only)
Yes
Yes
View proxy properties
No
No
Yes
View alert properties
No
No
Yes
Edit operator properties
No
No
No
Edit local job properties
No
Yes (Owned only)
Yes (Owned only)
Edit multiserver job
properties
No
No
No
Edit schedule properties
No
Yes (Owned only)
Yes (Owned only)
Edit proxy properties
No
No
No
Edit alert properties
No
No
No
Start/stop local jobs
Yes (Owned only)
Yes (Owned only)
Yes
Start/stop multiserver jobs
No
No
No
View local job history
Yes (Owned only)
Yes
Yes
View multiserver job history
No
Yes
Yes
Delete local job history
No
No
Yes
Delete multiserver job history No
No
No
Attach/detach schedules
Yes (Owned only)
Yes (Owned only)
Yes (Owned only)

SQL Server Agent Proxy Accounts
By default, all job steps run under the context of the SQL Server Agent service account.
Adopting this approach, however, can be a security risk, since you may need to grant the
service account a large number of permissions to the instance and objects within the
operating system. The amount of permissions you need to grant the service account is
especially important for jobs that require cross-server access.
To mitigate this risk and follow the principle of least privilege, you should instead
consider using proxy accounts. Proxies are mapped to credentials within the instance
level and you can configure them to run only a subset of step types. For example, you
can configure one proxy to be able to run operating system commands while configuring
another to be able to run only PowerShell scripts. This means that you can reduce the
permissions that each proxy requires.
For job steps with the Transact-SQL (T-SQL) script step type, it is not possible to
select a proxy account. Instead, the Run As User option allows you to select a database
user to use as the security context to run the script. This option uses the EXECUTE AS
functionality in T-SQL to change the security context.
Creating SQL Server Agent Jobs
In the following sections, we create a simple SQL Server Agent job, which runs an
operating system command to delete old backup files. We then create a more complex
SQL Server Agent job, which backs up a database and runs a PowerShell script to ensure
the SQL Server Browser service is running. Before creating the SQL Server Agent jobs,
however, we first create the Chapter22 database, as well as security principles that we
use in the following sections.
You can find the script to perform these tasks in Listing 22-2. The script uses
PowerShell to create two domain users: SQLUser and WinUser. It then uses SQLCMD to
create the Chapter22 database, before creating a login for SQLUser and mapping it
to the Chapter22 database with backup permissions. You can run the script from the
PowerShell ISE (Integrated Scripting Environment) or from the PowerShell command
prompt. You should run the script on a Windows Server operating system; if you are
running it on a different operating system, you need to prepare the environment
manually.

Listing 22-2.  Preparing the Environment
Set-ExecutionPolicy Unrestricted
import-module SQLPS
import-module servermanager
Add-WindowsFeature -Name "RSAT-AD-PowerShell" -IncludeAllSubFeature
New-ADUser SQLUser -AccountPassword (ConvertTo-SecureString -AsPlainText
"Pa$$w0rd" -Force) -Server "PROSQLADMIN.COM"
Enable-ADAccount -Identity SQLUser
New-ADUser WinUser -AccountPassword (ConvertTo-SecureString -AsPlainText
"Pa$$w0rd" -Force) -Server "PROSQLADMIN.COM"
Enable-ADAccount -Identity WinUser
$perm = [ADSI]"WinNT://SQLServer/Administrators,group"
$perm.psbase.Invoke("Add",([ADSI]"WinNT://PROSQLADMIN/WinUser").path)
invoke-sqlcmd -ServerInstance .\MasterServer -Query "--Create the database
CREATE DATABASE Chapter22 ;
GO
USE Chapter22
GO
--Create and populate numbers table
DECLARE @Numbers TABLE
(
        Number        INT
)
;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1

FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE;
--Create and populate name pieces
DECLARE @Names TABLE
(
        FirstName       VARCHAR(30),
        LastName        VARCHAR(30)
);
INSERT INTO @Names
VALUES('Peter', 'Carter'),
      ('Michael', 'Smith'),
      ('Danielle', 'Mead'),
      ('Reuben', 'Roberts'),
      ('Iris', 'Jones'),
      ('Sylvia', 'Davies'),
      ('Finola', 'Wright'),
      ('Edward', 'James'),
      ('Marie', 'Andrews'),
  ('Jennifer', 'Abraham');
--Create and populate Customers table
CREATE TABLE dbo.Customers
(
        CustomerID       INT           NOT NULL    IDENTITY    PRIMARY KEY,
      FirstName          VARCHAR(30)   NOT NULL,
      LastName           VARCHAR(30)   NOT NULL,
      BillingAddressID   INT           NOT NULL,
      DeliveryAddressID  INT           NOT NULL,
      CreditLimit        MONEY         NOT NULL,
      Balance            MONEY         NOT NULL
);

SELECT * INTO #Customers
FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID())
FirstName,
                 (SELECT TOP 1 LastName FROM @Names ORDER BY NEWID())
LastName,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID())
BillingAddressID,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID())
DeliveryAddressID,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 10000
                  FROM @Numbers
                  ORDER BY NEWID()) CreditLimit,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 9000
                  FROM @Numbers
                  ORDER BY NEWID()) Balance
        FROM @Numbers a
) a;
--Create the SQLUser Login and DB User
USE Master
GO
CREATE LOGIN [PROSQLADMIN\sqluser] FROM WINDOWS WITH DEFAULT_
DATABASE=Chapter22 ;
GO
USE Chapter22
GO
CREATE USER [PROSQLADMIN\sqluser] FOR LOGIN [PROSQLADMIN\sqluser] ;
GO
--Add the SQLUser to the db_backupoperator group
ALTER ROLE db_backupoperator ADD MEMBER [PROSQLADMIN\sqluser] ;
GO"

Creating a Simple SQL Server Agent Job
We start by creating a simple Server Agent job, which uses an operating system
command to delete backup files that are older than 30 days, and schedule this job to run
on a monthly basis. We create the SQL Server Agent artifacts using the New Job dialog
box. To invoke this dialog box, drill through SQL Server Agent in Object Explorer, and
select New Job from the Jobs context menu. Figure 22-2 illustrates the General page of
the New Job dialog box.
Figure 22-2.  The General page
On this page, we name our job DeleteOldBackups and change the job owner to
be the sa account. We can also optionally add a description for the job and choose a
category.
On the Steps page, we use the New button to invoke the New Job Step dialog box.
The General tab of this dialog box is illustrated in Figure 22-3.

On this page, we give our job step a name, specify that the step is an operating
system command in the Type drop-down, and confirm in the Run As drop-down that
the step runs under the security context of the SQL Server Agent service account. In the
Command section, we enter a batch command, which deletes all files from our default
backup location that are older than 30 days and have a file extension of .bak. You can
find this batch command in Listing 22-3.
Listing 22-3.  Removing Old Backups
forfiles -p "C:\Program Files\Microsoft SQL Server\MSSQL12.MASTERSERVERMSSQL\Backup" -s -m *.bak /D -30 /C "cmd /c del @path"
On the Advanced page of the New Job Step dialog box, shown in Figure 22-4, we
leave the default settings. We could use this page, however, in more complex scenarios,
to configure logging and to control decision-tree logic. We discuss this in the next
section.
Figure 22-3.  The General page

Once we have configured our job step, we can exit out of the New Job Step dialog box
and return to the New Job dialog box. Here, we now move to the Schedules page. On this
page, we use the New button to invoke the New Job Schedule dialog box, illustrated in
Figure 22-5.
Figure 22-4.  The Advanced page

In the New Job Schedule dialog box, we first enter a name for our schedule. The
default schedule type is Recurring, but the screen changes dynamically if we choose
other options. In the Frequency section of the screen, we select Monthly. Again, the
screen changes dynamically if we select weekly or daily in this drop-down.
We can now configure the date and time that we would like the schedule to invoke
job execution. In our scenario, we leave the default option of midnight, on the first day of
each month.
On the Notifications page of the New Job dialog box, we configure any actions that
we want to occur when the job completes. As illustrated in Figure 22-6, we configure an
entry to write to the Windows Application Log if the job fails. This is an especially useful
option if your enterprise is managed by a monitoring tool such as SCOM, because you
can configure SCOM to monitor for a failure entry in the Windows application log and
send an alert to the DBA team. In the next section, we discuss how to configure e-mail
notifications directly from SQL Server Agent.
Figure 22-5.  The New Job Schedule dialog box

Creating a Complex SQL Server Agent Job
In the following sections, we create a more complex SQL Server Agent job, which backs
up the Chapter22 database. The job then checks that the SQL Server Browser service
is running. We use Run As to set the context under which the T-SQL job step runs and
a proxy to run the PowerShell job step. We also configure Database Mail so that an
operator can be notified of the success or failure of the job and schedule the job to run
periodically. You can also see how to create the SQL Server Agent artifacts using T-SQL,
which may prove useful when you are working in Server Core environments.
Creating the Credential
Now that our environment is prepared, we create a SQL Server Agent job, which first
backs up the Chapter22 database. The job then checks to ensure that the SQL Server
Browser service is running. Checking that the browser service is running is a useful
practice, because if it stops, then applications are only able to connect to the instance
Figure 22-6.  The Notifications page

if they specify the port number of the instance in their connection strings. We run the
backup as a T-SQL command under the context of SQL User, and we use PowerShell
to check that the browser service is running by using the WinUser account. Therefore,
our first step is to create a credential, which uses the WinUser account. We can achieve
this in SQL Server Management Studio by drilling through Security and selecting New
Credential from the Credentials context menu. This causes the New Credential dialog
box to be invoked, as shown in Figure 22-7.
Figure 22-7.  The New Credential dialog box
In this dialog box, use the Credential Name field to specify a name for your new
credential. In the Identity field, specify the name of the Windows security principle that
you wish to use and then type the Windows password in the Password and Confirm
Password fields. You can also link the credential to an EKM provider. If you wish to do
this, check Use Encryption Provider and select your provider from the drop-down list.
EKM is discussed further in Chapter 11.

Creating the Proxy
Next, let’s create a SQL Server Agent proxy account, which uses this credential. We
configure this proxy account to be able to run PowerShell job steps. We can achieve this
through SSMS by drilling through SQL Server Agent in Object Explorer and selecting
New Proxy from the Proxies context menu. This causes the General page of the New
Proxy Account dialog box to display, illustrated in Figure 22-8.
Figure 22-8.  The New Proxy Account dialog box
On this page, we specify a name for our proxy account and give it a description. We use
the Credential Name field to select our WinUserCredential credential and then use the Active
To The Following Subsystems section to authorize the proxy to run PowerShell job steps.
Tip  If you enter the new proxy account from the node of the relevant subsystem
located under the Proxies node in Object Explorer, the relevant subsystem is
automatically selected within the dialog box.