# Upgrades of the Microsoft SQL Server DB engine When Amazon RDS supports a new version of a database engine, you can upgrade your DB instances to the new version. There are two kinds of upgrades for SQL Server DB instances: major version upgrades and minor version upgrades. *Major version upgrades* can contain database changes that are not backward-compatible with existing applications. As a result, you must *manually* perform major version upgrades of your DB instances. You can initiate a major version upgrade by modifying your DB instance. However, before you perform a major version upgrade, we recommend that you test the upgrade by following the steps described in [Testing an RDS for SQL Server upgrade](USER_UpgradeDBInstance.SQLServer.UpgradeTesting.md). *Minor version upgrades* contain only changes that are backward-compatible with existing applications. You can upgrade the minor version for your DB instance in two ways: + *Manually* – Modify your DB instance to initiate the upgrade + *Automatically* – Enable automatic minor version upgrades for your DB instance When you enable automatic minor version upgrades, RDS for SQL Server automatically upgrades your database instance during scheduled maintenance windows when critical security updates are available in a newer minor version. For minor engine versions after `16.00.4120.1`, `15.00.4365.2`, `14.00.3465.1`, `13.00.6435.1`, the following security protocols are disabled by default: + `rds.tls10` (TLS 1.0 protocol) + `rds.tls11` (TLS 1.1 protocol) + `rds.rc4` (RC4 cipher) + `rds.curve25519` (Curve25519 encryption) + `rds.3des168` (Triple DES encryption) For earlier engine versions, Amazon RDS enables these security protocols by default. ``` ... "ValidUpgradeTarget": [ { "Engine": "sqlserver-se", "EngineVersion": "14.00.3281.6.v1", "Description": "SQL Server 2017 14.00.3281.6.v1", "AutoUpgrade": false, "IsMajorVersionUpgrade": false } ... ``` For more information about performing upgrades, see [Upgrading a SQL Server DB instance](#USER_UpgradeDBInstance.SQLServer.Upgrading). For information about what SQL Server versions are available on Amazon RDS, see [Amazon RDS for Microsoft SQL Server](CHAP_SQLServer.md). Amazon RDS also supports upgrade rollout policy to manage automatic minor version upgrades across multiple database resources and AWS accounts. For more information, see [Using AWS Organizations upgrade rollout policy for automatic minor version upgrades](RDS.Maintenance.AMVU.UpgradeRollout.md). **Topics** + [Major version upgrades for RDS for SQL Server](USER_UpgradeDBInstance.SQLServer.Major.md) + [Considerations for SQL Server upgrades](USER_UpgradeDBInstance.SQLServer.Considerations.md) + [Testing an RDS for SQL Server upgrade](USER_UpgradeDBInstance.SQLServer.UpgradeTesting.md) + [Upgrading a SQL Server DB instance](#USER_UpgradeDBInstance.SQLServer.Upgrading) + [Upgrading deprecated DB instances before support ends](#USER_UpgradeDBInstance.SQLServer.DeprecatedVersions) # Major version upgrades for RDS for SQL Server Amazon RDS currently supports the following major version upgrades to a Microsoft SQL Server DB instance. You can upgrade your existing DB instance to SQL Server 2017 or 2019 from any version except SQL Server 2008. To upgrade from SQL Server 2008, first upgrade to one of the other versions. **** | Current version | Supported upgrade versions | | --- | --- | | SQL Server 2019 | SQL Server 2022 | | SQL Server 2017 | SQL Server 2022 SQL Server 2019 | | SQL Server 2016 | SQL Server 2022 SQL Server 2019 SQL Server 2017 | You can use an AWS CLI query, such as the following example, to find the available upgrades for a particular database engine version. **Example** For Linux, macOS, or Unix: ``` aws rds describe-db-engine-versions \ --engine sqlserver-se \ --engine-version 14.00.3281.6.v1 \ --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" \ --output table ``` For Windows: ``` aws rds describe-db-engine-versions ^ --engine sqlserver-se ^ --engine-version 14.00.3281.6.v1 ^ --query "DBEngineVersions[*].ValidUpgradeTarget[*].{EngineVersion:EngineVersion}" ^ --output table ``` The output shows that you can upgrade version 14.00.3281.6 to the latest available SQL Server 2017 or 2019 versions. ``` -------------------------- |DescribeDBEngineVersions| +------------------------+ | EngineVersion | +------------------------+ | 14.00.3294.2.v1 | | 14.00.3356.20.v1 | | 14.00.3381.3.v1 | | 14.00.3401.7.v1 | | 14.00.3421.10.v1 | | 14.00.3451.2.v1 | | 15.00.4043.16.v1 | | 15.00.4073.23.v1 | | 15.00.4153.1.v1 | | 15.00.4198.2.v1 | | 15.00.4236.7.v1 | +------------------------+ ``` ## Database compatibility level You can use Microsoft SQL Server database compatibility levels to adjust some database behaviors to mimic previous versions of SQL Server. For more information, see [Compatibility level](https://msdn.microsoft.com/en-us/library/bb510680.aspx) in the Microsoft documentation. When you upgrade your DB instance, all existing databases remain at their original compatibility level. You can change the compatibility level of a database by using the ALTER DATABASE command. For example, to change a database named `customeracct` to be compatible with SQL Server 2016, issue the following command: ``` 1. ALTER DATABASE customeracct SET COMPATIBILITY_LEVEL = 130 ``` # Considerations for SQL Server upgrades Amazon RDS takes two DB snapshots during the upgrade process. The first DB snapshot is of the DB instance before any upgrade changes have been made. The second DB snapshot is taken after the upgrade finishes. **Note** Amazon RDS only takes DB snapshots if you have set the backup retention period for your DB instance to a number greater than 0. To change your backup retention period, see [Modifying an Amazon RDS DB instance](Overview.DBInstance.Modifying.md). After an upgrade is completed, you can't revert to the previous version of the database engine. If you want to return to the previous version, restore from the DB snapshot that was taken before the upgrade to create a new DB instance. During a minor or major version upgrade of SQL Server, the **Free Storage Space** and **Disk Queue Depth** metrics will display `-1`. After the upgrade is completed, both metrics will return to normal. Before you upgrade your SQL Server instance, review the following information. **Topics** + [Best practices before initiating an upgrade](#USER_UpgradeDBInstance.SQLServer.BestPractices) + [Multi-AZ considerations](#USER_UpgradeDBInstance.SQLServer.MAZ) + [Read replica considerations](#USER_UpgradeDBInstance.SQLServer.readreplica) + [Option group considerations](#USER_UpgradeDBInstance.SQLServer.OGPG.OG) + [Parameter group considerations](#USER_UpgradeDBInstance.SQLServer.OGPG.PG) ## Best practices before initiating an upgrade Before starting the upgrade process, implement the following preparatory stpes to allow optimal upgrade performance and minimize potential issues: Timing and workload management + Schedule upgrades during low transaction volume periods. + Minimize write operations during the upgrade window. This allows Amazon RDS to complete upgrades faster by reducing the number of transaction log backup files that RDS needs to restore during secondary-to-primary pairing. Transaction management + Identify and monitor long-running transactions. + Ensure all critical transactions are commited before starting the upgrade. + Prevent long-running transactions during the upgrade window. Log file optimization Review and optimize transaction log files: + Shrink oversized log files. + Reduce high log consumption patterns. + Manage Virtual Log Files (VLFs). + Maintain adequate free space for normal operations. ## Multi-AZ considerations Amazon RDS supports Multi-AZ deployments for DB instances running Microsoft SQL Server by using SQL Server Database Mirroring (DBM) or Always On Availability Groups (AGs). For more information, see [Multi-AZ deployments for Amazon RDS for Microsoft SQL Server](USER_SQLServerMultiAZ.md). In a Multi-AZ deployment (Mirroring/AlwaysOn), when an upgrade is requested, RDS follows a rolling upgrade strategy for the primary and secondary instances. Rolling upgrades ensure at least one instance is available for transactions while the secondary instance is upgraded. The outage is expected to only last the duration of a failover. During the upgrade, RDS removes the secondary instance from the Multi-AZ configuration, performs an upgrade of the secondary instance, and restores any transaction log backups from the primary taken during the time it was disconnected. After all the log backups are restored, RDS joins the upgraded secondary to the primary. When all the databases are in a synchronized state, RDS performs a failover to the upgraded secondary instance. Once the failover is completed, RDS proceeds with upgrading the old primary instance, restores any transaction log backups, and pairs it with the new primary. To minimize this failover duration, we recommend using AlwaysOn AGs availability group listener endpoint when using client libraries that support the `MultiSubnetFailover` connection option in the connection string. When using the availability group listener endpoint, failover times are typically less than 10 seconds, however, this duration does not include any additional crash recovery time. ## Read replica considerations During a database version upgrade, Amazon RDS upgrades all of your read replicas along with the primary DB instance. Amazon RDS does not support database version upgrades on the read replicas separately. For more information on read replicas, see [Working with read replicas for Microsoft SQL Server in Amazon RDS](SQLServer.ReadReplicas.md). When you perform a database version upgrade of the primary DB instance, all its read-replicas are also automatically upgraded. Amazon RDS will upgrade all of the read replicas simultaneously before upgrading the primary DB instance. Read replicas may not be available until the database version upgrade on the primary DB instance is complete. ## Option group considerations If your DB instance uses a custom DB option group, in some cases Amazon RDS can't automatically assign your DB instance a new option group. For example, when you upgrade to a new major version, you must specify a new option group. We recommend that you create a new option group, and add the same options to it as your existing custom option group. For more information, see [Creating an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Create) or [Copying an option group](USER_WorkingWithOptionGroups.md#USER_WorkingWithOptionGroups.Copy). ## Parameter group considerations If your DB instance uses a custom DB parameter group: + Amazon RDS automatically reboots the DB instance after an upgrade. + In some cases, RDS can't automatically assign a new parameter group to your DB instance. For example, when you upgrade to a new major version, you must specify a new parameter group. We recommend that you create a new parameter group, and configure the parameters as in your existing custom parameter group. For more information, see [Creating a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Creating.md) or [Copying a DB parameter group in Amazon RDS](USER_WorkingWithParamGroups.Copying.md). # Testing an RDS for SQL Server upgrade Before you perform a major version upgrade on your DB instance, you should thoroughly test your database, and all applications that access the database, for compatibility with the new version. We recommend that you use the following procedure. **To test a major version upgrade** 1. Review [Upgrade SQL Server](https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-sql-server) in the Microsoft documentation for the new version of the database engine to see if there are compatibility issues that might affect your database or applications. 1. If your DB instance uses a custom option group, create a new option group compatible with the new version you are upgrading to. For more information, see [Option group considerations](USER_UpgradeDBInstance.SQLServer.Considerations.md#USER_UpgradeDBInstance.SQLServer.OGPG.OG). 1. If your DB instance uses a custom parameter group, create a new parameter group compatible with the new version you are upgrading to. For more information, see [Parameter group considerations](USER_UpgradeDBInstance.SQLServer.Considerations.md#USER_UpgradeDBInstance.SQLServer.OGPG.PG). 1. Create a DB snapshot of the DB instance to be upgraded. For more information, see [Creating a DB snapshot for a Single-AZ DB instance for Amazon RDS](USER_CreateSnapshot.md). 1. Restore the DB snapshot to create a new test DB instance. For more information, see [Restoring to a DB instance](USER_RestoreFromSnapshot.md). 1. Modify this new test DB instance to upgrade it to the new version, by using one of the following methods: + [Console](USER_UpgradeDBInstance.Upgrading.md#USER_UpgradeDBInstance.Upgrading.Manual.Console) + [AWS CLI](USER_UpgradeDBInstance.Upgrading.md#USER_UpgradeDBInstance.Upgrading.Manual.CLI) + [RDS API](USER_UpgradeDBInstance.Upgrading.md#USER_UpgradeDBInstance.Upgrading.Manual.API) 1. Evaluate the storage used by the upgraded instance to determine if the upgrade requires additional storage. 1. Run as many of your quality assurance tests against the upgraded DB instance as needed to ensure that your database and application work correctly with the new version. Implement any new tests needed to evaluate the impact of any compatibility issues you identified in step 1. Test all stored procedures and functions. Direct test versions of your applications to the upgraded DB instance. 1. If all tests pass, then perform the upgrade on your production DB instance. We recommend that you do not allow write operations to the DB instance until you confirm that everything is working correctly. ## Upgrading a SQL Server DB instance For information about manually or automatically upgrading a SQL Server DB instance, see the following: + [Upgrading a DB instance engine version](USER_UpgradeDBInstance.Upgrading.md) + [Best practices for upgrading SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server](https://aws.amazon.com/blogs/database/best-practices-for-upgrading-sql-server-2008-r2-to-sql-server-2016-on-amazon-rds-for-sql-server/) **Important** If you have any snapshots that are encrypted using AWS KMS, we recommend that you initiate an upgrade before support ends. ## Upgrading deprecated DB instances before support ends After a major version is deprecated, you can't install it on new DB instances. RDS will try to automatically upgrade all existing DB instances. If you need to restore a deprecated DB instance, you can do point-in-time recovery (PITR) or restore a snapshot. Doing this gives you temporary access a DB instance that uses the version that is being deprecated. However, after a major version is fully deprecated, these DB instances will also be automatically upgraded to a supported version.