Schedules

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 times—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 runs 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.

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

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 23-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.

Alerts

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.

SQL Server Agent Database Roles

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 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 23-2 illustrates the General page of the New Job dialog box.

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 23-3.

On the Advanced page of the New Job Step dialog box, shown in Figure 23-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.

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 23-5.

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 23-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.

Creating a Complex SQL Server Agent Job

In the following sections, we create a more complex SQL Server Agent job, which backs up the Chapter23 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 Chapter23 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 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 23-7.

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 12.

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 23-8.

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.

On the Principles 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 23-9.

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 23-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 an e-mail.

EXEC sp_configure 'show advanced options', 1 ;

GO

RECONFIGURE

GO

EXEC sp_configure 'Database Mail XPs', 1 ;

GO

RECONFIGURE

GO

Listing 23-4

Enabling Database Mail XPs

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 23-10.

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 23-11.

Let’s now use the Add button to add one or more SMTP (Simple Mail Transfer Protocol) e-mail accounts to the profile via the New Database Mail Account dialog box, show in Figure 23-12.

In this dialog box, we specify a name for the account and, optionally, a description. We then need to specify the e-mail 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 23-13.

On the Configure System Parameters page of the wizard, illustrated in Figure 23-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

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 23-15.

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 23-16.

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 23-17.

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 DBAs’ login, when they leaves the company.

On the Steps page of the dialog box, we use the New button to add our first step—backing up the Chapter23 database. The General page of the New Job Step dialog box is illustrated in Figure 23-18.

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 23-5 contains the backup script.

Tip

Make sure to always test scripts before you add them to your jobs.

BACKUP DATABASE Chapter23

        TO DISK =

            'C:Microsoft SQL ServerMSSQL16.MSSQLSERVERMSSQLBackupChapter23.bak'

        WITH NOINIT

        ,NAME = N'Chapter23-Full Database Backup'

        ,SKIP

        ,STATS = 10 ;

Listing 23-5

Backup Script

On the Advanced page of the dialog box, shown in Figure 23-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 one-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.

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 23-20. Listing 23-6 shows the command that we use.

Get-Service | Where {$_.name -eq "SQLBrowser"}

Listing 23-6

Checking Browser Service

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 23-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 23-22). We use this dialog box to select our maintenance schedule.

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 23-23.

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.

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 23-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.

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 23-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.