How do I upgrade or downgrade the SQL Server engine edition in RDS for SQL Server?

7 minute read

I want to upgrade or downgrade the SQL Server engine edition in Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server.

Short description

Amazon RDS for SQL Server supports Express, Web, Standard, and Enterprise editions. You can't use the Amazon RDS console or AWS Command Line Interface (AWS CLI) to perform a SQL Server edition change as an in-place modification.

Resolution

Upgrade the SQL Server engine edition

Create an RDS snapshot of your existing RDS for SQL Server instance, and then restore a new RDS instance from the snapshot. For upgrade limitations, see Microsoft SQL Server considerations.

Complete the following steps:

Create a snapshot of the original RDS for SQL Server instance.
Restore the snapshot to create a new RDS instance. When you restore the instance, select a later edition of the database.
Rename or delete the original RDS for SQL Server instance so that you can reuse the DNS endpoint name. For more information, see the Rename the RDS instance section of this article.

To upgrade from Standard edition to Enterprise edition, see Modify an Amazon RDS for SQL Server instance from Standard Edition to Enterprise Edition.

You can use the same snapshot and restore method for the following types of upgrades:

Standard edition to Enterprise edition
Web edition to Standard edition or Enterprise edition
Express edition to Web edition, Standard edition, or Enterprise edition

Note: When you upgrade the edition, snapshot restoration creates a new RDS for SQL Server instance. The new instance has a different RDS endpoint from the snapshot source instance.

Downgrade the SQL Server engine edition

Amazon RDS doesn't support an in-place downgrade of an RDS for SQL Server instance from later to earlier editions. However, you can complete any of the following types of downgrades:

Enterprise edition to Standard, Web, or Express edition
Standard edition to Web or Express edition
Web edition to Express edition

To downgrade your RDS for SQL Server edition, use the native backup and restore option, AWS Database Migration Service (AWS DMS), or other tools. Choose a downgrade option based on factors such as downtime, effort, and the complexity of your use case.

Use native backup and restore to downgrade the edition

Native backup and restore creates a full backup of the databases on the existing source RDS for SQL Server instance.

To downgrade from a source Enterprise instance to a target Standard instance, complete the following steps:

Create a new RDS for SQL Server DB instance with Standard edition.
Add the native backup and restore option on the source Enterprise and target Standard edition instances.
Back up each user database on the source Enterprise instance to an Amazon Simple Storage Service (Amazon S3) bucket.
Run the sys.dm_db_persisted_sku_features query on each database on the source Enterprise instance:
```
USE database-name
GO
SELECT feature_name FROM sys.dm_db_persisted_sku_features;
GO
```
Note: Replace database-name with the name of your database. The preceding query checks whether any features use the later edition. Features that use the later edition might not work when you restore the databases to the earlier edition target instance. For more information, see sys.dm_db_persisted_sku_features (Transact-SQL) on the Microsoft website.
Restore the backups from the Amazon S3 bucket to the target Standard instance.
Create the required logins and users on the target Standard instance databases. Also, create the correct security group and attach the correct parameter-option groups.

Note: You can use the preceding steps to export and import databases across any editions of SQL Server on RDS.

Use AWS DMS to downgrade the edition

AWS DMS replicates ongoing changes from the later edition instance to the earlier edition instance. AWS DMS allows unidirectional replication and bulk-load tables, and it captures supported data changes.

For more information, see the following AWS documentation:

Use other tools to import and export SQL Server data

You can also use the following tools to import and export your database:

SQL Server Import and Export Wizard
Generate and Publish Scripts Wizard
Bulk copy (bcp utility)

Note: Before you use any of the preceding tools, you must launch the instance with the earlier SQL Server edition.

When you use the tools to move data, multiple data consistency or integrity issues might occur that you must resolve. Before you use one of the tools, test the process in a test environment.

For the SQL Server Import and Export Wizard, create and copy the schema of the source instance's databases and objects onto the target instance. Then, use the wizard to copy tables, views, or queries from one RDS for SQL Server DB instance to another data store.

For the SQL Server Generate and Publish Scripts Wizard and bcp utility, use the wizard to create scripts for an entire database or selected objects. Run the scripts on a target SQL Server DB instance to recreate the scripted objects. Then, use the bulk copy feature to export the data for the selected objects to the target DB instance. Run the bcp utility from an Amazon Elastic Compute Cloud (Amazon EC2) instance that has connectivity to both the source and target RDS instances.

Rename the RDS instance

All the preceding options result in the creation of a new target RDS instance. The new RDS instance has a different RDS DNS endpoint from the existing source RDS instance.

When you update the new RDS endpoint across applications and services, you might miss a connection string update in a component. This can result in errors.

To prevent this issue, it's a best practice to rename the source and target RDS instances. When you rename the instances, the target edition instance has the same RDS DNS endpoint as the original source edition instance. You don't need to change the connection strings of dependent applications or services after you change the edition.

To rename the source and target RDS instances after you change the edition, complete the following steps:

Note: In the following example procedure, the source RDS instance is rds-original with Enterprise edition and the target instance is rds-new with Standard edition.

Stop all incoming traffic to the source instance rds-original.
Upgrade or downgrade the SQL Server edition on the RDS instance.
Note: After you upgrade or downgrade, the source instance is rds-original and the target instance is rds-new.
Modify the source instance to rename the DB instance from rds-original to a different name, such as rds-original-old.
After the rds-original-old instance is in the Available state, rename the target DB instance from rds-new to the original name of the source instance rds-original.
Confirm that the instance names changed to rds-original-old and rds-original and that both are in the Available state.
Keep the new target RDS instance security groups the same as the source instance to maintain network connectivity from the existing applications.
Allow incoming traffic to the rds-original instance that has the required SQL Server edition. You don't need to change the application connection strings because RDS has the same DNS endpoint as the source instance.
Test the application to make sure that the RDS instance edition change doesn't cause any errors.
If there are no errors, then create a final snapshot of the rds-original-old instance.
(Optional) To reduce costs, delete the instance.