If you ask a random Microsoft partner about their worries with Business Central there is a fair chance “Performance” is in their top three.
It probably depends if this partner has a background with NAV. If this is the case it’s a guarantee it’s in the list.
As long as I remember I’ve been doing some form of performance tuning. First with the old Native Navision database, later with SQL Server and now on Azure SQL.
It’s not really a surprise that when I joined QBS a few weeks ago it was just a matter of time before the first performance cases started rolling my way.
Performance Tuning in the cloud is a different animal, that’s for sure, but the essence of the job does not change. Get as much telemetry as you can and deduct root cause from this.
If you have a history with NAV that goes back more than ten years you’ll probably remember that the first versions were not exactly efficiently leveraging SQL Server.
This is probably the reason why a lot of legacy NAV partners are affraid of the cloud because they think they loose control over what they perceive as a monster that they had just contained.
Fortunately this is not correct and Business Central in basic has a very good performance in the cloud and Microsoft offers a lot of ways to get telemetry.
The teams in Lyngby and Fargo have done a tremendous job in the last years to bridge the gap between Business Central and Azure SQL and this is helped by the fact that C/Side is no longer there and is not holding the team back when it comes to innovation.
Also Azure SQL has come a long way and offers insights that back in the days we could only dream of.
I got a lot of help from Kennie Pontoppidan in the last period where he guided me through all the documentation and sample dashboards that Microsoft has published and in exchange I promissed him to write a blog about what I’ve learned.
Because it is simply too much to put into one blog it will be a series covering different topics and while writing the series I will probably learn even more along the way.
I’ve always found that writing blogs helps organising my thoughts while documenting your learnings. It’s quite frequent that I end up on my own blog via Google if I run into a problem I have solved before in the past.
This entire series will be about Business Central hosted in the cloud running on SQL Azure, either controlled by Microsoft (BC Saas) or on an instance you host yourself.
The latter would not be something I’d recommend unless you have a valid excuse. Setting up an environment on Azure for Business Central is not exactly super easy and then you don’t get things like CDS intergration for free.
Running on Azure essentially elliminates the ever lasting discussion about how the hardware is setup and how the virtualisation layer (most often VMWare) is configured.
This was always the biggest nightmare in the past.
Azure makes things a lot easier and it’s even easier if you just select the “Hosted by Microsoft” option and don’t have to worry about it.
NOTE: Microsoft calls everything that is not managed by them “OnPrem”. Even if you run on your own Azure SQL database. This can be confusing.
Performance tuning is about telemetry and understanding the application. While the latter takes time, the telemetry is now easily available for everyone.
We’ll spend a lot of time in the blog series on what you can do with Azure Application Insights and how to set it up with examples and best practices.
I’ll also spend some time on showing what you can get out of the Query Store that you get with every Azure SQL Database. Currently this is only for instances that you host yourself but who knows what we will get in the future.
There is a lot of information already out there and I’ll probably not cover anything new in my blog other than the pitfalls I ran into, which were quite a few so I expect things to be worth the time and effort.
Using SQL Query Store
Blog by Dmitry Katson
Blog by Stefano Demiliani