Migrating AWS RDS(MSSQL) From Standard Edition to Web Edition

You can't perform a RDS MSSQL Server edition change as an in-place modification. To downgrade from Standard edition to Web edition, use one of these methods. For all of these methods please spin up a new web edition of SQL instance in parallel to the existing Standard edition. • 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.

Option 1: Use the 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. Store the backups on Amazon Simple Storage Service (Amazon S3) and then restore the backup files onto a new target RDS instance. This is a straight forward and comprehensive approach and is preferred whenever your application permits limited downtime required while backing up and restoring the databases.

1. Create a new RDS for SQL Server with Web edition SQL Server. This is the new target instance. [+]. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateDBInstance.html#USER_CreateDBInstance.Creating
2. Add the native backup and restore option on the source Standard and target Web edition instances. [+]. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.BackupRestore.html
3. Back up each user database on the source instance to an S3 bucket. [+]. https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/
4. Restore the backups from the S3 bucket to the target (Web) RDS instance. [+]. https://aws.amazon.com/premiumsupport/knowledge-center/native-backup-rds-sql-server/#:~:text=Use%20one%20of%20the%20following%20SQL%20statements
5. 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.

Option 2: Use AWS DMS Use AWS DMS to migrate your databases. AWS DMS also replicates ongoing changes from source to target and can be used for migrations which need to be performed with low downtime. For more information, see these topics: • Using a Microsoft SQL Server database as a source for AWS DMS [+]. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html • Using a Microsoft SQL Server database as a target for AWS Database Migration Service [+]. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.SQLServer.html • Limitations on using SQL Server as a source for AWS DMS [+]. https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.Limitations • Migrating your SQL Server database to Amazon RDS for SQL Server using AWS DMS [+]. https://aws.amazon.com/blogs/database/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 [+].https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.Snapshots.html#SQLServer.Procedural.Exporting.SSIEW • Bulk copy: [+]. https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.Snapshots.html#SQLServer.Procedural.Exporting.SSGPSW

Keep in mind that for options 2 and 3, only the data is migrated and secondary objects like indexes, constraints, triggers… need to be moved outside of the data movement steps.