SQL Assessment API

Every so often, Microsoft puts some extra into their SQL Server product, which doesn’t get as much time in the spotlight as it should. Nope, not talking about any of the great, new things that we got coming in with SQL Server 2022. Today’s post will be used to look at something called. SQL Assessment API. It’s not a very exciting name for a feature, but it definitely provides a new, exciting capability!

So, what exactly is the SQL Assessment API, and where, and how would you use it?

(more…)

Nevergreen SQL Server deployments and mitigating patching pains

Not green, but still pretty

Not too long ago, Microsoft released The Final Service Pack for any version of SQL Server, as a part of their move to a Modern Servicing Model. This (and the fall time here in Finland) got me again thinking about the benefits of running your database workloads on PaaS over self-managed VMs. And one of the very real benefits is, that PaaS databases tend to be running with up-to-date versions. This is what you might have seen called as “evergreen” by Microsoft. When it comes to SQL Server running on VMs, well, these tend to be more of a “nevergreen” type of deployments. At best, they are somewhat yellow, but almost always never green.

And this isn’t an on-premises problem only, patching self-managed database services in the cloud is just as unpleasant as it is on-premises. But unlike death and taxes, continuously patching your operating system and the database software is something that you can avoid, without having to fear cosmic powers or a prison. It does require us to adjust our thinking on how we’re managing these systems, though: The databases aren’t the same as the virtual machines, and your virtual machines should be treated like they are disposable, not like pets.

(more…)

Running non-SQL Server database workloads in Azure

So many databases…

I’ve written few (most of my) posts about running SQL Server, and very often about running it in Azure. While Azure is absolutely the best cloud platform for SQL Server, it is also an excellent platform for plenty of other database workloads. In short: “If it runs on a server or a virtual machine, you can move it to the Azure.” However, I also feel that most blog posts should exceed tweets in length, which is why we’ll go a little deeper into this.

Not so deep, though, that we’d start discussing non-relational databases (those can go to CosmosDB).

(more…)

Officially the best Azure VM for SQL Server

Number one!

I was just recently going through my backlog of podcasts and came across Data Exposed episode with Microsoft’s Pam Lahoud (@SQLGoddess on Twitter) about SQL Server VMs in Azure. Now, we have it from the official sources that one of the best VM sizes for SQL Server workloads is the Edsv4-series.

Incidentally, this is also one of my favorites for running SQL Server, and one that I often end up recommending due to missing monitoring data. Read further to find what exactly makes this VM series so good for many of the SQL Server workloads.

(more…)

Azure Defender for SQL

It’s always a great time to write about security, so let’s go with this topic today. One of the main reasons I love public cloud is, that beyond nice offering for databases, they also provide great features around security.

One of these features, and the topic of this post, is Azure Defender for SQL. While Azure Defender has been around for a while, it was only very recently it was also made available for SQL Servers running on VMs.

So what is Azure Defender for SQL and why you should be enabling this today, if you already haven’t?

(more…)

Azure SQL Managed Instance Storage Performance

Spinning rust

Let’s continue with the storage performance topic a bit further. One thing that sometimes can be confusing is the storage performance with SQL Server Managed Instances. The reason for confusion comes from having two separate performance tiers (General Purpose and Business Critical), with different method of managing the IO performance.

In General Purpose the method to get better performance is to go with bigger files. In Business Critical the better performance comes with cost, by adding more vCores to your Managed Instance. There are also a couple other details to keep in mind, when figuring out what exactly you need for your workloads.

(more…)

The need for (storage) speed and the Cloud

Feel the need, the need for speed…

In a past couple months I’ve seen this happen in a couple of different customers, so I thought I’d write a short post about the topic. While it’s easy to think that running SQL Server workloads on cloud VM’s is the same as running them on VM’s in an on-premises environment, this is typically not the case. There is one huge difference to cloud based VMs.

The storage.

And as you are well aware, what databases commonly are used for, it’s storing data. But that data doesn’t exist in a limbo (though sometimes the cloud makes me wonder…) but in an actual storage device somewhere. In the on-premises world, if you’re running any type of serious workloads, that storage is going to be an expensive SAN device. In the cloud your storage is also very like coming from a super-expensive, state-of-the-art storage device, but that’s not the experience you’re going to get. Instead of having bandwidth measured in GB’s, you’re getting something that is measured in MB’s.

But why, you may ask? Read on.

(more…)