Database administrators often need to upgrade SQL Server versions for on-premise infrastructure. However, when it comes to AWS cloud infrastructure, there are two options for database deployments: SQL Server deployed on the AWS EC2 instance and an AWS managed instance relational database service.

In this article, we will focus on the process of upgrading minor and major versions for AWS RDS SQL Server. Understanding the difference between minor and major version upgrades is crucial. Minor version upgrades involve applying service packs, hotfixes, and cumulative packs, while major version upgrades involve changing the SQL Server version itself, such as upgrading from SQL Server 2017 to SQL Server 2019.

It is important to note that AWS RDS SQL Server does not support automatic minor version upgrades, even if the “Enable auto minor version upgrade” option is enabled. Manual upgrades are required for both minor and major versions.

The upgrade process for AWS RDS SQL Server involves taking two database snapshots: one before starting the upgrade and another immediately after the upgrade completes. These snapshots act as restoration points in case any issues arise during the upgrade.

Before performing a major version upgrade, it is essential to check the supported upgrade paths. This can be done by browsing the SQL Server versions supported in RDS, checking the supported upgrade RDS versions in the AWS web portal, or using the AWS CLI to find out the supported database upgrade versions.

To perform a major version upgrade, the existing RDS instance needs to be modified by choosing the desired upgraded version. This can be done immediately or during the maintenance window. The changes should be reviewed and then the upgrade can be initiated.

It is worth mentioning that certain combinations of SQL RDS and instance types may not be compatible. Therefore, it is important to validate the compatibility before planning for upgrades.

Once the upgrade is complete, it is recommended to run quality assurance tests and application validations to ensure everything is functioning as expected. It is also important to note that existing user databases will remain in the old version compatibility level, while new databases created after the upgrade will take on the compatibility level of the new version.

In conclusion, upgrading SQL Server versions in AWS RDS requires careful planning and consideration of application compatibility, compatibility level, deprecated features, and enhancements. It is advisable to upgrade development databases first and then proceed to production once everything has been thoroughly tested.