CODEWITHSUNDEEP
azure-sql-databaseazure-data-factoryservicenow
Jeff
Jeff

Reputation: 1

Export ServiceNow data into SQL Database Using Azure Data Factory

I currently have a pipeline set up using the native ServiceNow connector as my source dataset. I'm using a simple query to pull records from the sc_req_item table. I have an Azure SQL database set up as my sink dataset. When I execute the pipeline, it works and copies over around 107k records into my Azure database, but it takes over 10 hours to run. Is there any way to increase the performance of this? I don't think it should take this long to run. Has anyone achieved implementing something like this in an alternative way?

I created pipeline using the native ServiceNow connector as my source dataset with a simple SQL query to pull records from the sc_req_item table. I created an Azure SQL database and set it up as my sink dataset. The pipeline runs but takes 10+ hours to finish. I was expecting this to be a lot shorter for 100k record or even more.

Upvotes: 0

Views: 705

Answers (1)

Ziya Mert Karakas
Ziya Mert Karakas

Reputation: 891

Depending on what performance units you have on ADF, this can either be due to not enough compute on ADF, not enough compute on your sink side, or not enough compute on your source side. It can also be due to network related latency between your components, this is more common with source or sink based on-premises instead of cloud. Speaking from my experience, assuming you are on Azure Integration Runtime, if your DIUs on ADF seems to be ok for the size of the data to be copied, its most likely that your ServiceNow platform is not scaled enough to handle the copy process, but of course its very hard to say with very limited information that you have provided here... If your azure sql seems to be the bottleneck, you can see its DTU usage if its near %100 in the metrics/monitoring section of your database.

Please follow the copy performance guides (in case you are not aware):

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-performance

The idea with troubleshooting copy performance is that you try to find the bottleneck(s) in the whole process by testing and by researching your platforms in depth. You can also see in the details tab of your copy activity, how much time it has spent listing, reading from source, how much time writing to sink. You can also see any throttling errors in this screen. If you can point out your DIU on ADF, and the data size in MBs I can try to tell you if its enough or not. Your Azure SQL tier is good to know as well.

Upvotes: 0

Related Questions