Introducing Maintenance Windows for scheduling Automation documents against your SQL Server instances

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.

Scheduling

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:

The details page for the window includes its ID, name, state (in this example, Enabled), cron/rate expression, duration, next execution time, and more.

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.

The Tasks tab of the details page is selected. Under Actions, the Register Automation task option is highlighted.

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.

Under Maintenance window task details, the Name field displays backup-tpcc-tlog. The Description field displays backup the tpcc database transaction log.

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

Four Automation documents are displayed in the list: AWSSQLServer-Backup (in this example, selected), AWSSQLServer-DBCC, AWSSQLServer-Index, AWSSQLServer-Restore.

Figure 4: AWSSQLServer-Backup

For Document version, choose Default version at runtime:

Use the Document version field to choose the document version you want to run. In this example, Default version at runtime is selected from the dropdown.

Figure 5: Document version

In Targets, search for the SQL Server instance on which you’ll be running the transaction log backup.

Under Target by, Selecting unregistered targets is selected. In the list of managed instances, ec2-us-sql-prod01 is selected.

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

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.

$The input parameters include Database (in this example, tpcc), BackupType (LOG), Directory (g:\backup), BackupRetentionPolicy (Never delete older backups), DeleteBackupsOlderThan (0), SecretsManagerCredential (sqlsa), S3Bucket (sql-backups-ssm), and more.$

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.

The History tab of the maintenance window executions includes execution IDs, status, status details, start time, and end time.

Figure 8: History

On the details page, in Tasks Invocation, choose the step ID, and then choose View details.

Under Execution status, the overall status is Success. All three executed steps succeeded. Under Executed steps, step 3, ExecuteBackup, is highlighted.

Figure 9: Execution status

Choose the ExecuteBackup step ID to view the output.

The output of the execution says the database backup of type LOG on TPCC SUCCEEDED. The database backup took 0 minutes. The S3 upload took 0 minutes.

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:

Transaction logs for ec2-us-sql-prod-01 are displayed in a list organized by data modified, type, and size.

Figure 11: Transaction log backups

You can also see the transaction logs were safely copied to the specified S3 bucket:

In the EC2 console, the transaction logs uploaded to S3 are displayed in a list organized by name, type, last modified, size, and storage class.

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.

Summary

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:

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