Azure ADLS Gen2

Question

Hi All,

I need to know is it possible to replicate data from On-Premises SQL SERVER 2022 to ADLS Gen2 Storage directly. If possible, please provide me some links or give some guidance.

Not to use: CDC, Triggers, Third Party tools for ETL

Note: I need real-time data.

Right or wrong, I would appreciate your inputs

Regards,

Vignesh K

DBA

Answer 1

Hi,

No, u can’t replicate SQL Server 2022 to ADLS Gen2 directly in real time without CDC, Triggers, or ETL tools.

Best option is use Azure Data Factory (ADF) with Copy Activity or Synapse Pipelines. These aren’t "third-party tools," they’re Azure-native.

If u really want real-time, set up Azure Stream Analytics with a SQL Server Change Tracking-based approach.

Or just rethink ur requirements, because real-time without proper tools is not happening.

Just in cases simple expalanations + docs according urs issue.

Azure Data Factory (ADF) with Self-hosted Integration Runtime (SHIR): ADF allows you to create pipelines that move data from your on-premises SQL Server to ADLS Gen2. By deploying a SHIR on your local network, ADF can securely access your on-premises data sources. While ADF typically operates in batch mode, you can schedule frequent pipeline runs to simulate near real-time data replication.

Copy data from on-premises SQL Server to Azure Data Lake Storage Gen2 using Azure Data Factory

Azure SQL Data Sync: This service enables bi-directional data synchronization between your on-premises SQL Server and Azure SQL Database. By syncing your on-premises data to an Azure SQL Database, you can then use ADF or Azure Databricks to move the data from the Azure SQL Database to ADLS Gen2.

Sync data between on-premises and cloud databases using Azure SQL Data Sync

Transactional Replication to Azure SQL Managed Instance: Set up transactional replication from your on-premises SQL Server to an Azure SQL Managed Instance. Once the data is in Azure SQL Managed Instance, use ADF or Azure Databricks to transfer the data to ADLS Gen2.

Transactional replication to Azure SQL Managed Instance

Please note that achieving true real-time replication without CDC, triggers, or third-party tools is challenging. The solutions above can approach near real-time replication, but there may be slight delays depending on the configuration and network latency.

Please click Accept Answer and upvote if the above was helpful.

rgds,

Alex