DP-300 Microsoft Azure Database – Introduction to Virtual Machines and Managed Instances (MI)

1. 4-8. Evaluating MIs and VMs based on specific requirements

Hello. And in this part of the course, we’ll be moving away from what you can do in Azure SQL Database and going towards looking at what you can do in Azure SQL managed instance and SQL Server on Azure Virtual Machines. Now, before we install an SQL managed instance in a virtual machine and look at how we can use the configuration for scale and performance affords you, is probably at this stage a good reminder of the differences between SQL Database managed instance and Azure Virtual Machines. So the managed instance is much closer to the database in that it is a pass, a platform as a service, as opposed to IAS infrastructure as a service for the SQL Server.

So Wave D is your virtual machine. You have to manage your Windows. It gives you control of the database, so it gives you control over what version you’re going to be using. For instance, you can shut down resources when not in use, so you can shut down your virtual machine, but you can’t do that in the managed instance.

There’s no such thing as a Serverless managed instance as though it’s in a Ger SQL Database. IAS is good for lift and shift, so you can move from one on Prem Server to another. Then if you can do that, you can do that with an Azure Virtual Machine and it gives 100% of the compatibility, as the on Prem manage instance doesn’t do that, but it gives a much higher percentage than the Azure SQL Database. You can use Azure Hybrid Benefit to reduce the cost if you’ve got an SQL Server license, and if you’re using a virtual machine, you’ve got a Windows license.

So here you can see some of the differences and quite a lot of the similarities between these various versions. So managed instance, you can have databases up to eight terabytes, SQL Database up to 100 terabytes. If you’re using Hyperscale as your SQL virtual machine, it is up to the instance size. With Azure SQL database, you have built in backups patching and recovery. The basic premise on a virtual machine it’s a computer, it’s your computer.

You manage backups and patches, though as we will see, there is a way of being able to assist with that. Now, you’ve always got the latest stable enterprise edition with managed instance. But for the Azure SQL Server virtual machine version, you can choose which version you want anywhere from 2008 R. Two both managed instances and the virtual machine can use SQL Agent Jobs as opposed to the Elastic Job Agent that you’ve got to use for SQL Database. Now, just like the SQL Database, you can add more Compute Power whenever you want. Shading is not as easy on the managed instance as it is on the database, so it is possible now they made a difference.

Here the managed instance. You can have up to 100 databases on a single managed instance. Whereas you’ve probably noticed that when I create a database, you have to go through the created database on the portal or a similar thing PowerShell. Whereas with managed instance, you just create the managed instance with the portal and then you can use whatever you wish, like for instance, SSMS to create your database. The virtual machine, well, it’s just like an on prem. You’ve got up to 99. 99% availability, so that’s the four nines for the managed instance and on the virtual machine. However, firstly you have to have some things in place to get that. For the virtual machine it requires a second virtual machine and using an always on availability group, we’ll have a look at that later on in the course. But that doesn’t include the SLA, the service level agreement for SQL Server, that’s just the virtual machine. You’ve got automatic backups in place for between one and 35 days. Seven is a default. Again for the virtual machine you have to configure the backups yourself. In managed instance you can perform copy only backups for long term backup retention. We’ve seen the Ltr in the Azure SQL database where it used to be. You couldn’t do that in managed instance.

It’s now coming in. You can do Point in Time Restores, but you need the appropriate backups on the server. You can configure auto failover groups on managed instance just like you do on the database. You can use Azure Failover Cluster instances using shared storage for the virtual machine. Auditing works at the server level for both of managed instance and the virtual machine. All of these include Azure Defender for SQL which includes vulnerability assessment and threat detection.

You’ve got data encryption using transport layer. Security transparent data encryption and always encrypted together with firewalls. And you can use SQL authentication on all of them. For the managed instance you can also use as your Active Directory. But because you have got essentially a Windows installation on the SQL Server virtual machine, you can use Windows authentication as well. You can also install Linux X version on your virtual machine and put SQL Server on that. So this is a brief reminder of the differences between Azure SQL managed instance and the virtual machine. And in the next video we’ll be installing an Azure SQL managed instance.

2. 10, 53, 56. configure Azure SQL Managed Instance for scale and performance

In this video we’re going to configure Azure SQL managed instance. So let’s go into Azure SQL as we’ve done previously, Create. And here we’ve got a managed instance. So I’m going to click on Create here. So I’ll select a resource group. I’m going to have a new resource group. So this is my managed instance. So I’m going to give it an instance name, so DP 300 Mi and you can see specified name is already in use. So I need to expand it. So. SQL mi. There we go. So a region. So again, I’ll do it one close to me. If you’re following along, you should do it one close to you. And then let’s have a look at the compute and storage. Now, you will notice firstly there is a lack of basic standard and premium. Here we’ve just got the general purpose and business critical.

You’ll also notice there’s no hyperscale. So general purpose is suitable for most workloads. Business critical is where you’ve got low latency workloads, high resilience to failure requirements and fast failovers. But you can see the starting price. For the general purpose we start at about $1,900 a month, whereas for business critical we start at double that, around 2000 2100. So I’m going to go for the general purpose and I’m going to have the minimum that I can. vCores four and storage 32GB. But you’ll notice that you can go up to 80 V cores and you can go up to well, with four V cores you can go up to two terabytes, but with 80 you can go all the way up to 16 terabytes of memory. For business critical, it’s not that high, you can go up to four terabytes.

So this is shared among all of the databases. But notice how expensive it is. If you go that high and you don’t need it, we’re talking about $20,000 per month. So I’m going to reduce these to the minimum I can get away with. You can also have at the Azure hybrid benefit if you already have an SQL license. So you can see that roughly takes a third off your price. You can select the backup storage redundancy, so Geor, redundant, zone redundant and locally redundant. So we’ve had a look at what each of those means before. But as a quick reminder, in a region you’ve got various zones. So here it would be locally redundant if we have them in different computers in the same building or the same buildings which are powered by the same infrastructure. It would be zone redundant if we have it in multiple buildings in the same region. And it would be georedundant if we have it throughout the world. So maybe you have it on West US and East US for instance.

So now I’ve set that up. So as you can see, very important to set it up, the default was for an eight Vcore. Now set it down to a four Vcore. So let’s put in a managed instance, Admin. So I’m going to call this one DP 300 Mi. So again, you can see you can’t use things like Admin Administrator, I say Root, DB Manager, Login Manager and so forth, which is very odd because there aren’t any DB managers or login managers for Mis, they are specifically for Azure SQL Database.

And let’s put in a password and it’s got to be at least 16 characters in length, no more than 128, and they’ve got to have the usual complexity. So three of at least uppercase letters, lowercase letters, numbers and symbols. So you can choose three of those and have at least one of them each. And you can’t have three or more consecutive alphanumeric characters. So you can’t choose one, two, three or ABC. So let’s go into networking. So save that.

So it’s going to create a virtual network and we are wanting, in this particular case, a public endpoint. I’m not too worried about security because this is just a test and it’s going to be no confidential data in there. So collation fine time zone. Do you want to have this being used as a failover secondary? So you can use this new instance as a secondary. So if you said yes, then you would need to say where the primary was and then your maintenance window. When do you want any updates to happen? So let’s review and create that. So notice what it says at the top. Deploying a managed instance is a long running operation taking up to 6 hours to complete. So let’s click Create and it is Deploying.