SQL Server related services in Azure

Reading Time: 10 minutes

For this year’s Azure Advent Calendar, I decided to cover SQL Server related services in Azure.

Because I like to think as myself as a member of both the Azure family and the Data Platform family, I wanted to cover a topic which would crossover between the two communities.

Now, this post is one of three for the finale of this year’s Azure Advent calendar, which is hosted by Gregor Suttie and Richard Hooper. You can find out more about this years contributions for the Azure Advent Calendar here.

At the end of this post you will have a better understanding of which SQL Server related services are currently available in Azure. In addition, towards the end of this post you will find a link to the video version.

Topics

Topics I cover in today’s post about SQL Server related services in Azure includes the below.

• Azure SQL
• Compute offerings
  • Infrastructure as a Service
  • Platform as a Service
  • Other compute offerings
• SQL Server Virtual Machines
  • SQL Virtual Machine resource provider
  • Automating Infrastructure as a Service
• Platform as a Service offerings
  • Azure SQL Database
    • Elastic Pools
    • Purchasing models
  • Azure SQL Database Managed Instances
  • Azure Synapse Analytics
• Other services
• Applications
  • Other applications
• Video
• Final word

Azure SQL

Because I used it a lot in the video that goes with this post video, I thought I better discuss Azure SQL first.

Now in the past the SQL Server related services were scattered around in the Azure Portal. Because of this you had to navigate around the old-style dashboard or search for the services at the top of it.

So, in August this year Microsoft decided to create Azure SQL. Which is one central place for you to manage your SQL Server resources in Azure.

Within Azure SQL you can create different SQL Server services here with an easy to use wizard.

I should point out here that a lot of you will only use this portal when testing features. When it comes to looking after them in production A lot of you will end up PowerShell or applications to manage them instead.

Compute offerings

Just to quickly recap, there are two main types of compute offerings available in Azure that you can use to host your SQL Server databases. When I say compute offering, I mean how the SQL Server related services in Azure are presented to you.

Infrastructure as a Service

One is Infrastructure as a Service, or IaaS, which is where you deploy an entire virtual machine with SQL Server installed.

This is a good option if you know you want to quickly move your SQL Server instances to Azure as a first step of a migration, before you look to migrate them to use a Platform as a Service solution.

Which brings me nicely to the second type I want to discuss.

Platform as a Service

Platform as a Service, or PaaS, allows you to have your SQL Server database or instance directly available in Azure without any underlying Operating System in place.

For example, after logging into Azure Portal you are presented with the SQL Server application itself. Which means there’s no operating system to log into.

Which also means you’re no longer responsible for the patching and maintenance of any operating systems. I do demonstrate this in the video that goes along with this post.

Other compute offerings

These days there are other compute offering terms used. There are a couple of them I will cover during the course of the video.

First one is Database as a Service, or DBaaS. Which some people use to describe Platform as a Service offerings based on databases in Azure.

Another is Containers as a Service, or CaaS. Which some people use to describe services that manage containers like Azure Kubernetes Service.

Now we’ve covered compute offerings, lets breakdown the SQL Server related services a bit more.

SQL Server Virtual Machines

As I mentioned earlier you can deploy virtual machines in Azure with SQL Server preinstalled on them. If you use this method, then the amount you pay in Azure for these virtual machines includes the SQL Server licensing cost.

Now, because these virtual machines are based on an image you may have to customize them after you have created them.

For example, if you connect to a SQL Server virtual machine using a Remote Desktop session you can look at the services configuration. From there you can see the preset logins used for services.

You might want to change the login details for the service accounts or add additional SQL Server features. If you do want to change the login details I do recommend you use SQL Server Configuration manager instead of through Services console.

However, instead of using a Microsoft built image you can also build a SQL Server yourself on premises. Afterwards, you prepare the server to be used as a template for future installations by wiping all the unique information from it.

In Windows, you do this using a tool called sysprep. After you have done this, you can upload this template as an image into your Azure subscription for use. Bear in mind that if you use this method you have to check you are covered as far as licensing is concerned.

SQL Virtual Machine resource provider

One option you might be able to use to cover your licensing costs if you use your own image is to register your custom SQL servers with the SQL Virtual Machine resource provider.

Doing this allows you to have more flexibility with licensing and allows you to take advantage of things like automated patching and backups. Similar to what you can see if I drilldown into the virtual machine through Azure SQL.

You can also simply deploy a virtual machine with just an Operating System into Azure and then install SQL Server afterwards.

Again, bear in mind that if you do this you have to check you are covered as far as licensing is concerned and you might want to think about using the resource provider I have just mentioned.

Automating Infrastructure as a Service

One key point about Infrastructure as a Service is that you are responsible for the patching and maintenance of these virtual machines in Azure. If you are going to use IaaS for SQL Server, I recommend that you look at ways to automate things as much as possible in Azure.

You can configure some of this in the portal on a per machine basis. However, to manage multiple servers PowerShell and ARM templates are more suitable.

If you use PowerShell, try and use the latest Az module.

ARM templates are basically JSON files in a certain format that you can use to specify what you want to deploy into Azure. You can see what they can look like by drilling down into the virtual machine and selecting Export template on the left-hand side.

They are known to be idempotent, which means if you delete a resource you should be able reissue the template to restore the deleted resource.

Now, two things I want to mention about idempotency here.

1. First of all, using programmatic logic in your ARM templates can stop them from being idempotent.
2. Secondly, never mispronounce idempotent when recording a video.

In addition, there are some extensions you can add to your virtual machines to help with other tasks like patching and backups.

Platform as a Service offerings

Now that we have looked at Virtual Machines, I will discuss Platform as a Service offerings available in Azure.

Azure SQL Database

Let’s start with the traditional Azure SQL Database offering. Which is a Platform as a Service solution which presents a single SQL Server database for use.

Within Azure you can easily create a single Azure SQL Database. However, even though each Azure SQL Database can be used by itself, each one must belong to a logical server that you create in Azure.

Because it’s a logical SQL server, you can only manage it though Azure. As you can see yourself if you drilldown into one there are various things you can configure for these logical servers within Azure.

In addition, you can drill down into the Azure SQL Database. You can see there’s a lot of things you can configure for the Azure SQL Database in the portal alone.

For example, being able to write your own SQL queries in Query editor and easily setting up replication to another region using Geo-replication.

You can also manage Azure SQL databases using PowerShell. In addition, you can connect to them in SQL Server Management Studio, which I show in the video.

Elastic Pools

In the beginning, Microsoft introduced Azure SQL Database to present a single SQL Server database that you could access in Azure. However, extra functionality has been added over time to meet business needs.

For example, you can group some of these databases together to share compute resources.

When a database is busier than usual it can use more of these resources, and then release them when no longer required so that other databases in the same group can use them.

Which is a concept Microsoft calls Elastic Pools.

Which you can see in Azure SQL yourself if you drilldown into an Elastic Pool. From there you can change your elastic pool setting. You can also click on Databases to see which databases belong to the elastic pool.

As you can imagine, this is very useful when you have multiple databases that are busy during certain periods.

Purchasing models

Now you can choose between two types of purchasing models for the traditional Azure SQL database service.

Which are DTU based or v-Core based.

DTU stands for Database transaction unit. It is the original purchasing model that was used when Azure SQL Database was first released.

It’s based on a fixed measurement Microsoft came up with to represent the amount of compute required by SQL Server.

V-Core based model on the other hand is more flexible and allows you so specify separate compute and storage resources you want to use. Now, if you decide to use the V-core based model to deploy Azure SQL Database you have a choice of three tiers.

Which are General Purpose, Hyperscale and Business critical.

General purpose tier can be used for your mid sized workloads. In addition, is the best tier to use when testing new things as that is the cheapest.

Especially if you are testing using a Visual Studio subscription and you have limited Azure credit.

Hyperscale is the newest offering available. It allows you to deploy a version a SQL Server database which is designed specifically for people that have high transactional requirements in Azure.

Business critical tier is the one recommended for your most important workloads. Because it offers low latency and high resiliency.

Now, it’s worth noting here that if you use the V-core based model you can also choose how you want the compute to be created. Whether you want the it to be provisioned in advance, or alternatively dynamically created for as and when you need it.

Which Microsoft calls serverless. In addition, in some regions you can also choose which generation of hardware you want to use, basically whether to use an older or newer processor with these databases.

Azure SQL Database Managed Instances

Up until now we’ve mostly focused on the single SQL Server database offerings in Azure. Now it’s time to discuss Azure SQL Database Managed Instances, or simply put Managed Instances.

Basically, for many years Microsoft only presented a service for a single database. Finally, they released an offering presenting an entire instance instead.

In addition to this, managed instances are more secure, because they are installed within a secure virtual network. Now, there are some caveats to using this, so I suggest you do your homework online beforehand.

Just to set realistic expectations here, as I mentioned in a previous post here Managed Instances can take a while to deploy. In fact, Microsoft states this now if you go to deploy through the portal.

In fact, the fastest time I’ve been able to deploy one is 3 hours 42 minutes. I was able to do that by turning all the compute settings right down.

If you drilldown into a Managed Instance in the Azure Portal you can see various options, including a Quick start guide. Which contains various items, including a couple of ways you can connect to your Managed Instance since it is secured.

Azure Synapse Analytics

Azure Synapse Analytics is the final Platform as a Service offering I want to cover in this section about SQL Server related PaaS offerings.

It was previously known as Azure SQL Datawarehouse, however earlier this year it has been rebranded and given additional functionality. You can still install an Azure SQL Datawarehouse as part of it though.

Now, you are not able to create an Azure SQL Datawarehouse within Azure SQL. However, as you can see here, you can still see the logical SQL Server an Azure SQL data warehouse if you create an Azure SQL Datawarehouse elsewhere.

Azure Synapse Analytics allows you to query data from data warehouse and big data solutions that are petabytes in size using SQL. In fact, you can use a lot of the transact SQL language that comes with SQL Server within Azure Synapse Analytics.

Whereas Azure SQL Database uses DTU’s to represent the amount of compute units, Azure Synpase Analytics uses DWUs to represent scalable units. Which stands for DataWarehouse Units.

Some of the new features since the rebranding are still in Preview. For example, being able to view streaming jobs and integrating Power BI.

From the amount of excitement it has caused since its announcement at Ignite this year, it’s worth looking into.

Other services

I thought I would give some honourable mentions to some other Azure Services that are linked to SQL Server.

First, I want to mention Azure Data Factory which is gaining popularity and is recommended for use with Azure Synapse Analytics. You can think of it as a version of SQL Server Integration Services hosted in Azure.

Second, I’ll quickly mention Power BI here since we just talked about it in the previous section. It’s a powerful data visualization tool which is also very popular in the Data Platform community.

It allows you to produce powerful reports and dashboards.

Thirdly, I feel I should mention Azure Kubernetes Service, or AKS. Because it can be used with a new feature that comes with SQL Server 2019 that is called Big Data Clusters.

Basically, you have to host Big Data Clusters on a Kubernetes service somewhere. And Azure Kubernetes Service is one of the Services you can deploy Big Data Clusters on.

Applications

I want to cover some applications you can use to manage these SQL Server related services in Azures.

First of all, there’s SQL Server Management Studio. Which is the traditional tool people have used over the years to manage SQL Server. If you watch the video that goes with this post I show you how an Azure SQL database and a Managed Instance both look in Management Studio.

Next I want to cover Azure Data Studio. Which is a newer application that has being introduced by Microsoft, and for those of you who know branching terms, it is forked from Visual Studio Code.

Which is why it might look familiar to some of you who have yet to see it.

It’s main advantage over SQL Server Management Studio is that it is multi-platform. Which means it can also run on Linux and MacOS.

Another advantage is that you can run notebooks within Azure Data Studio. In fact, if you can use the “Deploy SQL Server” wizard to generate a notebook you can use to deploy a Big Data Cluster.

Which is another thing I demonstrate in the video linked to this post. As I mention in the video, I did do a post previously with some personal guidelines about deploying Big Data Clusters. You can read that in detail here.

Other applications

A few other applications I want to briefly mention include the below.

Azure CLI, which is the Azure Command Line tool. Which is easy to use and can be installed on either Windows, Linux or MacOS.

PowerShell can also be very useful for deploying and managing services in Azure. As I mention in the video, if you are still using the old AzureRM module you may want to migrate to the newer az module instead.

Finally, I want to mention a command line tool called kubectl.

If you are going to look into Big data Clusters using Azure Kubernetes Service I recommend you learn how to use this as it can be a very useful utility.

Video

I hope you have enjoyed todays finale to the Azure Advent Calendar. A big thanks to Gregor and Richard for letting me take part in this.

You can also watch the video that goes with this post that also has been released today. Which I will admit was my first ever video like this and was an interesting learning curve.

You can watch the full video about SQL Server related services in Azure by either clicking here or on the image below.

Final word

Anyway, that’s it from me. I hope you enjoyed my personal review of SQL Server related services in Azure.

Happy holidays, and for those of you who celebrate it Merry Christmas and a Happy New Year.