Webstep
Published in

Webstep

Are you relying on automated upgrades of your SQLServer in AWS?

UPDATE: As of August 16, 2021, Amazon RDS for SQL Server now supports Automatic Minor Version Upgrades This does NOT apply to all versions of SQL-server — you can use the CLI-command to check for availability.

Amazon Web Services (AWS) Relational Database Service (RDS) covers PostgreSQL, MySQL, MariaDB, Oracle and SQLServer in addition to Amazon Aurora with PostgreSQL and MySQL compatibility.

When you spin up one of these databases AWS is doing the heavy lifting for you like setting up a server, installing software and making sure you get your data backed up daily and giving you point in time recovery. Patching and upgrading is also done in minutes and can be automatically applied by AWS in most cases.

How do you upgrade minor versions?

A minor version upgrade is not hard in RDS, all you need to do is trigger a modify-db-instance with the CLI or schedule an update in the console. Then you wait for AWS to do the rest.

Most customers rely on automated upgrades of minor version, but there is one exception a lot is unaware of — automated upgrades of SQLServer is not supported.

As described in RDS-documentation there are two categories of upgrades; minor and major

There are two kinds of upgrades: major version upgrades and minor version upgrades. In general, a major engine version upgrade can introduce changes that are not compatible with existing applications. In contrast, a minor version upgrade includes only changes that are backward-compatible with existing applications.

Major upgrades will never happen without your interaction. A minor upgrade can happen automatically, if you opt in to it, according to the RDS-documentation.

For minor version upgrades, you can manually modify the engine version, or you can choose to enable auto minor version upgrades.

This statement is only true for all databases except SQL Server. For SQLServer you must trigger the upgrade yourself (or find a way to automate the trigger)

Be aware that even if the RDS-service say that automated upgrades are turned on — it is not for SQLServer

In the CLI you can run with this result

...
"AutoMinorVersionUpgrade”: true
...

This is what the console is showing for SQL Server

RDS console with upgrade enabled

How to automate minor upgrades of SQLServer?

If you try to automate — be aware that the event “RDS-EVENT-0155 (The DB instance has a DB engine minor version upgrade available.)” is not available for SQLServer. This is not documented AFAIK, but has been confirmed by AWS Support.

According to support the only way to “automate” an alert is to check the “Recommendations”-menu in the RDS-console. Unfortunately this option is not available in all regions, like my “home region” eu-north-1.

If you want to do the job of checking it manually, you can check the version you are running in the console and see list of available versions in the documentation.

If you would like to follow AWS recommendation, and automate everything, you would need to know the value of and of your SQLServer. Then query the RDS service for any updates

rds describe-db-engine-versions — engine-version $YOURVERSION -engine $YOURENGINE

If you get an empty array of you are on the latest minor and major version. If you get an array back, you would need to check for to avoid kicking of a major upgrade which can have unwanted side-effects.

> rds describe-db-engine-versions — engine-version 14.00.3049.1.v1 — engine sqlserver-web...
"ValidUpgradeTarget": [
{
"Engine": "sqlserver-web",
"EngineVersion": "14.00.3281.6.v1",
"Description": "SQL Server 2017 14.00.3281.6.v1",
"AutoUpgrade": false,
"IsMajorVersionUpgrade": false
},
{
"Engine": "sqlserver-web",
"EngineVersion": "14.00.3294.2.v1",
"Description": "SQL Server 2017 14.00.3294.2.v1",
"AutoUpgrade": false,
"IsMajorVersionUpgrade": false
},
{
"Engine": "sqlserver-web",
"EngineVersion": "14.00.3356.20.v1",
"Description": "SQL Server 2017 14.00.3356.20.v1",
"AutoUpgrade": false,
"IsMajorVersionUpgrade": false
},
{
"Engine": "sqlserver-web",
"EngineVersion": "15.00.4043.16.v1",
"Description": "SQL Server 2019 15.00.4043.16.v1",
"AutoUpgrade": false,
"IsMajorVersionUpgrade": true
},
{
"Engine": "sqlserver-web",
"EngineVersion": "15.00.4073.23.v1",
"Description": "SQL Server 2019 15.00.4073.23.v1",
"AutoUpgrade": false,
"IsMajorVersionUpgrade": true
}
],

Summary

If you are running SQLServer be sure someone or something is checking your database-version and apply pending minor upgrades if any. They can be applied during your maintenance window or immediately.

Always make sure your list of “Alternate Contacts” under “My account” in the console is up to date. Then you will get an email if your RDS minor version is coming to end of life.

--

--

Webstep is an IT consultant company composed of 500 employees located in Sweden and Norway. We have consultants who are experts within, System Development, Business Intelligence, IoT, Data Science (ML/AI) and IT-Management. We love to learn new things and share it with others.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Anders Bjørnestad

Consultant, AWS Community Hero, APN Ambassador and AWS Trainer based in Norway. Have worked with AWS technology since 2010.