AWS Cloud Operations 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

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:

Under Databases, MyCorruptDB is highlighted.

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.

Under Input parameters, the Show interactive instance picker option is selected. In the list of managed instances, ec2-us-sql-prod01 is selected.

Figure 2: Input parameters

Enter your parameters, and then choose Execute.

The PhysicalOnlyCheck field is highlighted and Yes is selected from the dropdown. For Database, MyCorruptDB is entered.

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:

The Executed steps list displays columns for step ID, step number, step name, action, status, start time, and end time.

Figure 4: Executed steps

On completion, there’s a failure:

Under Execution status, the overall status for the Automation document is Failed.

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:

CHECKDB output is highlighted. It found 0 allocation errors, 2 consistency errors in the table, and 2 consistency errors in the MyCorruptDB database.

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:

SSMS Query window showing DBCC statements to repair corruption.

Figure 7: Repair option

Let’s re-run the SQLDBCC automation with the same parameters to confirm the corruption has been fixed:

Automation console output shows all steps succeeded.

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:

detailed step output showing success of repair.

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:

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.

About the author

Alan Cranfield

Alan Cranfield

Alan Cranfield is a Senior Systems Engineer in the EC2 Windows team where he focuses on enabling and optimizing SQL Server workloads for AWS.