Rajendra Gupta

Performing minor and major version upgrades for AWS RDS SQL Server

January 29, 2021 by

Usually, database administrators upgrade the SQL Server versions for on-premise infrastructure. The AWS cloud infrastructure for SQL Server supports two flavors for database deployments.

  • SQL Server deployed on the AWS EC2 instance
  • An AWS managed instance relational database service

On the EC2 based SQL Server, DBAs are responsible for performing database upgrades, patching, applying cumulative packs. In this article, we discuss the minor and major version upgrades for AWS RDS SQL Server.

Minor version vs Major version upgrades

SQL Server regularly releases the service packs, hotfixes and cumulative packs for a specific version. If we apply them for SQL instance, it is known as Minor version upgrades. Starting from SQL Server 2017, Microsoft releases the cumulative packs only.

In the minor version upgrades, the changes are backward-compatible with an existing application and do not deprecate any existing functionality.

While the major versions involve changing the SQL versions such as SQL Server 2017 to SQL Server 2019, in the major version, Microsoft introduces several new features, enhance existing features or deprecate certain functionality as well. Therefore, Major versions for RDS SQL Server: SQL Server 2019, 2017,2016,2014 and 2012.

We can apply both minor and major version upgrades to the supported AWS RDS SQL Server. Once you deploy an RDS SQL instance, you might have noticed an option- Enable auto minor version upgrade. As per its definition, if we enable this functionality, AWS automatically upgrades the minor version during the maintenance window for the database.

Enable auto minor version upgrade

So, do we need to worry about applying minor version upgrades if you have enabled this option – Enable auto minor version upgrade.

According to AWS documentation, AWS RDS SQL Server doesn’t support automatic minor version upgrades even if we have enabled the feature. We need to upgrade the minor version as well manually.

In this article, I use the SQL Server 2017 version 14.0.3281 as a source RDS SQL Server.

AWS RDS SQL Server

In the article, Learn AWS CLI: An Overview of AWS CLI (AWS Command Line Interface), we configured the command-line interface (CLI) for AWS resources. Configure a CLI profile and run the following command:

>aws rds describe-db-engine-versions –engine sqlserver-ex –engine-version 14.00.3281.6.v1

In this command, specify the following parameter as per your RDS instance.

  • Engine: It is the edition for the SQL Server such as express, standard, enterprise. I have SQL Server expression edition, therefore, specify the value as sqlserver-ex
  • –engine-version: I have deployed SQL Server 2017 14.0.3281 .6 (CU19) as source RDS. However, to specify the value, we use it as 14.00.3281.6.v1

You can refer to SQL Server versions supported in RDS for this.

SQL Server versions supported in RDS

The above AWS CLI command returns the false value for Auto Upgrade even if you have enabled the feature. It verifies that you have to upgrade an RDS minor and major versions manually.

CLI output

AWS RDS SQL Server upgrade process

AWS takes two database snapshots during the upgrade of both minor and major version upgrades.

  • It takes the first snapshot before starting the database upgrades. It acts as a restoration point during the rollback, if required. You can restore the database snapshot if you face any issues after the database on a new version
  • The second snapshot is immediately after the RDS version upgrade completes
  • Note: You should have set the backup retention period greater than zero for the automated snapshot to work. If you do not have it already set up, you can modify the RDS instance and set the backup retention period

SQL Server upgrade process

In the below image, we get the overall process for upgrading an AWS RDS SQL Server.

process for upgrading database

You can upgrade following major upgrades in the AWS RDS SQL Server similar to the on-premise SQL instance.

Source RDS version

Supported upgrade versions

SQL Server 2012

SQL Server 2014

SQL Server 2016

SQL Server 2017

SQL Server 2019

SQL Server 2014

SQL Server 2016

SQL Server 2017

SQL Server 2019

SQL Server 2016

SQL Server 2017

SQL Server 2019

SQL Server 2017

SQL Server 2019

Find the available upgrades paths using AWS CLI

While planning for a database upgrade, you need to know the supported AWS RDS SQL Server versions that you can upgrade.

You can check the supported upgrade path in the following ways.

  • Browse to URL SQL Server versions supported in RDS and check the supported upgrade RDS versions
  • Select the RDS instance in the AWS web portal, click on Modify. In the DB engine version, you get the current RDS version along with the supported DB upgrade paths

    AWS RDS SQL Server: available upgrades paths

  • You can use AWS CLI to find out the supported database upgrade version. Run the following CLI command with your SQL Server engine and version:

    aws rds describe-db-engine-versions \
    –engine sqlserver-ex \
    –engine-version 14.00.3281.6.v1 \
    –query “DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}”

    As shown below, the SQL Server version 14.00.3281.6 can be upgraded to the following versions:

    • Minor version: 14.00.3294.2 (SQL Server 2017 CU20)
    • Major version: 15.00.4043.16 (SQL Server 2019 CU5)

    SQL Server engine and version

Performing a major version upgrade for AWS RDS SQL Server

Let’s assume we require to perform a major version upgrade from SQL Server 2017 to SQL Server 2019. For this purpose, modify the existing RDS instance, choose the required upgraded version.

AWS RDS SQL Server: major version upgrade

You can plan to upgrade immediately or during the maintenance window. Review your changes and click on Modify DB Instance for performing upgrades.

You can use the AWS CLI for DB upgrades as well. Let’s use this method in this article. In the below CLI command, specify your source RDS instance identifier, target engine version, argument allow-major-version-upgrade and apply-immediately. In case you want to upgrade during the maintenance window, use the argument –no-apply-immediately.

aws rds modify-db-instance ^
–db-instance-identifier sqlshackdemo ^
–engine-version 15.00.4043.16.v1 ^
–allow-major-version-upgrade ^
–apply-immediately

When I executed the script, it failed with the following error message.

Review your changes

In my demo environment, I am using the db.t2.micro instance with SQL Server 2017. In the SQL Server 2019, we cannot use the db.t2.micro instance. You can validate the combination of SQL RDS and instance type combination before planning for upgrades. You can refer to DB instance class support for Microsoft SQL Server for more details.

Before I upgrade my RDS to SQL Server 2019, I need to modify the instance type. As shown below, I upgrade it to db.t3.small from the db.t2.micro.

modify the instance type

Apply your changes immediately; it takes a few minutes for DB instance upgrade.

DB instance upgrade

Once the RDS instance is in available status with the new instance type (db.t3.small), rerun the AWS CLI command for a database upgrade.

This time CLI command works fine, and it returns the following output in the JSON format.

instance is in available status

In the below image, note-down the following information

  • Current RDS SQL version: 14.00.3281.6.v1
  • Target RDS SQL version: 15.00.4043.16.v1
  • Parameter Group and Options Group: By default, AWS uses the parameter and options group based on your SQL version, edition. In the newer RDS instance, AWS assigns the default parameter and options group. Usually, DBA creates new groups based on their configuration requirements. For example, if you want to enable the native backup to AWS S3 bucket, you create a new option group and modify your RDS instance to use it

Therefore, you should note down your existing configurations before upgrading RDS, create new options, parameter groups as per your targeted SQL version, edition and modify targeted RDS to use them.

Parameter Group and Options Group

Refresh your RDS dashboard and status changes from Available to Upgrading, as shown below. We cannot access the database while an upgrade is in process.

Refresh your RDS dashboard

Once the AWS RDS SQL Server upgrades to SQL Server 2019, connect it using SSMS and run quality assurance tests, application validations to be sure that everything is working per your expectation.

Validate SQL versions

By default, all existing user databases remain in the old version compatibility level. However, if you create a new database after the upgrade, it takes the compatibility level of the new version. For example, in my case, the database compatibility level for the new database is 150 ( SQL Server 2019).

database compatibility level

Conclusion

In this article, we explored the minor and major version upgrades for AWS RDS SQL Server. You should plan the upgrades with application compatibility, compatibility level, deprecated features, enhancements. You can plan to upgrade the development databases first and move to production once everything remains perfect.

Rajendra Gupta
AWS, AWS RDS, SQL Server 2019

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

1,330 Views