SQL Azure is a very interesting service. It’s as interesting as it is misunderstood both in terms of how exactly it works, and what it’s intended to be used for.
First of all, it’s not really the same thing as SQL Server that you install on your box, virtual or physical. It certainly provides the same functionality, and from functional perspective most of things you can do with SQL Server, you can do with SQL Azure. But it behaves in so many different ways that you can’t truly compare them side by side.
Another thing is what SQL Azure was designed for. It’s designed for massive cloud workloads where concurrency is more important than sheer speed. And in that respect it is just brilliant. However, to get most out of it, you have to write and optimize your database access code specifically to take advantage of its features and behavior, otherwise, you simply get performance that can be qualified as mediocre at best.
What happens when you put NAV on SQL Azure? Well, that’s something that you certainly can do – Microsoft does it as well. The thing is – it works. It leaves a bit to be desired if you intend to run heavy processing, but my firm conviction – having tested it and having gone medieval on it with my tests.
While NAV has traditionally mostly been a domestic animal, it’s now getting pushed more and more cloudwards. A large number of partners and customers are considering Azure VMs as their platform of choice as it has many benefits over purchasing and running the whole show in your own basement.
So, I have tested several Azure VM configurations to show what A, D, and G tiers bring to the show, and how much those numbers next to tier letters really mean.
These were the contestants:
I chose A7, DS13 and GS3 because they all have fairly similar resources:
The only significant difference here is that DS13 and GS3 use premium disks, while A7 uses standard disks, however there is also some difference in hardware configurations. G tier has much better processors than D tier, which has better processors than A tier.
The other two contestants are somewhat undersized and I used them to check if those configurations can cope with sustained pressure:
And, there is an unexpected guest in this roundup: the DS13 machine with SQL Azure P11. It’s an oversized and unbelievably expensive options, and the reason why I put it here is that it’s the only SQL Azure configuration that can remotely hope to compete with VM deployments of SQL.
On-prem is where NAV has ran for ages, and where it will still primarily run for a while more. So I test several on-prem configurations with different processors, disks, and memory, to see how these configuration behave in scenarios I described in the original post.
I don’t have too many different hardware configurations to test, but I do have those
An i7-3770 machine with two OCZ Vertex4 SSD disks and a Western Digital Caviar this or that, and 32 GB of RAM.
An i7-6700K machine at 4GHz with two Samsung M2 950 SSD disks (at 2.5 GB/s read and 1.5 GB/s write speed), two Samsung EVO Pro 850 (at 500 GB/s read/write), and a Seagate something or other 8TB drive, and 64 GB of RAM.
All in all, I tested these configurations with:
Data and log being on different SSD disks.
Data and log being on the same SSD disk.
Data and log being on the same magnetic HDD drive.
Yes, I have been a lazy blogger lately. With so many people blogging about NAV nowadays, it’s really difficult to come up with original content all the time.
So, I decided to test performance of NAV under various configurations, including on-prem, Azure VM and SQL Azure, and share my findings with you. And while I am writing these lines, there are four machines sweating the crap out under some performance test code I recently wrote.
If you are developing .NET assemblies for use with NAV, then sooner or later you’ll figure out that the new database deployment of add-ins in NAV 2016 is broken.
I’ve just suffered through medieval torture of attempting to have my NAV forget about a database-deployed assembly.
First of all – if you are merely consuming an off-the-shelf assemblies developed by somebody out there, you’ll probably not need to worry at all. However, if you are developing your own assemblies, then sooner or later you’ll find yourself stretched in exactly the same torture rack.