Set up Database Mail

To begin sending automated emails, you must configure Database Mail. Then, configure SQL Server Agent to use the Database Mail profile you create.

In SQL SSMS, start the Database Mail Configuration Wizard by locating it in the Management folder of the server node you are configuring and selecting Configure Database Mail. You’ll need to set up a profile and then an associated account.

The wizard turns on the Database Mail feature in the Surface Area facet of the SQL Server instance. You need to do this only once. Database Mail is among a select few Surface Area facets that you should turn on for most SQL Server instances. To view surface area configuration settings in SSMS, open Object Explorer, connect to the SQL Server, right-click the server, and select Facets on the shortcut menu. (The Facets window sometimes takes a moment to load.) Then, in the dialog box that opens, change the value in the list box to Surface Area Configuration. Choose Database Mail and set the property value to True.

A Database Mail profile can be public or private. In the case of a private profile, only sysadmins and specific associated server principals are given access (users or roles in databases). A public profile allows any principal that is a member of the built-in database role DatabaseMailUsersRole in the msdb database.

Ideally, all Database Mail profiles are private. That way, by default only members of the sysadmin role are allowed access, and only those credentials that will be used to send emails will be given access. If you would like to grant additional database principals to access private database mail profiles, you can use the stored procedure msdb.dbo.sysmail_add_principalprofile_sp. This is crucial in a multitenant environment, or an environment that allows access to external developers or vendors, but even in internal environments, it could provide protection against malicious use to send emails.

When creating a Database Mail profile, you have the option to specify whether the profile will be the default—even if there is only one Database Mail profile created. If you choose a Database Mail profile to be the default, it will be used as the global profile for outgoing mail and will be used to send mail if a profile is not specified with the dbo.sp_send_dbmail stored procedure.

You can configure a Database Mail profile to use almost any SMTP configuration, including nonstandard ports and Secure Sockets Layer (SSL). You also can configure it with Windows Authentication (common for SMTP servers in the same domain), basic authentication (common for web authentication), or no authentication (common for anonymous relay in the local network, usually with an IP allow list).

• For more on SSL and TLS, refer to Chapter 13, “Protect data through classification, encryption, and auditing.”

You can configure Database Mail to use any SMTP server that it can reach, including web-based SMTP servers. You can even use Outlook web mail or another web-based email account if you’re configuring for testing purposes or have no other viable internal SMTP options. An internal SMTP server with Windows Authentication using a service account is preferred, though, because it gives you more control over your own environment.

After you configure your account’s SMTP settings (you’ll need to test them later), the Database Mail account will have several options that you can adjust:

• Account Retry Attempts. Defaults to 1. You should probably leave this as is to avoid excessive retries that could lock out an account or trigger spam detection.

• Account Retry Delay (seconds). Defaults to 60. Again, you should leave this as is, for the same reasons as for Account Retry Attempts.

• Maximum File Size (Bytes). Defaults to roughly 1 MB. You should change this only if necessary.

• Prohibited Attachment File Extensions. Specifies which file extensions cannot be sent. It is commonly set if third-party or multitenant development occurs on the SQL Server instance. This is a comma-delimited list that, by default, is “exe,dll,vbs,js.”

• Database Mail Executable Minimum Lifetime (seconds). Defaults to 600 Seconds (10 minutes), which is a counter that starts after an email message is sent. If no other messages are sent in that time frame, the Database Mail executable stops. If stopped, the Database Mail process is started again any time a new email is sent. You’ll see messages indicating “Database Mail process is started” and “Database Mail process is shutting down” in the Database Mail log when this happens.

• Logging Level. Defaults to Extended, which includes basic start/stop and error messages that should be kept in the Database Mail log. Change this to Verbose if you are troubleshooting Database Mail and need more information, or to Normal to suppress informational messages and see errors only.

After you’ve set up a Database Mail profile and account, you can send a test email via SSMS. Right-click Database Mail and select Send Test E-Mail on the shortcut menu that opens. Or you can send a plain-and-simple test email via T-SQL by using the following code:

Click here to view code image

exec msdb.dbo.sp_send_dbmail
@recipients ='[email protected]',
@subject ='test';

This code does not specify a @profile parameter, so the command will use the default profile for the current user, the default private profile if it exists, or the global (default public) profile. If you do not have a default Database Mail profile set up, you will get the following error:

Click here to view code image

Msg 14636, Level 16, State 1, Procedure msdb.dbo.sp_send_dbmail, Line 112 [Batch Start
Line 0]
No global profile is configured. Specify a profile name in the @profile_name parameter.

This is all that is necessary for developers and applications to send emails using Database Mail.

• In case you have issues with setting up or sending emails, we cover troubleshooting later in this chapter in the “Troubleshoot Database Mail” section.

To allow SQL Server Agent to send emails based on job outcomes and alerts, you need to create an operator in SQL Server Agent, and then configure SQL Server Agent’s alert system to use a Database Mail profile. We look at SQL Server Agent and its initial configuration in depth later in this chapter.

Allow anonymous relay for anonymous authentication

If you’re using anonymous authentication internally with Microsoft Exchange, verify that the internal SMTP anonymous relay has a dedicated receive connector that allows for anonymous relay. By design, a receive connector just for anonymous relay should allow only a small list of internal hosts—your SQL Server instance(s) among them.

Maintain email history in the msdb database

The email messages attempted and sent by Database Mail are recorded and queued in a table in the msdb database named dbo.sysmail_mailitems. As you might suspect, data in the msdb tables for Database Mail will grow, potentially to an unmanageable size. This can cause queries to the msdb’s Database Mail tables to run for a long time. There is no automated process in place to maintain a retention policy for these tables, though there is a stored procedure to delete older messages, as well as a lengthy reference article in place to guide you through creating a set of archive tables and a SQL Server Agent job to maintain data over time. You can find both of these at https://learn.microsoft.com/sql/relational-databases/database-mail/create-a-sql-server-agent-job-to-archive-database-mail-messages-and-event-logs.

Troubleshoot Database Mail

We’ve already mentioned the Database Mail log. Now let’s go over the other diagnostics available for Database Mail.

• Find out more about reading the Database Mail log at https://learn.microsoft.com/sql/relational-databases/database-mail/database-mail-log-and-audits.

Read email logs in the msdb database

If the SMTP Server or the Database Mail process becomes unavailable, the messages are queued in a table in the msdb database named dbo.sysmail_mailitems. This msdb database contains metadata tables for the Database Mail feature, including dbo.sysmail_allitems, which tracks all outbound email activity. Look for items for which the sent_status doesn’t equal sent for signs of messages that weren’t successfully sent; for example:

Click here to view code image

--Find recent unsent emails
SELECT m.send_request_date, m.recipients, m.copy_recipients, m.blind_copy_recipients
, m.[subject], m.send_request_user, m.sent_status
FROM msdb.dbo.sysmail_allitems AS m
WHERE
-- Only show recent day(s)
m.send_request_date > dateadd(day, -3, sysdatetime())
-- Possible values are sent (successful), unsent (in process),
-- retrying (failed but retrying), failed (no longer retrying)
AND m.sent_status <> 'sent'
ORDER BY m.send_request_date DESC;

There is also a view provided in msdb, dbo.sysmail_unsentitems, that filters on (sent_status = 'unsent' OR sent_status = 'retrying'). There are four possible values for sent_status in dbo.sysmail_allitems: sent, unsent, retrying, and failed.

• You can find more information about these and other sysmail views, including failed items and sent items, at https://learn.microsoft.com/sql/relational-databases/system-catalog-views/sysmail-allitems-transact-sql.

Enable Service Broker on the msdb database

After restoring the msdb database or setting up Database Mail for the first time, the Service Broker feature might not be turned on for the msdb database. You can check the is_broker_enabled field in the system catalog view sys.databases; if it is 0, this is the case, and you must remedy it. Otherwise, if you try to send email and the Service Broker is disabled, you will receive the following self-explanatory error message:

Click here to view code image

Msg 14650, Level 16, State 1, Procedure msdb.dbo.sp_send_dbmail, Line 73 [Batch Start
Line 18] Service Broker message delivery is not enabled in this database. Use the ALTER
DATABASE statement to enable Service Broker message delivery.

To turn on Service Broker for the msdb database, you must stop the SQL Server Agent service and close any connections active to the msdb database before running the following code:

Click here to view code image

ALTER DATABASE msdb SET ENABLE_BROKER;

Identify SMTP server authentication

Authentication with the SMTP server is likely the problem if you observe errors in the Database Mail log after attempting to send email, such as:

Click here to view code image

Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1
Unable to relay...

or:

Click here to view code image

Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1
Service unavailable...

Configure SQL Server Agent jobs

A job contains a series of steps. Each job step is of a type that allows for different actions to take place, such as the aforementioned T-SQL, CmdExec, or PowerShell tasks.

A job can be automatically started based on a number of conditions, including the following:

• A predefined schedule or schedules

• In response to an alert

• As a result of running the dbo.sp_start_job stored procedure in the msdb database

• When SQL Server Agent starts

• When the host computer is idle

You can script jobs in their entirety through SSMS, providing script-level recoverability, migration to other servers, and source control possibility for SQL Server Agent jobs. Jobs are backed up and restored via the msdb database or scripted for backup and migration.

In SSMS, in Object Explorer, expand SQL Server Agent, then expand the Jobs folder. Next, right-click any job, select Properties, and navigate to the Steps page. Here you will see that job steps do not necessarily need to run linearly. You can set a job to default to start at any job step. Additionally, when starting a job, you can manually change the start step for the job.

Each job step reports back whether it succeeded or failed, and you can configure it to move to another step or fail based on the job step outcome. These step completion actions are defined on the Advanced page of the Job Step Properties dialog box. However, for future ease of management, we recommend that you create job steps that are designed and run in a consistent pattern. Using a standard process will help others more easily understand and support these SQL Server Agent jobs. This embraces another key aspect of database automation: creating processes that can be easily managed.

You can assign jobs to categories. In fact, many system-generated jobs (such as replication) are assigned to categories. You can access settings to create your own categories in SSMS by right-clicking the Jobs folder under SQL Server Agent and selecting Manage Categories on the shortcut menu.

In T-SQL, you can run this script to add a new category:

Click here to view code image

EXEC msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Health Check';

This should aid your efforts to report on, maintain, redeploy, and migrate jobs in the future.

Understand job step security

A critical step that many developers and administrators skip is the use of credentials and proxies in SQL Server Agent job steps. SQL Server Agent jobs, by default, run steps in the security context of the SQL Agent service account. This may be acceptable for some local usage such as indexing, but using a proxy to run a job step instead of the SQL Server Agent service account or another named user is the most secure approach. Proxies make it possible for administrators to set job steps to run under a specific credential rather than giving the SQL Server Agent service account access to everything that each job needs.

Proxies are used for all job step types except one. It is not possible to run a T-SQL script job step using a proxy. A T-SQL step will run in the security context of the owner of the job if the owner is not a sysadmin. If the owner of the job is a member of the sysadmin server role, the job will run as the SQL Server Agent service account.

For all other job step types, there is a proxy. You can select the job step to run as a proxy on the Job Step Properties page. SQL Server Agent checks for access to the subsystem each time the job step is run to verify that the security has not changed.

A subsystem can be one of the following items:

• Operating system (CmdExec)

• Replication agent (Snapshot, Log Reader, Distribution, Merge, or Queue Reader)

• Analysis Services query or Analysis Services command

• SSIS package execution

• PowerShell script

• Microsoft ActiveX script (discontinued as of SQL Server 2016; replaced with PowerShell script)

You can associate each proxy with one or more subsystems, but to reduce your attack surface, you should create many proxies for different job step security requirements and subsystems.

Without a proxy specified, jobs must be owned by a member of the sysadmin role to run job steps other than the T-SQL step type. This is because the T-SQL step type allows you to specify an account under Run as so that the code will run under a different user’s permissions. These job steps will then run as the SQL Server Agent service account. This isn’t ideal, for two reasons:

• The SQL Server Agent service account should not have local administrator privileges on the server. This reduces the risk to the operating system (OS) from potential misuse for SQL Server Agent jobs. Service accounts are discussed in Chapter 4, “Install and configure SQL Server instances and features,” and Chapter 12, “Administer instance and database security and permissions.”

• The SQL Server Agent service account must be a member of the sysadmin server role, so it has far too many privileges inside SQL Server than necessary to safely run SQL Agent jobs.

The owner of the job must also have permission to use any subsystem that the job’s steps use. This is important because job steps often need to access other servers, and proxies give you the ability to assign pinpoint rights to those other resources. You will not be able to create or modify a job step for a subsystem if the job owner is not listed as a principal who has access to the proxy. Sysadmins automatically have access to all proxies.

Proxies map to credentials on the SQL Server; you’ll find a subfolder for credentials in the Security folder on the Server level in Object Explorer in SSMS. Each proxy is linked to a credential in SQL Server. The credential stores the account’s username and password, which means that if it changes, the proxy and SQL Server Agent job steps that depend on it will not be able to authenticate and will fail. Therefore, you should use service accounts, not individuals’ named accounts, in credentials that will be used by proxies. Credential account passwords shouldn’t be widely known, and the accounts should not regularly be used interactively by administrators to avoid accidentally becoming locked out.

You can create a credential for a local Windows account or a domain account. You also can create credentials for accounts on Extensible Key Management (EKM) modules, including the Azure Key Vault service. The Windows account of the credential must have Log on as a batch job permission on the server. As a local administrator, you can grant this permission in the Local Security Policy dialog box.

• You can read more about Azure Key Vault and EKM modules in Chapter 13.

Secure permissions to interact with jobs

To set up a SQL Server Agent job in SSMS, your login must be a member of the sysadmin server role or one of the SQL Server Agent database roles in the msdb database. The SQLAgentOperatorRole, SQLAgentReaderRole, and SQLAgentUserRole have permission to create jobs, start jobs, view jobs, view a job’s history, and edit job properties, though mostly only for jobs they own.

• For granular details on the limitations and overlapping of each role, visit https://learn.microsoft.com/sql/ssms/agent/sql-server-agent-fixed-database-roles.

The SQLAgentUserRole is the least privileged of the three roles, but the other two roles are members of the SQLAgentUserRole. Typically, membership to these roles is limited to service accounts and third-party developers. Grant permission on proxies to custom database roles, individuals, or service accounts. Do not grant permissions directly to the SQLAgentUserRole database role, including the ability to use proxies.

Schedule and monitor jobs

A job can be run based on one or more schedules assigned to it. You give schedules a name upon creation and can assign them to multiple jobs. This can be especially useful for uncommon or esoteric job schedules, or to centralize management of jobs that should run simultaneously. To view and select schedules from other jobs, select the Pick button on the Schedules tab of the Job Properties dialog box. You will see only the job schedules to which you have access.

There are four schedule types:

• Start automatically when SQL Server Agent starts

• Start whenever the CPUs become idle

• Recurring

• One time (for running a schedule manually—for instance, during testing or a one-off index rebuild)

Jobs run asynchronously when they are started manually or by SQL Server Agent. A dialog box with a spinning progress icon appears, but you can close it, and the job will continue to run until completion. You can monitor the progress of jobs in SSMS by viewing the Job Activity Monitor, and you can observe the job’s current request in sys.dm_exec_requests.

You can use T-SQL to query the status of jobs with the undocumented stored procedure master.dbo.xp_sqlagent_enum_jobs, which you can join to msdb.dbo.sysjobs, as shown here:

Click here to view code image

--Jobs still running
DECLARE @xp_sqlagent_enum_jobs TABLE (
id int not null IDENTITY(1,1) PRIMARY KEY,
Job_ID uniqueidentifier not null,
Last_Run_Date int not null,
Last_Run_Time int not null,
Next_Run_Date int not null,
Next_Run_Time int not null,
Next_Run_Schedule_ID int not null,
Requested_To_Run int not null,
Request_Source int not null,
Request_Source_ID varchar(100) null,
Running int not null,
Current_Step int not null,
Current_Retry_Attempt int not null,
[State] int not null);

INSERT INTO @xp_sqlagent_enum_jobs
EXEC master.dbo.xp_sqlagent_enum_jobs 1, '';

SELECT j.name
, state_desc = CASE ej.state
WHEN 0 THEN 'not idle or suspended'
WHEN 1 THEN 'Executing'
WHEN 2 THEN 'Waiting for thread'
WHEN 3 THEN 'Between retries'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Suspended'
WHEN 7 THEN 'Performing completion actions'
END
, *
 FROM msdb.dbo.sysjobs j
 LEFT OUTER JOIN @xp_sqlagent_enum_jobs ej
 ON j.job_id = ej.Job_ID
ORDER BY j.name;

Configure and view job history

Every time a job is run, a record is maintained in the msdb database in the dbo.sysjobhistory table. To review the job’s history, right-click it in SSMS and select Job History on the shortcut menu. History is stored for each job step. You can expand a given job to view the output for each step, including any errors.

With jobs that run frequently (for example, transaction log backup jobs), a large amount of job history will be created and stored in msdb. It is initially defaulted to two very low and likely unrealistic row caps: 1,000 rows of history for all jobs, and 100 rows of history at most for one job. If a job runs once per hour, it loses visibility into history after just four days—likely an unrealistic window for troubleshooting and diagnostic information.

In SSMS, in Object Explorer, right-click SQL Server Agent, select Properties, and select the History page. As shown in Figure 9-1, this page is not intuitive. The first option, Limit size of job history log, is a rolling job history retention setting. You might find it a good start to simply add a 0 to each value, increasing the maximum log history size in rows from the default of 1,000 to 10,000 or more, and also increase the maximum job history per job in rows from the default of 100 to 1,000 or more. These settings would store just over 41 days of history for a job that runs hourly, if this were the only job on the server. You might find these numbers also insufficient on a SQL Server instance with many frequently running jobs, so you should increase these settings until you have a comfortable job run history retention.

The job history log can be useful, but it has two limitations of which you should be aware:

• The message text in the Job History viewer is truncated after 1,024 characters. To view the full results of the output, you must query the dbo.sysjobhistory table. The message column in that table is considerably larger at 8,000 characters.

• The history of SQL Server Integration Services (SSIS) package execution in the SQL Server Agent job history is extremely limited—reduced to details around the fact that the package started, completed, and/or errored, without additional details. More verbose detail appears in the thorough history available in the SSISDB. To access and view this history, open the Integration Services Catalogs menu in SSMS and select the project or packages that failed.

You can also configure additional logging for each job step to capture the full step output and, more commonly, the error text. The following options are available on the Advanced page of the Job Step Properties dialog box:

• Output file. You can send step output to an Output file and Append output to existing file. Be wary of keeping the Append output to existing file option turned on long term—the output file can grow to a significant size in a short amount of time. If your history file becomes too large, this may cause the SQL Server Agent to crash.

• Log to table. This writes to the dbo.sysjobstepslogs table in the msdb database. This table has an nvarchar(max) data type for the Log field, allowing for more output data to be captured per step if needed. Be careful of this option, as well—the table can grow to a significant size in a short amount of time. You should schedule the stored procedure sp_delete_jobsteplog to remove old records from the table over time.

• Include step output in history. This adds a row to the job history log to include the output of the job step. This should contain valuable information, and, unlike with the other two options, job history is automatically maintained over time by SQL Server Agent.

Administer SQL Server Agent operators

An operator is an alias in SQL Server Agent that allows you to set up a name and email address(es) to receive messages. Usually, an operator should not be pointed to an individual (although you can create a semicolon-delimited list of email address); instead, it should be pointed to a distribution group (even if that group initially contains only one person). In most situations, you will create an operator to notify SQL Server first responders in your environment. You should maintain your environment’s list of DBA personnel in distribution lists, and not inside the operator lists of each SQL Server instance.

To set up an operator, in SSMS, in Object Explorer, expand the SQL Server Agent folder, right-click Operator, and select New Operator on the shortcut menu.

Configure alerts

Alerts are created to set conditions and, when met, prompt email notifications or the kickoff of SQL Server Agent jobs in response. Alerts are versatile and can look for SQL Server events in the Error Log for performance conditions that you would view in the Performance Monitor application or for Windows Management Instrumentation (WMI) queries.

As recommended in the “Set up SQL Agent” section of Chapter 4, you should set up alerts for high-severity SQL Server errors. However, do not overcommit your personal inbox with alerts, and do not set an inbox rule to Mark As Read and file away emails from SQL Server. By careful selection of emails, you can assure yourself and your team that emails from SQL Server will be actionable concerns that rarely arrive.

With a large number of SQL Server instances under your purview, email alerts for even severe issues can become too numerous and frequent. This is especially true if you receive individual alerts from each server! Look into gathering and queuing actionable errors in a system that provides for aggregation, dashboarding, and team assignment. There are third-party log-collection and log-management software applications that perform the task of log aggregation and centralized alerting.

You might also configure the Delay between responses setting for each alert to prevent an unchecked flooding of emails arriving from a repeating error. Consider a delay of up to 5 minutes between responses, as your environment and Service-Level Agreement (SLA) deem appropriate.

You can specify only a single error message or severity per alert. Consider scripting the mass creation of a standard batch of alerts, to be created consistently on all your SQL Server instances. We include a script in the accompanying downloads for this book as an example that includes the alerts we will examine in just a moment.

First, we review the three types of alerts you can set up:

• SQL Server event alert

• Performance condition alert

• WMI event alert

SQL Server event

You should set up alerts on actual error messages that are important enough for you to receive emails. SQL Server generates a lot of informational-only events, such as successful backup messages, for which you would not want to receive messages.

You can set up alerts based on the actual error number (samples follow shortly) or any error of a certain severity (1 to 25). You can optionally filter the alert to a single database or for a specific message text.

It is common practice to set up alerts for severity 16 through 19 and 21 through 25 because these tend to be actionable errors. Severities 21 and above are severe and unrecoverable errors.

The most common severity 20 errors are nuisance authentication-related and transient (the user tried, experienced an error, tried again, and succeeded). An alert for severity 20 might send out many unactionable alerts to the SQL Server DBA team. You will still see severity 20 issues in the SQL Server Error Log and should make note of them as they appear, especially if they do so in large numbers, given that this can be a sign of greater authentication or domain issues or malicious intrusion attempts. The goal of alerts is to send out actionable errors or performance conditions worth investigating.

You might also want to configure alerts to send out error messages for the following SQL Server error numbers that are not already covered in severities 16 through 19 and 21 through 25. These errors are rare, but immediately actionable:

• 825 (severity 10). A dreaded “read-retry” error, which occurs when a file read succeeds after failing n number of times. This is often a harbinger of a database-integrity failure and should prompt immediate action.

• 854, 855, 856 (severity 10). Uncorrectable hardware memory corruption detected via the operating system’s memory diagnostics. This may indicate a potentially immediate threat to system stability due to memory.

• 3624 (severity 20). An internal SQL Server error called an assertion failure. This is typically a software bug, though it could indicate internal data corruption. Errors at this severity are often addressed via a SQL Server cumulative update or patch.

Performance conditions

On Windows, you can set up performance condition alerts for any performance counter in the SQLServer category—the same set of alerts you would see in Windows Performance Monitor with the prefix SQLServer or MSSQL$instancename. For example, if you want to receive an email when the SQL Server’s page life expectancy (PLE) drops below a certain value, you select it in the same way you would in Performance Monitor: Choose the Buffer Manager object, the Page Life Expectancy counter, the Falls Below comparison operator, and a comparison value. In the case of PLE, this is measured in seconds.

WMI event alert conditions

The third option for SQL Server Agent alerts allows for custom WMI queries to be run (for SQL Server on Windows only). WMI queries can gather alerts on a variety of Data Definition Language (DDL) events in SQL Server, such as CREATE, ALTER, and DROP. While WMI queries follow the basic syntax of T-SQL queries, the FROM of the WMI query will be a WMI object, not an object in a SQL Server database.

• You can see an example of a WMI event alert at https://learn.microsoft.com/sql/relational-databases/wmi-provider-server-events/sample-creating-a-sql-server-agent-alert-with-the-wmi-provider.

• You can reference the WMI provider classes and properties at https://learn.microsoft.com/sql/relational-databases/wmi-provider-server-events/wmi-provider-for-server-events-classes-and-properties.

This type of alert is not as straightforward as the other types. In general, you might find better results, more flexibility, and less complexity by using Extended Events, SQL Server Agent jobs, SQL Server Audit, and/or third-party monitoring tools than by using WMI alert queries.

• You can read more about Extended Events in Chapter 8.

Setting up an email recipient for a WMI event alert does not send over any useful or actionable information in the email aside from the alert’s name. This does little more than let you know that a WMI event occurred (observed asynchronously, so there might be some delay).

To view the information regarding the event—for example, the T-SQL command associated with the event—you must enable token replacement. To do so, open the SQL Server Agent Properties dialog box. Then, at the bottom of the Alert System page, select the Token Replacement check box. This allows for the tokenization (replacement at runtime) of WMI commands in a T-SQL job step.

• For more on the tokens that you can use in a T-SQL job step, visit https://learn.microsoft.com/sql/ssms/agent/use-tokens-in-job-steps.

We have prepared a sample WMI event alert to capture the CREATE DATABASE DDL event in the accompanying downloads for this book.

SQL Server Agent job considerations when using availability groups

If you are running SQL Server Agent jobs in an availability group environment, you will still need to configure maintenance plans on each SQL Server instance. You must cover databases that are writeable when the instance is the primary replica, databases not included in availability groups, as well as the system databases master and msdb. You should ensure that your maintenance plans, regardless of platform, are consistently updated on all replicas and also are aware of their local replica role so that maintenance plans do not need to be turned on, turned off, or reconfigured when a failover occurs.

The SQL Server Agent must be enabled, and your SQL Server Agent jobs must exist on all replicas of the availability group and be replica-aware. (The script should know if it is running on the primary replica for a database.) You will need multiple versions of any custom maintenance task in order to separate scripts for databases in each availability group, and one more for databases not in an availability group (including any system databases that you intend to maintain with custom scripts).

To avoid having SQL Server Agent jobs generate an error when their local replica is not the primary replica for a database, you can add a T-SQL step to the start of the job to detect and raise a failure. The goal of the first step is to prevent subsequent job steps from running and failing against secondary replica databases, which will not be writeable. Name the first step “Am I Primary?” or something similar, and then add the following script:

Click here to view code image

--add as step 1 on every AG-aware job
IF NOT EXISTS (
SELECT @@SERVERNAME, *
   FROM sys.dm_hadr_availability_replica_states rs
   INNER JOIN sys.availability_databases_cluster dc
   on rs.group_id = dc.group_id
   WHERE is_local = 1
   and role_desc = 'PRIMARY'
--Any databases in the same Availability Group
   and dc.database_name in (N'databasename1', N'databasename2'))
BEGIN
   print 'local SQL instance is not primary, skipping';
   throw 50000, 'Do not continue', 1;
END;

This code causes step 1 to fail when it is not run on a primary replica for the specified database(s). If you run the preceding code referencing a database that is not in an availability group, the script will also cause step 1 to fail. In the Advanced settings of the “Am I Primary?” job step, the On Success Action setting should be Go to The Next Step, as usual, but the On Failure Action setting should be Quit the Job Reporting Success, which would not register as a job failure. Instead of a green check mark or a red X next to the job, SQL Server Job History displays a yellow triangle. This prevents subsequent job steps from running and failing against secondary replica databases, which will not be writeable.

• For more about availability groups, see Chapter 11, “Implement high availability and disaster recovery.”

Check Database Integrity task

The Check Database Integrity task runs DBCC CHECKDB to check for database corruption—a necessary task that you should run periodically. You should run DBCC CHECKDB at least as often as your backup retention plan. For example, if you keep local backups around for one month, you should perform a successful DBCC CHECKDB no fewer than one time per month—and more often, if possible. On large databases, this task could take hours.

• For more about data corruption and checking database integrity, see Chapter 8.

The options available in the maintenance plan task match the common parameters you would use in the DBCC CHECKDB command. The Physical Only check box uses the PHYSICAL_ONLY parameter of DBCC CHECKDB, which limits DBCC CHECKDB to checking physical structures, torn pages, checksum failures, and common hardware failures. It is less comprehensive as a result. However, using PHYSICAL_ONLY can take significantly less time to complete while still detecting the signs of common storage hardware failure.

Shrink Database task

There is no sound reason to ever perform a Shrink Database task on a schedule. A Shrink Database task removes free space from a file and returns it to the OS, causing the file to experience an autogrowth event the next time data is written to it. Do not ever include the Shrink Database task in the maintenance plan.

Reorganize Index task

A Reorganize Index task runs an ALTER INDEX ... REORGANIZE statement, which reduces index fragmentation but does not update statistics. By default, this task does not reorganize all indexes. If you need to reorganize all indexes on a scheduled basis, you will need to remove the default values on this task. On large databases, this could take hours, but will have less overhead, less query disruption, and finish faster than a Rebuild Index task (discussed next).

Because Reorganize Index is an online operation and reads only one 8-KB data page at a time, it will not take long-term table locks and might block other user queries for only a very short amount of time. Online index operations will consume server resources and generate large amounts of logged transactions.

If you use this task in your maintenance plan, remember to add an Update Statistics task to run immediately after it.

• The “Rebuild Index task” section covers a method to maintain indexes that are above a certain fragmentation percentage.

Rebuild Index task

More thorough than a Reorganize Index step at removing index fragmentation, this task runs an ALTER INDEX ... REBUILD statement and does update statistics. The options available in the Rebuild Index dialog box correspond to the options for the ALTER INDEX ... REBUILD syntax.

With Enterprise edition, you can perform a Rebuild Index step as an online operation, which is not likely to block other user queries like an offline rebuild does. Not all indexes and data types can have an online rebuild performed, so the Maintenance Plan dialog box for the Rebuild Index task will ask you what you want to happen.

Rebuilding indexes without the ONLINE option will block other user queries attempting to use that index, and will consume additional server resources. On large tables, this could take hours to finish, and even more without the ONLINE option due to the overhead of managing blocking.

Maintain indexes above a certain fragmentation percentage

You can intelligently limit index maintenance to certain thresholds, starting with the options to select between Fast (LIMITED), Sampled, and Detailed. This corresponds to the parameters provided to the structural statistics dynamic management function (DMF), sys.dm_db_index_physical_stats.

You can configure the Reorganize Index and Rebuild Index tasks to maintain only indexes filtered by percentage of fragmentation and page count, both from sys.dm_db_index_physical_stats, and/or actual index usage (based on the sys.dm_db_index_usage_stats DMF). The fragmentation threshold is 15 percent by default in the Reorganize Index task, and 30 percent in the Rebuild Index task, but these values are only a guideline.

Other options added to the Reorganize Index and Rebuild Index tasks match the options for the ALTER INDEX … REORGANIZE, and REBUILD T-SQL commands, covered in detail in Chapter 8.

Update Statistics task

The Update Statistics task runs an UPDATE STATISTICS statement, rebuilding index statistics, which we discuss in Chapter 15. Do not follow an Index Rebuild task with an Update Statistics task for the same objects, because this is redundant work. Updating statistics is an online operation, so it will not block other user queries, but it will consume server resources unnecessarily. This task should finish faster than either a REORGANIZE or REBUILD step. On larger databases, or databases with flash storage, this task can replace more frequent Rebuild Index or Reorganize Index tasks.

History Cleanup task

This task deletes older rows in msdb tables that contain database backup and restore history, prunes the SQL Server Agent log file, and removes older maintenance plan log records. These are accomplished by running three stored procedures in the msdb database: dbo.sp_delete_backuphistory, dbo.sp_purge_jobhistory, and dbo.sp_maintplan_delete_log, respectively. You should run this task to prevent excessively old data from being retained, according to your environmental data-retention requirements. This will save space and prevent large table sizes from degrading the performance of maintenance tasks. This step should finish quickly and not disrupt user queries. This step does not delete backup files or maintenance plan log files; that is the job of the Maintenance Cleanup task, discussed next.

Maintenance Cleanup task

The Maintenance Cleanup task deletes files from folders and is commonly used to delete old database backup files, using the system stored procedure master.dbo.xp_delete_file. You also can use it to clean up the .txt files that maintenance plans write their history to in the SQL Server instance’s Log folder. You can configure the task to look for and delete any extension by folder directory, and then specify that subdirectories be included. The date filter uses the Date Modified file attribute (not the Date Created attribute). While this may not be an issue in many cases, it can prevent files from being cleaned up if you are appending data to these files. Combined with the option to create a subdirectory for each database, this means you can create and remove backup files in the folder structure for each database.

The Maintenance Cleanup task deletes files only from folders, and thus isn’t an option to enforce a retention policy for backups to URL in Azure Storage.

Execute SQL Server Agent Job task

Using this task, you can orchestrate the asynchronous start of another SQL Server Agent job during the maintenance plan, perhaps to start another middle-of-the-night process as soon as possible after maintenance is complete.

Back Up Database (Full, Differential, Transaction Log) task

With this task, you can take a backup of any kind of the specified databases. The options in the Maintenance Plan dialog box for the backup are similar to the SSMS Database Backup dialog box, plus some minor extra options, including an option to ignore replica priority in an availability group database.

The Back Up Database task affords you multiple strategies for backups, including backing up to disk or to Azure Storage via URL as well as to the deprecated tape backup support.

Backing up to URL writes files directly to Azure Storage natively, without the need to install any software or network connections. This was a fairly limited feature before SQL Server 2016, but now can be accomplished via a shared access signature (SAS) credential for secure access to Azure Blob Storage.

• A step-by-step walk-through is available at https://learn.microsoft.com/sql/relational-databases/tutorial-use-azure-blob-storage-service-with-sql-server-2016. Note that this process hasn’t changed much since SQL Server 2016.

You can configure disk backups to append multiple database backups multiple times to the same file or files, or create a backup file and a subdirectory for each database per backup. This is generally recommended both for ease of accessing files and for using tools to remove out-of-date files as needed. For backups to disk, we recommend that each database has a subdirectory in the folder location to separate the backup files of databases with potentially different retention plans or recovery strategies. The maintenance plan backup will automatically create subdirectories for new databases, and when performing backups, append a timestamp and a unique string to backup names in the following format:

Click here to view code image

databasename_backup_yyyy_mm_dd_hhmmss_uniquenumber.bak|dif|trn

We recommend that you select the options for Verify Backup Integrity, CHECKSUM, and Compress Backup for all database types, for all databases.

This is supported even for backups to URL. Keep in mind that the Verify Backup step performs a RESTORE VERIFYONLY statement to examine the backup file and verify that it was valid, complete, and should be restorable. Should be is key, because the only way to truly test whether the backup was valid is to test a restore. The RESTORE VERIFYONLY does not actually restore the database backup, but could give you an early heads-up on a potential drive or backup issue, and is always recommended when time permits. The Verify Backup step could significantly increase the duration of the backup, scaling with the size of the database backup, but is time well worth spending in your regular maintenance window.

Execute T-SQL Statement task (not available in the wizard)

This task can run T-SQL statements against any SQL Server connection, with a configurable time-out. A simple text box accepts T-SQL statements. Because of its simplicity, we recommend that instead of pasting lengthy commands, you reference a stored procedure. This would be easier to maintain and potentially keep in source control by developing the stored procedure in other tools. You may find it useful to keep this and other stored procedures in a database specifically dedicated to database administration tasks.

When not to use SQL Server maintenance plans

Personal preference, of course, is a fine enough reason not to use built-in maintenance plans. You can write your own, as long as your library of scripts or third-party tools accomplishes the necessary maintenance tasks with consistency, configurability, and good logging for review.

For SQL Server instances with manageable maintenance windows, maintenance plans will meet your needs if the schedules are set up appropriately. You can create a variety of maintenance plans to cover databases with various levels of importance or availability based on business requirements (with the caveat of not being able to detect new databases). For very large databases or databases with 24×7 availability requirements, more granularity for maintenance operations will likely be necessary.

Not every business has the luxury of having all night and/or all weekend to perform maintenance outside of business hours. When you become familiar with the T-SQL commands and their various options, you can be creative to overcome tight scheduling, crowded maintenance windows, very large databases, or other maintenance plan complications.

After reviewing the capabilities of the Back Up Database and Rebuild Index tasks, you should consider maintenance plans more full-featured and capable of handling the bulk of maintenance, even on larger databases with tight schedules. Ultimately, the success of maintenance plans or custom scripts depends on your understanding of the various options available for the seven core maintenance tasks listed earlier in this chapter and elsewhere in this book.

Understand backup priority values

In SSMS, in Object Explorer, expand the Always On High Availability node for the instance of the primary replica, and then the Availability Groups node. Right-click the availability group you want to view, select Properties, and look at the Backup Preferences page. It’s important to understand the priority values and how they interact with various backup tasks.

The default option is Prefer Secondary, which specifies that backups occur on the secondary replica first or, if it is not available, on the primary replica. You then can provide priority values (0 to 100, where 100 is highest) to decide which of the multiple secondary replicas should be the preferred backup location. The values apply to both full and transaction log backups.

Other self-explanatory options include Primary, Secondary Only, and Any Replica, which uses the priority values to decide which replica is preferred for the backups. When failing over to another replica, you will need to review and script the changes to the backup priority. Your planned failover scripts should include the reassignment of backup priority values.

This Backup Preferences page affects only backup systems or scripts that are aware of the backup preferences. For example, in SSMS, in Object Explorer, right-click a database, select Tasks on the shortcut menu, and then Backup. The dialog box that opens takes a backup of a database but does not include any availability groups–aware settings. On the other hand, the Back Up Database task in SQL Server maintenance plans is aware of availability group backup priority value settings.

You are limited to taking full copy-only database backups and transaction log backups on readable secondary replica databases. When including databases in an availability group, the maintenance plan Back Up Database task will warn you if you are attempting to configure a full database backup without a copy-only check or differential backup, or if you select the For availability databases, ignore Replica Priority for Backup and Backup on Primary Settings check box. If this is misconfigured, it is possible to create a maintenance plan that will run but not create backups of databases in an availability group.

• For more information on using replica backup priorities, visit https://learn.microsoft.com/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups and https://learn.microsoft.com/sql/database-engine/availability-groups/windows/configure-backup-on-availability-replicas-sql-server.

Use replica backup priority in your backup schedules

If you attempt to configure a full database backup without copy-only or a differential backup on a secondary node, you will see the warning in the Back Up Database task, “This backup type is not supported on a secondary replica and this task will fail if the task runs on a secondary replica.” If you select the Ignore Replica Priority check box, the warning will read, “Note: Ignoring the backup priority and availability group settings may result in simultaneous parallel backups if the maintenance plan is cloned on all replicas.”

Maintenance plans should run on a schedule on all availability group replicas. The priority values for backups can cause a backup not to be taken on a primary or nonpreferred secondary replica, but the maintenance plan backup task will start and complete as usual. The maintenance plans will use the backup priority values and do not need to be configured when different replicas in the availability group become primary.

You can still take a manual backup of any type of the databases and bypass the availability group backup preferences. In fact, your backup strategy might include intentionally taking full backups in more than one node of a geographically dispersed availability group.

If you are not using SQL Server maintenance plans or scripting to take backups, be aware that not all third-party backup solutions are aware of availability group backup preferences or even availability groups in general. Maintenance plans in SSMS are aware of these backup preferences, and your custom scripting can be, too, via the scalar function sys.fn_hadr_backup_is_preferred_replica. This function returns a 0 or 1, based on whether the current SQL Server instance is operating as the preferred backup.

Create an MSX/TSX with SSMS

In SSMS, in Object Explorer, right-click SQL Server Agent, select Multi Server Administration, and then select Make This A Master to launch the Master Server Wizard. The wizard first sets up a special operator just for running multiserver jobs; this operator is created for you and named MSXOperator. You can specify only one operator to run multiserver jobs, so think carefully about who should be notified about these jobs. Specify the email address. As always with operators, it’s best not to use an individual’s email addresses, but to use an email distribution group, instead.

Next, the wizard presents locally registered and Central Management–registered servers so you can select them as targets for the MSX. Select the target servers from the list or, at the bottom, select Add Connection and add servers not registered in your list.

When you are finished with the wizard, the labels in Object Explorer will be different for both master and target SQL Server Agents. Similarly, on the MSX, under SQL Server Agent in Object Explorer, you will see two new subfolders under Jobs: Local Jobs and Multi-Server Jobs.

By default, SSL encryption is used between the servers, but you can change this through a registry setting. You should not need to change it, however, because encrypted communication between the servers is recommended, even on your internal networks. You will need to install an SSL certificate on the server before using it through the wizard.

• If you cannot use SSL but need to secure the connection between a TSX and an MSX, see https://learn.microsoft.com/sql/ssms/agent/set-encryption-options-on-target-servers for the exact entries.

Although we do not recommend it, you can disable the encryption of an MSX/TSX by changing the registry set to not use encryption. The registry key HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerinstanceSQLServerAgentMSxEncryptChannelOptions is by default 2, which means all communication is encrypted. Changing this to 0 on all servers removes encryption.

Manage multiserver (MSX/TSX) administration

To manage SQL Server Agent jobs in a multiserver environment, set up the job the same way you would any other job on the MSX. Then, navigate to the targets page on the job, which outside of a multiserver environment has no use. From there, select the Target Multiple Servers option button; then select the servers on which the job should run. In the background, code is run to send the jobs to the TSX.

Any jobs with steps that use proxies will need the proxy accounts to have access to the TSX, and a proxy by that same name on the TSX. Otherwise, the job will not find a proxy on the TSX. By default, matching proxy names from MSX to TSX isn’t allowed, because of the potential for malicious action. This must be enabled via the registry on the TSX.

• For more information, visit https://learn.microsoft.com/sql/ssms/agent/troubleshoot-multiserver-jobs-that-use-proxies.

On a TSX, jobs appear in the SQL Server Agent, in the Jobs folder, but you can’t edit them.

Sometimes, synchronizing job definitions to the TSX will not be queued up and will not post to a server. In this case, issue the following command, but be aware that this will delete and re-create any jobs specified for the target server, therefore cancelling those jobs on the TSX:

Click here to view code image

EXEC msdb.dbo.sp_resync_targetserver '<Target Server Name>';

Most other issues with multiserver jobs are solved by defecting the TSX and then adding it again.

Manage multiserver administration in availability groups

MSX/TSX works with availability groups and can be quite useful for ensuring that jobs stay synchronized across servers. MSX/TSX data is stored in system tables in the msdb database, which cannot be part of an availability group.

While SQL Server 2022 allows you to set up the MSX feature in a contained availability group, it is not recommended due to the additional complexity in managing SQL Server Agent jobs. Thus, you should not have the MSX on a SQL Server instance in the availability group, because this would limit your ability to failover and use SQL Server Agent Multiserver Administration. You would lose your ability to orchestrate jobs across the target servers if one of the nodes in your availability group was unreachable, compromising your failover state. Instead, consider other high-availability solutions for the MSX.

Using MSX/TSX for availability group SQL Server Agents doesn’t change the need to set up the “Am I Primary?” logic in step 1 of any job that should run only on a SQL Server instance that currently hosts the primary replica.

• For a code sample, refer to the section “SQL Server Agent job considerations when using availability groups” earlier in this chapter.

Set up event forwarding

You configure event forwarding in the SQL Server Agent Properties dialog box. In SSMS, open the SQL Server Agent Properties dialog box and choose the Advanced page. In the SQL Server Event Forwarding section, select the Forward Events to a Different Server check box. In the text box, type the name of the alerts management server. Be aware that the alerts management server that receives forwarded events must be the SQL Server default instance of a server. (In other words, it cannot be a named instance.)

You can also choose whether to send all events or only unhandled alerts that have not been handled by local alerts on each SQL Server instance. You can then specify a minimum error severity to be forwarded. Choose a severity level that matches your needs, keeping in mind the caveat of nuisance severity errors.

• For information about alerts and error severity, refer to the section “Foundations of SQL Server automated administration” earlier in this chapter.

Evaluate policies and gather compliance data

After you create a policy, you have multiple options for when to evaluate it: on demand (manually); on a schedule; and continuously, which either logs or blocks policy violations.

SQL Server maintains a history of all policy evaluations in a policy history log, which is available within SSMS. To access it, expand the Management folder for the instance, right-click Policy Management, and select View History on the shortcut menu.

In the System Policies subfolder, you’ll find 14 prebuilt policies for checking the health of availability groups, and two more for the SQL Server Managed Backup feature, first introduced in SQL Server 2014. The SQL Server Managed Backup policies begin with SmartAdmin.

For example, after creating a policy, you can configure it with On Demand Evaluation Mode (see the code examples at the end of this section), and then test it by enabling the AdHocRemoteQueries setting in the Surface Area Configuration facet. You can also enable it using T-SQL with the following command:

Click here to view code image

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

A message will immediately appear in the SQL Server Error Log stating, “Policy ‘Keep AdHocRemoteQueries Disabled’ has been violated by target ‘SQLSERVER:SQLservernameinstancename’,” accompanied by a severity 16 error 34053 message.

The on-demand Evaluate mode gives the administrator an immediate report of all policy compliance. From the Evaluate Policies window—which you access by expanding Policy Management, right-clicking Policies, and choosing Evaluate—you can view any number of policies. Should any expressions fail, the policy will display a red X beside it. In this example, evaluating the policy with the AdHocRemoteQueries facet enabled displays an error message and provides you with the ability to apply the change to bring the servers in line with policy.

In the Target Details pane of the Evaluate Policies window, you can select Details to start an analysis of all expression evaluations in the condition.

You can have the policies run automatically by either defining a schedule or having the policy evaluated in response to an action. To select either of these options, go to Policy Management, expand the list, and choose Policies. To set up an evaluation method for a specific policy, right-click the policy and select Properties. Then, in the Evaluation Mode drop-down near the bottom of the window, select On Schedule. You will get the option to set up the schedule in the same window. The scheduled Evaluate mode generates only SQL Server Error Log activity.

The other option in the drop-down is On Demand. This option makes it possible for you to create a policy that prevents developers or other administrations from making DDL changes that violate condition expressions. Not all facet expressions allow for the On Change: Prevent Evaluation mode.

This is accomplished by rolling back the transaction that contains the violating action. As with all rollbacks, this transaction could contain other statements and could cause a database change deployment to fail in a manner that could complicate your change process. In many cases, it may be preferable to have rules automated so that the developers can quickly see if their changes don’t meet coding standards. Not only can this save time in the development and QA process, but it removes the burden of enforcing these changes from the DBA. Administrators and database developers should be aware of the potential impact to database deployments. You should limit the use of the On Change: Prevent Evaluation mode to security-related or stability-related properties in both production and QA environments, such as the following:

• Server security. @CrossDBOwnershipChainingEnabled

  Enforce the evaluation of this to False unless this is part of application security design.

• Server security. @PublicServerRoleIsGrantedPermissions

  Enforce the evaluation of this setting to False in any circumstance.

• Login. @PasswordPolicyEnforced

  Enforce the evaluation of this setting to True for all logins.

• Certificate. @ExpirationDate

  Enforce that this date is not within a certain time frame in the future (six months), in every database (sample code to follow).

• Database. @AutoShrink

  Enforce the evaluation of this setting to False in all databases.

• Database. @AutoClose

  Enforce the evaluation of this setting to False in all databases.

Samples

You can script policies to T-SQL for application to multiple servers, though be wary of applying policies for production systems to development systems, and vice versa. You can use a T-SQL script to create the two aforementioned samples. This script is available in the accompanying downloads for this book.

The first part of the script creates a sample policy to keep the Surface Area Configuration option AdHocRemoteQueries turned off (it will be evaluated on demand). The result sets returned by these queries contain only the integer IDs of new policy objects that have been created.

The second part of the script verifies whether any non-system certificates have an expiration in the next six months and, if so, fail the policy. The policy will also ignore any certificates with ## in the name. These are built into SQL Server, are for internal use only, are generated when SQL Server is installed, and cannot be modified. Also keep in mind that certificates used for transparent data encryption (TDE) will continue to work just fine after expiration, so there’s no cause for concern if they show up in this list.

The result sets returned by these queries contain only the integer IDs of new policy objects that have been created.

Offline installation

To install the module on a server or workstation that is not Internet-connected or cannot reach the PowerShell Gallery, go to a workstation that can reach the PowerShell Gallery and has at least Windows PowerShell 5.0 or PowerShell 7. Then, use the following command to download the module, making sure to choose a folder that exists on your machine.

Click here to view code image

Save-Module -Name SQLServer -LiteralPath "C:	emp"

Next, copy the entire C:tempSqlServer folder to the machine that cannot reach the PowerShell Gallery to a path that is in the list of PSModule paths. The potential paths for modules list is stored in a PSModulePath environment variable, which you can view in Windows System Properties, or more easily with this PowerShell script:

Click here to view code image

$env:PSModulePath.replace(";","`n"")

The default folder for the module downloaded from the gallery would likely be C:Program FilesWindowsPowerShellModules on Windows, or /usr/local/share/powershell/Modules on Linux. Verify that this path is available or choose another PSModule folder, and then copy the downloaded SQLServer folder there. The following script adds the SQLServer module, and then shows a list of all available modules on your workstation:

Import-Module SQLServer
Get-Module

Backup-SqlDatabase

Let’s learn about some more basics and syntax of PowerShell via the Backup-SqlDatabase cmdlet. With this PowerShell cmdlet, you have access to the same parameters as the T-SQL command BACKUP DATABASE.

Again, use PowerShell’s built-in help files to see full syntax and examples, many of which will be familiar to you if you have a good understanding of the BACKUP DATABASE options.

Click here to view code image

Get-Help Backup-SqlDatabase -Examples

Here is an example of how to back up all databases on a local SQL Server instance, providing the backup path, and including a subfolder with the database’s name. If you are using a Windows computer, the default execution policy is RemoteSigned. If you would like to verify your execution policy before running the following script, you can run Get-ExecutionPolicy. If your policy is not set to RemoteSigned, you can run the following: Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope LocalMachine. The script also adds the current date and time to the name of the backup file:

Click here to view code image

#Backup all databases (except for tempdb)
$instanceName = "localhost" #set instance to back up
$path = "F:Backup"
Get-SqlDatabase -ServerInstance $instanceName | `
   Where-Object { $_.Name -ne 'tempdb' } | `
    ForEach-Object {
    Backup-SqlDatabase -DatabaseObject $_ `
    -BackupAction "Database" `
    -CompressionOption On `
    -BackupFile "$($path)$($_.Name)$($_.Name)_$(`
        Get-Date -Format "yyyyMMdd")_$(`
        Get-Date -Format "HHmmss_FFFF").bak" `
    -Script #The -Script generates T-SQL, but does not execute
}

Here are some notes about this script:

• Adding the -Script parameter to this and many other cmdlets outputs only the T-SQL code, split by GO batch separators; it does not actually perform the operation.

• The back tick, or grave accent (`) symbol (below the tilde on most standard keyboards), is a line extension operator. Adding the ` character to the end of a line gives you the ability to display long commands, such as in the previous example, over multiple lines.

• The pipe character (|) is an important concept in PowerShell to grasp. It passes the output of one cmdlet to the next. In the previous script, the list of databases is passed as an array from Get-SQLDatabase to Where-Object, which filters the array and passes it to ForEach-Object, which loops through each value in the array.

Remove-Item

Let’s learn some more about common PowerShell syntax parameters. You can use the Remove-Item cmdlet to write your own retention policy to delete old files, including backup files, stored locally. Remember to coordinate the removal of old local backups with your off-premises strategy that keeps backups safely in a different location.

In this script, we use the Get-ChildItem cmdlet to Recurse through a subfolder, ignore folders, and select only files that are more than $RetentionDays old and have a file extension in a list we provide:

Click here to view code image

$path = "F:Backup"
$RetentionDays = 1
$BackupFileExtensions = ".bak", ".trn", ".dif"
Get-ChildItem -Path $path -Recurse | `
    Where-Object { !$_.PSIsContainer `
         -and $_.CreationTime -lt (get-date).AddDays(-$RetentionDays) `
        -and ($_.Extension -In $BackupFileExtensions) `
         } | Remove-Item -WhatIf

Here are some notes about this script:

• The $RetentionDays parameter is a positive value, but a negative is added to it as part of the AddDays() method to subtract the number of retention days from the current date (specified with get-date).

• The Get-ChildItem cmdlet gathers a list of objects from the provided path, including files and folders. The -Recurse parameter of Get-ChildItem causes the cmdlet to include subfolders.

• The $_ syntax is used to accept the data from the object before the previous pipe character (|). In this example, the objects discovered by Get-ChildItem are passed to the Where-Object, which filters the objects and passes that data to Remove-Item.

• Adding the -WhatIf parameter to this and many other cmdlets does not actually perform the operation, but provides a verbose summary of the action, instead. For example, rather than deleting old backup files, this PowerShell script returns something similar to the following sample:

  Click here to view code image

  What if: Performing the operation "Remove File" on target "F:Backupackup_
  test_202202010200.bak".

Invoke-Sqlcmd

The Invoke-Sqlcmd cmdlet can run T-SQL commands, including on remote SQL Server instances and Azure SQL databases. Invoke-Sqlcmd can run batch file scripts that used to be run by sqlcmd. Use Invoke-Sqlcmd when no cmdlet exists to return the same data for which you’re already looking. In this script, we connect to a database in Azure SQL Database and run a query to see current sessions:

Click here to view code image

$instanceName = "azure-databasename.database.windows.net"
Invoke-Sqlcmd -Database master -ServerInstance $instanceName `
-Query "select * from sys.dm_exec_sessions" | `
Format-Table | Out-File -FilePath "C:TempSessions.txt" -Append

Here are some notes about this script:

• The Invoke-SqlCmd cmdlet uses Windows Authentication by default. Notice that we passed no authentication information at all. You can also provide the UserName and Password parameters to the Invoke-SqlCmd to connect via SQL Authentication to SQL Server instances, though this is not recommended unless you are on a secure connection. There is also the option to connect to Azure SQL Database or Azure SQL Managed Instance using an access token or a service principal.

• The | Format-Table cmdlet has a big impact on the readability of script output. Without Format-Table, the script returns a long list of column names and row values. The Format-Table output does not include all columns by default, but returns a wide line of column headers and row values, similar to how SSMS returns results in Text mode.

• The | Out-File cmdlet dumps the output to a text file instead of to the PowerShell console, creating the script if needed. The -Append parameter adds the text to the bottom of an existing file.

• The Out-File can be handy for archival purposes, but for viewing live rowsets—especially SQL Server command results—try using the Out-GridView cmdlet instead (Windows-only). It provides a full-featured grid dialog box with re-sortable and filterable columns, and so on. Out-GridView is used instead in the following sample:

  Click here to view code image

  $instanceName = "localhost"
  Invoke-Sqlcmd -Database master -ServerInstance $instanceName `
  -Query "select * from sys.dm_exec_sessions" | `
  Out-GridView