Data Transfer in real time between on-prem and cloud

Rajshekar Mani 6 Reputation points
2023-01-22T18:41:59.0333333+00:00

Hello,

i basically need my on-prem MS SQL db to be synced with cloud MS SQL DB at all times.

As of now, i am thinking of using Azure Data factory but i fear it cannot perform near realtime data transfer.

Other option is not use kafka to move data, but not sure of this solution..

Do we have some other mechanisms that can transfer data from on-prem MS SQL DB data to Cloud MS SQL DB in near realtime with no downtime?

Cheers.

Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
1,919 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,542 questions
Azure Data Lake Analytics
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SanthiSwaroopNaikBukke-4908 595 Reputation points
    2023-01-22T18:48:39.7133333+00:00

    Yes, there are several other mechanisms that can be used to transfer data from an on-premise MS SQL DB to a cloud MS SQL DB in near real-time with no downtime. Some examples include:

    1. Data Virtualization: Data Virtualization allows you to create a virtual layer that sits on top of your on-premise and cloud data sources. This layer presents the data as a single, unified data source, allowing for real-time data access and integration.
    2. Change Data Capture (CDC): CDC captures and records all changes made to a specific set of tables in an on-premise MS SQL DB. This data can then be used to replicate the changes to a cloud MS SQL DB in real-time.
    3. Real-time Data Warehousing: Real-time Data Warehousing solutions allow you to collect, store, and analyze data in real-time. This can be used to replicate data from an on-premise MS SQL DB to a cloud MS SQL DB in real-time.
    4. APIs: APIs can be used to transfer data in real-time between an on-premise MS SQL DB and a cloud MS SQL DB. You can use APIs to expose the data in the on-premise MS SQL DB and then use them to consume the data in the cloud MS SQL DB.

    It's important to consider that each of these solutions comes with a different complexity level, costs and also performance trade-offs. It is recommended to evaluate each option based on your specific use case, data volume, and replication needs to determine the best fit for your specific requirements.

  2. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2023-02-15T04:21:30.3566667+00:00

    @Rajshekar Mani : Just to clear when you say "near real time" what is the lag you can accommodate? I am sure if you are using the ADF you will have to use scheduled triggers and the triggers are run every 15 mins . So if you cannot have a lag of 15 mins , you will have to look for other solution . I am just curious. if you got a chnace to look into this doc ( yes it does call out ADF also :) )

    https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql

    Thanks

    HImanshu