CODEWITHSUNDEEP
azure-sql-database
chi
chi

Reputation: 501

Azure Sql Database Archiving

If a table in an Azure Sql Db is getting too large, is there a suggested pattern to archive the older records in order to reduce the table size?

Upvotes: 4

Views: 6401

Answers (1)

Debra Dove
Debra Dove

Reputation: 126

There are a number of options to archiving older records, depending on how those older records need to be used in future reporting if at all. If the older data is obsolete, a customer can simply truncate table and with the usage of partitions, they can truncate based on the partition values.

Another option is to auto-archive data by exporting the older data into a bacpak and storing it in Azure Storage. In this case, you shrink the amount of data within the database, while keeping access to the old data in a cheaper storage solution, if the data ever needs to be re-loaded and referenced.

Auto-archival can be automated using Elastic Jobs or Azure Automation where you can add the monitoring and archiving logic together into a scheduled job. Here is an example using Azure Automation: https://gallery.technet.microsoft.com/scriptcenter/Azure-Automation-Your-SQL-30f8736b

The same scheduled job can be created using Elastic Jobs (preview), especially if you are dealing with a multi-tenant application and have a large number of databases, each a tenant per DB, and you want a single automated job to run across all DBs to monitor the table size and truncate the data as needed. Here is an example on getting started with Elastic Jobs: https://gallery.technet.microsoft.com/scriptcenter/Automating-management-of-e7c15e2f

Upvotes: 4

Related Questions