Data Migration - From Cosmos DB to Azure SQL Database through Azure Data Factory

Question

Requirement - We have to migrate a large cosmos DB (approx. 4GB) data to Azure SQL. This migration involves 2 parts.

1. Migrating complete existing data to Azure SQL.
2. Migrating any delta changes to Azure SQL.

Trials and attempts - Based upon our study and analysis we learn that using azure data factory we can migrate data from cosmos DB to azure SQL. However, the diverse document structures in the cosmos DB are huge. it is not practically possible to manually create required azure SQL database table as per document structure.

Advise required - We need to know how this migration (from Cosmos Db to Azure SQL) can be done automatically without the need of manually creating tables in azure SQL.

Any links pointers will be appreciated. Kindly let know if any further details required.

Answer 1

Unfortunately, when Azure SQL Database as sink, it's table can't be empty. And Azure Data Factory doesn't support creating Azure SQL table within Copy activity.

Answer 2

Please use Cosmos DB Synapse Link, refer https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/query-cosmos-db-analytical-store?tabs=openrowset-key#explore-azure-cosmos-db-data-with-automatic-schema-inference It does have capability to infer the schema automatically. After that you can store you dataframe to SQL Server

You can look for changefeed via https://docs.microsoft.com/en-us/azure/synapse-analytics/synapse-link/how-to-query-analytical-store-spark#load-streaming-dataframe-from-azure-cosmos-db-container and write onto data frame which will help you solve your use case to source delta