Archival of Azure DB

Question

Hi Team,

Currently, we have had one Azure DB (Primary+Secondary) running for the past 2 years.

Now we are planning to define an archival database that will have an exact DDL replica of primary/secondary. The idea is to back up all data from the primary database to the archival database which means we need to have all the historical and current data and purge historical data from the primary database based on some logic.

 

We did like to understand the best approach to achieve the following

 

1. Create an archival database with an initial load from primary or secondary
2. Sync the archival database on a daily basis or in real-time from primary or secondary to accommodate all changes (insert, delete, updates) happening in the primary/secondary database except the changes from the purge job
3. Apply the purge criteria in the primary database for each table based on business logic to delete the data but still keep them in the archival database assuming they are already synced as part of daily sync or real-time sync.

Answer 1

Hello, i believe this steps will help with the issue - let me know if it helps:

:

1. Create an Azure SQL Database for the archival database, and use Azure Data Sync to synchronize the data between the primary/secondary database and the archival database. Azure Data Sync is a service that enables you to synchronize data between Azure SQL Databases and on-premises SQL Server databases. You can define sync groups to synchronize specific tables or entire databases. This will help you keep the archival database up-to-date with the primary/secondary database.
2. Migrate the initial data from the primary/secondary database to the archival database using a data migration tool like Azure Database Migration Service (DMS). Azure DMS is a fully managed service that provides a seamless migration experience, and can migrate data from a variety of sources to Azure. This will help you get an exact replica of the primary/secondary database in the archival database.
3. Set up a scheduled or continuous sync job using Azure Data Sync to sync the data changes from the primary/secondary database to the archival database. You can also use Azure Event Grid to trigger a sync job in near real-time whenever a data change occurs in the primary/secondary database. This will help you keep the archival database up-to-date with the latest changes in the primary/secondary database.
4. Define the criteria for data purging based on business logic, and create a stored procedure or a SQL Server Agent job in the primary/secondary database to perform the purge. The stored procedure or job should delete the data that meets the purge criteria, but leave a copy of the deleted data in the archival database. This will help you delete old data from the primary/secondary database while still keeping it in the archival database for future reference.
5. To ensure that the deleted data is still present in the archival database, you can use a database trigger to capture the deleted data and insert it into a separate table in the archival database. The trigger should be defined on the table(s) that are being purged in the primary/secondary database. This will help you track the data changes in the primary/secondary database, and keep a copy of the deleted data in the archival database.
6. To avoid data conflicts, use a timestamp-based approach to track the data changes in the primary/secondary and archival databases. Each table in the primary/secondary and archival databases should have a timestamp column that tracks the last update time for each row. When syncing the data changes from the primary/secondary to the archival database, only the rows that have been updated since the last sync should be synced. This will help you avoid conflicts that may arise when syncing data between the primary/secondary and archival databases.

Answer 2

@Vijay Kumar Thanks for posting your question in the Microsoft Q&A forum.

This can be achieved through many different ways, based on your constraints and use cases.

Below has some of the partners, solution providers & sample solutions to which you can refer.

Backup Storage and Data Archiving | Microsoft Azure

Regards

Geetha