question

NagaSri-9719 avatar image
0 Votes"
NagaSri-9719 asked Srinivas9668-0447 commented

Queries on On Premises VS Azure cloud VS Synapse

Hi team,

Have below queries, Please provide some inputs . That would be great help.

  1. How to move on prime database into dedicated SQL pool.

  2. How to move SQL server database into azure SQL database.


Regards,
NagaSri



azure-data-factoryazure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
1 Vote"
PRADEEPCHEEKATLA-MSFT answered Srinivas9668-0447 commented

Hello @NagaSri-9719,

Thanks for the question and using MS Q&A platform.

Before we go into the answer for which you are looking let understand the integration runtimes available in Azure Data Factory/ Synapse Analytics.

Azure Data Factory offers three types of Integration Runtime (IR), and you should choose the type that best serve the data integration capabilities and network environment needs you're looking for. These three types are:

130545-image.png

  1. Azure – Helps to connect data sources in Azure Cloud.

  2. Self-hosted – Helps to connect data sources from on-premise.

  3. Azure-SSIS – Helps to lift and shift existing SSIS workload in Azure

Note: Synapse pipelines currently only support Azure or self-hosted integration runtimes.

For more details, refer to Integration runtime in Azure Data Factory

How to move on-premise database into dedicated SQL pool.

Option1: To get data from on-premise data sources to Azure you can use the SHIR (self-hosted integration runtime).

A self-hosted integration runtime can run copy activities between a cloud data store and a data store in a private network. It also can dispatch transform activities against compute resources in an on-premises network or an Azure virtual network. The installation of a self-hosted integration runtime needs an on-premises machine or a virtual machine inside a private network.

For more details, refer to Copy and transform data in Azure Synapse Analytics by using Azure Data Factory or Synapse pipelines

Option2: You can use Azure Synapse Pathway to migrate from on-premise data warehouse to Synapse dedicated SQL Pool.

For more details, refer to Migrate a data warehouse to a dedicated SQL pool in Azure Synapse Analytics

There is a video which provides more details about Integration with On-Premises Data Sources in Azure Synapse Analytics

How to move SQL server database into azure SQL database.

Option1: To get data from on-premise data sources to Azure you can use the SHIR (self-hosted integration runtime).

For more details, refer to Copy and transform data in Azure SQL Database by using Azure Data Factory or Azure Synapse Analytics

Option2: Migrate SQL Server to Azure SQL Database using DMS

You can use Azure Database Migration Service to migrate the databases from a SQL Server instance to Azure SQL Database.

For more details, refer to Tutorial: Migrate SQL Server to Azure SQL Database using DMS

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130577-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


image.png (697.2 KiB)
image.png (2.6 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @NagaSri-9719,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

0 Votes 0 ·

@PRADEEPCHEEKATLA-MSFT : The above explanation is very helpful so to summarize:
a) Azure IR : only b/w cloud data stores ( like azure sql, adls,storage account) etc
b) Self Hosted IR : b/w public and private n/w.
like copy b/w ADLS and SQL Server which is hosted in Azure Virtual Machine.

From https://docs.microsoft.com/en-us/azure/data-factory/copy-activity-overview#supported-data-stores-and-formats
I see Azure IR supports SQL Server as source but does it mean we can use Azure IR for SQL Server hosted in Azure VM?

0 Votes 0 ·

Hello @Srinivas-9668,

Self-hosted Integration in the option for connecting ADF to SQL Server on Azure VM.

A self-hosted integration runtime can run copy activities between a cloud data store and a data store in a private network, and it can dispatch transform activities against compute resources in an on-premises network or an Azure virtual network. The installation of a self-hosted integration runtime needs on an on-premises machine or a virtual machine (VM) inside a private network.

For more details, refer to my answer on this thread: How to connect with ADF to SQL Server on Azure VM?

Hope this will help. Please let us know if any further queries.

0 Votes 0 ·

@PRADEEPCHEEKATLA-MSFT : Thanks Pradeep, based on analysis have done an experiment using SHIR and is successful, in mean process of improvising it I observed below so trying to understand if SHIR is only option to connect to SQL Server from Azure VM or can we use Azure IR through some private n/w link.

131710-1-untitled.png


0 Votes 0 ·
1-untitled.png (23.6 KiB)

Hello @Srinivas-9668,

If you want to connect with Azure IR - You can use the managed virtual network integration runtime feature in Azure Data Factory to access the on-premises network without installing and configuring a self-hosted integration runtime.

132308-image.png

Hope this will help.

0 Votes 0 ·
image.png (88.0 KiB)

@PRADEEPCHEEKATLA-MSFT : The article very well explains how to connect using Managed Vnet.
But, my scenario is I have SQL Server hosted in Azure VM with private ip's. So does Azure IR will serve the need or SHIR is the only option.

https://docs.microsoft.com/en-us/azure/data-factory/managed-virtual-network-private-endpoint

it says Azure Data Factory supports private links. Private link enables you to access Azure (PaaS) services (such as Azure Storage, Azure Cosmos DB, Azure Synapse Analytics).

Thanks in advance! you support is very helpful to progress on experiments.

0 Votes 0 ·