Cloud-Service-Broker - Restoring a backup in Amazon RDS for MSSQL using native method

Products

• VMware Tanzu Application Service

Issue/Introduction

There are two main methods to complete backup and restore in AWS RDS instance using snapshots or using native method MSSQL procedures

Using the fist method is well documented and can be used, however in certain conditions the native backup and restore method have to be used.

More details on https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.Native.Using.html

 

Environment

EAR on AWS

Cloud-Service-Broker 

Cause

Cloud Service Broker provisions Amazon RDS MSSQL instances and Amazon S3 buckets as completely independent services.

CSB's MSSQL Terraform creates the RDS instance but does not create an IAM Role for S3 access, does not configure an Option Group with backup/restore capabilities, and does not link the two services in any way.

Application binding credentials are intentionally scoped to a single database for security. Native backup and restore from S3 requires a one-time AWS infrastructure setup that CSB does not provision automatically.

 

Resolution

Following procedure can be used to complete the restore to a new RDS service instance:

• Developer side:
  • Creates S3 Bucket via Marketplace (in order to differentiate the level of access required for restore) 
  • Uploads backup to S3 Bucket
  • Creates MSSQL RDS DB via Marketplace with Target DB name 
    
    • cf create-service csb-aws-mssql PLAN_NAME YOUR_SERVICE_NAME -c '{"db_name":"my-database"}'
  • Contacts Ops-Team in regards to DB restore via S3 and provides with name of s3 service-instance-name and mssql service-instance-name
• Ops-Team:
  
  • Create the Option Group
  • Create IAM Policy with only access to the newly created s3 bucket
  • Create IAM Role and attach IAM Policy
  • Add SQLSERVER_BACKUP_RESTORE to the Option Group
  • Attach the Option Group to the MSSQL RDS Instance
  • Obtain Master/Admin Credentials (Path B)
    • cf update-service YOUR_SERVICE_NAME -c '{"use_managed_admin_password": true, "rotate_admin_password_after": 30}' (Run 2x)
  • Amazon RDS cannot restore a backup to a database name that already exists on the instance therefore in case there is any data DROP the database prior restore
  • Execute the Restore
• Developer side:
  
  • Verify

Additional Information

Regarding reading the master password from the CSB broker database: this is not an officially supported operation and is intentionally complex — it requires direct access to the CSB broker's backing PostgreSQL database AND the CSB encryption key which is stored in Ops Manager tile configuration. This is not something a customer or operator should be expected to do in normal circumstances.

The confirmed procedure for the customer's production restore remains using Secrets Manager:

1. Set up the option group + scoped IAM Role 
2. Use  

   cf update-service mssql2 -c '{"use_managed_admin_password": true}'

   ⚠️ Known issue (Broadcom Troubleshooting Docs): This update will fail on the first attempt due to an AWS provider bug. Run the exact same command a second time. It will succeed on the second attempt.

3. Execute the restore with the credentials retrieved from Secrets Manager
4. Recreate service bindings if needed after the restore

For the detailed plan on completing the steps please reach out to Tanzu Support.