Data ETL with Azure or on-prem SQL Server
I've got a potential new client who wants me to perform some ETL operations on a set tables (SCADA data) which is in the format:
EntityID FieldName FieldValue ReportTime
I've worked with this particular data provider before and getting the data from that table structure into several relational tables involves some serious translation operations. The client wants to extract data from this data table every 20 minutes for some data fields, and once a day for others.
Eventually, we will also be creating a FE to link to the Azure database for limited data entry, modification, and reporting.
Having only minimal experience with Azure and the billing model, I need to know:
1. Can you create linked servers in Azure? so that I can easily extract data from this offsite data source?
2. Would it make more sense, giving the Azure billing scheme (which I don't understand at all) to use an on-prem SQL Server to perform the ETL operations and then push it to Azure?
EntityID FieldName FieldValue ReportTime
I've worked with this particular data provider before and getting the data from that table structure into several relational tables involves some serious translation operations. The client wants to extract data from this data table every 20 minutes for some data fields, and once a day for others.
Eventually, we will also be creating a FE to link to the Azure database for limited data entry, modification, and reporting.
Having only minimal experience with Azure and the billing model, I need to know:
1. Can you create linked servers in Azure? so that I can easily extract data from this offsite data source?
2. Would it make more sense, giving the Azure billing scheme (which I don't understand at all) to use an on-prem SQL Server to perform the ETL operations and then push it to Azure?
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Currently Azure doesn't allow creating the linked servers. As a workaround, you can use the Elastic Query.
You can use Azure data factory to perform the push operations in Azure. Which tool are you using in the on-premises for ETL operations?
I will not recommend maintaining two servers, one on IaaS and one on PaaS. Instead, you can go for PaaS.
If the client demands the data extraction every 20 minutes, Azure Data Factory (ADF) enables you to set triggers (in ADF v2) and schedules (in ADF v1).
Regards,
Pratik