Need recommendations around RDS back up in production account and restore to non-production account

Based on your requirements for moving from on-premises to AWS RDS for SQL Server, here are recommendations for your backup and restore procedures across accounts:

For your production environment:

1. Enable automated backups on your RDS SQL Server instance. Amazon RDS performs daily backups during your defined backup window and uploads transaction logs to S3 every 5 minutes. You can configure retention for up to 35 days.

2. For your 15-minute restore point requirement (similar to Delphix functionality), RDS's point-in-time recovery (PITR) capability will be valuable. Since RDS uploads transaction logs every 5 minutes, you can restore to any point in time within your retention period, up to the latest restorable time (typically the last five minutes).

3. Take manual snapshots before and after your nightly batch cycles. These snapshots persist until you explicitly delete them and provide an additional layer of protection for critical processing periods.

For cross-account restoration to non-production environments:

1. Set up a cross-account backup solution using AWS Backup. This involves:

• Creating AWS KMS customer managed keys and backup vaults in both production and non-production accounts
• Creating a backup plan in your production account to schedule the RDS database backups
• Configuring the backup plan to copy backups to your non-production accounts (stage, QA, dev)

2. For SQL Server specifically, you can also leverage native backup and restore functionality:

• Enable the SQLSERVER_BACKUP_RESTORE option on your RDS option group
• Create full or differential backups of your databases and store them in S3
• Restore these backups to your non-production RDS instances

This approach will allow your production support team to access data with minimal lag (meeting your 15-minute requirement) in non-production environments for troubleshooting, while also providing the pre and post cycle backups needed for dev and QA environments.
Sources
Backup and recovery for Amazon RDS - AWS Prescriptive Guidance
Automate cross-account backup of Amazon RDS for Oracle including database parameter groups, option groups and security groups | AWS Database Blog
Support for native backup and restore in SQL Server - Amazon Relational Database Service
Backing up and restoring your Amazon RDS DB instance - Amazon Relational Database Service
Community | Architecting for Zero Data Loss Disaster Recovery using Amazon RDS Solutions

Migrating your critical SQL Server application to AWS RDS requires a shift in thinking from traditional on-prem tools like Delphix, but you can absolutely achieve and even exceed your required restore flexibility using native AWS features. The key is to combine two core RDS capabilities: Point-in-Time Restore (PITR) for your production support team's high-frequency needs, and Manual Snapshots for your Dev/QA batch cycle requirements.

To address your first and most critical requirement providing the support team with a database copy with a maximum 15-minute lag, you will rely on RDS's automated backup and Point-in-Time Restore functionality. When you enable automated backups on your Multi-AZ production instance, RDS continuously archives your transaction logs to S3. This gives you a Recovery Point Objective (RPO) of approximately 5 minutes, easily meeting your 15-minute target. For troubleshooting, your support team can initiate a PITR to any specific second within your backup retention period (up to 35 days). It is crucial to understand that while your restore point is available within minutes, the restore time (RTO) for large databases like your 1.2TB instance will be longer than 15 minutes, as RDS must create and hydrate an entirely new instance. The process would be to restore a PITR copy of the production database directly into your non-production AWS account as a new, temporary RDS instance whenever troubleshooting is needed.

For your second requirement providing Dev and QA with pre- and post-batch cycle copies the ideal solution is using RDS Manual Snapshots. You can easily automate this process by using an AWS Lambda function triggered by Amazon EventBridge to create a named manual snapshot of your production instance immediately before your nightly batch cycle begins and another one immediately after it finishes. These snapshots are stable, point-in-time images of your database. You can then share these manual snapshots with your other AWS accounts (dev, qa, stage). Your development and QA teams can then restore a new RDS instance from the appropriate pre- or post-batch snapshot whenever they need it, giving them a perfect, isolated environment for their work. This automated snapshot strategy fully covers the needs of your development lifecycle and is a clean, cost-effective way to manage predictable restore points.

Thanks Avinash, for clarifying your requirement.

I understand that you want to take DB level backup and not instance level. While you tried DB level backup, timing of backup is your main concern. I would like to suggest few parameters to improve backup. Kindly retry one more time with below parameters and see if this help to optimize the backup timing.

=> Key parameter optimization:

- Use maximum @number_of_files=10 for parallel processing
- Set @max_transfer_size to 4194304 (4MB) for larger chunks
- Use larger @block_size=65536 for better I/O performance
- Increase @buffer_count for more I/O buffers

You can refer below document for more details on same and also go through limitations, to be more aware about all use cases and parameter limitations.

[+] https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/SQLServer.Procedural.Importing.Native.Using.html#SQLServer.Procedural.Importing.Native.Using.Backup

Hope it helps and if it does, I would appreciate if answer can be accepted so that community can benefit for clarity when searching for similar enquiries in repost/aws guide.