Alternative to using Azure SQL Managed Instance for Complex calculations

Kman 41

I have inherited an Architecture which carries out complex calculation in Azure SQL Managed Instance via Databricks. The Databricks is connected via Apache Spark connector. The Databricks does the calculations in SQL and the end results are in Tables and Views in SQL. This process takes hours.

I just wanted to know if there is another efficient way to do this?

I would have preferred to get this done in Databricks but is this the best way in terms of cost? I have heard carrying out complex calculations in .NET is more optimal but is it?

I am happy to use any other Azure Resources to carry out the calculation providing its is faster.

Any help or ideas would be helpful.

MartinJaffer-MSFT 26,051 Reputation points

2020-09-02T23:53:55.167+00:00

Hello KaisMalique-9406 and thank you for your question. Before I can try to answer your question, there are a few points I would like clarified.

Are you saying, in your architecture, Databricks is owning the data, but then outsources the compute to a SQL managed instance?
Or did I get that backwards, and the SQL managed instance owns the data, but outsources the compute to Databricks?

I would also like to know two more things:
How much data is it? The size of your data can dictate which services make more sense. There are some services where the compute time increases slowly with data size, but it has a very large base time due to overhead. Other services have the opposite where they are fast to spin up, but volume rapidly slows them down.

What makes the calculations complex? Are they difficult to implement in, say SQL due to the logic involved, so you use a service with a different language? Or is it more about the mathematical calculations?

Thank you,
Martin
Kman 41 Reputation points

2020-09-03T09:49:26.643+00:00
Hi @MartinJaffer-MSFT .

The data resides in Azure SQL Managed Instance in views and tables but the compute is outsourced to Databricks once its done the output goes back into Azure SQL Managed Instance in tables and views. As shown below.

The Architecture is:

ADLS -> Azure Managed Instance SQL -> Databricks -> Azure Managed Instance SQL

The Size of data it processes is 500GB which includes historical data and new data.

The Calculation is complex its full of complex algorithm (This element can be done in Machine Learning).

Please let me know if you need further information.

Thank You

1 answer

MartinJaffer-MSFT 26,051 Reputation points

2020-09-04T02:12:28.973+00:00

Part of the pain is all the data movement. I see your data comes from ADLS originally. Does the Managed Instance add value for you?

There are a number of services to consider. Chief among them is Azure Synapse (Workspace). Everything big data seems to be converging in Azure Synapse. This includes Spark and Machine Learning. The linked document says it better than I can. It may be possible to replace both your Managed SQL and Databricks.

Azure Synapse is still new, and there are so many options, such as on-demand SQL and pausing pools, it is hard to say what the optimal solution is. However the more I think about it, the more I am convinced Azure Synapse is something you should take a close look at.

If your ADLS is Gen2, as opposed to Gen1, then you can ingest directly from the Storage Account instead of going thru the Managed Instance.
Please sign in to rate this answer.
Kman 41 Reputation points

2020-09-04T09:00:42.13+00:00

Hi @MartinJaffer-MSFT . Managed Instance is not working for us its too slow with Databricks as outsource compute and I don't think its good for complex calculations. I am looking into Synapse as you say its new.

As an option would Databricks be prefect to do all the calculations and store the data within Databricks. This means we will no longer use SQL but Databricks to store the data and complex calculation output?

As a temporary measure one option we are looking at is to export all the SQL tables and views into ADLS Gen 1 as Parquet files and then use Databricks to perform the calculations and the output will be in ADLS in parquet format. The reason for this approach is because Databricks as an outsource compute engine with Azure SQL Managed Instance where the data resides, running calculation is not fast enough.

MartinJaffer-MSFT 26,051 Reputation points

2020-09-04T19:07:20.343+00:00

Moving completely into Databricks, and out of the Managed Instance is a good option, but I question using ADLS Gen 1, when you could use ADLS Gen 2. Gen 1 and Gen 2 are very different products.

Azure Data Lake Analytics, is a service which works specifically with ADLS Gen 1, and not Gen 2 or other stores. ADLA is a bit old, but you can write custom C# code for it. If you are dedicated to ADLS Gen 1, it is worth knowing about ADLA due to its tight integration with ADLS Gen 1.

The cadence of your workload will also play a part in how much you benefit. If your workload is always on, running jobs all the time, I might suggest HDInsights. If your workload is only a few times a day, I would recommend against HDInsights, due to it being "always on" and requiring more work in cluster management. Databricks manages the clusters for you and can be turned off. Personally I find Databricks an order of magnitude easier than HDinsight.

MartinJaffer-MSFT 26,051 Reputation points

2020-09-04T19:15:15.15+00:00

When you move all the data to Databricks, be careful about storing it directly in DBFS. I recommend instead mounting your storage. This way when you write to the mount, it is written back to your non-volatile storage. Keep the original, raw data in your (mounted) ADLS, and keep the mutable working sets in the local DBFS. This should put the items you frequently write to close to the compute, while keeping the cold storage safe.

Kman 41 Reputation points

2020-09-07T08:44:43.737+00:00

@MartinJaffer-MSFT Thanks for your input your correct to question why we use Gen 1. In the new Architecture I will be proposing Gen 2 with Databricks.

Is there anything else you think I should consider?

MartinJaffer-MSFT 26,051 Reputation points

2020-09-08T20:49:40.35+00:00

I think you should consider Azure Key Vault backed Secret Scopes and Version Control integration. This takes credentials out of your code and keeps them in Key Vault so even if disaster strikes in the form of an intern deleting your Databricks workspace, the passwords are safe in the vault, and the code is safe in a repository and the data is safe in storage. You can rebuild. An external metastore makes rebuilding even faster, as this stores table information outside Databricks.
Or you can realize permissions in AAD are separate from permissions in Databricks. The intern need not have write permissions on resources to work in Databricks.

This scenario is fictional.

MartinJaffer-MSFT 26,051 Reputation points

2020-09-10T18:49:35.49+00:00

@KaisMalique-9406 I will now convert this conversation from comment to answer. If I answered your inquiries satisfactorily, please consider marking it as answered, and upvoting and particularly helpful posts.

If I did not answer your question, please let me know what I can do to better assist.

Thank you,
Martin

MartinJaffer-MSFT 26,051 Reputation points

2020-09-16T19:45:52.69+00:00

@KaisMalique-9406 Did you find your own solution? If so kindly share it here to help the community. Thank you!
Sign in to comment

Share via

Alternative to using Azure SQL Managed Instance for Complex calculations

1 answer