Restore TDE Encrypted Multi-AZ RDS MS SQL server to self hosted MS SQL server on EC2

To restore a TDE (Transparent Data Encryption) encrypted Multi-AZ RDS MS SQL Server database to a self-hosted MS SQL Server on EC2, you'll need to follow these steps:

1. Back up the TDE certificate from the RDS SQL Server instance:

   • Connect to the RDS instance using SQL Server Management Studio (SSMS).
   • Run a T-SQL command to identify the TDE certificate name for your database.
   • Use the appropriate T-SQL command to back up the certificate.

2. Create an S3 bucket for storing the database backup and transaction logs.

3. Set up an IAM role that allows access to the S3 bucket and create a symmetric AWS KMS key for encryption.

4. Perform a native backup of your RDS database:

   • Use the rds_backup_database stored procedure to create a full backup of your database.
   • Specify the S3 bucket ARN and KMS key ARN in the backup command.

5. Restore the TDE certificate on the EC2 SQL Server instance:

   • Transfer the backed-up certificate to the EC2 instance.
   • Import the certificate into the EC2 SQL Server instance.

6. Copy the database backup from S3 to the EC2 instance.

7. Restore the database on the EC2 SQL Server:

   • Use the RESTORE DATABASE command, specifying the backup file location and the WITH MOVE option to place data and log files in the appropriate locations.

8. If needed, apply any transaction log backups to bring the database up to date.

9. Verify the restored database on the EC2 instance.

Remember that this process involves handling sensitive encryption keys and certificates, so ensure you follow security best practices and have proper authorization. Also, be aware that restoring to a self-hosted environment means you'll be responsible for managing backups, high availability, and other operational aspects that were previously handled by RDS.

This process requires careful planning and execution, especially when dealing with TDE-encrypted databases. It's recommended to test the procedure thoroughly in a non-production environment before attempting it on production data.
Sources
Implementing a fall forward strategy from Amazon RDS for SQL Server Transparent Data Encryption (TDE) and Non-TDE Enabled databases to self-managed SQL Server | AWS Database Blog
Using native backup and restore - Amazon Relational Database Service
Back up and restore transparent data encrypted databases across accounts in Amazon RDS for SQL Server | AWS Database Blog