CODEWITHSUNDEEP
azure-sql-database
ALAL
ALAL

Reputation: 57

Azure SQL database data archive solution

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.
  4. Looking for recurring job need to execute on every week.

Please recommend an azure solution to achieve this.

Upvotes: 5

Views: 4130

Answers (1)

Utkarsh Pal
Utkarsh Pal

Reputation: 4544

Here are the approaches you can try:

  1. If you want to archive the complete database, you can Export a bacpac directly from Azure Portal. This bacpac file will be stored in existing Azure Storage account. Once done, you can delete the data from database. Refer Export to a BACPAC file - Azure SQL Database.

enter image description here

If you need to only archive 2 years of data, you can create a Stored Procedure for each table in your database. You can run that SP using Stored Procedure activity in Azure Data Factory.

  1. The cheapest (free) option to store archive data is by creating bacpac file and store it in local machine. Or else you can use Blob Storage cold storage service for archiving data.

  2. To restore data from bacpac, just simply import the bacpac file in your database. To restore from Blob Storage, update the container from cool to hot and use ADF to copy from that file into destination database.

  3. If you are using SP activity in ADF as mentioned in point 1, you can trigger the ADF pipeline to run your SP weekly/monthly or whenever as per your requirement. Refer Schedule Trigger in ADF.

Upvotes: 4

Related Questions