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

3. More about Azure SQL Managed Instance

So while we’re waiting for it to be deployed, just a few more things about Azure managed instance. It has a few features which are not available in Azure SQL Database, so they include cross database queries. So you notice I wasn’t able to refer to the Master database when I was in the DP 300 database, where you can in Azure managed instance, you’ve got Clr common language runtime. So this is the execution environment for net framework code.

So you could be talking about C sharp code or VB net code. So dotnet framework code is also known as managed code. So the Clr is the common language runtime clr in SQL Server. You may also hear the phrase Clr integration. As we said in the previous video, you’ve also got SQL Agent as opposed to the Elastic jobs. You’ve got access to the msdb system database and you can manually make a copy only backup of a database.

Now, notice it’s a copy only backup of a database and not of the managed instance. You’ve noticed that it does not support the DTU based purchasing model. So if anyone talks about DTUs or EDTUS or Elastic Pools, they’re talking about Azure SQL Database and not the managed instance we’ve seen. You can’t use Hyperscale, Azure, SQL Managed instance. Managed instance deploys a dedicated ring for your data. It’s called a virtual cluster.

That means that it’s a set of service components hosted on a dedicated set of isolated virtual machines that run inside a virtual network subnet. A virtual cluster can host multiple managed instances and it can expand and contract as needed. Tempdb is available in the Azure SQL managed instance, and the managed instance creates toll files regardless of the number of Vcards.

Now, we saw that there was general purpose and business critical. So General Purpose offers a budget oriented, balanced compute and storage options, whereas Business Critical has high transaction rates and low eye or latency. But you do have double the cost. So we’re talking about the difference between five to ten milliseconds for General Purpose. That’s the latency and one to two milliseconds for Business Critical. That’s because general purpose uses as your Blob storage, whereas business critical uses SSD solid state disks or solid state drives one to two milliseconds. So you are paying extra, but you’re getting extra. The compute size between four and 80 V cores above.

But you can also have two vCores available in pools. So these aren’t elastic pools, these are instance pools which provide what Microsoft calls a convenient and cost effective way to migrate smaller SQL Server instances into the cloud. So as I said before, General Purpose uses remote storage, business critical SSD local storage. The database size can go down to very small level, relatively speaking, but still probably ample for more databases, all the way up to eight terabytes for General Purpose and four terabytes for Business critical Temp DB size. As I said, there were twelve files and the file size does vary. So you got 24GB per Vcore up to around two terabytes.

Business critical can go up to around four terabytes. There’s a bit of a mass calculation as to the relationship between the V cores and the Temp DB size. Slightly faster. Log right throughput for the business critical. Again it’s per core and it is throttled. It is limited on the general purpose more than it is on the business critical. You have four nines availability could be four and a half lines on business critical with a zone redundant single database. In terms of readonly replicas, there is one boot in for the business critical and that’s included in the price. But you can have zero to four. Using geo replication backups you can go from one to 35 days.

So seven days is the default. You are charged for the recalls for the reserved storage, so not the storage that you actually use, but the storage you say you want to allocate and the backup storage but I ops input output per second is not charged. You can have discounts on reserved instances. So you can pay upfront for one year or three years. For instance. You can have a gear hybrid benefit and you can have enterprise and pay as you go dev and test subscription. So if you’re wanting to do some developer and testing, that might be for you. So these are some of the differences in general purpose and business critical. So you can see that deployment is still in progress. So I’m going to pause the video until it’s finished.

4. Accessing Azure SQL Managed Instance in SSMS

Well, it’s taken around four or 5 hours to install, but it’s finally installed so I can go to my new managed instance resource and there it is. So with zero managed instant databases. So what I’m going to do is connect. So here is the host. And so you might be thinking you’d be connecting through the host and you can do that through using a private endpoint, but I want to use SSMS through a public endpoint. So what I need to do is I need to go down to the connection strings and it’s this bit that I’m interested in. So it’s the bit after the column not including the colon all the way to the semicolon, but not including the semicolon and that includes the comma and the number at the end. So I’m going to paste that into my notepad. So what I’m going to do now is go to my notepad, I’ll go to Connect database engine, so I will paste in my server name so it has the word public in it and ends of comma 3342, that’s the port you’ll need to use.

Then put in your login and password and then if everything has been configured okay, then you should be able to connect. If you can’t, and it seems like I’m having a problem, then here’s what you do. Let’s go into the overview and then this virtual network subnet and then going to subnets and we can see the security group, nsgdp 300, SQL, Mi. If we go into that subgroup, you can see that these are the things that have been set up. So I need to get to this with the things down the left hand side. So I’m going to go back into my managed instance and going to my resource group and then I can see this security group. So now I can have this lock down the left hand side. I can go to inbound security rules. And what I’m going to do is I’m going to duplicate this free three four, two rule.

So I’m going to click, add, say, source, any, leave all the rest as they are, change the port to 3342, the protocol to TCP I’m going to allow. And here’s an important bit. This number should be less than the deny all inbound. So I’m just going to make it 1301. So this is my port 3342 rule. So it’s fairly identical to the one above, except the source is N as opposed to the Azure cloud. So it might just take a few seconds for that rule to be created. Once it has been done, then I will try connecting again. So it might just take a few seconds and eventually it opens. It probably took quite a few attempts, maybe I just needed to wait a moment or two. But you can see finally it’s open.

Now let’s just compare and contrast this managed instance with this one here, which is the Azure SQL database. So you can see we’ve got Databases security and Integration Services catalogs involved. But in addition we have got Server Objects, Replication Management, the SQL Server Agent and the Extended Events Profiler. So you can see already that we have got additional functionality there. Now what we don’t have are any databases. So I could go to the portal and in the overview I can click on new database and you can use existing data like a backup but you can’t use sample data or alternatively you can create it through SSMS. So if I have a DP 300 Mi database for instance, then that just creates there. So I can actually create databases not using the portal.

So it just takes a few seconds. So it took about 20 seconds but there it is, it is done. So I didn’t have to go back to the portal and ask for it to be created. Now I’ve got no tables so what I’m going to do is just create a table with some dummy data. So I’ll be doing this when I’m looking at the virtual machine as well. I’m just creating a table with an object name and an ID, my table column, which is an identity column, so that’s one that just auto numbers 1234, etc. And then I’m taking the data from the standard Sys objects table, a system table. But what I’m doing is I’m multiplying it up so it gives me 11,000 rows and then I’m inserting it into the new table. So if I do that now so that you can see the speed that that had 11,000 rows and if I select that there we go.

So if I wanted to create another database, I could just create another database and another and another up to 100 databases. So this is how we’ve been able to log in to our Mi. And if you want any more help of actually connecting through the public endpoint then I found this article to be quite useful. You don’t need to worry about the PowerShell so it gives you step by step just like what I’ve done.

You need it to ensure that the public endpoint is enabled. We did that as part of the setup but if you disabled it, you need to enable it. Then you need to go into the virtual network and ensure that there is an appropriate inbound security rule and then you need the managed instance public endpoint connection string, so not the private endpoint. And you will find that in the portal if you go to connection strings and it is everything after the colon and before the semicolon in the public endpoint.