copydata - from on premises to azure SQL. Dealing with fails

[Roo_The_Poo]

Hi All.

I'm currently trying to build a azure cloud sql database from various on prem sql databases.

So far what I have done is ive created upsert pipeline for the smaller tables.  these are generally fine.

Then split the larger table into static (data before the beginning of the year) and Live (the more recent data)

With the live - I'm using the same upsert data which is also generally working.  However with the static bulk pipelines I'm running into problems and these constantly fail.  usually timing out after 5-6 hours.  I've tried both the insert and the upsert methods to try and build this data.

For large copydata transactions, am I using the correct method?  on reading other articles I wouldn't consider my data that big (4-5 million rows with upto 5 columns.  One index column).

If anyone has some pointers how I can avoid these fails I would be most appreciative 

[v-venuppu · Accepted solution]

Hi @Roo_The_Poo ,

Thank you for reaching out to Microsoft Fabric Community.

you're on the right track with the split between static and live data. For the static part that's failing after long runs, here are a few things that might help:

1.Try landing the data first into Azure Blob Storage or Azure Data Lake using a Copy Activity, then load it into Azure SQL from there. This reduces strain on the gateway and network during long transfers.

2. Instead of one big load, break the static data into smaller chunks (e.g., by year, month, or even week depending on size). You can loop through partitions with a parameterized pipeline.

3.Make sure your Azure SQL DB has enough DTUs/vCores for the load, and indexes are optimized. Disable constraints/indexes during bulk load if possible, then rebuild after.

4.If you’re hitting timeouts, check integration runtime settings and increase timeout where possible, especially for copy activity.

5. If you're hitting limits with Copy Data, try using Data Flows which can handle transformations and retries more gracefully.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.