Here’s some brand new content from the SoftwareArchitect.ca YouTube channel that you might find interesting.
In this video, I create a SQL Server 2016 database running inside a Windows virtual machine, using Microsoft Azure. You can see everything that I do to create it, the settings I chose, and I explain why.
SQL Server in a VM allows an easy transition from hosting your own SQL Server, with an easy migration path to the cloud.
Quickstart Series: Windows Web App in Azure
In this video, I show you how to create a Windows Web App in Microsoft Azure. Windows Web App. We go through the Azure Portal, and see how form fields get filled in. If you can’t afford to create resources in Azure, but want to see how it’s done in 2018, this is the video for you.
Or you can see the video directly on YouTube.
Transcript:
Hi there, this is Scott Duffy from SoftwareArchitect.ca. In this video, we’re going to create a SQL server running in a Windows Virtual Machine in Microsoft Azure. I’ve logged into the Azure portal. I’m going to go to the new resources, the plus sign at the top left. Now I’m going to search the marketplace for SQL Server. I know that I’m looking for the developer edition, which is the free edition. You can see that we have a number of options, but we have the free license of SQL Server 2016, on Windows Server 2016. That’s good enough for me, so this is the one we’re going to select.
We’re always going to deploy new resources, in the resource manager model, there’s really no reason to deploy to the classic model unless you have a whole bunch of classic resources, and you need to maintain that. So the resource manager model is the way to go. Now I got to create a name for the virtual machine, so I’m going to call it aznewsjdvm. I have to give a username. Now this username is what I’m going to be using to log into the virtual machine using Windows Remote Desktop. I’m going to use my existing subscription, which happens to be pay-as-you-go. If you’re running in the free account, you may have a free subscription, if you’re running MSDN, or you’re Enterprise, you’ll have a different value.
I have to create a resource group. Out of laziness, I’m going to create the same name as the virtual machine, and we get to choose where in the world we want our virtual machine. Everywhere from Korea and Japan, Europe, India, and Canada, and United States. So I’m going to put this in Central US. I do have the choice of using an existing Windows license, but I don’t have one, so I’m going to say leave that at no, and say okay.
Now Microsoft has come out with this brand new list of virtual machine sizes, so I like it a lot. I think it’s easier to find virtual machines that fit the needs that you’re looking for. For instance, I know I don’t need anything more than four CPUs, and so I can just drag down this slider and get between one and four CPUs. I can also choose everything from general purpose, memory optimized, storage optimized servers. So maybe for a database server, a memory optimized server would be more useful. Then we can see here that the pricing, just sort of eyeball it down from 200 dollars, 400 dollars, a thousand dollars, seventeen-hundred dollars, et cetera. Now because my account is … I have not upgraded my account, that there are some sizes that are just not available to me, if I said, the mouse over says this size currently unavailable in this location for this subscription. So I would have to open a support ticket with Microsoft and get them to open this up if I wanted the GS size, specifically.
So I want to pick one of the DS sizes. So I’m going to choose DS 11, which is two CPUs, 14 gigabytes of RAM, and eight data disks. Now there is a cost. You see that it runs around $300 a month, but this is just a test, and it’s not going to last more than a day or two, so it’s going to cost me about $10 to do this test, if I leave it all day. I’m not going to set it up in an availability zone, or using availability set right now. This would be more for if you’ve got high availability needs, you need to have multiple sets of VMs running in availability set. I’ll let them use the manage disks, otherwise I’m going to have to choose storage sizes, and manage that. I’ll just let Microsoft take care of the storage stuff for me. The network is set up as a new network for me, so if I had an existing virtual network, I could install this virtual machine on it, but for this testing purpose, I don’t mind leaving the defaults here.
You’ll see that I have an option for accelerated networking, and that is the high performance, high throughput networking option, that’s actually enabled for this DS 11. I’m not going to install any extensions for the virtual machine. I could use an extension for metrics, guest operating system metrics, et cetera. Well you see I could just turn it on right here. I do want the auto-shutdown, so it’s around, let’s see, what time is it … it’s around 9:00 right now, so why don’t I set this to turn off at eleven. I’m going to tell it my timezone, and so in two hours from now, this machine will shut down by itself, and I don’t need to be notified. We’ve got monitoring, boot diagnostics, and guest OS diagnostics enabled. The guest OS diagnostics is to give you more than just CPU disk rights and network speed. If you want any more performance diagnostics, you have to turn this on to get that service into Azure. It’s going to create a new storage account for my diagnostics. I am not going to hook it up with Azure Active Directory. I’m going to say okay.
Now we’re getting into the SQL server settings. You’ll see here that this image that I chose, the virtual machine image, has the SQL server built in. I could have chosen a Windows virtual machine, and installed SQL server using a downloadable image, et cetera, but this is way more convenient. Now one of those first decisions I get to make is how secure I want this to be. I could turn off all outside connection from the machine, so that no other machine can connect to this SQL server, I can allow only virtual network, connect computers to connect to it, or I could open it up to the public, which includes myself, and my own computer. I’m going to leave this open to the public. This is typically not a great idea, and you’re going to want to set very specific network security group security on this, maybe specific IP address, or a specific range, but this is just a temporary test. Like we said, it’ll only last for a couple of hours.
We do need to enable SQL authentication. That’s the only way that we can log into SQL server over the open internet. It does not support Windows authentication over the internet. So that’s turned on. It’s going to take my Windows username and password as the username and password for SQL authentication. We can leave the storage options, the way that it does patching, backups, Azure key vault, et cetera. R Services, which is advanced analytics, typically used in big data, et cetera. So I’m going to leave all the defaults from that point, and say okay.
Now Microsoft Azure will do the final calculations. It’s telling me that a standard DS 11 V2 plan, with the free version of SQL server, developer edition, will run me 40 cents Canadian per hour. So I know that I’m incurring this charge, as soon as I start it up. This 40 cents per hour is charged to the nearest minute, so rounded down. So if I run it for even just for five minutes, I’m going to get four or five cents charge for this configuration. I can now say create, and Microsoft will go off and create me a new virtual machine running SQL Server 2016, running Windows Server 2016. I’m going to pause the video, and we’ll let this machine boot up.
All right, well that took a few minutes because Microsoft Azure has a bunch of things to do. Actually, if I go up to the resource group, you’ll remember that we created this resource group brand new, and now we can see that it’s actually created 11 resources within that resource group, so besides the virtual machine itself, it’s created a couple of disks, it’s created the network interface, a storage account for diagnostics, and IP address, and all that wonderful stuff. So let’s go into the virtual machine. We can see here, on-screen, that it has a public IP address. Now it is a SQL server, so I would have to connect to it using SQL Server’s management studio, which I can do, and we can see the overview here, with CPU, Network. This is a fairly powerful box, I mean it’s two CPUs, not a screaming powerful box, but 14 gigabytes of memory. So it didn’t take too long to start up. The CPU is running around 50% average, in this time, so it’s probably still doing some stuff, but right now it’s down in the 6% range.
So let’s connect to this using SQL Server Management Studio, and show you that we have a database now, running within Microsoft Azure. So I started SQL Server Management Studio on my local machine. This is something that you do have to download and install, if you don’t have. I entered the public IP address of this server, and the username and password based on SQL Server authentication, so I should be able to click connect, and you can see here that it’s connected to the machine, and it successfully logged in. Now I don’t have … this is a brand new machine. There are no user databases. Only the four default system databases that we all know and love. So this is a machine ready for me to start developing against it, or to import my database from another server, et cetera.
So that’s how you create SQL Server, running within Microsoft Azure, using a Windows Server 2016 virtual machine. Hopefully that was interesting to you. You will see that it wasn’t too difficult. The only painful part, if anything, is having to pay for it, because like we said, it’s going to cost me 40 cents per hour, and that’ll add up to a couple of hundred, almost $300 over the course of a month, so this is for a business purpose, hopefully, and you’re going to save money by not having to purchase a machine, not having to purchase SQL Server, and have that running within your own hosting environment.
