Efficiently Archiving Transactional Data in RDS MSSQL to S3

Here are a few recommendations for automating the archival of historical transaction data from your Amazon RDS SQL Server database to Amazon S3: [1]

• Use AWS Database Migration Service (DMS) to set up a task that runs periodically (e.g. weekly) to migrate data older than 3 months from your RDS instance to an S3 bucket. DMS supports homogeneous migrations between SQL Server and S3.
• Alternatively, write a stored procedure in your RDS database that identifies and exports old data to S3. You can trigger the procedure with an AWS Lambda function scheduled through CloudWatch Events.
• For security, ensure your Lambda function/DMS task uses an IAM role with minimum permissions to access just the relevant RDS and S3 resources. Also consider enabling encryption in transit and at rest for data in S3.
• For cost optimization, configure S3 lifecycle rules to transition archived data to Amazon S3 Glacier after a period of time for low cost archival storage.
• Monitor costs using S3 Intelligent-Tiering which will automatically move data between access tiers based on usage patterns.

Let me know if you have any other questions! Proper data lifecycle management can help optimize both costs and performance for your analytics workloads.

Sources:

[1] Reduce data archiving costs for compliance by automating Amazon RDS snapshot exports to Amazon S3