Renaming or deleting a replicated RDS DB for SQL Server

aws

If you ever tried to either rename or delete a replicated SQL Server RDS database, you would have noticed AWS does not allow us to perform the tasks.

The error we get when trying to rename a database is:

Renaming RDS SQL Server database error

User does not have permission to alter database 'XYZ', the database does not exist, or the database is not in a state that allows access checks. (5011)

The fix is running a Stored Procedure which AWS gives us access to. The SQL to execute database rename is:

EXEC rdsadmin.dbo.rds_modify_db_name N'XYZ', N'NEW_XYZ'
GO

A similar error is thrown when we try to drop a database:

Deleting RDS SQL Server database error

The database 'XYZ' is enabled for database mirroring. Database mirroring must be removed before you drop the database. (3743)

The issue we encounter this time is that using RDS we do not have access to enable or disable mirroring for a specific database, in a Multi-AZ RDS deployment all databases are always synchronised.

Luckily AWS puts at our disposal another Stored Procedure which will allow us to perform the deletion task.

EXECUTE msdb.dbo.rds_drop_database  N'XYZ'
GO

An error while trying to drop a database will not be seen if the database has not yet been fully replicated.