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

The 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 MyCorruptDB:

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:

CHECKDB found 0 allocation errors and 3 consistency errors in table 'ToLeaveYourLover' (object ID 885578193).
CHECKDB found 0 allocation errors and 3 consistency errors in database 'MyCorruptDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MyCorruptDB, noindex).

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.

Summary

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:

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.

About the author