Setting up Azure SQL Analytics (Preview) – Dynamics NAV

Telemetry is everything, you cannot have enough data when users start asking you why the system is behaving differently than yesterday or performance is changing over time.

This is where Azure SQL stands out from On Premises. You can get so much more data and in an easy way to analyse.

However, you need to know where to find it because not everyting is setup automatically after you create a database. Some is, some is not.

This blog is about how to connect Azure SQL Analytics to your Azure Monitor.

The steps how to do this are described in this docs entry and I don’t want to repeat existing documentation. I will add some screenshots of some results for a 220 GB Microsoft Dynamics NAV database with 80 concurrent users.

Step 1 – Patience!

After you have activated Azure SQL Analytics it will not be visible for a while. It takes time in the background to be generated and but together by the Microsoft Minions who control your tenant in the background. Remember that these Minions have labour contracts and a rights to have a break every now and then.

Step 2 – Azure Monitor & More…

When the Minions are finished the data will show up in Azure Monitor. Search for it in your environment

And then, at least in my case you have to click on More…

This should show a link to your Azure SQL Analysis. In my case with two databases. DEV and PROD.

Step 3 – The Dashboard

The first dashboard you’ll see is something like this, except for the fact that this shows data 24 hours after activation and we had a busy friday with a performance incident. I’ll get back to that.

There are some interesting statistics here already visible like wait stats, deadlocks and autotuning. I’ll handle wait stats in this blog and maybe I’ll get back to deadlocks and autotuning later. There is a “good” reason the autotuning is red and I’ll look at that tomorrow (sunday) when nobody is working on the system.

Step 4 – Drill Down | Database Waits

If we drill down into the Database Waits we see more details on what types of waits we are dealing with here.

It does not help looking at these waits without narrowing down into specific moments in time when “things go wrong” because specific events relate to specific wait stats and some waits are just there whether you like it or not. We all know CXPPACKET because NAV/Business Central fires a lot of simple queries to the Azure SQL engine resulting in CPU time wasted. There is not much you can do about that. (As far as I know).

Step 5 – Houston we have a problem!

It’s 3:51pm on friday afternoon when my teammate sends me a message on Skype that users are complaining about performance. Since we just turned on this great feature I decide to use it and see what goes wrong.

We drill down again one more time and click on the graph showing the waits.

Note that this screenshot was created a day after the incident but it clearly illustrates and confirms that “someting” is off around the time my teammate sent me a message. The wait time on LCK_M_U goes through the roof! We have a blocker in our company.

Hey, this is KQL again!

Now we are in a familiar screen, because this is the same logging that Business Central Application Insights is using. Drilling down into the graph actually generated a KQL query.

Step 6 – What is causing my block?

To see what query is causing my block I have to go back to the Azure Dashboard and click on Blocks like this

From here we have two options. If I click on the database graph I get taken into the KQL editor and if I click on a specific block event I get a more UI like information screen. Let’s click on the latter.

Step 7 – Get the Query Hash

This is where it get’s nerdy. The next screen shows the blocking victim and the blocking process.

It also shows a Query Hash.

This is where I had to use google, but I learned that each “Ad-Hoc” query targetted against SQL Server gets logged internally with a Query Hash.

Since NAV/Business Central only used Ad-Hoc queries we have a lot of them and it’s important to understand how to read them.

What worries me a bit here is the Blocking Process’ Status which is sleeping. I have to investigate this more, but I interpret this as a process that went silent and the user is not actively doing something.

Step 8 – Get the Query

Using Google I (DuckDuckGo actually) also found a way to get these queries as long as they still exist in the cache of your SQL Server. Simply use this query

SELECT deqs.query_hash ,
deqs.query_plan_hash ,
deqp.query_plan ,
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE deqs.query_hash = 0xB569219D4B1BE79E

This will give you both the query and the execution plan. You have to use SQL Server Management studio to execute this against your Azure SQL Database

Step 9 – Restart the service tier

Unfortunately for me this journey resulted in having to restart the service tier. We could not identify the exact person/user who had executed the query that was locking. Maybe we will be able to do that in a future incident since I’m learning very fast how to use this stuff and time is off the most essence when incidents like this happen on production environments.

Needless to say that the NAV Database Locks screen was not showing anything. I would have used that otherwise off course.

Azure Application Insights 101

In my series around Application Insights for Microsoft Dynamics Business Central / NAV this is probably the most booring one. However it is quite important. In order to teach you folks about KQL and the Application Insights API etc.

Step 1 – Create Application Insights

In your Azure Tenant search for Application Insights and select Add.

There is not much to fill in here. The Resource Group is probably most important if you have a bigger Azure Tenant. You want to group your stuff together.

Step 2 – Grab the key!

After the resource is created grab the key to your clipboard and now leave the Azure Portal and move to the Business Central Admin Portal

Step 3 – Put the key in Business Central and Restart your system

Step 4 – Analyse the data

But that’s for the next blog, about KQL. This will be a language at least 1 person in your company needs to master. Definately.

Wait… is that all??

Essentially yes, but there is a caveat…

The million dollar question is probably whether or not to pot multiple customers into one Application Insights resource.

This probably depends on one question. Does your customer want to access the data? If they do, the data needs to be in it’s own AppInsights resource so you can grant your customer access.

The good news is, and we’ll get to that, is that you can query accross application insights instances.

Tip #69 | Default Implementation for AL Interfaces

I just love it when I get an error and nothing I search for answers what to do next.

Like this one

Value ' ' does not implement interface 'ForNAV Layout' and there is no default implentation for the mentioned interface.AL(AL0596)

There is no mentioning of default implementations in the Microsoft documentation.

And in fact, in this enum value, I do want a default implementation since “Empty” is a fallback since I want to use the new expandable and collapsable row feature in BC16.

The solution: this is a property on Enum level

DefaultImplementation = “ForNAV Layout” = “ForNAV Layout Default”;

The motivation here for me to work with an Enum and an Interface is that we have a partner that want’s to implement a feature called “multiple layouts” that we think does not fit with the simplicity we have in mind for our core product.

This allows the partner to create a new App in AppSource with a dependency on ForNAV that introduces new features that only a subset of our customers need.

The majority of our customers is not burdoned with unnessesairy complexity while the few who need it have a solution they can subscribe to.

That my friends is what we mean with Extendability by design.

Tip #68 | Using Read-Scale Out in Business Central to improve Performance

Another quick tip for something I’ve used this week to help out a QBS partner with performance issues on Business Central.

Since the last release it’s possible to issue read-only commands on a real-time copy of your Business Central database by using the DataAccessIntent property.

This allows API Pages, reports and queries to be executed outside of your production database which is ideal for Power Apps, Power BI and websites that for example only show status information on outstanding orders.

Then I remembered, “off course” we can also use that with the ForNAV report pack for financial reports that run longer, like the Inventory to G/L Reconciliation. (Which already runs 10 times faster than the out of the box version).

So here is a screenshot:

DataAccessIntent = ReadOnly

And the documentation


You cannot use this for document reports! Document Reports in Business Central write to the database for logging purposes and VAT/Sales Tax calculation.

Document Reports are not good candidates for this feature since they are more or less WYSIWYG. They print litteraly what is in their source tables.

Also they leverage the Service Tier caching so they probably don’t even take a roundtrip to the database for most of the data they print.

Business Central Performance Tuning Series | Application Insights & More

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.

Continue reading “Business Central Performance Tuning Series | Application Insights & More”

Business Central; Developers, Developers, Developers

A new version of Business Central is around the corner. The codename is BC16 or 2020 Wave I. Both are the same thing.

I think it is safe to say that Business Central is a great succes. In the competing world of Cloud Business Solutions it is the most flexible product and the customer base is growing rapidly.

Business Central has a fantastic user interface and the extensibility model is next to nothing else out there.

Last week I was talking to a reseller of Business Central on the west coast of the US. He said he was small reseller since he only sold 10 installations in 2 years. Yes, he was new to BC and had no prior NAV experience.

Knowing that in the old world NAV resellers often only sold 3 licenses or less per calendar year I had to laugh a bit.

Cloud brings both challenges and opportunities that are beyond anything we have ever seen in our ecosystem.

I can see the opportunities from my perspective as a freelance developer. The demand for AL developers is insane. In Europe it seems to be doing ok, but in the US they are screaming for resources.

As for the reasons I can only guess, but it seams that growth of Business Central is happening most from outside the traditional NAV world. New partners are jumping on the opportunity to then find out that implementing BC almost always means implementing a few changes that require per-tenant extensions.

Many traditional NAV resellers still struggle with Business Central because of two reasons.

  1. Their IP is a big mess
  2. Their COGS is too high

Also, almost always they know nothing about the Azure stack and integration with Office 365.

In my opinion it’s time to change but this seems very difficult. NAV partners have a very expensive (pre) sales department that is funded with large license revenues. Their Developers have been rewarded for 20 years to deliver crappy hacks in C/Side that only had to work as quickly as possible to then start on the next hack.

I’ve seen situations where it was considered “normal” that the service desk employees of a partner logged into a customers system almost on a weekly bases to fix data with reports and customers actually pay premium service for this.

Grim Reaper in the NAV ecosystem

It seems so hard to change, that many owners of traditional NAV resellers choose the easy way out. They sell their company to investment companies.

This is probably also due to the current economy where interest is at an all time low, stock markets are way overpriced (although “Corona” settled that last week) and money is looking to be spent.

Titanic or Oil Tanker

It’s not the first time that the faith of our ecosystem is influenced by global economy. When the Three Tier concept was presented the world decided to have one of the biggest recessions in history.

If you had asked me two or three years ago I would have compared Microsoft to Titanic. In all their glory they wanted to go so fast that they ignored all the rules and headed straight for an iceberg.

Now that Business Central is being picked up by new partners and a (small) subset of the existing NAV ecosystem I am a bit more positive. Actually a lot more possitive.

Today I would compare Microsoft more with an Oil Tanker that is trying to change course but it is going insanely slow. People at the bridge are giving orders to change direction but by the time this is happening it’s (almost) too late.

Now is the time!

It’s easy to say to existing NAV partners that they are too late, but that is unfair and not true. The ERP world is moving a lot slower than Microsoft wants. ERP is not implemented at startups but at companies that have been in business for a longer period of time.

Business Central 2020 Wave I contains a few changes that are important for the old NAV ecosystem. The most important ones are the actual implementation of Enum’s, the introduction of flexibility of the sales pricing module and integration with Common Data Services.

Microsoft is listening but change is slow, but slow sometimes is good.

Oops, they did it again

The previous release of Business Central caused a massive riot because Microsoft broke the majority of Apps published by their partners on the AppSource. This resulted in a lack of trust and the compeition even started to use this as selling against Business Central.

Microsoft made a drastic, but important executive decision to promise not to make breaking changes anymore.

Unfortunately this seems to be an impossible promise since the enum implementation again breaks existing extensions, although I think it will be a minority this time. I haven’t checked this however.

Since Wave I 2020 is not officially released Microsoft might actually decide to fix this. Let’s see what will happen.

Small NAV Partners, now is the time!

A few things need to happen.

  1. Stop paying sales people that cannot even do a proper demo. They are not needed anymore. Instead you need industry specialists that understand the customer.
  2. Stop calling everyone who can make a change to C/Side a developer.
  3. Embrace Azure

What if they don’t?

What we see in the US will happen in Europe too. New partners will start selling Business Central that don’t have the legacy. Legacy in expensive sales people and legacy in bad code. Even legacy in stupid customers who accept crappy solutions.

It’s time to decide which side of the Business Central ecosystem you want to be part of, because Business Central will continue to be the best ERP in the cloud, with or without you.

Developers don’t have to be affraid of the future. If you are willing to let go of writing fast and crappy solutions and invest in repeatable quality apps the future is fantastic.