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 HAQM S3. 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 HAQM 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 HAQM Simple Storage Service (HAQM 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 HAQM S3.

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

Prerequisites

The following resources are required for this solution.

Limitations

The new functionality added to Ola Hallengren’s SQL Server Maintenance Solution for HAQM S3 backups is only supported on SQL Server 2022.

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 HAQM S3 bucket and a mirror bucket path.

  • @URL
    • This should contain the full path to the HAQM 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 HAQM 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 HAQM S3 cross region replication that can be configured at the HAQM 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 HAQM 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

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 HAQM S3 support within your SQL Server 2022 servers and edit existing jobs to include the new parameters.

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