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

5. 11, 53, 56. configure SQL Server in Azure VMs for scale and performance – Part 1

In this video, we’re going to start configuring SQL Server in Azure Virtual Machines. So let’s go to Azure SQL and click Create. And on the right hand side we have got SQL Virtual Machines and you can see that there are a lot of different images that we can use. So you can go all the way back to 2008 R Two if you so wished. You’ve also got the choice of Windows and Linux, and which variant of Linux you want, which flavor. Now these aren’t the only things you can have a look at. If we go back home and have a look at Create A Resource, then if I type in SQL Server, you’ll see that at the bottom there are some 24 pages of different types of images, some of which are relevant, some of them perhaps less so. But you certainly have more choices than what is there on this initial page.

So what I’m going to do is use the Developer Edition. So the Developer Edition is for testing and development, essentially purposes only, it’s not for business use. So in other words, you can’t actually host your real data on there. It is like a test environment. So I’m going to install it on Windows Server and click Create. Right? We need a new resource group.

So this is going to be my Virtual Machine and a Virtual machine name. So I’m going to call this DP 300 virtual machine. Now, this region is actually more important than you might think because of these availability options. If I click on the availability options as is with the region being West US, you’ll notice that two of the available options here are not actually allowed and that’s because they are not available for the location you’ve selected.

So if you decide you want Availability Zones or Availability Sets or a Virtual Machine Scale Set, which means multiple virtual machines across Zones, you need to make sure that you get the right region. But why would you be interested in having Availability Zones or Availability Sets? Well, let’s go back to this service level agreement and you can see that if you have got no Availability Set or dedicated Horse Group, then your maximum SLA is going to be three nines. If you have got an Availability Set, then you have got three and a half nines needed to say you need at least two or more instances, not just one instance in an Availability set, that doesn’t actually do any difference.

However, if you’ve got two or more in Availability Zones, then you can have up to four nines. Now, there are other things to consider and we’ll have a look at those a little bit later. So in the West US can’t do this. So if I go to the UK South, then I could have availability zone and availability sets if I wished. And if I had an Availability Set, then I would say, okay, what set do I want? If I want availability zone. I would say which zone number? One, two or three? I’m going to say no infrastructure required for redundancy. So we’ve got this image and if I want to change the image and I can click See all images and that takes me back to the marketplace. So let’s not do that.

Now the next thing is the Spot instance. Do you want to create a Spot instant? And that basically means do you want a discounted rate for using unused Azure capacity? So you might have to have some infrastructure loss. When Azure is on the heavy intensive side, it might just say it’s going to prioritize people who actually paid the full rate rather than you. So you could say yes, you could say no and you can say, okay, what do I want to do? Do I want to say this is going to be my maximum price to pay and if it goes above that, then the virtual machine can be evicted or do I want to have a price or capacity? So I’m not going to do any of those. Next is the size.

And click on see all sizes. Now the best practice is to choose at least four vCPUs. So we’ve got that for instance in B four Ms or D four SV three. Now these are general purpose ones, so let’s have a quick look at the different series that you can have. So the D series is for general purpose. It’s got a balanced CPU to memory. It’s good for testing and development, small to medium databases or traffic web servers. We’ve got Compute optimized so high CPU. So these are your B series. It’s good for medium traffic web servers, network applications, batched processors and application servers. We’ve got memory optimized, high memory up to four terabytes.

So this would be ideal for SQL Server. It’s good for relational database servers, medium to large caches, and in memory analytics, you’ve got GPU, specialized virtual machines, heavy graphic rendering and video editing, as well as model training and inferencing with deep learning. And you’ve also got entry level workloads which are best for development and test. The best practice for virtual machine with SQL Server is a memory optimized VM. So we see that in the eseries. So for your high memory needs, you can have a higher memory to VCO ratio for mission critical or data warehouses. So what I’m going to do is choose this one, which has got four vCPUs and 32 megabytes of Ram. And if you wait quite a long time, you see the cost, click select and it should be some 300 $350 per month.

Obviously for this testing purpose, you can go down to two CPUs, you can have a D or a B instead of an E. It’s entirely your choice. Next we’re going to have an administrative account. So I am going to have a similar password and username to the one that I’ve got for the managed instance, just so I don’t have to remember lots of different types of credentials. Ideally, of course, you would have different credentials.

Then we have a look at the inbound port rules. I’m going to select what is there. So I have an RDP, a remote desktop protocol. I can also choose other things if I so wish. I don’t need to do that. If you’ve got an existing Windows Server license, you can save up to 49%, and that is what is on the basic section. So we’re going to have a look in the next video at some of the other sections, starting with discs.

6. 11, 53, 56. configure SQL Server in Azure VMs for scale and performance – Part 2

In this video we’re going to have a look at some of the other options you can have. And we’re going to start with disks. So the operating system disk type could be premium SSD, standard SSD, so solid state drives and heat DD. So high definition drives. And notice the high definition drives. If you don’t have availability sets or availability zones, then it could be out of action one out of 20 times. So that is not a really high ratio, that is half a nine. So you’re looking for at least two and a half nine for standard SSD and three nine for premiums. So I’m going to keep it as a premium SSD and just use everything else as here. Now, talking about disks, if we go into the SQL Server settings, we have storage here and we can see that there is storage for your data storage, for your lock storage and your Tempdb storage. So best practices for your data storage is to have S 30 or S 40 drives for cache support.

So I’m going to have four of those for my particular image, then log storage. So again there is a recommendation P 30 up to P 80. Or you can use Autodesks for sub millisecond latency. I’m not actually going to be using them on this, I’ll just stick with the standard S 30. The Tempdb should be stored on a separate local SSD drive on your D drive. So that’s the best practice. So I’m going to say OK to that. So you can choose whether to have SQL authentication.

I’m going to say yes please. And again I’m going to be using my standard credentials. And as your key vault integration, I’m going to say no for now. If you have an SQL Server license, then you could additionally save more money. There is automated patching availability if you want it and you can add automated backups as well. So I’ll be disabling that for now. So scrolling back to the top in networking, there will be a virtual network created for you. So I’m just going to leave everything here as is. Again, this is just a test machine, so I’m going to allow all IP addresses to access my virtual machine. But this course is not about a detailed security for your virtual machines.

The DP 300 exam. So we got Azure Security Center, which we can have lots of different things. So you can enable Auto shutdown. So that could be useful, especially in a test machine. And you can say I’m going to shut it down at a particular point and receive an email notification. You can enable hot patches, disaster recovery and quite a few other things in here. The advanced that you probably won’t need to go into on Towel, you really do have a need for it. So that is some of the other options here. So I’m going to now click Review and create. So again, we’ve got this big warning we have set the Remote Desktop Protocol ports open to the Internet, so we have a charge of about 45.

So that’s about five and a half year. So you might want to consider whether everything here is what you want. And if it is, then we click Create so you can see that the deployment is being initialized and it is now in progress. And it may well take less time than the managed instance to set up. So while it’s deploying, here are some best practices for SQL Server. On a Geo virtual machines, you should enable database page compression where appropriate.

You should enable backup compression and instant file initialization. You should limit auto growth and disable auto shrink. So we’ve spoken about disabling auto shrink auto growth limitation. While you probably don’t want it to go out of control, you should use one temp DB data file per core up to eight files. You should apply any cumulative updates for your version of SQL Server. So if you got SQL Server 2012, then you should have the latest version of SQL Server 2012, say Service Pack Free.

You may wish to consider registering with the SQL IAS Agent extension. We’ll have a look at that in a later video. And you should enable auto shutdown for development and test environment. So I’m going to let the deployment continue and in the next video we’ll open up our virtual machine.