Hi,
My company is looking to implement a data integration method. The project has been assigned to me but I’m not a data engineer and so I would like your guidance on the recommendation.
I have the need to ingest several (only twelve at present) 3rd-party data sources into our domain so the data can be reported on. These external data sources are simple RDMS (most likely all to be MS SQL Server) and the volume of data, due to the 3rd-party creating a View for me, is only going to be around 20 columns and 20,000 rows, per data source. It’s all structured data.

My intention is to use Azure Data Factory (ADF) as the integration tool. The reason for this is we are entirely MS cloud-based and I see the ADF as the most suitable (simple, robust, cheap) MS cloud-based integration tool available – although you may inform me otherwise.

I need to decide on the storage to hold the external data. I’ve had very brief experience with Synapse Serverless Pool, as it was the recommended substitute for Data Export Services (DES) (we use Dynamics 365 as our transactional system), which I found limiting in the SQL commands compatibility. Many of the SQL Views I had wrote upon DES weren’t compatible in Synapse – I guess due to Synapse being written in Spark. For this reason, I am reluctant to use Synapse as the data storage. It is for this same reason I am reluctant to use the ADF Storage Account as I believe it is too written in Spark.

Please can you advise on the below questions:
1) Is the ADF Storage Account written in Spark and thus prone to the same incompatibility as Synapse Serverless Pool is?

2) What are the benefits to using the ADF Storage Account over Azure SQL Server, and visa versa?

3) I know this question configuration specific but I’ll ask anyway. Which is cheaper based on our basic use-case – ADF Storage Account or Azure SQL Server? I have trouble understanding the online pricing calculators.

4) I understand to execute activities/pipelines between Azure storage sources (ADF Storage Account, Azure SQL Server, etc.. Azure products) a ‘Azure integration runtime’ is needed. I also understand to extract data from an On-Premise SQL Server database a ‘Self-Hosted integration runtime’ is required – is this correct, and where will this ‘Self-Hosted integration runtime’ need to be installed (on the box that is running On-Premise SQL Server?)?

I think that’s all my questions for now. Thanks for your help.

​Hi,My company is looking to implement a data integration method. The project has been assigned to me but I’m not a data engineer and so I would like your guidance on the recommendation.I have the need to ingest several (only twelve at present) 3rd-party data sources into our domain so the data can be reported on. These external data sources are simple RDMS (most likely all to be MS SQL Server) and the volume of data, due to the 3rd-party creating a View for me, is only going to be around 20 columns and 20,000 rows, per data source. It’s all structured data.My intention is to use Azure Data Factory (ADF) as the integration tool. The reason for this is we are entirely MS cloud-based and I see the ADF as the most suitable (simple, robust, cheap) MS cloud-based integration tool available – although you may inform me otherwise.I need to decide on the storage to hold the external data. I’ve had very brief experience with Synapse Serverless Pool, as it was the recommended substitute for Data Export Services (DES) (we use Dynamics 365 as our transactional system), which I found limiting in the SQL commands compatibility. Many of the SQL Views I had wrote upon DES weren’t compatible in Synapse – I guess due to Synapse being written in Spark. For this reason, I am reluctant to use Synapse as the data storage. It is for this same reason I am reluctant to use the ADF Storage Account as I believe it is too written in Spark.Please can you advise on the below questions:1) Is the ADF Storage Account written in Spark and thus prone to the same incompatibility as Synapse Serverless Pool is?2) What are the benefits to using the ADF Storage Account over Azure SQL Server, and visa versa?3) I know this question configuration specific but I’ll ask anyway. Which is cheaper based on our basic use-case – ADF Storage Account or Azure SQL Server? I have trouble understanding the online pricing calculators.4) I understand to execute activities/pipelines between Azure storage sources (ADF Storage Account, Azure SQL Server, etc.. Azure products) a ‘Azure integration runtime’ is needed. I also understand to extract data from an On-Premise SQL Server database a ‘Self-Hosted integration runtime’ is required – is this correct, and where will this ‘Self-Hosted integration runtime’ need to be installed (on the box that is running On-Premise SQL Server?)?I think that’s all my questions for now. Thanks for your help.  Read More

​