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.
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.
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.
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.
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
In the below image, we get the overall process for upgrading an AWS RDS SQL Server.
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
-
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)
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.
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.
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.
Apply your changes immediately; it takes a few minutes for 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.
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.
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.
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.
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).
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023