AWS Database Blog

Upgrade Amazon RDS for SQL Server 2014 to a newer supported version using the AWS CLI

Amazon Relational Database Service (Amazon RDS) is a managed database service that simplifies the process of setting up, operating, managing, and scaling your database in the cloud environment. Amazon RDS supports multiple versions of Microsoft SQL Server, ranging from SQL Server 2014 to the latest SQL Server 2022 release.

As SQL Server 2014 approaches its end of support on July 9, 2024, it’s crucial to understand your options and take a proactive approach in planning and upgrading your SQL Server databases to the latest version. Instances running on an end-of-support version can be vulnerable and pose security risks. Upgrading your RDS for SQL Server 2014 instances will not only help you comply with security mandates but will also allow you to fully leverage the latest security, performance, and innovation features offered in the newer version of Amazon RDS for SQL Server.

We support several different major and minor version combinations so you can upgrade your RDS for SQL Server 2014 instances directly to the latest minor version of SQL Server 2016, 2017, 2019, or 2022. For more information, see Upgrading the Microsoft SQL Server DB engine.

In this post we show you how to leverage AWS Command Line Interface (AWS CLI) automation to upgrade your current RDS for SQL Server 2014 instance to a more recent supported version. If your RDS for SQL Server instances are managed by an AWS CloudFormation stack or other infrastructure as code (IaC) pipelines, you may need to update your continuous integration and continuous delivery (CI/CD) pipelines to detect and resolve drift with import operations after the upgrade process. If you have multiple instances requiring an upgrade, this automation can help reduce the manual efforts involved in the upgrade process and is less error-prone compared to manually upgrading your RDS for SQL Server instance from the AWS Management Console.

Solution overview

RDS for SQL Server instances require you to attach a parameter group and option group, which can be default or custom. You can utilize your parameter group to configure the parameters on Amazon RDS for SQL Server based on your performance requirements. You can also use option groups to enable and configure additional features, which make it easier to manage data and databases, and to provide additional security for your database. During the upgrade process for your RDS for SQL Server instance, you have the option to create a new parameter group and option group.

If you wish to copy existing parameters and options to a higher version, there is currently no available automation to address this requirement. This becomes more challenging when you need to upgrade multiple RDS for SQL Server instances at the same time. This blog walks you through our single-click deployment automation, aws-rds-sqlserver-2014-upgrade, to address this challenge and makes your upgrade journey less cumbersome

When preparing your SQL Server upgrade strategy, you must consider several factors. One crucial consideration is the ease of rolling back the upgrade in case of an issue and the permissible downtime. In general, upgrades for Multi-AZ instances will experience less downtime than Single-AZ instances. If your downtime tolerance is minimal, you can plan a side-by-side upgrade where a new RDS for SQL Server instance is built, and data is replicated to it using custom log shipping, AWS Database Migration Service (AWS DMS),or any other replication methodology. After synchronizing the data between the two instances, you can point the application to the new instance endpoint. Similarly, upgrading an RDS for SQL Server instance using the automation script described in this post will cause downtime, and your rollback strategy will involve restoring from a snapshot taken before the upgrade process began.

In this solution, you will use a custom script to copy the existing parameters and options from a lower version to a higher version of SQL Server using the AWS CLI.

The following are the high-level tasks for the automation:

  1. Validate the upgrade path for your RDS for SQL Server 2014 instance.
  2. Create a new parameter group for the target SQL Server version.
  3. Create a new option group for the target SQL Server version.
  4. Copy parameters to the newly created parameter group.
  5. Copy options to the newly created option group.
  6. Upgrade the existing RDS for SQL Server instance to the target SQL Server version.
  7. Reboot the upgraded RDS for SQL Server instance.

The following diagram illustrates the solution architecture.

RDS SQL Server upgrade Automation using AWS CLI Solution diagram

Prerequisites

In order to set up the automation and successfully test the solution, you should meet the following prerequisites:

  1. Have a source DB instance (Amazon RDS for SQL Server 2014).
  2. Create an AWS Identity and Access Management (IAM) role with the following permissions to perform the automation tasks. Download a sample policy template statement from the GitHub repo. Restrict access to only instances that need to be upgraded by specifying the ARN of the RDS for SQL Server instance in the Resource part of policy. 
    "rds:CreateDBParameterGroup",
"rds:ModifyOptionGroup",
"rds:ModifyDBParameterGroup"
"rds:CreateDBSnapshot",
"rds:ModifyDBClusterParameterGroup",
"rds:ModifyDBInstance",
"rds:ModifyDBCluster",
"rds:CreateDBClusterParameterGroup",
"rds:CreateOptionGroup"
  3. Install the latest version of the AWS CLI.
  4. Install Python version 3 or later.
  5. Back up your existing RDS for SQL Server 2014 instance. For more information, refer to Backup and Restore Strategies for Amazon RDS for SQL Server.

Automation overview

We have created an automation script to upgrade your RDS for SQL Server 2014 instance, which can be executed using the AWS CLI. In this section, we will discuss in greater detail the tasks performed by the script.

Validate your RDS for SQL Server instance upgrade path

You can upgrade an existing RDS for SQL Server 2014 Single-AZ instance to any supported target version up to SQL Server 2022 without any additional changes. Once the upgrade is complete, you have the option to manually modify the deployment to a Multi-AZ instance configuration.

This automation will upgrade both the primary and secondary hosts of the RDS for SQL Server 2014 Multi-AZ instance. Amazon RDS performs rolling upgrades, which means you will experience an outage only during the duration of a failover. If your existing RDS for SQL Server Multi-AZ instance is using Database Mirroring, you cannot directly upgrade to SQL Server 2022 because Database Mirroring is not supported in this major version on RDS for SQL Server. In that case, you need to convert your RDS for SQL Server instance to Single-AZ, upgrade the instance to SQL Server 2022, and then convert it back to Multi-AZ.

Run the following T-SQL code in SQL Server Management Studio to check whether your Multi-AZ instance is using database mirroring:

SELECT CASE WHEN dm.mirroring_state_desc IS NOT NULL 
THEN 'Multi-AZ (Mirroring)' WHEN dhdrs.group_database_id IS NOT NULL THEN 'Multi-AZ (AlwaysOn)' ELSE 'Single-AZ' END 'high_availability' 
FROM sys.databases sd LEFT JOIN sys.database_mirroring dm 
ON sd.database_id = dm.database_id LEFT JOIN sys.dm_hadr_database_replica_states dhdrs 
ON sd.database_id = dhdrs.database_id AND dhdrs.is_local = 1 WHERE DB_NAME(sd.database_id) = 'rdsadmin';

Create a new parameter group for the target SQL Server version

For RDS for SQL Server 2014 instances that are using a custom parameter group, this script will allow you to create a new parameter group compatible with the newer target version. If you decide to go with the default parameter group, provide the input default during the upgrade.

Create a new option group for the target SQL Server version

For RDS for SQL Server 2014 instances that are using a custom option group, this automation will allow you to create a new option group compatible with the newer target version. Make sure the option group aligns with the features and settings compatible with the target SQL Server version. If you decide to go with the default option group, provide the input default during the upgrade.

Copy parameters to the new parameter group

This automation will identify all non-default parameters in the custom parameter group of Amazon RDS for SQL Server 2014 and copy those parameters to the newer target version parameter group.

Copy options to the new option group

This automation copies the options from the current custom option group to the new option group that is created for the new SQL Server engine version. This helps carry over all the options that were used in Amazon RDS for SQL Server 2014 to the newer target version (if it’s supported in the newer target version). For more information about options compatible with your target SQL version, refer to Options for the Microsoft SQL Server database engine.

Upgrade the existing RDS for SQL Server instance to the target SQL Server version

After the parameter group and option group are created, the next step in the automation is to perform the upgrade. During the upgrade, the automation attaches the new parameter group and option group to the target DB instance, and upgrades the target DB instance to the newer target version.

Reboot the upgraded RDS for SQL Server instance

The automation will reboot the RDS for SQL Server instance so that the new custom parameter group and option group take effect.

Deploy the automation

Download the aws-rds-sqlserver-2014-upgrade automation files on a machine that has AWS CLI access to the RDS for SQL Server 2014 instance. Read through the README.md file for complete instructions on how to run this automation.

From the Amazon Elastic Compute Cloud (Amazon EC2) instance or virtual machine where the automation script was downloaded, as an authenticated IAM user with the IAM policy attached as described in the prerequisites section, run the rds_upgrade_2014.sh script. Running the script will prompt you to provide following details:

  • RDS for SQL Server 2014 instance identifier (RDS for SQL Server endpoint): < database-1>
  • RDS for SQL Server 2014 instance AWS Region (where Amazon RDS for SQL Server is deployed): <us-east-2>
  • RDS for SQL Server instance target parameter group name (higher version): <test-upgrade-pg-sql2019>
  • RDS for SQL Server instance target parameter group family (higher version): sqlserver-se-15.0
  • RDS for SQL Server instance target option group name (higher version): test-upgrade-og-sql2019
  • RDS for SQL Server instance target engine version (higher version): 15.00.4345.5.v1

The following screenshot shows sample input:

RDS SQL Server upgrade automation sample output

Based on the user inputs, the automation validates the upgrade path. If the upgrade path is feasible, automation performs the upgrade. If it fails, an error message appears and further action is required. Make sure to validate the upgrade process in a non-production environment, and make necessary changes before applying it to the production instance.

Test the upgraded instance

After running the automation, execute post-upgrade tests against the upgraded database instance. Test all your stored procedures and functions, and perform a comprehensive validation of the database and application functionality with the new SQL Server version. Identify any compatibility issues and resolve them in a lower environment.

If all your tests are successful, proceed with the upgrade on your production database instance. Avoid allowing write operations until you confirm that everything is functioning as expected to achieve a seamless transition to the newer SQL Server version in the production environment, minimizing downtime and potential issues.

Conclusion

In this post, we covered how to upgrade your RDS for SQL Server 2014 instance to a newer supported version using a custom automation script using the AWS CLI. Upgrading your RDS for SQL Server instance is more than just an upgrade; it’s a proactive decision that provides your SQL Server databases with cutting-edge security, enhanced performance, and the latest AWS-powered innovations.

In preparation for the deprecation of SQL Server 2014, refer to Best practices for upgrading SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server for many of the best practices that apply to all versions of Amazon RDS for SQL Server.

About the authors

Aravind Hariharaputran is Database Consultant with the Professional Services team at Amazon Web Services. He is passionate about databases in general with Microsoft SQL Server as his specialty. He helps build technical solutions that assist customers to migrate and optimize their on-premises database workload to the AWS Cloud. He enjoys spending time with family and playing cricket.

Yogi Barot is Principal Solutions Architect who has 25 years of experience working with different Microsoft technologies, her specialty is in SQL Server and different database technologies. Yogi has in depth AWS knowledge and expertise in running Microsoft workload on AWS.

Sabarinath Nair is Senior Database Consultant with the Professional Services team at Amazon Web Services. He has over 18 years of experience in Microsoft SQL Server and other Relational and non-Relational Database technologies. He works with customers on architecture, migration and optimizing their database workloads to AWS and helps them improve the value of the solutions.