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.
Figure 1: Documents page of Systems Manager console
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:
password. For more information, see Authentication and access control for AWS Secrets Manager.
- On Windows, the
passwordmust be the Active Directory domain account and password used to authenticate SQL Server.
- On Linux, the
passwordmust be the SQL Server login name and password.
- On Windows, the
usernamemust be assigned, at minimum, the following database role:
- 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
- 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:
Figure 2: AWSSQLServer-Backup
Choose Execute automation.
Figure 3: Execute automation
Under Execute automation document, choose Simple 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.
Figure 5: Managed EC2 instances running SQL Server
Enter parameters for the database, S3 bucket, Secrets Manager credential, and more, and then choose Execute.
Figure 6: Parameters
The console displays the execution status:
Figure 7: Execution status
The console displays details for the executed steps:
Figure 8: Executed steps
On the Executions tab, you can confirm the successful execution of the AWSSQLServer-Backup document:
Figure 9: Executions tab
To view the output of each step, choose the execution ID link:
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:
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.
Figure 12: Backup file in Amazon S3 console
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.
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:
- Introducing the AWSSQLServer-Restore Automation document for SQL Server maintenance
- Introducing the AWSSQLServer-Index Automation document for SQL Server maintenance
- Introducing the AWSSQLServer-DBCC Automation document for SQL Server maintenance
- Introducing Maintenance Windows for scheduling Automation documents against your SQL Server instances
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.