Amazon VGT2 Las Vegas: A Guide to Minor Version Upgrades for Amazon RDS Custom SQL Server CEV with Multi-AZ

More Info

Amazon Relational Database Service (Amazon RDS) Custom offers a managed database experience while allowing administrative access to the underlying operating system (OS) and database (DB) environment. This flexibility enables you to deploy third-party applications, install necessary drivers, or utilize native features of SQL Server or Windows, all while benefiting from the managed service. You can choose to use SQL Server Developer Edition or leverage your existing SQL Server licenses through the Bring Your Own Media capability.

You can set up an RDS Custom for SQL Server instance with either an Amazon RDS provided engine version (RPEV) or a custom engine version (CEV). The CEV deployment option permits you to utilize your own customized Amazon Machine Image (AMI). By configuring an Amazon Elastic Compute Cloud (Amazon EC2) instance with your preferred OS settings, drivers, and agents, you can create an AMI tailored to your needs. For more details, refer to our blog post on Creating a CEV for RDS Custom for SQL Server.

When using RDS Custom for SQL Server with CEV deployment, there is a shared responsibility model regarding OS and database patching. As of this writing, any OS-level changes made after the instance creation will not be retained. To keep any OS-level customizations such as settings or drivers, you must create a new customized AMI followed by a CEV and then modify the existing instance to utilize this new CEV.

In this article, we will outline the steps to perform a minor database version upgrade (patch) on a CEV instance with Multi-AZ. RDS Custom conducts rolling upgrades, resulting in downtime only for the failover period and the time required for post-upgrade scripts until the instance becomes fully operational.

Solution Overview

To implement this solution, follow these high-level steps:

1. Begin by creating a CEV (CEV1) for RDS Custom for SQL Server, either by generating an AMI using Bring Your Own Media (BYOM) or by using the pre-installed SQL Server (LI) AMI. For further guidance, see Preparing to create a CEV for RDS Custom for SQL Server.
2. Launch the RDS Custom instance1 with Multi-AZ using CEV1.
3. Apply database upgrades to the AMI created in Step 1, and build a new AMI (AMI2) to prepare a new CEV (CEV2).
4. Validate CEV2 by launching RDS Custom instance2.
5. Update RDS Custom instance1 to use the CEV that includes the latest upgrades (CEV2).
6. While the rolling upgrades take place, ensure that the instance remains accessible.

Prerequisites

Before we proceed, ensure you have the following prerequisites:

• An AWS account.
• The necessary networking, instance profile, and encryption key to deploy an RDS Custom for SQL Server CEV. For setup instructions, refer to Get started with Amazon RDS Custom for SQL Server.
• If you’ve previously created a Single-AZ instance for your RDS Custom CEV, make sure you have included the new prerequisites for Multi-AZ.
• A Windows EC2 instance with SQL Server 2019 CU22 and SQL Server Management Studio (SSMS) installed.
• Communication between your EC2 instance and RDS Custom should be permitted through a security group.
• The AWS Command Line Interface (AWS CLI) should be installed and configured (optional).
• A basic understanding of database upgrades.

For more details, check out our post on working with custom engine versions for RDS Custom for SQL Server. Note that this solution involves creating and using new AWS resources, which will incur costs on your account. We highly recommend setting this up in a non-production environment and conducting thorough validations before deploying it in your production environment.

Upgrade the DB Engine Version for RDS Custom SQL Server with a CEV and Multi-AZ

In this section, we start with an existing EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22) that is 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 utilized to launch an RDS Custom instance (rdsc-db-patch-demo-maz) with a Multi-AZ configuration. Subsequently, a minor database 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 following the upgrade on EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22). A RDS Custom instance (rdscustom-for-cev2-validation) is then launched to validate CEV2, transitioning its status from Pending validation to Available. Lastly, the DB engine version for the existing RDS Custom instance (rdsc-db-patch-demo-maz) is modified to incorporate the new CEV2 (15.00.4345.5.cev2-rdscustom-cu24) with the latest upgrades.

Deployment Steps

1. In the Amazon EC2 console, select the EC2 instance to upgrade (RDSCustom-CEV-Goldcopy-SS19CU22).
2. From the Actions menu, click on Image and templates, then choose Create image (ami1-rdscustom-cu22).
3. Wait until AMI ami1-rdscustom-cu22 is marked as Available.
4. Prepare CEV1 (15.00.4322.2.cev1-rdscustom-cu22).
5. Utilize CEV1 to launch an RDS Custom instance (rdsc-db-patch-demo-maz) with Multi-AZ.
6. Validate the instance configuration using SSMS from a remote EC2 machine.
7. Execute the minor database version upgrade on EC2 instance (RDSCustom-CEV-Goldcopy-SS19CU22), updating SQL Server from CU22 to CU24.
8. Create a new AMI (ami2-rdscustom-cu24) using the upgraded EC2 instance and prepare a new CEV2 (15.00.4345.5.cev2-rdscustom-cu24).
9. Launch an RDS Custom instance (rdscustom-for-cev2-validation) to validate CEV2, changing its status to Available.
10. Modify the existing RDS Custom instance (rdsc-db-patch-demo-maz) to use the new CEV2 (15.00.4345.5.cev2-rdscustom-cu24) with the latest upgrades.
11. Select Apply immediately to initiate the modifications. The status of the RDS Custom instance will shift to Upgrading as it starts the rolling upgrades on the passive node first.
12. During the upgrade in a Multi-AZ setting, the standby instance will be patched first, followed by a failover. You can continue to connect to the primary RDS Custom instance while the standby instance upgrade is in progress. Patching events are logged under Logs and Events, accessible via the Amazon RDS console during the rolling upgrades.
13. After failover and once the instance is in an available status, reconnect to the RDS Custom instance endpoint to verify connectivity and confirm a successful database upgrade.

Considerations

This section highlights key considerations regarding specific SQL Server features on a Multi-AZ RDS Custom for SQL Server instance with a CEV during database patching. It is advisable to maintain a checklist to reconfigure features as needed.