Introducing the AWSSQLServer-Backup Automation document for SQL Server maintenance

This is the first in a five-part series to introduce you to AWS Launch Wizard Systems Manager Automation documents for SQL Server. AWS maintains these predefined Automation documents, which you can use to perform critical maintenance tasks for SQL Server, including backup, restore, reindex, and DBCC. In this post, I will describe the purpose of the AWSSQLServer-Backup Automation document, share prerequisites, and show you how to run it from the AWS Systems Manager console.

The SQL Server maintenance initiative is a joint venture between Launch Wizard and AWS Systems Manager to help ease the burden of maintenance operations on deployed SQL Server instances. These runbooks are implemented using a combination of SSM Automation documents and scripts that execute on your SQL Server instance. You can use the Systems Manager console or the AWS CLI to enter parameters and then execute the scripts on your SQL Server instance.

Ola Hallengren’s scripts

If you ask any SQL Server DBA what they’re using for maintenance, they’ll invariably reply, “Have you checked out Ola Hallengren’s scripts?”

Ola’s SQL Server Maintenance Solution is based on a set of stored procedures for running backups, integrity checks, and index and statistics maintenance on all editions of Microsoft SQL Server on Windows and Linux. Ola designed the solution for the most mission-critical environments. It’s used in many organizations around the world.

We’ve implemented Ola’s SQL Server Maintenance Solution through four SSM Automation documents. They are exposed as shared document runbooks through Application Manager, a capability of Systems Manager.

If you search for AWSSQLServer- in the Systems Manager console, you’ll see the four SQL Server maintenance documents.

On the Owned by Amazon tab of the Documents page, the four Automation documents are displayed: AWSSQLServer_Backup, AWSSQLServer-DBCC, AWSSQLServer-Index, AWSSQLServer-Restore.

Figure 1: Documents page of Systems Manager console

AWSSQLServer-Backup

The AWSSQLServer-Backup Automation document includes the steps to back up a specified database in full, differential, or transactional mode. After the backup is complete, you can upload it to a specified folder in an Amazon Simple Storage Service (Amazon S3) bucket.

The backup modes are defined as follows:

Full: A complete backup of the database.
Differential: The delta of changes since the last full backup.
Transactional: A log of changes from the last backup.

Prerequisites for using this document

The instance must be running SQL Server.
An AWS Secrets Manager database credential must be configured with the following two fields: username and password. For more information, see Authentication and access control for AWS Secrets Manager.
- On Windows, the username and password must be the Active Directory domain account and password used to authenticate SQL Server.
- On Linux, the username and password must be the SQL Server login name and password.
The username must be assigned, at minimum, the following database role: db_backupoperator.
You might need to grant additional permissions or assign roles on the instances that allow access to AWS Secrets Manager credentials and Amazon S3.
Backups must be staged to the local disk to run AWSSQLServer-Backup.
The maximum size of the backup file for uploading to Amazon S3 is 500 GB or less.

Run AWSSQLServer-Backup from the Systems Manager console

On the Documents page, choose the AWSSQLServer-Backup document:

The AWSSQLServer-Backup document is displayed on the Documents page of the console. It has a document type of Automation, an owner of Amazon, platform types of Windows, Linux, macOS, and a default version of 4.

Figure 2: AWSSQLServer-Backup

Choose Execute automation.

On the AWSSQLServer-Backup page, there are Delete and Execute automation buttons and an Actions menu.

Figure 3: Execute automation

Under Execute automation document, choose Simple execution.

Under Execute automation document, there are options for Simple execution, Rate control, Multi-account and Region, and Manual execution.

Figure 4: Execute automation document

Under Input parameters, choose one of your managed EC2 instances running SQL Server. I used the interactive instance picker to show managed instances only.

Under Input parameters, the Show interactive instance picker option is selected. In the list of managed instances, ec2-us-sql-prod01 is selected.

Figure 5: Managed EC2 instances running SQL Server

Enter parameters for the database, S3 bucket, Secrets Manager credential, and more, and then choose Execute.

The Database, S3Bucket, and SecretsManagerCredential fields are highlighted. For Database, tpcc is entered. For S3Bucket, sql-backups-ssm is entered. For SecretsManagerCredential, sqlsa is entered.

Figure 6: Parameters

The console displays the execution status:

Under Execution status, the overall status is In Progress. Two steps have been executed and both succeeded. There are 0 failed, cancelled, or timed out steps.

Figure 7: Execution status

The console displays details for the executed steps:

The Executed steps list displays columns for step ID, step number, step name, action, status, start time, and end time.

Figure 8: Executed steps

On the Executions tab, you can confirm the successful execution of the AWSSQLServer-Backup document:

On the Executions tab, the AWSSQLServer-Backup document has a status of Success.

Figure 9: Executions tab

To view the output of each step, choose the execution ID link:

Under Output, the database backup took 11 minutes. The S3 upload took 6 minutes.

Figure 10: Outputs

The output shows that the database backup took 11 minutes and the upload to S3 took 6 minutes.

You can confirm by navigating to the backup file on the SQL Server instance at the location specified in OutputPayload:

In Windows Explorer, the backup file appears in the location specified in OutputPayload.

Figure 11: Backup file

Because I added an S3 bucket location (Figure 6), I can see the same backup file has been uploaded to the S3 bucket folder. Considering the backup size of 50 GB, the S3 upload speed is more than 150 MB/second. Your upload speed might vary, depending on your instance size, EBS volume type, and network bandwidth.

The backup file is displayed in the folder in the S3 console.

Figure 12: Backup file in Amazon S3 console

Summary

In this first post, I introduced you to Automation documents for SQL Server and showed how you can implement the AWSSQLServer-Backup document to back up a database on your SQL Server instance.

In part 2, I’ll show you how to implement the AWSSQLServer-Restore and restore a database backup stored in S3.

In part 3, I’ll show you how to implement the AWSSQLServer-Index document to identify and reindex fragmented indexes.

In part 4, I’ll show you how to implement the AWSSQLServer-DBCC document to execute database consistency checks (DBCC) against your database.

In part 5, I’ll introduce you to Maintenance Windows, a powerful tool included with AWS Systems Manager for scheduling these actions against your SQL Server instances.

For more information about running SQL Server workloads on AWS, see Best practices for deploying Microsoft SQL Server on Amazon EC2. For more information about SSM Automation documents, see AWS Systems Manager Automation in the AWS Systems Manager User Guide.

To read the other posts in this series, see:

Available in These AWS Regions

The AWS Launch Wizard Systems Manager Automation documents for SQL Server are generally available. You can use them in the following AWS Regions:

US East (N. Virginia)	US East (Ohio)	US West (N. California)
US West (Oregon)	Canada (Central)	South America (Sao Paulo)
Asia Pacific (Mumbai)	Asia Pacific (Singapore)	Asia Pacific (Sydney)
Asia Pacific (Seoul)	Asia Pacific (Tokyo)	EU (Frankfurt)
EU (Ireland)	EU (London)	EU (Stockholm)

Support for the AWS Regions in China and for the GovCloud Region is in the works. There is no additional charge for using these Automation documents, only for the resources they create.

AWS Cloud Operations Blog