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 perform a SQL Server edition change as an in-place modification using the RDS console or using the AWS Command Line Interface (AWS CLI).

To upgrade your SQL Server edition, create a snapshot and then restore using the higher engine edition. To downgrade, use one of these methods:

• Use the native backup and restore option in RDS for SQL Server.
• Use AWS Database Migration Service (AWS DMS).
• Import and export SQL Server data using other tools.

Resolution

Upgrade the SQL Server engine edition

To upgrade the SQL Server engine edition, create an RDS snapshot and then restore from that snapshot. For upgrade limitations, see Microsoft SQL Server considerations.

To upgrade using a snapshot, follow these steps:

1.    Create a snapshot of the original RDS for SQL Server instance.

2.    Restore the snapshot taken in Step 1, to create a new RDS instance. Change the required edition to the higher edition during the restore.

3.    Rename or delete the original RDS for SQL Server instance to free up the DNS endpoint name for reuse. For more information, see the section Rename the RDS instance.

For detailed instructions and steps on upgrading 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 these upgrades:

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

Important note: Snapshot restoration while upgrading the edition creates a new RDS for SQL Server instance. The new instance has a different RDS endpoint than the snapshot source instance.

Downgrade the SQL Server edition

In-place downgrading of RDS for SQL Server instance from higher to lower editions isn't supported because of limitations with SQL Server as a product. However, you can downgrade your RDS for SQL Server edition in any one of these combinations following the workaround options mentioned later:

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

To downgrade the RDS for SQL Server edition use one of these options:

Option 1: Use the native backup and restore option in RDS for SQL Server

Note: You can also use this option to move databases from lower to higher editions of RDS instances.

Native backup and restore creates a full backup of the databases on the existing source RDS for SQL Server instance. Store the backups on Amazon Simple Storage Service (Amazon S3) and then restore the backup files onto a new target RDS instance.

To downgrade from a source Enterprise instance to a target Standard instance, follow these steps:

1.    Create a new RDS for SQL Server with Standard edition SQL Server. This is the new target instance.

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 S3 bucket.

4.    Run the sys.dm_dm_persisted_sku-features (Transact-SQL) query on each database on the source instance. This query checks if there are any features currently in use that are bound to the higher edition. Features bound to the higher edition might not work when you restore the databases on to the lower edition target instance.

USE [database-name]
    GO
    SELECT feature_name FROM sys.dm_db_persisted_sku_features;
    GO

5.    Restore the backups from the S3 bucket to the target (Standard) RDS instance.

6.    Make sure to create the required logins and users on the target RDS instance databases. Also create the appropriate security group and attach the appropriate parameter-option groups. These are the same as the source RDS instance.

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

Option 2: Use AWS DMS

Note: You can use also use this option to move databases from lower to higher editions of RDS instances.

Use AWS DMS to migrate your databases. AWS DMS also replicates ongoing changes from the higher edition instance (the source endpoint) to the lower edition instance (the target endpoint).

AWS DMS allows unidirectional replication, bulk-load tables, and captures data changes (if supported by the source and target RDS for SQL Server instance versions).

For more information, see these topics:

• 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

Option 3: Import and export SQL Server data using other tools

You can use these additional tools to import and export your database:

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

The instance with the lower SQL Server edition must be created and active before using these tools.

Keep in mind that these tools require more effort than native backup and restore or AWS DMS. You might experience multiple data consistency or integrity errors that must be fixed. These errors occur when moving data using these tools. Thoroughly test the process in a test environment before deciding to use one of these tools.

• SQL Server Import and Export Wizard: Copy and create the schema of the source instance's databases and object on to the target instance. Then, use this wizard to copy one or more tables, views, or queries from one RDS for SQL Server DB instance to another data store. For more information, see SQL Server Import and Export Wizard.
• SQL Server Generate and Publish Scripts Wizard and bcp utility: Use the SQL Server Generate and Publish Scripts Wizard to create scripts for an entire database or selected objects. You can run these scripts on a target SQL Server DB instance to recreate the scripted objects. Then, use the bcp utility to bulk 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. For more information, see SQL Server Generate and Publish Scripts Wizard and bcp utility.

Note: All the options mentioned in this section can also be used to migrate databases from lower edition to higher edition RDS for SQL Server instances. However, the approach explained in the Upgrade SQL Server edition section is easier. Deciding which option to use depends on factors such as downtime, effort, complexity involved, and so on.

Rename the RDS instance

The options described for upgrading or downgrading the RDS for SQL Server edition always result in the creation of a new target RDS Instance. The new RDS instance has a different RDS DNS endpoint than the existing source RDS instance.

Sometimes, updating the new RDS endpoint across applications and other services misses the connection string update in one or more of these components. When this occurs, you might run into issues after the edition change of the RDS for SQL Server instance.

To avoid this, consider renaming the source and target RDS instances. Renaming makes sure that the target edition instance has the same RDS DNS endpoint as that of the original source edition instance.

Doing this avoids making changes in the connection strings of the dependent applications or services after the edition change on the RDS for SQL Server instance.

To rename the source and target RDS instances after changing the edition, follow these steps:

This example assumes that the source RDS instance is rds-original with Enterprise edition. The target instance is rds-new with Standard edition.

1.    Stop all incoming traffic (stop application) to the source instance rds-original.

2.    Follow any of the preceding steps or options for upgrading or downgrading the SQL Server edition on the RDS Instance. After the edition successfully changes, there are two instances: the source instance is rds-original and the target instance is rds-new.

3.    Modify the source instance to rename the DB identifier from rds-original to a different name, such as rds-original-old.

4.    After the instance rds-original-old is in the Available state, rename the target instance DB Identifier from rds-new to the name of original instance, rds-original.

5.    Verify that the instances are renamed to rds-original-old and rds-original and are in the Available state.

6.    Make sure to keep the related RDS security groups that are attached to the new edition target RDS instance the same as the source instance. This makes sure that network connectivity from the existing applications remains the same.

7.    Allow incoming traffic (start application) now to the instance rds-original that has the required SQL Server edition. No changes are required for the application connection strings , since RDS has the same DNS endpoint as source instance.

8.    Perform the application testing to make sure that there is no impact after RDS instance edition change.

9.    If everything works, create a final snapshot of the instance rds-original-old, and then delete this instance to save on costs.

Note: It's a best practice to test activities first in a lower environment before implementing on the production environment. This gives you an estimate of how much time the changes take. Also, you can identify any issues that occur during the activity to help make implementation in the production environment smoother.

---

Related information

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