Performing a minor version upgrade for Amazon RDS Custom for SQL Server CEV with Multi-AZ

Amazon Relational Database Service (Amazon RDS) Custom is a managed database service that provides administrative access to the underlying operating system (OS) and database (DB) environment. This allows you to run third-party applications, install drivers, or access native features of SQL Server or Windows, while getting the benefits of a managed database. You can also use SQL Server Developer Edition or use your existing SQL Server licenses through the Bring Your Own Media capability.

You can deploy an RDS Custom for SQL Server instance using either an Amazon RDS provided engine version (RPEV) or a custom engine version (CEV). CEV deployment allows you to bring your own customized Amazon Machine Image (AMI). You can configure an Amazon Elastic Compute Cloud (Amazon EC2) instance with desired OS settings, drivers, and agents, and create an AMI from it. For more information, refer to Creating a CEV for RDS Custom for SQL Server.

RDS Custom for SQL Server with CEV deployment comes with a shared responsibility model for OS and Database patching. At the time of this writing, any OS level changes that are made after the instance creation are not persisted. If you want to persist any of the OS level customization such as OS settings, drivers and so on, It is necessary to create a new customized AMI followed by CEV and later modify the existing instance to use this new CEV.

In this post, we explain how to perform a database minor version upgrade (patch) with Multi-AZ on CEV instance, where RDS Custom performs rolling upgrades, so you have an outage only for failover period and the time needed for post-upgrade scripts until the instance is fully operational.

Solution overview

To implement the solution, complete the following high-level steps:

1. Start by creating a CEV (CEV1) for RDS Custom for SQL Server either by creating an AMI using Bring Your Own Media (BYOM) or using the pre-installed SQL Server (LI) AMI. For more information, refer to Preparing to create a CEV for RDS Custom for SQL Server.
2. Deploy the RDS Custom instance1 with Multi-AZ using CEV1.
3. Apply database upgrades on the AMI used in Step 1 and create a new AMI (AMI2) and prepare a new CEV (CEV2).
4. Validate the CEV2 by launching a RDS Custom instance2.
5. Modify the RDS Custom instance1 and choose the CEV with the latest upgrades (CEV2).
6. When the rolling upgrades are in progress, verify the instance is still accessible.

Prerequisites

Before we begin, we assume that you have the following prerequisites:

• An AWS account.
• The networking, instance profile, and encryption key needed to deploy an RDS Custom for SQL Server CEV. For instructions, refer to Get started with Amazon RDS Custom for SQL Server using an AWS CloudFormation template (Network setup).
• If you previously created a Single-AZ instance for your RDS Custom CEV, make sure you included the new prerequisites for Multi-AZ. For more information, see Modifying an RDS Custom for SQL Server Single-AZ deployment to a Multi-AZ deployment.
• A Windows EC2 instance with SQL Server 2019 CU22 and SQL Server Management Studio (SSMS) installed.
• Communications between your EC2 instance and RDS Custom through a security group are allowed.
• The AWS Command Line Interface (AWS CLI) installed and configured (optional). For instructions, refer to Installing or updating the latest version of the AWS CLI.
• Basic understanding of Database upgrades

For more details, refer to Working with custom engine versions for RDS Custom for SQL Server.

This solution involves the creation and utilization of new AWS resources. Therefore, it will incur costs on your account. Refer to AWS Pricing for more information.

We strongly encourage that you set this up in a non-production environment and run end-to-end validations before implementing this solution in your production environment.

Upgrade the DB engine version for RDS Custom SQL Server with a CEV and Multi-AZ
In this post we start with using an existing EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22) preconfigured with SQL Server 2019 CU22 to launch a new AMI (ami1-rdscustom-cu22) and prepare CEV1 (15.00.4322.2.cev1-rdscustom-cu22). CEV1 is then used to launch an RDS Custom instance (rdsc-db-patch-demo-maz) with Multi-AZ configuration. Following this, a database minor version upgrade is performed on the EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22), upgrading SQL Server from CU22 to CU24. A new AMI (ami2-rdscustom-cu24) and CEV2 (15.00.4345.5.cev2-rdscustom-cu24) are created after database upgrade on EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22). A RDS Custom instance (rdscustom-for-cev2-validation) is launched to validate CEV2, changing CEV2 (15.00.4345.5.cev2-rdscustom-cu24) status to Available. Finally, DB engine version for existing RDS Custom instance (rdsc-db-patch-demo-maz) is modified to use the new CEV2 (15.00.4345.5.cev2-rdscustom-cu24) with the latest DB engine version upgrades. For information and detailed guidance for preparing the CEV, refer to Preparing to create a CEV for RDS Custom for SQL Server.

Deployment:

1. On the Amazon EC2 console, select your EC2 instance to upgrade (RDSCustom-CEV-Goldcopy-SS19CU22).
2. On the Actions menu, choose Image and templates and Create image (ami1-rdscustom-cu22).
   
3. Wait until AMI ami1-rdscustom-cu22 is in Available state.
   
4. Prepare CEV1 (15.00.4322.2.cev1-rdscustom-cu22).
   
5. Use the CEV1 you created and launch an RDS Custom instance (rdsc-db-patch-demo-maz) with Multi-AZ configuration.
   
6. Validate the instance configuration using SSMS on a remote EC2 machine.
   
7. Perform Database minor version upgrade on EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22). For this post, we update the SQL Server on EC2 instance of CEV1 from CU22 to CU24.
8. Create a new AMI2 (ami2-rdscustom-cu24) using the EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22) that is upgraded with new database minor version and prepare a new CEV2 (15.00.4345.5.cev2-rdscustom-cu24).
   
9. Launch a RDS Custom instance (rdscustom-for-cev2-validation) to validate the CEV2, which will change the status of the CEV2 from Pending validation to Available. In order to upgrade or patch an RDS instance, the CEV must be in Available state.
   
10. Modify the existing RDS Custom instance (rdsc-db-patch-demo-maz) by choosing the new CEV2 (15.00.4345.5.cev2-rdscustom-cu24) with the latest upgrades in the DB engine version section.
    
11. Select Apply immediately to roll out the modifications.
    The status of the RDS Custom instance changes to Upgrading while it starts the rolling upgrades on the passive node first.

    

12. During the engine version upgrade in a Multi-AZ configuration, standby instance will be patched first, followed by a failover. You can continue to connect to primary RDS Custom instance while the upgrade is in progress on standby instance.

    RDS Custom logs the patching events under Logs and Events, which can be viewed on the Amazon RDS console while performing the rolling upgrades.

    

13. After the failover and instance is in available status, you can connect to the RDS Custom instance endpoint again to verify connectivity and successful database upgrade.
    

Considerations

In this section, we discuss considerations around certain key SQL Server features on a Multi-AZ RDS Custom for SQL Server instance with a CEV while performing the database patching. It is recommended that you have a checklist available to reconfigure the features when required.

• If you upgrade an RDS Custom for SQL Server DB instance in a Multi-AZ deployment, Amazon RDS performs rolling upgrades, so you will have an outage for failover period and the time needed for post-upgrade scripts until the instance is fully operational. You should plan this outage during non-critical business hours.
• Your database can only be upgraded to the same or a newer version, whether it’s a major or minor upgrade.
• The AMI will be validated only during the first instance launch. Subsequent instances for database patching to the same engine version can directly use the validated CEV.
• The CEV must comply with RDS Custom support requirements. For more details, refer to Limitations for RDS Custom for SQL Server.
• For cross-Region RDS Custom CEV deployments, the AMIs deployed can be copied to a different AWS Region. For instructions, refer to Copy an AMI.
• Refer to Configure Multi-AZ with Amazon RDS Custom for SQL Server for key considerations while using RDS Custom with Multi-AZ and SQL Server Reporting Service (SSRS), log shipping, and replication.

It’s important that your RDS Custom for SQL Server instance remains in a supported configuration; this allows the Amazon RDS automation to manage your instance for you, including providing automated backups. Refer to Troubleshooting DB issues for Amazon RDS Custom for SQL Server for examples of unsupported configuration issues and how to fix them.

Clean up

To avoid incurring unwanted charges, delete the resources you created as part of this post:

1. Delete your RDS Custom for SQL Server DB instance.
2. Delete the test RDS Custom instance (rdscustom-for-cev2-validation) you created after the CEV has been validated. For instructions, refer to Deleting a DB instance.
3. Delete your EC2 instance.
4. If you created the prerequisite resources (network, instance profile, and encryption key) using the provided CloudFormation template, delete the stack to remove the resources.

Summary

In this post, you learned how to perform rolling database upgrades for an existing RDS Custom for SQL Server 2019 instance with a CEV from CU22 to CU24 using a Multi-AZ deployment and then test the availability of a database. This approach allows you to fulfill your business and application high availability needs by ensuring minimal downtime during database patching for an existing CEV instance.

Try out this solution in your AWS account and if you have any comments or questions, leave them in the comments section.

About the Authors

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.

Kalyan Banala is an accomplished Database Engineer, and works with the Amazon RDS Custom for SQL Server team at AWS. He enjoys working on technical challenges and is passionate about learning from and sharing knowledge with his teammates and AWS customers.

Kanwar Nain Singh is a Specialist Solutions Architect with Amazon Web Services. He has over 13 years of experience engineering and architecting migrations and modernization of relational and non-relational database stack on AWS Cloud.