Posted on June 4, 2018 by Matthew Bradley
At least once a week, someone will ask me ‘Which is the best SQL offering on Azure?’, which isn’t really even a valid question. Each product has pros and cons and so the question to ask isn’t ‘Which is the best SQL offering?’, it’s ‘Which is the best SQL offering for myneeds?’
Azure has several platforms to run your databases; each suited for different types of workload.
This is why I would never advise clients to look at Azure SQL products as the first stage. I ask them to write down their requirements, how much control over the environment they want, what level of performance they need and how their databases are accessed. When they have those details, that’s when I sit down with their developers and DBA’s to talk about how their applications work and what those requirements are. It is only after this discussion that we look at the Azure SQL offerings to see which one their database needs best align with.
If you’ve ever played the board game ‘Perfection’, where you have shaped pieces that you have to put into the matching holes on the board, the thought process for choosing which SQL product on Azure to use is similar. You think about all of your requirements and this gives you your ‘piece’, you then look at which offering your piece fits in to and that is the platform that you choose.
If it doesn’t fit then it isn’t going to work!
In its most basic form, you have IaaS (SQL installed on a dedicated VM), Managed Instances (dedicated full SQL instance but you don’t have any control of the underling host) and Azure SQL (this is a multi-tenant platform so you don’t have control over the instance level settings). The PaaS products then have their own different types of sub-offering.
But ultimately, the more you move away from IaaS, the less management overhead you have but you also start to get far less control:
Generally, the easiest one to rule out is Azure SQL. This is because you don’t have any control over the instance. So if you have intensive workloads and want to follow TempDB best practices, optimise for ad hoc workloads or if you need to control MAXDOP & cost threshold for parallelism then you can immediately rule that option out.
So, how do you narrow things down further? As people reading this will have different requirements, let’s look at the main differences that I get asked about:
The next bit of fun is looking at performance and performance tiers. But I only wanted to talk about features here so it wasn’t too long a blog post. I’ll look to do a separate SQL performance blog that delves into the performance tiers, performance tuning and the magic cauldron of DTU’s.
If you have any questions about this then just leave a comment or drop me a message 🙂