Microsoft Workloads on AWS

Ola Hallengren’s SQL Server Maintenance Solution in AWS

In this blog, we will demonstrate using Ola Hallengren’s SQL Server Maintenance Solution with native backups to Amazon S3 and support for Amazon RDS for SQL Server. A common task for customers is to configure database backups and maintenance jobs for their SQL Servers. Ola Hallengren’s SQL Server Maintenance Solution scripts can simplify this task by automating SQL Server database backup and maintenance tasks. AWS is excited to collaborate with Ola Hallengren, adding SQL Server 2022 backup to Amazon S3 functionality via Ola Hallengren’s SQL Server Maintenance Solution.

Beginning with SQL Server 2022, Microsoft introduced a native solution for performing backups directly to Amazon Simple Storage Service (Amazon S3) object storage. This new feature streamlines the backup process, eliminating the need for intermediate storage or additional third-party tools. It offers enhanced security, improved performance, and seamless integration with AWS services. For a comprehensive overview of this feature and step-by-step implementation guidance, we recommend reviewing the AWS blog post, Backup SQL Server to Amazon S3.

Ola Hallengren’s SQL Server Maintenance Solution with Amazon S3 and Amazon EC2

Prerequisites

The following resources are required for this solution.

Limitations

The new functionality added to Ola Hallengren’s SQL Server Maintenance Solution for Amazon S3 backups support:

  • SQL Server 2022 on Amazon EC2
  • Amazon RDS Custom for SQL Server 2022
  • SQL Server editions – Developer, Enterprise or Standard

For a complete list of supported environments and limitations please reference the SQL Server Maintenance Solution documentation.

Overview of the implementation

The updated DatabaseBackup stored procedure leverages the existing URL parameters, which can now be used for your primary Amazon S3 bucket and a mirror bucket path.

  • @URL
    • This should contain the full path to the Amazon S3 bucket, the pseudo directory structure created within the bucket will be generated to match existing structure Ola Hallengren’s SQL Server Maintenance Solution creates.
    • Example: @URL = ‘s3://amzn-s3-demo-bucket.s3.us-east-1.amazonaws.com’
  • @MirrorURL
    • This should contain the full path to the Amazon S3 bucket the backup will be mirrored to, the pseudo directory structure created within the bucket will be generated to match existing structure Ola Hallengren’s SQL Server Maintenance Solution creates.
    • Example: @MirrorURL = ‘s3://amzn-s3-demo-bucket-mirror.s3.us-east-2.amazonaws.com’

Note: The @MirrorURL parameter continues to support a single URL while the TSQL command allows for three. Using multiple mirrors does significantly limit the maximum 12.2 TB backup size supported so we recommend leveraging Amazon S3 cross region replication that can be configured at the Amazon S3 bucket level if you need multiple mirrors.

Other parameters such as @MaxTransferSize = 20971520 will also need to be set, for a complete discussion on the needed parameters review, Backup SQL Server databases to Amazon S3.

Configuration

By default, the MaintenanceSolution.sql script will create the required SQL Server Agent backup jobs and will include the @URL parameter if specified (Figure 1).

Figure 1 – @BackupURL parameter

To add the @URL parameter to your existing SQL Server backup jobs you can edit the individual steps on each SQL Server Agent Job and add the parameter manually (Figure 2).

Figure 2 – Job step properties for @URL parameter

The @MirrorURL parameter cannot be specified during the initial setup and will need to be manually added to each job (Figure 3).

Figure 3 – Job step properties for @MirrorURL parameter

Ola Hallengren’s SQL Server Maintenance Solution with Amazon S3 and Amazon RDS for SQL Server

Solution Overview

AWS has published a GitHub repo, aws-sql-server-maintenance-solution where additional functionality has been added providing support for native backups within Amazon RDS for SQL Server while maintaining the current procedure and job structure customers deploy and are familiar with today. This is a direct replacement for your current MaintenanceSolution deployment and no existing functionality within the solution has been removed or altered.

With Amazon RDS, you can use the SQL Server Agent on a database instance running SQL Server Enterprise, Standard, or Web edition. By default, an Amazon RDS for SQL Server installation has no SQL Server Agent jobs configured. Leveraging the solution in this post will allow the integration of the aws-sql-server-maintenance-solution scripts into the Amazon RDS for SQL Server Agent jobs for seamless backups and maintenance. This updated solution supports all versions of SQL Server on Amazon RDS.

Prerequisites

The following resources are required for this solution.

Implementation

The MaintenanceSolution.sql TSQL script is downloaded from the GitHub aws-sql-server-maintenance-solution repository and run on the Amazon RDS for SQL Server instance. The parameters should be adjusted as follows.

Existing parameters:

New parameters:

  • @S3BucketArn
    • This will contain the Amazon Resource Name (ARN) for the Amazon S3 bucket the backups should be written to. This can be found in the properties tab of your Amazon S3 bucket (Figure 4).
    • For example: @S3BucketArn = ‘arn:aws:s3:::amzn-s3-demo-bucket’

Figure 4 – Amazon S3 bucket ARN

  • @kms_master_key_arn
    • This contains the ARN for the symmetric encryption KMS key to use to encrypt the item, if not specified the backup file will not be encrypted. This ARN will be found within the General configuration of your key (Figure 5).
    • For example: @kms_master_key_arn = ‘arn:aws:kms:us-east-1:00000000:key/abcdefgh-ijkl-mnop-qrst-uvwxyz012435’

Figure 5 – AWS KMS key ARN

The jobs created on an Amazon RDS instance (Figure 6).

Figure 6 – Amazon RDS for SQL Server agent jobs

Scheduling

The jobs can be scheduled using the SQL Server Agent as a standard TSQL agent job, for more information see, Configure schedule for SQL Server Agent job.

Monitoring and alerting

Due to the functionality of the msdb.dbo.rds_backup_database stored procedure provided for native backups within Amazon RDS for SQL Server, we will not be able to leverage the SQL Server Agent built in functionality.

We will leverage Amazon CloudWatch to capture backup failures from the SQL logs and leverage Amazon Simple Notification Service (Amazon SNS) to send alerts based on the specific error message using the architecture shown in Figure 7.

Figure 7 – Amazon RDS for SQL Server alerts architecture

Configuring Amazon CloudWatch log collection

To monitor for these changes, we first need to configure our RDS for SQL Server instance to export its logs to Amazon CloudWatch.

    1. Open the Amazon RDS Console, select your instance and choose Modify.
    2. Check Error Log under Log exports, choose Continue.
    3. Choose Apply Immediately.

Figure 8 – Log exports

After this step has been completed, your Amazon RDS for SQL Server Error Logs will be posted to Amazon CloudWatch Log Groups. For more information about publishing logs to CloudWatch see, Publishing SQL Server logs to Amazon CloudWatch Logs.

In the error logs you will see two errors posted currently but additional customized errors can be added as needed.

  • “RDS:Msg Errors Detected in RDS Backup Queue Status” – This is logged when a final state of ERROR is returned from msdb.dbo.rds_task_status.
  • “A task has already been issued for database: xx with task id yy, please try again later” – This is proactively logged when a duplicate backup command is issued. This is informational but may be an event that requires alerting.

Alerting Configuration

We can now configure pattern matching filters to identify the specific wording of the errors and generate alerts.

To create SNS alerts

  1. Open Amazon CloudWatch console and choose Log groups.
  2. Enter the Amazon RDS instance name (DB identifier) in the search to locate the log group.
  3. Choose the log group and select Create metric filter under the Actions menu.

Figure 9 – Log groups

  1. Enter, Errors Detected in RDS Backup Queue Status in the Filter pattern and choose Next.

Figure 10 – Create filter pattern

  1. Use “RDSBackup” for Filter name, Metric namespace, and Metric name.
  2. Enter “1” for Metric value and choose Next.

Figure 11 – Metric details

  1. Review the final settings and choose Create metric filter.

Figure 12 – Assign metric

  1. Check the “RDSBackup” filter displayed and choose Create alarm.

Figure 13 – Metric filters

  1. Within the Metric choose statistics Minimum and a period of 1 minute.

Figure 14 – Metric

  1. For Conditions keep the defaults of Static and Greater > threshold and set the threshold value to “0“.
  2. Expand the Additional configuration and select “Treat missing data as good” and choose Next.

Figure 15 – Conditions

  1. For alarm state trigger of In Alarm either Select an existing SNS topic and select topic or Create new topic and choose Next. To learn more about creating a SNS topic, see Creating an Amazon SNS topic.

Figure 16 – Notification

  1. Provide an Alarm name and choose Next.

Figure 17 – Name and description

  1. Review alarm setting and choose Create alarm.

You will initially see the alarm listed with insufficient data while the logs are reviewed, the state will change to OK when complete.

Figure 18 – RDSBackup state

These settings are examples of how to configure the alarm, but we always recommend you review these settings based on your business needs.

The monitoring and alerting steps can also be implemented using this Configuration Script with AWS CloudShell, as described in What is AWS CloudShell?

Cleanup

If you deployed any AWS resources following the solution outlined in this blog, they will incur costs. To avoid incurring unwanted charges, delete any unused resources.

Conclusion

In this post we have demonstrated how to setup Ola Hallengren’s SQL Server Maintenance Solution scripts for Amazon S3 support within your SQL Server 2022 servers and edit existing jobs to include the new parameters. We also reviewed how to setup the aws-sql-server-maintenance-solution scripts for your Amazon RDS for SQL Server environment. We then showed how to bring your SQL error logs into Amazon CloudWatch and setup monitoring and alerting utilizing Amazon SNS.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.

Phil Ekins

Phil Ekins

Phil Ekins is a Senior Solutions Architect in Amazon Web Services within the Microsoft technologies area and a SME on SQL Server. With over two decades of DBA experience on SQL Server and extensive experience guiding customers on Cloud Architectures, Migrations, Virtualization and HA/DR Solutions. As an AWS Architect and as a seasoned SQL Server Professional, Phil brings the DBA’s needs to the world of cloud computing.

Aravind Hariharaputran

Aravind Hariharaputran

Aravind Hariharaputran is Database Consultant with the Professional Services team at Amazon Web Services. He is passionate about databases in general with Microsoft SQL Server as his specialty. He helps build technical solutions that assist customers to migrate and optimize their on-premises database workload to the AWS Cloud. He enjoys spending time with family and playing cricket.