Data Archival strategy in Azure SQL Database

Question

hello

We have Azure SQL database , we need build data archival solution to manage data more than 2 years old.

Requirement:

1) Archive and delete more than 2 years data from certain transaction tables.
2) Should archive the data in low cost storage.
3) Should be able to quickly restore the data if required.

Please recommend an azure solution to achieve this.

Answer 1

Hey,
Please find below the responses:

1) Archive and delete more than 2 years data from certain transaction tables.

Ans: Assuming you have audit columns/ period column identifying the timeline of the data, if yes, then you can write a stored procedure within your database to delete the data and trigger that stored procedure via ADF on a monthly or adhoc scheduled basis

2) Should archive the data in low cost storage.

Ans : Do you want to archive the entire database or a specific table or the data that is being deleted in #1
If entire database, then you can create a BACPAC else you can use ADF to create a file of that deleted data or table into Azure blob storage and make the container as cool/archive for minimal cost

3) In case if the data needs to be restored quickly, update the container from cool/archieve to hot and use ADF to copy from that file into table/database

Additional details would be helpful