Blob Data Loading Into Azure Sql Database - Best Approach

Question

Blob Data Loading Into Azure Sql Database - Best Approach

jase jackson USA 201

Hi

Use Case: daily load of blob mutiple csv data into azure sql database (80+ files / daily size approx 50mb- 80mb)

What are the pros and cons to using either

(a) T-Sql to Bulk Insert the data from the blob csv files into the azure sql tables or
(b) Using ADF Copy Activity to loop through the blob files and load into the azure sql database.

Both appear to offer the same capability.

Particularly interested if there are any key considerations/challenges on best practice, azure costing (is one approach significantly cheaper), any future deprecation, particular technical benefits (parallel processing/speed).

Thanks and happy to claryfy where necessary.

Jase

KalyanChanumolu-MSFT 8,361 Reputation points

2020-08-10T06:52:17.777+00:00

Just checking in to see if the answer(s) helped.
If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.
And If you have further questions or issues please let us know.

2 answers

Your answer

KalyanChanumolu-MSFT 8,361 Reputation points

2020-08-10T06:52:17.777+00:00

Just checking in to see if the answer(s) helped.
If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.
And If you have further questions or issues please let us know.

Answer 1

KalyanChanumolu-MSFT 8,361

@jase jackson USA Thank you for your interest in Azure SQL Database and Azure Data Factory

As you rightly pointed out, both solutions allow you to load data into Azure SQL database.

From your requirement, it appears that you need to schedule this activity daily.

If you use T-SQL, you will have to depend on an external scheduling solution like an Azure function app or Azure Run Book to run the T-SQL at the scheduled time.

ADF appears to be a more convenient solution because it offers both scheduling and ingestion capabilities. You can also enhance it for better error handling and logging capabilities in future.

Please let us know if you have further questions.

--

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

jase jackson USA 201 Reputation points

2020-08-18T09:19:02.357+00:00

Thanks for the response.

Can I check if I were to call from a sql stored procedure from ADF that performed the import of 100+ files from blob into sql (using openquery) would this a be a cheaper (azure cost) route than performing a "For Each" task in ADF?

Surely the single call to a SP would be cheaper than looping and performing multiple copy activities in ADF? Does Azure charge for each activity and therefore file?

Answer 2

KalyanChanumolu-MSFT 8,361

@jase jackson USA

We cannot really answer this question without looking at your files, the logic in your stored procedure to read and load the files into tables etc.
If you need the exact DTUs the operation consumes, I would recommend you enable Query performance Insights on your database and run some tests.

If you have too many records in your files you might run into SQL Azure throttling issues if your database is not adequately scaled. Please test before you go to production.

If you have a VM already running on Azure, you could schedule the execution of the stored procedure from the VM using PowerShell and not use ADF altogether.

ADF is not very expensive. Please look at this example to get an overview.

If it is within your budget, I would still recommend using ADF because you get better control of the execution, ability to resume from last failed run, better logging etc.

Please let us know if you have further questions.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

jase jackson USA 201 Reputation points

2020-08-18T16:47:42.627+00:00

Thanks for the response. I'll do some testing with SQL pulling the files to understand potential costs however you're quite right ADF offers much in capabilities.

The reason I'm hesitant to date is I'm slightly surprised at the cost of importing a 60mb file from on-prem into SQL via Blob. Processed the file a few times during the day and incurred a charge of approx $5. If this were real world we would be importing significantly more than this daily so could therefore see the costs increasing higher just to land the data into SQL database. This is before any further processing and the visualization layer.

Appreciate we would have a lower price in production than simple PAYG I'm testing due to a 1 or 3 year agreement however costs are now transparent so the correct approach needed from the start.

Thanks
KalyanChanumolu-MSFT 8,361 Reputation points

2020-08-18T17:09:16.19+00:00

Sure, this is an interesting conversation.
Please post your findings after you are done testing.
They could be very useful to the rest of the community members.
jase jackson USA 201 Reputation points

2021-01-24T16:32:49.527+00:00

We have found using T-SQL and bulk copy to be a faster and cheaper option than using ADF copy activity where there are a high number of files (i.e. 200+) to be loaded.

Share via

Blob Data Loading Into Azure Sql Database - Best Approach

2 answers

Your answer