SQLToSQLADF

Azure SQL to SQL Pipeline

This is a sample ADF and SQL based generic solution template that can be used to copy incremental data from one SQL server database to another. Solution uses SQL ChangeTracking feature on the source database to identify incremental changes and export incremental changes to destination database and merge it.

Deployment Steps

Setup Source and Destination Database

1. Identify Source and Destination database
2. Connect to source database and execute Source SQL Script
3. Connect to destination database and execute Destination SQL Script

Create and deploy Azure Data Factory Template

1. Create a Azure Data Factory Resource in Azure Subscription
2. Note down Source and Destination Database connection string
3. Download the ARM template file to your local directory.
4. Click [Template deployment] https://ms.portal.azure.com/#create/Microsoft.Template
5. Click build your own template in the editor option
6. Click load file and locate the ARM template file TemplateForDataFactory.json and click Save.
7. Provide required parameters and review + create.

Execute pipeline

1. Open Azure Data Factory Resouce and Click Author and Monitor
2. Click Manage> Linked Services and validate Source and Destination database link services.
3. Click Author and Execute FULL_EXPORT_SQL_TO_SQL, Provide table name as parameter
4. FULL_EXPORT_SCHEDULE pipeline can be used to add multiple tables for export
5. Montitor the pipeline execution
6. Execute INCREMENTAL_SQL_TO_SQL manually or via schedule trigger to execute incremental data copy from source to destination