AWS Cloud Operations & Migrations Blog
Introducing the AWSSQLServer-DBCC Automation document for SQL Server maintenance
In the first three posts in 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, and AWSSQLServer-Index Automation documents.
In this post, I’ll show you how to use the AWSSQLServer-DBCC document for SQL Server to execute database consistency checks (DBCC) against your database.
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.
AWSSQLServer-DBCC Automation document includes the steps to perform database integrity checks on a specified database. You can control the type of database checks that are run. You can also adjust the execution parameters, such as the tables to check, maximum CPU utilization, and more.
Prerequisites for using this document
- The instance must be running SQL Server.
- An AWS Secrets Manager database credential must be configured.
- The username must be assigned, at minimum, the following database role: db_owner.
- You might need to grant additional permissions or assign roles to the instances that allow access to AWS Secrets Manager credentials.
In this post, I’ll show how the AWSSQLServer-DBCC document can detect and alert on database corruption. I artificially corrupted a database by using a hex editor to mess with the header in the database file. For information about how to do this, see Let’s Corrupt a SQL Server Database Together by Brent Ozar. The name of the database I’ll run the check on is
Figure 1: MyCorruptDB
In the Systems Manager console, on the Documents page, choose the AWSSQLServer-DBCC document, and then choose Execute automation.
Under Input parameters, choose Show interactive instance picker, and then choose your SQL Server instance from the list.
Figure 2: Input parameters
Enter your parameters, and then choose Execute.
Figure 3: Parameters
By default, the Automation document selects the least intrusive or performance-impacting options. In reality, you’ll want to balance the degree of checks against your maintenance window and the acceptable level of interruption to the database. In many cases, to avoid production impact, customers run these checks against read-only secondary databases or databases restored to test machines.
The console displays the status of the execution steps:
Figure 4: Executed steps
On completion, there’s a failure:
Figure 5: Execution status
A failed Automation document could mean that DBCC detected corruption. Choose the step with a status of Failed to view the output:
Figure 6: Output
The error output gives you the information you need to repair the corruption. In this case, SQL Server advises that:
I’ll run the minimum repair option against the database to see if it repairs the corruption:
Figure 7: Repair option
Let’s re-run the SQLDBCC automation with the same parameters to confirm the corruption has been fixed:
Figure 8: Re-run automation
The step shows success. To confirm let’s take a look at the step output by clicking on the Step ID link:
Figure 9: Output
We see a successful execution of the DBCC CHECKDB.
In this blog I’ve introduced AWS Launch Wizard Systems Manager Automation documents for SQL Server and provided an example of how to use AWSSQLServer-DBCC document in your own environment 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 automation documents against your SQL Servers.
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 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.