How do I upgrade or downgrade the SQL Server engine edition in RDS for 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

Note: If you receive errors when you run AWS CLI commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

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:

1. Create a snapshot of the original RDS for SQL Server instance.
2. Restore the snapshot to create a new RDS instance. When you restore the instance, select a later edition of the database.
3. 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're upgrading 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 edition

An in-place downgrade of an RDS for SQL Server instance from later to earlier editions isn't supported. 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.

Native backup and restore option in RDS for SQL Server

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:

1. Create a new RDS for SQL Server DB instance with Standard edition.
2. Add the native backup and restore option on the source Enterprise and target Standard edition instances.
3. Back up each user database on the source Enterprise instance to an Amazon Simple Storage Service (Amazon S3) bucket.
4. Run the sys.dm_dm_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 are currently connected to the later edition. Features that are connected to 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.
5. Restore the backups from the Amazon S3 bucket to the target Standard instance.
6. Create the required logins and users on the target Standard instance databases. Also, create the appropriate security group and attach the appropriate parameter-option groups.

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

AWS DMS

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 captures supported data changes.

For more information, see the following AWS Documentation:

• Using a Microsoft SQL Server database as a source for AWS DMS
• Using a Microsoft SQL Server database as a target for AWS Database Migration Service
• Limitations on using SQL Server as a source for AWS DMS
• Migrating your SQL Server database to Amazon RDS for SQL Server using AWS DMS

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, thoroughly 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 object on to 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. You can 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 of 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.

Sometimes when you update the new RDS endpoint across applications and services, the connection string update is missed in a component and results 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. So, you don't need to change the connection strings of the 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.

1. Stop all incoming traffic to the source instance rds-original.
2. 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.
3. Modify the source instance to rename the DB instance from rds-original to a different name, such as rds-original-old.
4. 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.
5. Confirm that the instances are renamed to rds-original-old and rds-original and are in the Available state.
6. Keep the new target RDS instance security groups the same as the source instance to maintain network connectivity from the existing applications.
7. 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 source instance.
8. Perform the application testing to make sure that the RDS instance edition change doesn't cause any errors.
9. If there are no errors, then create a final snapshot of the rds-original-old instance, and delete the instance to reduce costs.

Note: Before you implement changes in the production environment, it's a best practice to test activities in a test environment.

Related information

AWS Prescriptive Guidance - Evaluate downgrading Microsoft SQL Server from Enterprise edition to Standard edition on AWS