How to perform Archiving and purging in Azure SQL database

Question

I'm using Azure SQL database as my source database.Which include 10 tables. I need to do archiving and purging in the table in a period gap of one year.Currently I am using Azure data factory to move data from source database to data warehouse.Can I do archiving and purging using data factory.If not please suggest me a right way to perform this Activity.

HI,If my answer is helpful for you, you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. Thank you. — Leon Yue, Sep 11 '19 at 01:04

score 0 · Answer 1 · edited Sep 13 '19 at 05:49

If I understood your question correctly, you need to empty your table based on certain set of logics and criteria in a interval of one year.

What you can do is create your own SQL purge query and create a new pipeline to call it in stored procedure activity. Also, create a scheduled trigger which will run the pipeline once a year automatically say Jan 01, every year.

Every year on Jan 01 your pipeline will be triggered and call that stored procedure and execute it to purge the data.

score 0 · Accepted Answer · edited Oct 13 '19 at 16:30

Can I do archiving and purging using data factory?

Yes, you can. Azure Data Factory support Azure data warehouse. You can followed this tutorial: Transform data by using the SQL Server stored procedure activity in Azure Data Factory.

Firstly, you need to create a stored procedure in your database.

For example, below is the procedure to delete all the table data in database:

IF EXISTS (SELECT * FROM sysobjects WHERE type='P' and name=N'P_DeleteAllData' ) 
    DROP PROCEDURE P_DeleteAllData 
GO 

CREATE PROCEDURE P_DeleteAllData 
AS
    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
    EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
    EXEC sp_MSForEachTable 'DELETE FROM ?'  
    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
    EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL' 
GO

Create a stored procedure activity, choose the Azure data warehouse as the linked service.

Choose the stored procedure in you data warehouse:

Add a trigger to schedule the pipeline execute:

Hope this helps.

How to perform Archiving and purging in Azure SQL database

2 Answers2