AWS RDS MS SQL Data archiving approaches

There are several approaches you can consider for archiving old data from your RDS MS SQL instances to both another RDS MS SQL instance and S3 storage for access through Athena:

1. Using AWS Database Migration Service (AWS DMS):

   • Set up a DMS task to periodically migrate data older than a specified threshold from your source RDS MS SQL instances to the target RDS MS SQL instance for application access.
   • Configure another DMS task to migrate the same old data to Amazon S3, which can then be queried using Athena.

2. Custom SQL Server stored procedures:

   • Develop stored procedures in your source RDS MS SQL databases to identify and export old data.
   • Use these procedures to transfer data to both the target RDS MS SQL instance and S3.
   • You can schedule these procedures using SQL Server Agent jobs or trigger them using AWS Lambda functions scheduled through Amazon CloudWatch Events.

3. AWS Glue ETL jobs:

   • Create AWS Glue ETL jobs to extract old data from your source RDS MS SQL instances.
   • Configure the jobs to write data to both the target RDS MS SQL instance and S3 in a format suitable for Athena querying (e.g., Parquet).

4. Third-party tools:

   • Utilize third-party data integration or ETL tools that support both SQL Server and S3 as destinations.

For the S3 storage component:

• Store the archived data in compressed Parquet format for efficient querying with Athena.
• Implement a partitioning strategy based on date or other relevant criteria to optimize query performance.
• Use S3 lifecycle policies to transition older data to more cost-effective storage tiers like S3 Glacier for long-term retention.

Security considerations:

• Ensure that all data transfers are encrypted in transit.
• Use IAM roles with least privilege access for any services or functions involved in the archiving process.
• Enable encryption at rest for both the target RDS MS SQL instance and S3 bucket.

Performance and cost optimization:

• Schedule archiving operations during off-peak hours to minimize impact on production workloads.
• Monitor and adjust the frequency of archiving based on data growth patterns.
• Regularly analyze and optimize Athena queries to minimize data scanned and reduce costs.

By implementing these approaches, you can effectively manage your growing RDS MS SQL data by archiving old data to both another RDS instance for application access and S3 for cost-effective long-term storage and analysis through Athena.

Sources
Efficiently Archiving Transactional Data in RDS MSSQL to S3 | AWS re:Post
Turn petabytes of relational database records into a cost-efficient audit trail using Amazon Athena, AWS DMS, Amazon RDS, and Amazon S3 | AWS Database Blog