Deployment choices for IaaS
This section will cover the different ways in which you can deploy a VM in Azure, along with considerations and best practices that are applicable no matter which method is used, as well as briefly talk about licensing SQL Server in Azure.
Deployment methods
There are three options for deploying a VM in Azure for use with SQL Server:
- Choose a pre-built template, also known as an image, with SQL Server already installed.
- Choose a template with the OS but without SQL Server installed, which will be done after the VM is built.
- Build a custom image using a virtual hard drive and upload it to Azure.
No matter which of the three deployment methods you choose, you can deploy using Cloud Shell (Bash or PowerShell)6, also known as the Azure command-line interface (CLI), Azure portal7, Azure Resource Manager templates8 that are written in JSON, and the Azure PowerShell module9. Builds can be automated and incorporated into DevOps processes, which is often a best practice in many environments.
The remainder of this section will describe each of these methods in further detail.
Using an image with SQL Server installed
Microsoft provides images in Azure that have SQL Server already installed. Using one may save time and effort and provide a level of confidence in the overall configuration. The OS, versions, and editions of SQL Server available can be seen by querying Azure or looking in Azure Marketplace using the Azure portal. One example query using the Azure CLI that shows all Ubuntu images in the East US 2 region with SQL Server Enterprise Edition, as of the time of writing of this chapter, is as follows:
az vm image list --location eastus2 --offer Ubuntu --publisher SQLServer --sku enterprise --all --output table
The results are shown in Figure 2.1. A similar query could be executed for Windows Server or any other distribution of Linux or edition of SQL Server:
Figure 2.1: Ubuntu 16.04 images preconfigured with SQL Server
Note
There are multiple versions of SQL Server for a given major release such as SQL Server 2019 (15.0.x). Each corresponds to the build number of SQL Server. If your company has a requirement for a specific build of SQL Server and it matches one of these images, you can use it. If not, then you will need to utilize the second or the third method mentioned previously, either by deploying a VM without SQL Server pre-installed or by creating an image from scratch.
When using the Azure portal, the Azure Marketplace defaults to using the latest image version. This means that for older versions, such as the ones shown in Figure 2.1, you would need to use another method, such as PowerShell or CLI, to deploy that image.
The easiest way to find an image is to search for it in the Azure Marketplace in the Azure portal. For example, you can search for SQL Windows 2019 and filter further as desired, as shown in Figure 2.2. Note the limited choices as compared to Figure 2.1. In some cases, the image available will be a specific version and/or edition of SQL Server with an OS, while on other occasions, it will be a major version of SQL Server with an OS:
Figure 2.2: Windows Server 2019-based SQL Server images in the Azure Marketplace
Depending on the image selected, you may also have to specify the edition of SQL Server as shown in Figure 2.3:
Figure 2.3: Choosing an edition of SQL Server
The Azure portal allows you to configure a VM's settings manually when you click Create. You will be walked through a wizard via a series of panes where you configure storage, networking, and, for Windows Server-based configurations, aspects of SQL Server itself. Most IT organizations will choose this if using the Azure portal or automate.
You also have the option to Start with a pre-set configuration, as shown in Figure 2.3. An example of what that would look like can be seen in Figure 2.4. You still need to go through the rest of the configuration process, but what you would need to alter is much less. Full documentation can be found in the topics Provision a Linux SQL Server virtual machine in the Azure Portal10 and How to provision a Windows SQL Server virtual machine in the Azure portal11.
Figure 2.4: Choosing defaults
One difference to note is that, unlike a Windows Server-based SQL Server Azure VM image, there are fewer SQL Server options available during configuration. With Linux, you can only choose the core aspects of the VM. Anything related to SQL Server, such as changing default data paths, is configured inside the VM once deployed. More information on the differences compared with Windows Server can be seen in the upcoming section, SQL Server on Azure Virtual Machines resource provider.
For Linux, the image with SQL Server pre-installed contains the Database Engine, SSIS, and the command-line tools (sqlcmd and bcp). The end user license agreement (EULA) is already accepted. SQL Server Agent is not enabled. SQL Server Agent along with any other SQL Server features must be configured12. You will also need to reset the sa password, which is set during configuration and not published, and, if desired, add the tools to your default path.
This means, for Linux-based images, that you need to do some configuration after the VM is built, but not as much as if you had to install SQL Server yourself using an image with just the Linux distribution that is described in the next section.
Windows Server-based images with Developer, Enterprise, and Standard Editions come with many SQL Server options pre-installed: The Database Engine (including replication and R services, full-text search, and Data Quality Services), Analysis Services, and Master Data Services. If you want to change the configuration, the full installation media can be found on the local system drive. An example would be if you are not using Analysis Services; you can simply uninstall it.
Even if you plan on uninstalling SQL Server for reasons such as needing to configure an Always On Failover Cluster Instance (FCI), if you are using pay-as-you-go licensing (refer to the upcoming section on licensing for more information), using an image with SQL Server already installed gets you not only set up but also gets you the license(s) required.
If the Marketplace images with SQL Server do not suit your needs, consider an image with just an OS or build your own.
Using an image without SQL Server installed
Instead of using a template with the OS and SQL Server already installed, you can choose one that has the desired OS only. Choosing an image with just the distribution of Linux is similar to what was described in the previous section.
There are a few reasons why you may choose to install SQL Server yourself. Three example scenarios are listed here:
- An image has something desired, such as the high availability add-on in RHEL needed for both FCIs and AGs already installed.
- A pre-built template with your desired version of SQL Server does not exist for the target OS, which has an image. For example, as of the time of writing this chapter, no RHEL 8 template is pre-built with SQL Server 2019. Another example would be if your corporate standard was a specific version or a build of Linux such as RHEL 7.5, which is supported for SQL Server but there is no pre-built image.
- Your company has other specific requirements or challenges for a build that are not met by any existing image, but a base OS provides a starting point. An example would be SQL Server 2019 running on Windows Server 2016.
Figure 2.5 shows an example of selected RHEL images available in East US 2 that are only an OS or a special variant:
Figure 2.5: Selected RHEL images
Once the VM with the chosen OS is built from the image, install SQL Server using the instructions found in the topic Installation guidance for SQL Server on Linux13 or SQL Server installation guide14 (for Windows Server-based VMs) and configure the instance of SQL Server to how you need it.
If a base OS image will not work, you still have one choice: create your own.
Creating your own image
To configure a custom hard drive image for use in Azure, follow the documentation linked below for each of the OSes supported by SQL Server:
Generic information that applies to the three Linux distributions for creating a custom hard drive can be found in the documentation topic, Information for Non-Endorsed Distributions19.
Similar to the previous section, you must also install and configure SQL Server in the OS.
SQL Server on Azure Virtual Machines resource provider
The SQL Server on Azure Virtual Machines resource provider is one way in which Microsoft makes deploying in Azure easier for administrators. This feature is only available for Windows Server-based VMs and can even be enabled if you deployed SQL Server yourself and did not use a Marketplace image pre-installed with SQL Server. To see how to perform this task, consult the following documentation: Register a SQL Server VM in Azure with the SQL VM resource provider20.
The resource provider allows you to configure certain aspects of SQL Server, but, more importantly, also tasks such as backups that Microsoft can do for you instead of you having to configure it all inside the VM. An example is shown in Figure 2.6:
Figure 2.6: Backups in the SQL Server resource provider
Other things that you can configure include security and patching. For patching, Microsoft will only apply updates marked as important, such as security updates. You will still have to apply things such as SQL Server Cumulative Updates, but you can specify the day, time, and maintenance window duration for applying important updates.
Common Azure VM deployment considerations for SQL Server
Last, but not least, there are some common considerations and best practices when deploying IaaS VMs in Azure for SQL Server, whether they are Linux- or Windows Server-based.
It is not recommended to assign a public IP address unless necessary as this exposes the server directly to the internet. VMs should be created on virtual networks that are private and accessible by authorized personnel. If connecting via on-premises to Azure, the assumption is that Express Route or a private VPN will be used so that the VM will be seen as if it was on-premises.
- If needed, open ports for accessing the VM, such as RDP (3389) or SSH (22).
- For most implementations of SQL Server, use a single virtual network interface card (vNIC), which is the default configuration. One vNIC is not a single point of failure as the underlying Azure network infrastructure is highly redundant and there are means such as Availability Sets and Availability Zones to ensure that VMs themselves will not be single points of failure.
- The VM relies on core elements of infrastructure such as DNS, to work properly. Even in a hybrid solution that would span on-premises and Azure, ensure that those key elements exist both on-premises and in the cloud.
Licensing SQL Server in Azure
Last, but not least, a major consideration for how you deploy in Azure relates to cost. All VMs deployed in Azure must be properly licensed even if what is inside is technically free. There are two aspects of licensing that must be accounted for: the OS and SQL Server.
Your choice of supported Linux distribution for SQL Server will dictate whether you need a paid license. For example, RHEL does require a license. All of the options are documented at the Red Hat on Microsoft Azure21 page on Red Hat's website. Consult the Red Hat Linux Enterprise Server section at that link. Windows Server always needs to be licensed.
When it comes to SQL Server editions, Standard, Enterprise, and Web editions always require a license. SQL Server Developer and Express are technically free, but do have a license, and there may be restrictions regarding use. For example, Developer edition cannot be used for production workloads.
There are two models for licensing SQL Server in Azure: bring your own license (BYOL), or pay for the license as part of the cost of the VM, known as pay-as-you-go. For pre-built images with SQL Server, both options are often feasible. As of the time of writing this chapter, BYOL images are only an option for Windows Server-based SQL Server images.
If you are migrating to Azure or have unused licenses and also have Software Assurance, the Azure Hybrid Benefit for SQL Server allows you to use existing on-premises licensing and apply it to an IaaS VM. This can potentially reduce the cost of an IaaS VM.
There is a new Azure-specific licensing benefit introduced with SQL Server 2019 if you have Software Assurance: the ability to run a VM that has a standby server for disaster recovery. For example, if you have an on-premises Always On availability group and want to add an asynchronous replica in Azure, it is now free. This could represent a significant cost saving and can even be seen in the Azure portal, as shown in Figure 2.7:
Figure 2.7: Licensing info from the Azure portal for a Windows Server-based VM
Full licensing information for SQL Server can be found on the Microsoft websites for on-premises pricing22 and SQL IaaS pricing23, as it will factor into the VM hardware choice and its cost since each SQL Server VM is usually licensed per virtual processor. More on choosing a VM size is covered in the next section.