In parts 1, 2, 3, and 4 of this five-part series, I introduced you to AWS Launch Wizard Systems Manager Automation documents for SQL Server and showed you how to run the AWSSQLServer-Backup, AWSSQLServer-Restore, AWSSQLServer-Index, and AWSSQLServer-DBCC Automation documents.
In this last post of the series, I’ll introduce you to Maintenance Windows, a powerful tool available in AWS Systems Manager that can be used to schedule these Automation documents against your SQL Server instances.
The SQL Server maintenance initiative is a joint venture between AWS 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.
Backups and reindexing must be done regularly, according to set schedules. AWS Systems Manager Maintenance Windows let you define a schedule for when to perform these maintenance actions on your SQL Server instances. Each maintenance window has a schedule, maximum duration, set of registered targets (the SQL Server instances), and set of registered tasks (the SSM Automation documents).
Create a maintenance window
First, you must create the maintenance window and define its schedule and duration:
- Open the AWS Systems Manager console.
- In the navigation pane, choose Maintenance Windows, and then choose Create a Maintenance Window.
- Enter a name and optional description for your maintenance window.
- Specify a schedule for the maintenance window by using one of the scheduling options.
For example, a schedule that runs every five minutes to back up a transaction log could look like this:
Figure 1: Window details
Assign tasks to your maintenance window
After your maintenance window is created you assign tasks to perform during the window.
From the details page of your maintenance window, choose Actions, and then choose Register Automation task.
Figure 2: Register Automation task
On the Edit Automation task page, in Name, enter a name for the task (for example,
backup-tpcc-tlog). In Description, enter an optional description.
Figure 3: Maintenance window task details
In Automation document, search for the AWSSQLServer-Backup document. Leave the task priority at the default value of 1 (the highest priority).
Figure 4: AWSSQLServer-Backup
For Document version, choose Default version at runtime:
Figure 5: Document version
In Targets, search for the SQL Server instance on which you’ll be running the transaction log backup.
Figure 6: Targets
In Rate control, for Concurrency, leave the selected default targets and specify
1. For Error threshold, leave the selected default errors and specify
In Role, specify an IAM service role that allows the maintenance window to perform the tasks being executed by the Automation document (for example, backing up the transaction log and copying it to an S3 bucket).
In Input parameters, enter parameters for the Automation document.
Figure 7: Input parameters
Choose Register Automation task to complete the task definition and return to the details page.
Review the maintenance window execution
After your maintenance window execution is complete, open the Systems Manager console, choose Maintenance Windows, and then choose the ID for your maintenance window.
On the details page, go to the History tab, choose an execution ID, and then choose View details.
Figure 8: History
On the details page, in Tasks Invocation, choose the step ID, and then choose View details.
Figure 9: Execution status
Choose the ExecuteBackup step ID to view the output.
Figure 10: Output of ExecuteBackup
You can see that the transaction log backups are being created every five minutes on the SQL Server instance at the configured location:
Figure 11: Transaction log backups
You can also see the transaction logs were safely copied to the specified S3 bucket:
Figure 12: Transaction logs uploaded to S3
You have now configured a maintenance window, assigned targets, assigned tasks, and validated their successful execution. You can use the same procedures to schedule the execution of any AWS Systems Manager Automation document.
In this post, I introduced you to Maintenance Windows, a powerful scheduling tool included in 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:
- Introducing the AWSSQLServer-Backup Automation document for SQL Server maintenance
- 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
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.