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

7. Logging into SQL Server on Azure Virtual Machines

So in this video, we’re going to log into our virtual machine with SQL Server. So you can see deployment was completed, took around 1214 minutes. So I’m going to go to the results and here it is. And you can see I can stop it, I can restart it and I can connect to it. Why would you want to stop the virtual machine? Well, in reality that’s more like deallocate. So what happens is you don’t actually pay for the virtual machine when it stopped. You do continue to pay for things like storage. So that’s why it’s quite important if you’re having a test or development environment that you have auto shutdown enabled. So I’m going to connect to it, click Connect and I’m going to use Bastion.

So this is a secure way of connecting to it. So use Bastion down here, connecting. So it’s fairly simple. Expand your virtual network address, create the Bastion subnet. So click Create subnet, create the Bastion itself and you can then click Create. Alternatively, you could, if I go back to the overview, connect using anything else that you have enabled like RDP Remote Desktop Protocol or SSH Secure Shell. So I’m going to go for the RDP, download the RDP file, click on it down here. So the publisher of the remote connection cannot be identified. You want to connect anyway. So here’s, windows, security. Enter your credentials. Well, I want more choices. I want to use a different account. So that gives me therefore a username and password.

So my username is that my password is that I will check remember me because it is just a test machine, the identity of the remote computer cannot be verified. Do I want to connect? Yes. And there was a checkbox for don’t ask me again for connections to this computer. But you can see now configuring remote connection. And here is my connection. So setting up personalized settings just as if you were logging into a Windows machine for the first time.

And here we are. So I’m just going to get rid of a few things. So we have our server manager. If you’ve not used Windows Server before, y, we won’t be going too deep into this. If you are, then you know all about this. Now we’ve got this blue bar at the top. I’ll keep it pinned simply so you can see when I’m looking at a virtual machine, but you can unpin it and then it will disappear. So if we look in the Start menu, we can see SQL Server 2019. And going further down we have SQL Server Management. We also have the Database Engine Tuning Advisor, which we’ll be looking at later. So you can see if you want to get the blue bar back, you just score to the top. So as I say, I’ll leave it to pinned so you can see it. So now I just log into my SQL Server, just like any other SQL Server which is actually on that machine. Well, that took a minute to open. Maybe it’s just because the computer is just waking up.

So I will try Windows authentication, click Connect and there you go. I’m in. So I don’t have any databases at the moment so that’s no problem. I’ll just right and click on databases, go to new database. So I’m going to call the DP 300 VM and I’m going to create a new query and I’m going to create a table with an object name and an ID identity. Populate it from Sys objects and there we have some dummy data. What I’m also going to do with this database is I’m going to right and click on it, go down to Properties and go down to the query store and you can see that it is disabled by default. So I’m going to enable it to be read, write. So once we’ve finished with our virtual machine we can either close it this only closes my remote session so the compute itself will keep going. So if I wanted to go back into it I’ll just either use the previous RDP remote desktop protocol or I would go to my downloads where I’ve downloaded the previous RDP file.

So now I’ve got to enter my credentials and when they are entered I’ll click Remember me still says the identity cannot be verified. I’m going to say don’t ask me again for connections to this computer. Then it’s connecting and I get back into my session exactly as I’ve left it. When I finished well, I can go to shutdown just like any other computer and what I can also do is go to the overview and stop it. And that means that I’ll no longer be charged for the virtual machine but I’ll still be charged for the storage. And then of course if I finished with it I can always delete the virtual machine.

But what would be better for me is to go into the resource groups and go into my virtual machine resource group that was this one. And then I could delete the entirety of the disks and the SQL virtual machines and the security groups and other things that the computer has created. I don’t have to delete them one by one. I could delete the entire resource group on mass.

So in this video we’ve had a look at how we can get into our virtual machines. So I just search for virtual machine in the portal to get to the page and then I can connect using whatever protocol I wish to do. I could also use Connect on this left hand side as well. And you can see there are other things I can do like for instance disks and configuring the size and other things if I so wished. So this is how we can connect to our virtual machine.

8. 52. Adding additional disks to the Azure Virtual Machine

In this video we’re going to talk about disks and how we can add additional disks to our virtual machine. So we can do that in the portal. So here is our virtual machine and we can go on the left hand side to disks. Now, you may remember that I created four data disks. Here they are. And what the computer has done is it’s taken those four disks and joined them all together. If I go back to my remote disk desktop connection, we have got the Server Manager. So this is the program that opens by default when you log in. If I go into File and Storage Services and then Storage Pools, you can see that I have got a storage pool of four terabytes. Indeed. If I open up Windows Explorer, open up this PC, you can see that I have got a data here of four terabytes available.

So what the computer has done is join them together in a single virtual disk. Now, it may be that you need to expand your virtual machine with additional disks. So I’m going to create and attach three new disks and you can say what sort of size you want. I’m just going to give it 128 gigabyte size for what I’m doing here each. And you can say whether it is a premium SSD, a standard SSD, a standard HDD or an old disk, which is best for IO intensive workloads. So off on the right hand side, you can see the disk caching level. So what is a disk cache? It’s a way for improving the time it takes for reading or writing. So it holds a bit of what is just read, for instance, in its memory. Now, it should be read only for SQL Server data files, as this improves reads from cache, which is much faster than reads from memory.

It should be known for SQL Server log files because the disk is written sequentially and therefore you don’t need to be able to reread it that quickly. ReadWrite caching shouldn’t be used for SQL Server files, as SQL Server does not support data consistency with this cache type. However, it could be used for the operating system drive, the Windows drive, but it’s not recommended to change the OS caching level. Any changes to the disk caching will require a reboot. So you can see all the information that we got here and we can also delete disks if we wish to. So I’m going to click save on that with some names. So I’m going to call this disk one, disk two and disk three.

So now it’s creating the disks and it’s just taken a few seconds for the disks to be created and for the virtual machine to be updated. So let’s go back into the virtual machine. And if I go into Disk Management, we can create and format hard disks. And here we can see our new disks 910 and eleven. Now, suppose that we wanted to join them together. It’s called striping.

So it’s similar to Raid, but what we can use is something called Storage Spaces, which allow us to do it in software. So the advantage of this is to increase input output bandwidth so you can read from one drive while you’re writing at another. So let’s see how we can do this. So if I go into the storage pools and I create a new storage pool. So I’m just going to call this my new storage pool. But there are no available disks that I can use, also known as a Primal pool. So why is that?

Well, even though the computer knows about these, it doesn’t know it in Server manager. So I’ve got to rescan the storage. And when I do, there will be a third line, which is the Primordal disks. Here we go. So these are unformatted disks. So now it’s refreshed. I can create a new storage pool. So again, I’m going to call this my new storage pool. Here is my Primordial pool. I can select the disks that I want to use to create the storage pool and click Create. And you can see the storage pool has just taken a few seconds. So I’ve created the individual disks. I’ve created a storage pool and now the only thing I need to do is create a single virtual disks. So I do that by right and clicking on my new storage pool, going to new virtual disk and I’m going to select my new storage pool for that.

Give it a name. So this is my striped disk and go down to the storage layout. And this is the key thing that I want to talk about in this particular video. So we have got three different types of layout. So first of all, we have got simple. Now, this needs at least one physical disk and it stripes the data on those physical disks. It maximizes disk capacity and increases throughput. However, there’s no resilience. So this does not protect you from a disk failure, so you can use it for high performance. When resiliency is not required for Striping, then we have got a mirror. This requires at least two physical disks to protect from single disk failure. So you need two or three disks to create the two or three copies of your data. It increases reliability but reduces capacity.

So you use it for greater data throughput and for lower access latency and Emira is probably suitable for most deployments. And then finally, there’s Parity. Parity requires at least three disks to protect from a single disk failure and seven to protect from two disk failures. So it strikes data and parity information across the disks. So what is parity information? Well, it’s a further piece of information to make sure that the first two pieces of information are correct. Like, for instance, suppose we had the number four in one disk and the number six in another parity could be ten.

So if the parity is not equal to the original data, four plus six, then you know that something is wrong. So that’s parity information at a very basic level. So it increases reliability but reduces capacity and performance, so increases resiliency. And you use this for archive and backup. So you select whatever layout you want, and then you can provision it. So select a size, maximum size, and then you can create it. So this is how you can create a striped disk using storage spaces on your Azure Virtual Machine. And this also creates a volume as well. So you create the individual disks. You can do that in the portal. You create a storage pool. And then you can create a single virtual disk, which could either be simple mirror or parity. And then you go and create a volume. So this is how you can add additional disks to your Azure Virtual Machine.