On the Principals page, we can add logins or server roles that have permissions to
use the proxy. In our case, this is not required, because we are using SQL Server with
an administrator account, and administrators automatically have permissions to proxy
accounts.
Creating the Schedule
Now that our proxy account is configured, we create the schedule to be used by our job.
We need our maintenance job to run on a nightly basis, so we configure the schedule to
run at 1 AM every morning. To invoke the New Job Schedule dialog box from SSMS, we
select New ➤ Schedule from the SQL Server Agent context menu in Object Explorer. This
dialog box is shown in Figure 22-9.
Figure 22-9.  The New Job Schedule dialog box

In this dialog box, we specify a name for the schedule in the Name field and select
the condition for the schedule in the Schedule Type field. Selecting any condition other
than Recurring causes the Frequency and Duration sections to become unavailable.
Selecting any condition other than One Time causes the One-Time Occurrence section
to become unavailable. We also ensure that the Enabled box is checked so that the
schedule can be used.
In the Frequency section, we select Daily in the Occurs drop-down list. Our selection
in this field causes the options within the Frequency and Daily Frequency sections to be
altered dynamically to suit our selection. Since we want our schedule to run daily at 1
AM, we ensure that 1 is specified in the Recurs Every field and change the Occurs Once
At field to be 1 AM. Because we want our job to start running immediately and never
expire, we do not need to edit the fields in the Duration section.
Configuring Database Mail
We would like our DBA’s distribution list to be notified if our job fails. Therefore, we need
to create an operator. Before we do this, however, we need to configure the Database Mail
on the instance so that the notifications can be delivered. Our first step is to enable the
Database Mail extended stored procedures, which are disabled by default, to reduce the
attack surface. We can activate these using sp_configure, as demonstrated in Listing 22-4.
Note  If you do not have access to an SMTP Replay server, then the examples in
this section will still work, but you will not receive e-mail.
Listing 22-4.  Enabling Database Mail XPs
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1 ;
GO
RECONFIGURE
GO

We can now launch the Database Mail Configuration Wizard by drilling through
Management in Object Explorer and selecting Database Mail. After passing through the
Welcome page, we see the Select Configuration Task page shown in Figure 22-10.
Figure 22-10.  The Select Configuration Task page
On this page, we should ensure that the Set Up Database Mail By Performing The
Following Tasks option is selected. On the New Profile page, we specify a name for our
profile. A profile is an alias for one or more mail accounts, which are used to send the
notification to the operator. It is good practice to add multiple accounts to a profile; that
way, if one account fails, you can use a different one. This page is illustrated in Figure 22-­11.

Let’s now use the Add button to add one or more SMTP (Simple Mail Transfer
Protocol) email accounts to the profile via the New Database Mail Account dialog box,
shown in Figure 22-12.
Figure 22-11.  The New Profile page

In this dialog box, we specify a name for the account and, optionally, a description.
We then need to specify the email address that we will use to send mails, along with the
name and port of the SMTP server that will deliver the messages. You can also specify a
display name for when the e-mails are received. For DBAs who receive the notification,
it helps if the display name includes the server\instance from which the notification
was generated. We have selected Anonymous authentication. This implies that access to
the SMTP server is controlled with firewall rules, as opposed to authentication. This is a
relatively common approach in enterprise environments.
After adding the account, we can move to the Manage Profile Security page of the
wizard. This page has two tabs: Public Profiles and Private Profiles. We configure our
profile as public and also mark it as the default profile. Making the profile public means
that any user with access to the MSDB database can send e-mail from that profile. If we
make the profile private, then we need to specify a list of users or roles who may use the
profile for sending e-mail. Marking the profile as default makes the profile default for
the user or role. Each user or role can have one default profile. The Public Profiles tab is
displayed in Figure 22-13.
Figure 22-12.  The New Database Mail Account dialog box

On the Configure System Parameters page of the wizard, illustrated in Figure 22-14,
you can alter the default system properties, which control how mail is handled. This
includes specifying the number of times an account should be retried and the time lapse
between retries. It also involves setting the maximum allowable size of an e-mail and
configuring a blacklist of extensions. The Database Mail Executable Minimum Lifetime
(Seconds) setting configures how long the Database Mail process should remain active
when there are no e-mails in the queue waiting to be sent. The logging level can be
configured with the following settings:
Normal: Logs errors
Extended: Logs errors, warnings, and informational messages
Verbose: Logs errors, warnings, informational messages, success
messages, and internal messages
Figure 22-13.  The Public Profiles tab

Caution  Unfortunately, attachment exclusions are implemented as a blacklist, as
opposed to a whitelist. This means that to achieve the best balance of security and
operational support, you should give time and thought to the file types that should
be excluded.
On the Complete the Wizard page, you are provided with a summary of the tasks that
will be performed. In our scenario, this includes creating a new account, creating a new
profile, adding the account to the profile, and configuring the profile’s security.
We now need to configure SQL Server Agent to use our mail profile. To do this, we
select Properties from the SQL Server Agent context menu in Object Explorer to invoke
the SQL Server Agent Properties dialog box and navigate to the Alert System page, shown
in Figure 22-15.
Figure 22-14.  The Configure System Parameters page

On this page, we check the Enable Mail Profile check box before selecting the DBA-­
DL profile from the drop-down list. After we exit the dialog box, operators are able to use
Database Mail.
Creating the Operator
Now that Database Mail has been configured, we need to create an operator that will
receive e-mails in the event that our job fails. We can access the New Operator dialog box
by drilling through SQL Server Agent in Object Explorer and by selecting New Operator
from the Operators context menu. The General page of the New Operator dialog box is
shown in Figure 22-16.
Figure 22-15.  The Alert System page

On this page, we specify a name for the operator and also add the e-mail address
that the operator will be using. This must match the e-mail address that has been
configured within Database Mail. The Notifications page displays details of the alerts
and notifications that are already configured for the operator, so it is irrelevant to us at
this point.
Creating the Job
Now that all of the prerequisites are in place, we can create the SQL Server Agent job. We
can achieve this in SQL Server Management Studio by drilling through SQL Server Agent
in Object Explorer and choosing New Job from the Jobs context menu. This causes the
General page of the New Job dialog box to display, as illustrated in Figure 22-17.
Figure 22-16.  The General page

On this page, we use the Name field to specify a name for our job and, optionally,
add a description in the Description field. It’s also optional to add the job to a category;
in our instance, we have added the job to the Database Maintenance category by
selecting it from the drop-down list. We also check the Enabled box so that the job will be
active as soon as it is created.
We also specify that the job owner will be sa. This is a controversial topic, but I
generally recommend this approach for the following reason: job ownership does not
matter much. No matter who owns the job, it functions in the same way. If the owner’s
account is dropped, however, then the job no longer functions. If you make sa the
owner, then there is no chance of this situation occurring. If you are using the Windows
authentication model as opposed to mixed-mode authentication, however, then it
is reasonable to use the SQL Server Agent service account as an alternative. This is
because, although it is possible that you will change the service account and drop the
associated login, it is more unlikely than dropping other user’s logins, such as a DBA’s
login, when he leaves the company.
Figure 22-17.  The General page

On the Steps page of the dialog box, we use the New button to add our first step—
backing up the Chapter22 database. The General page of the New Job Step dialog box is
illustrated in Figure 22-18.
Figure 22-18.  The General page of the New Job Step dialog box
On this page, we enter Backup as the name of the job step and type the BACKUP
DATABASE command in the Command field. The Type field allows us to select the
subsystem to use, but it defaults to T-SQL, so we do not need to alter this. Listing 22-5
contains the backup script.
Tip  Make sure to always test scripts before you add them to your jobs.
Listing 22-5.  Backup Script
BACKUP DATABASE Chapter22
        TO DISK =
            N'C:\Microsoft SQL Server\MSSQL15.PROSQLADMIN\MSSQL\BackupChapter22.bak'
        WITH NOINIT

,NAME = N'Chapter22-Full Database Backup'
        ,SKIP
        ,STATS = 10 ;
On the Advanced page of the dialog box, shown in Figure 22-19, we use the On
Success Action and On Failure Action drop-down boxes to configure the step so that
it moves to the next step, regardless of whether the step succeeds or fails. We do this
because our two steps are unrelated. We also configure the step to retry three times, at
1-minute intervals, before it fails.
We check the Include Step Output In History box so that the step output is included
in the job history (doing so helps DBAs troubleshoot any issues) and configure the step
to run as the SQLUser user. We configure the Run as User option because, as previously
discussed, job steps of the T-SQL type use EXECUTE AS technology, instead of a proxy
account, to implement security.
Figure 22-19.  The Advanced page
Once we exit the dialog box, we need to use the New button on the Steps page of the
New Job dialog box again to add our second job step. This time, on the General page,
we specify the PowerShell type and enter the PowerShell script that checks the status of

the SQL Server Browser service. We also use the Run As box to specify that the step runs
under the context of the PowerShellProxy proxy. This is demonstrated in Figure 22-20.
Listing 22-6 shows the command that we use.
Figure 22-20.  The General page
Listing 22-6.  Checking Browser Service
Get-Service | Where {$_.name -eq "SQLBrowser"}
On the Advanced page, we choose to include the step output in the job history. We
can leave all other options with their default values.
When we return to the Steps page of the New Job dialog box, we see both of our steps
listed in the correct order, as shown in Figure 22-21. If we wish to change the order of
the steps, however, we can use the up and down arrows in the Move Step section. We
can also bypass early steps by selecting to start the job at a later step using the Start Step
drop-down list.

On the Schedules page of the wizard, we click the Pick button; doing so displays a list
of existing schedules in the Pick Schedule for Job dialog box (see Figure 22-22). We use
this dialog box to select our maintenance schedule.
Figure 22-21.  The Steps page
Figure 22-22.  The Pick Schedule for Job dialog box

After we exit the dialog box, the Schedule displays on the Schedules page of the Job
Properties dialog box.
You can use the Alerts page to organize alerts for the job. This is not relevant to our
scenario right now, but alerts are discussed later in the chapter.
On the Notifications page, we configure the DBATeam operator we want notified by
e-mail in the event that the job fails. We do this by checking the E-mail check box and
selecting our DBATeam operator from the drop-down list, as shown in Figure 22-23.
Figure 22-23.  The Notifications page
You can use the Targets page to configure multiserver jobs, which are not relevant to
our current scenario, but we do discuss them later in this chapter.
Monitoring and Managing Jobs
Although jobs are usually scheduled to run automatically, you still encounter monitoring
and maintenance requirements, such as executing jobs manually and viewing job
history. These tasks are discussed in the following sections.

Executing Jobs
Even if a job is scheduled to run automatically, at times you may wish to execute a job on
an ad hoc basis. For example, if you have a job that is scheduled to run nightly to take full
backups of the databases within your instance, you may wish to execute it manually just
before a code release or software upgrade.
A job can be executed manually in SQL Server Management Studio by drilling
through SQL Server Agent ➤ Jobs in Object Explorer and then selecting Start Job at Step
from the Job’s context menu; doing this invokes the Start Job dialog box. Figure 22-24
displays the Start Job dialog box for the BackupAndCheckBrowser job. In this dialog box,
you can select the first step of the job you want to run before you use the Start button to
execute the job.
Figure 22-24.  Start Job dialog box
To execute a job using T-SQL, you can use the sp_start_job system stored
procedure. This procedure accepts the parameters detailed in Table 22-2.

Table 22-2.  sp_start_ job Parameters
Parameter
Description
@job_name
The name of the job to execute. If NULL, then the @job_name parameter must
be specified.
@job_id
The ID of the job to execute. If NULL, then the @job_name parameter must be
specified.
@server_name
Used for multiserver jobs. Specifies the target server on which to run the job.
@step_name
The name of the job step where execution should begin.
To run our BackupAndCheckBrowser job, we execute the command in Listing 22-7.
Once a job has been executed, it cannot be executed again until it has completed.
Listing 22-7.  Executing a Job
EXEC sp_start_job @job_name=N'BackupAndCheckBrowser' ;
If we wanted the job to start executing at a later step, we can use the @step_name
parameter. For example, in our scenario, imagine that we want to execute our job in
order to check that the SQL Server Browser service is running, but do not want the
database backup to occur beforehand. To achieve this, we execute the command in
Listing 22-8.
Listing 22-8.  Starting a Job from a Specific Step
EXEC sp_start_job @job_name=N'BackupAndCheckBrowser', @step_name =
'CheckBrowser' ;
Viewing Job History
You can view the job history for a specific job by selecting View History from the
Job context menu in SQL Server Agent ➤ Jobs within Object Explorer, or for all
jobs by opening Job Activity Monitor, which you can find under the SQL Server
Agent node in Object Explorer. Figure 22-25 shows what the job history of our
BackupAndCheckBrowser job looks like after a single execution.

Here, you can see that we have drilled through Job History to see the history of each
individual step. After highlighting the Step 2 progress entry, we can see that the results
of the PowerShell script have been written to the step history, and they show us that the
SQL Server Browser service is running, as expected.
Creating Alerts
Creating an alert allows you to proactively respond to conditions that occur within your
instance by either notifying an operator, running a job, or both. On our instance, we want
to notify the DBATeam operator in the event that our Chapter22 log file becomes more
than 75% full.
To create this alert in SQL Server Management Studio, we drill through SQL Server
Agent in Object Explorer and select New Alert from the Alerts context menu. This causes
the General page of the New Alert dialog box to display. This page is shown in Figure 22-­26.
Figure 22-25.  The job history

On this page of the dialog box, we use the Name field to specify a name for our alert
and select SQL Server Performance Condition Alert from the Type drop-down list. This
causes the options within the page to dynamically update. We then select the Percent
Log Used counter from the Databases object and specify that we are interested in the
Chapter22 instance of our object. (There is an instance of this counter for each database
that resides on the instance.) Finally, we specify that the alert should be triggered if the
value of this counter rises above 75 within the Alert If Counter section of the page.
On the Response page of the dialog box, shown in Figure 22-27, we check the Notify
Operators box if the condition is met, and then select an e-mail notification for our
DBATeam operator.
Figure 22-26.  The General page

On the Options page of the dialog box, you can specify if alert error text should be
included in the notification and also additional information to include. You can also
configure a delay to occur between occurrences of the response being triggered. This can
help you avoid duplicate notifications or needlessly running a job to fix an issue that is
already being resolved. Figure 22-28 shows that we included the server\instance name in
our notification to assist the DBAs in identifying the source of the alert.
Figure 22-27.  Response page