SQL Server on Azure Virtual Machines

A hands-on guide to provisioning Microsoft SQL Server on Azure VMs

Joey D'Antoni, Louis Davidson, Allan Hirt, John Martin, Anthony Nocentino, Tim Radney, Randolph West

• Publisher: Packt Publishing
• Year: 2020
• ISBN:
  9781800206588
• Edition: 1
• Topic: Informatique
• Subtopic: Bases de données

Learn how to combine SQL Server's analytics with Azure's flexibility and hybrid connectivity to achieve industry-leading performance and manageability for your cloud database.

Key Features

• Understand platform availability for SQL Server in Azure
• Explore the benefits and deployment choices offered by SQL IaaS
• Get to grips with deploying SQL Server on the Linux development ecosystem

Book Description

Deploying SQL Server on Azure virtual machines allows you to work on full versions of SQL Server in the cloud without having to maintain on-premises hardware. The book begins by introducing you to the SQL portfolio in Azure and takes you through SQL Server IaaS scenarios, before explaining the factors that you need to consider while choosing an OS for SQL Server in Azure VMs. As you progress through the book, you'll explore different VM options and deployment choices for IaaS and understand platform availability, migration tools, and best practices in Azure. In later chapters, you'll learn how to configure storage to achieve optimized performance. Finally, you'll get to grips with the concept of Azure Hybrid Benefit and find out how you can use it to maximize the value of your existing on-premises SQL Server.

By the end of this book, you'll be proficient in administering SQL Server on Microsoft Azure and leveraging the tools required for its deployment.

What you will learn

• Choose an operating system for SQL Server in Azure VMs
• Use the Azure Management Portal to facilitate the deployment process
• Verify connectivity and network latency in cloud
• Configure storage for optimal performance and connectivity
• Explore various disaster recovery options for SQL Server in Azure
• Optimize SQL Server on Linux
• Discover how to back up databases to a URL

Who this book is for

SQL Server on Azure VMs is for you if you are a developer, data enthusiast, or anyone who wants to migrate SQL Server databases to Azure virtual machines. Basic familiarity with SQL Server and managed identities for Azure resources will be a plus.

1. Introduction to SQL Server on Azure Virtual Machines

Introduction

In this chapter, we introduce the relational SQL Server products that you can use in Azure to store and process transactional data in a relational format, which is to say data stored in tables and columns. To get started on this discussion, there are a few terms and concepts that are important to understand.

The concepts of platform as a service (PaaS) and infrastructure as a service (IaaS) can be confusing and are used constantly when discussing services that allow you to build software on a cloud service. The fundamental distinction between the two lies in how managed the offering is. A service being managed means that the provider handles some amount of the operation (or management) of the service. When you install an operating system and SQL Server on your on-premises computer, you manage the entire hardware and software infrastructure yourself. This starts with making sure the server is plugged in and everything else moves from there. PaaS and IaaS both indicate managed services because the resources you use will be managed to some extent.

PaaS indicates that you are getting a platform to work with, and more of the management tasks such as software patching, performance tuning, backups, and fault tolerance will be handled by Azure. The goal is to let you focus on providing business value and leave the day-to-day operations to Microsoft. How much of the management is done for you is based on the features of the offering, but as an example, each of the PaaS offerings we will introduce will handle backups of your data without you or your customer thinking too much about it, until you find the need to restore your data.

The IaaS model primarily provides management of the hardware and network. You never need to, and never can, touch any of the physical resources or even access the location of the server. Just like when you get a new computer, an IaaS server may have software pre-installed for you, but once you take over the computer, managing and configuring the software and hardware is up to you. The Azure IaaS platform for SQL Server does include tools to help you automate the management of the software, giving you some of the characteristics of the PaaS model, but these tools will not be as controlled in the IaaS model as they will in a PaaS-model server.

A managed database service does not indicate that the Azure platform will change the meaning of any data or code you have written; in fact, it's quite the opposite. This is still part of what your organization needs to do. No changes will be made to your system that change the meaning of the structures you create. You will simply need to be less concerned with day-to-day processes that are common to pretty much every organization.

This book largely focuses on the IaaS offering using SQL Server on Azure Virtual Machines, though we will introduce the PaaS offerings for contrast. The rest of this chapter will introduce the Azure SQL offerings, the newest features in SQL Server 2019, and the value of using SQL Server in its IaaS configuration.

The Azure SQL portfolio

Microsoft Azure SQL is a modern SQL portfolio of offerings for storing relational data as a service. It is powered by the industry-leading SQL Server engine, which has evolved greatly over the years, retaining backward compatibility at the code level and continuing to provide monumental leaps in performance and storage capacity version over version. Some of the Azure SQL offerings are evergreen, meaning the offering is always up to date with the latest updates and patches. Because each offering is essentially based on the same SQL Server code, database administrators (DBAs) and developers can often use the tools and resources they are already familiar with from their past SQL Server experience, including graphical, command-line, and T-SQL–based tools, for much of the work they need to do.

For many organizations, having to build, house, and manage hardware and software is a large burden for a variety of reasons, but in most cases, cost and security are the most important. Beyond the easily quantified cost of purchasing hardware and software, there are costs in finding qualified persons to manage the hardware, operating systems (OSes), and database platform, all before considering the day-to-day operations such as tuning queries and executing backups.

Using the Azure platform, upgrades to VM type and size can be done by a simple UI operation rather than buying new hardware, configuring it, and migrating all of your data onto it (that process may still occur, but your experience is often checking a box or sliding a slider on a web page and letting the automation do the work for you.)

The second reason is the most important: security. What all the data breaches in recent history have demonstrated is that most databases are accessible from the internet in some manner. Having the management and security of your infrastructure in the hands of a company such as Microsoft pushes the technology burdens of a very large part of securing your data onto them. You can take comfort in the fact that the entire Azure business model rests on the security of all its customers' data, including yours. It will still be your responsibility to build proper security principals with adequate passwords and two-factor authentication, as no security will stop a user with proper credentials from accessing your online resources.

Azure has multiple other database management systems in the Data Platform portfolio for different types of database needs, including Cosmos DB1, and Synapse Analytics2, to name a few. Here's a full list of current products in the Azure family of services3. Note that Synapse Analytics uses relational tables, but it is focused on large-scale, specialized analytics. This chapter focuses solely on the relational SQL Server–based offerings (Azure also has relational database offerings such as MySQL4, PostgreSQL5, and MariaDB6).

The Azure SQL portfolio provides a consistent and unified management experience spanning three SQL Server offerings in Azure, each with its own targeted use cases. Almost any of the offerings will be perfectly acceptable to provide support for transaction processing (commonly referred to as online transaction processing (OLTP)) as well as most analytics (reporting) scenarios. Each offering is positioned to provide different levels of service, as we will discuss. The three offerings we will discuss specifically are:

• SQL Server on Azure Virtual Machines.
• Azure SQL Managed Instance.
• Azure SQL Database.

  Note

  There is one additional method of deployment using containers that we will not be specifically covering in this book. The container method is very similar in functionality to the VM deployment, except the VM is replaced by a lightweight, GUI-free container running Linux or Windows using Kubernetes or Docker.

In the following sections, we will introduce each of these offerings to provide you with an overview of their strengths and ideal usages. Each of the offerings provides you with the same SQL Server relational engine internals for storing and querying data using T-SQL. Each will have the same data manipulation language (DML), with only minor differences in data definition language (DDL) due to physical implementation differences. While some of the management tools and methods supported by each platform are different, the primary difference is based on how managed the service is.

SQL Server on Azure Virtual Machines

SQL Server on Azure Virtual Machines7 (or Azure SQL VMs for short) and indeed any of the Azure Virtual Machines offerings are considered IaaS. This is because Microsoft manages the hardware infrastructure, but you manage the software. As the DBA managing the server, it is generally no different than managing SQL Server on a computer that resides in your own server room.

When you create an Azure SQL VM, you are given the opportunity to use a pre-built VM image that has a supported version of SQL Server pre-installed, or you can choose to bring your own media to install from. There are licensing differences and benefits to both models, but we will not even begin to try to cover licensing in this book. Here are some more insights offered by Microsoft on Azure VM licensing8.

Whether you use a pre-built image or bring your own software, the VM can take advantage of some automation by using the SQL Server IaaS Agent Extension (not currently available on an Azure VM running Linux at the time of this book's publishing), which provides automated backup and patching capabilities, as well as configuration assistance with Azure Key Vault integration to store encryption keys outside of SQL Server. You are also fully able to use any method you wish for these tasks, including SQL Server Maintenance Plans or even third-party backup scripts and tools. Some additional tooling may be necessary in any case, because backups are just part of the regular upkeep needed for a healthy database that is even lightly used. The Agent Extension (along with several other features) is enabled automatically when using a pre-built image, or by registering9 your VM with the SQL VM resource provider.

It is important to catch the distinction between automation and a managed service. Automation provides tools that you can use to make managing your server easier. With the PaaS model of the next two Azure offerings, you don't need to monitor to see whether backups have failed, nor do you even need to do anything to ensure that your server is backed up. The Azure platform management system backs up your server based on the settings you choose (and you can't even accidentally choose not to back up at all either). With IaaS, only the hardware is truly managed by Microsoft. It is your responsibility to back up your databases and make sure those backups can be restored, even when using the SQL Server IaaS Agent Extension.

Note

Managing and supporting are two different concepts. A supported service means the host will help you if the software is not working properly. A managed service will have the host in charge of making sure things work properly based on your configuration.

An SQL Server VM gives you a highly compatible method to lift and shift many workload types to the cloud. This includes transactional workloads capturing customer orders or business intelligence workloads using analytics features such as Machine Learning Services, Reporting Services, Analysis Services, and so on. This is because the Azure VM presents itself as very much the same as your on-premises hardware, the only real difference being how you configure networking and security over the internet to work with your local security infrastructure. For SQL servers that use SQL authentication, the application will require little, if any, change, but using Active Directory will require...

Table of contents

• Foreword
• Preface
• 1. Introduction to SQL Server on Azure Virtual Machines
• 2. Getting started with SQL Server on Azure Virtual Machines
• 3. Hero capabilities of SQL Server on Azure Virtual Machines
• 4. SQL Server on Linux in Azure Virtual Machines
• 5. Performance
• 6. Moving workloads to SQL Server on Azure Virtual Machines
• 7. Hybrid scenarios (Microsoft SQL IaaS)
• Appendix A
• Index