Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Upgrading the Microsoft SQL Server DB Engine

When Amazon RDS supports a new version of Microsoft SQL Server, you can upgrade your DB instances to the new version. Amazon RDS supports the following upgrades to a Microsoft SQL Server DB instance:

  • Major Version Upgrades

  • 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.

You must modify the DB instance manually to perform a major version upgrade. Minor version upgrades occur automatically if you enable auto minor version upgrades on your DB instance. In all other cases, you must modify the DB instance manually to perform a minor version upgrade.

For information about what SQL Server versions are available on Amazon RDS, see Microsoft SQL Server on Amazon RDS.

Overview of Upgrading

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. If the upgrade doesn't work for your databases, you can restore this snapshot to create a DB instance running the old version. The second DB snapshot is taken after the upgrade completes.

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 a DB Instance Running the Microsoft SQL Server Database Engine.

After an upgrade is complete, you can't revert to the previous version of the database engine. If you want to return to the previous version, restore 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 complete, both metrics will return to normal.

Major Version Upgrades

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 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 2016

SQL Server 2017

SQL Server 2014

SQL Server 2017

SQL Server 2016

SQL Server 2012

SQL Server 2017

SQL Server 2016

SQL Server 2014

SQL Server 2008 R2 (Deprecated)

SQL Server 2016

SQL Server 2014

SQL Server 2012

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 in the Microsoft documentation.

When you upgrade your DB instance, all existing databases remain at their original compatibility level. For example, if you upgrade from SQL Server 2012 to SQL Server 2014, all existing databases have a compatibility level of 110. Any new database created after the upgrade have compatibility level 120.

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 2014, issue the following command:

ALTER DATABASE customeracct SET COMPATIBILITY_LEVEL = 120

Multi-AZ and In-Memory Optimization 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 Microsoft SQL Server.

If your DB instance is in a Multi-AZ deployment, both the primary and standby instances are upgraded. Amazon RDS does rolling upgrades. You have an outage only for the duration of a failover.

SQL Server 2014/2016/2017 Enterprise Edition supports in-memory optimization.

Option and Parameter Group Considerations

Option Group Considerations

If your DB instance uses a custom 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. In that case, you must specify a new option group when you upgrade. 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 or Making a Copy of an Option Group.

Parameter Group Considerations

If your DB instance uses a custom parameter group, in some cases Amazon RDS can't automatically assign your DB instance a new parameter group. For example, when you upgrade to a new major version. In that case, you must specify a new parameter group when you upgrade. 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 or Copying a DB Parameter Group.

Testing an 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 the upgrade documentation for the new version of the database engine to see if there are compatibility issues that might affect your database or applications:

  2. 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.

  3. 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.

  4. Create a DB snapshot of the DB instance to be upgraded. For more information, see Creating a DB Snapshot.

  5. Restore the DB snapshot to create a new test DB instance. For more information, see Restoring from a DB Snapshot.

  6. Modify this new test DB instance to upgrade it to the new version, by using one of the following methods:

  7. Evaluate the storage used by the upgraded instance to determine if the upgrade requires additional storage.

  8. 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.

  9. 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:

Important

If you have any snapshots that are encrypted using 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 a point-in-time restore (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.