How to Build Your First SQL Server Virtual Lab in Windows Azure

If you are a DBA who hasn't so far dived in head-first into using Azure, it is worth setting up an Azure 'Virtual Lab' environment the easy way, using a template. This will then allow you to experiment, try things out with SQL Azure, and get familiar with Resource Groups. Joshua shows how to build a virtual lab, from the ground up in the first of a series that aims to give you a grounding in Azure.

In this article, I’m going start off by introducing you to some key concepts that you’ll need for a basic understanding of how to use Microsoft Azure in your role as a DBA: Then we’re going to go over the basic foundation of Azure environments, namely Resource Groups. Finally, we’re going to actually build a virtual lab, from the ground up, using the powerful template-based deployment techniques that Azure makes available.

For many DBAs out there, Azure is a bit of a mystery. It’s still fairly new after all, and has really matured only in the last few years. Unless you’ve had experience with other cloud based platforms (like Amazon AWS for example), there’s going to be a quite a few moments where your head might turn just a bit sideways, like a dog slightly confused at what the human standing in front of it just said. But really, that’s expected and you shouldn’t let it discourage you. We’ve all been through it. Rather, you should take this as motivation to get your hands dirty, learning by doing, which is exactly what this article will help you do.

There are a lot of places where managing SQL Server in Azure is quite different from traditional on-premise (or even datacenter-hosted) installations. To name just a few:

  • You cannot attach multiple IP addresses to a virtual machine network card (NIC). (Well, you can, but the others won’t work.)
  • When building Windows failover clusters, you cannot contact the cluster remotely.
  • There is no concept of shared storage, except what is provided by third party software, such as SIOS DataKeeper.
  • Your alternatives in terms of storage allocation and performance are limited in several ways. For example, performance is throttled at both the storage account level (basically a container for storage objects) and the virtual machine level: Depending on the size of the virtual machine, there are limits on such things as total throughput and IOs per second.

My point is that a lot of things work differently in the Azure environment, and the best (and really only) way to gain a deeper understanding of them is by having a place in which you can safely explore the technology.

Fortunately, with a little help and guidance, it is extremely easy to get a SQL Server lab setup in Azure. So, let’s get started.

Building your first Azure lab environment

In this section, we are going to go through the steps required to get your first Azure virtual lab up and running. First, we’re going to sign up for a subscription (if you don’t already have one). Second, we’ll go through a number of the kinds of resources available within the Azure environment, so that we can understand how each works. Finally, we’ll create our first Azure lab using the powerful template-based functionality available to us in Azure. Let’s get started!

Creating your first subscription

The first step to becoming confident with Microsoft Azure is to sign up for an account on the service. At the time of writing this, Microsoft offers an option for a “free” month when you initially sign up, which you can do by visiting https://azure.microsoft.com/en-us/free/. I put “free” in quotes, because technically what they actually give you is a $200 credit towards whatever resources you want, good for up to one month. After this, you will begin paying for resources at the regular cost. My suggestion would be to try and get your employer to cover the cost of the subscription as a personal development tool. If not, I still believe that the cost is worthwhile, given that Azure is going to be a huge player in the cloud computing marketplace in the coming years.

Keep your costs low by not running resources 24×7

If you don’t want to end up spending a good bit of money on your lab every month, make sure that you shut down all virtual machines and minimize resources when you’re not actively using them. With Azure, and many cloud based platforms, you’re only charged for what you use, so if a virtual machine isn’t actively running, you’ll only incur costs for things like storage (which can still be high if you use premium storage, so be careful). But be careful, simply shutting down a VM from within the operating system doesn’t count. Instead, actually stop the VM through the Azure portal or by using the Stop-AzureRMVM Powershell command.

If you already have an MSDN (now called Visual Studio) subscription, then you qualify for getting a certain amount of credit each month. You can also sign up to receive special rates on resources used for testing purposes as well. To learn more visit this link: https://azure.microsoft.com/en-us/pricing/member-offers/msdn-benefits/. You can also get a $25 per month credit simply by signing up for a free Visual Studio Dev Essentials subscription, at https://www.visualstudio.com/dev-essentials/.

Tip: Prevent accidental huge costs by setting a spending limit on your account

Depending upon the type of subscription you use, you may be able to set a spending limit on your account. This means that once your resource costs hit a certain threshold, they will be disabled until either (a) your increase your spending limit, or (b) the current billing period ends. To see if your subscription has this feature, see the documentation at this link: https://azure.microsoft.com/en-us/support/legal/offer-details/.

Logging in to Azure

Before we can actually do any work, we need to log in to the Azure portal, at https://portal.azure.com. Use the same Microsoft account that you used to create your subscription.

Creating your first Resource Group

All resources in Azure are grouped into sets called, well, Resource Groups (hey, at least it makes sense!). Before we go into actually creating the resource group, it’s important to understand a little bit how they work.

When you build infrastructure in Azure, it’s wise to think ahead of time about how to group components together. If done properly, this makes it easier to manage your resources, understand and break down costs, and repeatedly deploy related sets of infrastructure. To better understand this, let’s give an example.

Say you have a particular application that requires the following components to function:

  • A SQL Server
  • 2 Web tier servers
  • 1 Application server

Some additional details:

  • The Web tier servers must reside in an isolated network area from the SQL and application servers, with limited access between them.
  • The Application and SQL servers must be placed in the same network zone (which can be shared across customers).
  • The Application and SQL servers must be on a Windows Active Directory domain, which already exists (and has two or more Domain Controller VMs).

Let’s imagine that you need to replicate one set of the items listed above for each customer using this application. With each new customer, you’ll need to create the three servers listed above, as well as all the required post-configuration work. In addition, the customer is billed for the cost of these three servers, plus a markup and management fee. Finally, the size of these VMs may vary depending upon the volume and size of the customer’s work.

Given these requirements, what is the best way to group the resources for each of the customers?

Is it:

  1. Place each VM in its own Resource Group
  2. Place all VMs in the same Resource Group
  3. Place each set of VMs related to a particular customer in the same Resource Group

The answer should be fairly obvious; (c), or “Place each set of VMs related to a particular customer in the same Resource Group.” By doing this, we make it easy to report on the cost of the entire setup for a given customer, as well as making deployments easier.

The first step in creating a resource group is to choose the location that you want to create it in. Azure (as of this writing) has 17 locations (not including special “Government only” datacenters) in countries as wide ranging as Hong Kong, Singapore, Ireland, Canada, and of course the United States (with a country leading 8 locations). For now, choose a region close to where you live, so that connections over remote desktop or other protocols don’t have far to travel. For a list of the Azure data center locations, see https://azure.microsoft.com/en-us/regions/.

Once you’ve chosen your region, we then need to create the resource group. This can be done by following these instructions:

  1. Click on the “Resource Groups” tab on the left hand navigation bar.
  2. Click the “Add” button.
  3. Enter a name for the resource group, and select a region that is close to you (so that latency over remote desktop sessions is tolerable).
  4. Click the “Create” button at the bottom of the screen.
    Now Azure will do its magic behind the scenes and create the group. Within a few seconds, you should see a notification letting you know that the group has been created.

Resource groups make it easy to clean up

If you’re going to experiment with a bunch of resources that you’ll probably be deleting later, make a new resource group and put them all in there. That way, when you’re finished, you can simply remove the resource group, and all resources will be deleted along with it.

Now that we have our resource group created, let’s explore a few of the resources we’ll be provisioning as part of our lab.

In total, the provisioning process will accomplish all of the following tasks:

  • Create a new Azure virtual network, along with a single subnet.
  • Create a Network Security Group (which is basically a firewall group), along with a rule that allows inbound connections on Remote Desktop from any originating IP address.
  • Create a new virtual machine, install Active Directory Domain Services, and provision a new domain forest.
  • Create two new virtual machines based off the Azure image that has SQL Server 2016 Developer Edition pre-installed. These VMs will include a separate data disk, which will be pre-formatted and assigned a drive letter. In addition, the virtual machines will be joined to the domain that we created in the step preceding this one.
  • Create a new Azure load balancer for use later in our work.

There are several ways we could accomplish these tasks. First, we could provision the resources by hand (either through the Azure web portal or by using Powershell), then configure them manually (including connecting via Remote Desktop and working on the operating system directly). However, this route is perilous at best for several reasons. First, we are all human, and no matter how carefully we may try, we are prone to mistakes. Sure, you could (and should) document every step of your build process, but even in the best case where are no mistakes, we will still need to spend a great amount of time and effort to complete our build.

The second method involves use of a powerful feature in Azure known as “ARM Templates”. In this approach, we effectively describe the component of our deployment using specially constructed JSON files, and the Azure platform takes care of the rest. Thanks to integration with other Microsoft platform technologies, templates allow us to automate not only the basic provisioning of resources, but also complex configuration exercises. For example, we will use one of the built in extensions within Azure to automatically join the virtual machines to the domain once they have been created. Because templates also support dependencies between resources, we can ensure that the SQL Server virtual machines are not created until after the domain is fully configured and available.

To begin the deployment process, we need only to go to a specially constructed URL, which lets the Azure portal know that we would like to use a publicly accessible template file. (A template file, as we’ll learn in a later article, is just a text file with JSON in it defining all the resources to be created.) Normally you would probably want to secure your templates, but since this is just a lab, I’ve made the template I’ve used freely available. All you need to do is follow this link: https://sqljosh.com/azure-sqlserverlab-template. This will bring up a screen similar to the one shown below, in which we’ll enter a number of pieces of information.

Here’s how you need to fill in each of those fields.

Field name

Description

value

Subscription

The subscription to create the resources in.

Leave the default selected.

Resource Group

Let’s you select the existing resource group to place the resources in, or create a new one.

Select “Create new”, then type in a name for your new resource group. Keep it something simple like “AzureTestLab”.

Location

The location that you wish resources to be created in.

Select one close to you for latency purposes.

Admin username

The name of the local and domain administrator user for all virtual machines to be created.

Enter a user name.

Admin password

The password for the local and domain administrator user.

Deployer initials

Your initials, used for generating unique names for resources.

Virtual Machine Size

The size of the SQL Server virtual machines to create.

Standard_DS1_V1

Leave all other parameters at their default values. Once this is all entered, select the “I agree” checkbox, and click “Purchase”.

Note

Even though the button says “Purchase”, you are not actually buying anything from me! While there is the ability to publish templates in the Azure marketplace and earn revenue from them, this is not the case here.

Once you begin the deployment, Azure will start provisioning the various resources that are required. In the background, the virtual machines, network interfaces (virtual NIC), public IP addresses, and other resources are created. This can take several minutes; if you want to view progress, you can click on the small bell icon in the top right hand corner of the screen, then click on the row corresponding to the ongoing deployment.

If you scroll down to the bottom of the resulting screen, you’ll see a list of resources to be deployed. As they are ready, you will see the icons to the left of them turn green.

Tip: Dealing with failures in deployments

Sometimes parts of the deployment may fail due to transient problems. For example, the extension that joins the VM to the Active Directory domain may fail because the VM didn’t pick up DNS settings properly from the Azure DHCP server (all VMs in Azure use DHCP, even ones with “static” IP addresses). If this happens, restart the machine, and deploy the template again. The beauty is that it will only re-apply anything that failed or still needs to be provisioned.

Once the deployment completes, you can now connect to your Azure virtual machine. To connect, you need to use the VM’s public IP address. Here are the steps to locate this:

  1. Select “Virtual Machines” from the left hand navigation bar.
  2. Click on the “Connect” button.

    This will cause an .RDP file to be downloaded, which you can open. When prompted for credentials, use the ones you provided during the provisioning process.

    Don’t forget to stop your virtual machine if you are not going to actively use it!

Congratulations! You now have a fully provisioned virtual lab in Azure, complete with an Active Directory domain, two SQL Server virtual machines, and various network components: Which begs the question: where to go from here?

First, take some time and play around with things you would normally do, so you can start understanding what works and what doesn’t. For example, you might try setting up a Windows failover cluster between the two virtual machines (hint: it won’t work, at least not immediately).

Second, explore Azure’s extensive documentation library. While it’s not perfect, it is constantly updated and is still the best reference for how to get things done.

In an upcoming article, I’ll go over a few of the various parts of the lab environment in more detail. For now, just enjoy and learn.

Tip: One last reminder

Be sure to shut down and stop your virtual machines when you are done with them, lest you get a very large bill from the folks in Redmond!

  • 9621 views

  • Rate
    [Total: 6    Average: 4.2/5]
  • Andrew P

    Hi Joshua

    Firstly, thanks for writing this – it looks really useful and I would like to use it to learn more about building my own Azure lab. I have a problem that I was hoping you might be able to help me out with? I have followed your instructions up until the point where I have just clicked the Purchase button. At this point, it would be building all the bits from your template I think. However, I get the following error:

    “The template deployment ‘Microsoft.Template’ is not valid according to the validation procedure.”

    and

    “The domain name label AP-labdc is invalid. It must conform to the following regular expression: ^[a-z][a-z0-9-]{1,61}[a-z0-9]$. (Code:InvalidDomainNameLabel)”

    Have you got any idea where I might be going wrong?
    Thanks
    Andy

    • Andrew G

      Also had this problem, don’t use Upper Case initials.. eg. ap instead of AP

    • Joshua Feierman

      Andrew G is correct, you need to use lower case letters for the initial parameter. That should be handled in the template though, which I’ve just fixed.