SQL Saved Process vs. Azure Information Manufacturing unit Information Circulate

Introduction

On this article, we’ll discover two widespread strategies for creating staging tables and migrating knowledge to Dynamics 365 Buyer Engagement (D365): utilizing SQL Saved Procedures and Azure Information Manufacturing unit (ADF) Information Flows**. This comparability highlights the professionals and cons of every strategy, specializing in execution time, value, and suitability for knowledge migration initiatives.

State of affairs: Migrating D365 Case Information

We’re tasked with migrating 5 check Dynamics 365 (D365) instances (incident entity) from a Supply SQL database. Key attributes of D365 Case data embody lookups and optionsets, which must be resolved earlier than migration.

• Lookups: Retrieve underlying D365 report distinctive identifiers (GUIDs).
• Optionsets: Map D365 optionset integer values.

As soon as these references are obtained, we are able to construct a staging desk to organize the info for migration.

Resolving References and Creating Staging Desk

Earlier than migrating data to D365, we have to create a staging desk by becoming a member of the supply desk with numerous D365 reference tables. Two widespread strategies to realize this.

• SQL Saved Process: Use a saved process to create the staging desk within the SQL database.
  
• ADF Information Circulate: Use ADF’s no-code knowledge circulation transformations to carry out the identical job.

Possibility 1. Utilizing SQL Saved Process

• Write and execute a SQL Saved Process to affix the supply desk with reference tables.
• Name the saved process by way of the ADF Saved Process Exercise to generate the staging desk.
• Use the Copy Exercise in ADF emigrate knowledge from the staging desk to D365.
  

Possibility 2. Utilizing ADF Information Circulate

• Construct the staging desk utilizing ADF Information Circulate, performing a collection of left joins between the supply desk and D365 reference tables.
• Add a column to deal with multi-entity lookups (e.g., `CustomerEntityReferenceType`).
• Use a Sink Activity to load the remodeled dataset into the SQL staging desk.
• Execute the Information Circulate in ADF by way of the Information Circulate Exercise within the pipeline.
  
  
  

Efficiency Comparability: Execution Time

• SQL Saved Process: Your complete pipeline took 22 seconds to finish, with the saved process itself taking solely 2 seconds.
• ADF Information Circulate: The pipeline utilizing ADF Information Circulate took virtually 6 minutes to finish, with the info circulation exercise itself taking 5 minutes 36 seconds.
• Conclusion: The saved process considerably outperforms ADF Information Circulate by way of execution time, making it a extra environment friendly choice for large-scale knowledge migrations.

Value Comparability: ADF Pricing

Let’s study the price of operating these pipelines primarily based on Azure pricing (as of July 2020).

• SQL Saved Process: Complete value is **£0.009574 for five data.
• ADF Information Circulate: Complete value is £0.6523 for a similar 5 data.
• Conclusion: ADF Information Circulate will not be solely slower but in addition far more costly in comparison with the SQL Saved Process methodology.

Key Takeaways

• Execution Time: SQL Saved Procedures are considerably sooner, particularly for advanced queries with a number of joins.
• Value Effectivity: Utilizing SQL Saved Procedures is way more cost effective, notably for large-scale migrations the place the distinction in execution time and price turns into extra pronounced.
• Ease of Use: ADF Information Circulate presents a no-code resolution, however the added comfort comes at a value by way of each time and price.
• Scalability: For small datasets or fast transformations, ADF Information Circulate could be ample. Nevertheless, for giant knowledge migrations, SQL Saved Procedures stay the popular choice on account of their pace and decrease value.

Conclusion

Whereas ADF Information Circulate presents a no-code strategy to knowledge transformation, the execution time and related prices make SQL Saved Procedures a greater choice for many knowledge migration duties, particularly when coping with giant volumes of knowledge in Dynamics 365 initiatives.