Chapter 10:​ SQL Server 2022 on Azure Virtual Machines 

Azure SQL Virtual Machines allow you to run your SQL Server on a server hosted by Microsoft rather than you own premises. Various integrations with Azure provide plenty of additional functionality to ease your infrastructure concerns (e.g. automatic backups). 

The chapter opens with a look at what Azure VM is, essentially Microsoft provides the hardware and services, and you manage the OS and SQL Server. You can use Windows or Linux, and SSIS, SSAS etc. 

There’s a useful section on planning for deployment (i.e. installation). Here you need to consider the resources you require e.g. number and speed of CPUs, memory, storage etc, tools, HA/DR. You are limited to what Microsoft provides, but it’s a generous selection.  

Next, there’s a discussion about the SQL Server IaaS Agent Extension. This aims to boost your IaaS offering by integrating many Azure goodies, including: Azure Portal Management, automatic backups, automatic security updates, SQL Best Practices Assessment, and Microsoft Defender for the Cloud. Details on how to install this agent are given. 

There’s a step-by-step walkthrough on deploying SQL Server on an Azure VM, with helpful screenshots and descriptions. This is followed with exploring and connecting to SQL Server VM. This leads naturally to discussions on migrating from on-prem to Azure SQL VM, with various options briefly examined. 

Performance is often a critical topic, and this links back to the choice you made in selecting the VM size (e.g. number of CPUs). It should be noted, it’s relatively easy to resize the VM up or down, later. The SQL Best Practices Assessment can be run to ensure your configuration etc is optimal. 

Details are provided on HA/DR features. A degree of automatic tolerance is provided, and other options are discussed. 

The chapter ends with a look at Monitoring. You can use many of the existing techniques, including: performance monitor counters, DMVs, Query Store, and XEs. There’s a helpful discussion on Azure Monitor, a centralised performance metrics tool. Lastly, Azure Insights can be enabled to provide rich metrics about the VM, where trends can be analysed. 

This chapter highlights the advantages of using Azure VMs, especially when you integrate the IaaS extension agent, this makes your IaaS offering feel more like PaaS, with automatic backups, Azure Portal Management etc. 

Chapter 11:​ SQL Edge to Cloud 

There are now many different flavours of SQL Server. Luckily, they have the same core engine, enabling you to use your skills across them all. The underlying idea is to develop your code once and deploy it anywhere. There’s a great diagram that discusses the different flavours of SQL Server.  

The chapter highlights the different flavours of SQL Server, what they are, and when they should be used. These include: 

• Azure SQL Edge – this version is typically used by Internet of Things (IoT) systems. It has a small footprint, and some unique features

• SQL Server – This is the standard SQL Server that runs on-prem. It has SQL Agent, SSIS, SSRS, SSAS, Machine Learning Services etc. It’s often used when you want complete control over deployment and management

• SQL Server on Azure VMs – like running your SQL Server on your own VM boxes, but instead the server is hosted by Microsoft. Typically used when you want all the SQL Server features e.g. SSAS. If used with the IaaS Agent Extension, then a large number of useful Azure features are provided e.g. automatic backups

• Azure Managed Instance – often used when you want server-level SQL Server features e.g. SQL Agent, and don’t want to worry about updates, patching, backups, HA etc. Can be viewed as a marriage of best of SQL Server and cloud

• Azure SQL Database – the most managed service of Azure SQL family. Version-less, automatic backups, built-in HA. Options: Serverless (pause/resume, auto scale workload) and Hyperscale (100TB database, up to 30 read-scale replica helps apps scale). The author suggests we may get both serverless and hyperscale soon!  

This chapter provides a useful summary of what the SQL family of products offer, and when each should be used. Luckily, much of the functionality on each is very similar.

Conclusion 

This book aims to explain the new features in SQL Server 2022, and certainly succeeds. The book is generally easy to read, with useful discussions, diagrams, inter-chapter links, helpful example code, and a vast number of useful website links for further information. The extensive weblinks could easily make this book 30-50 times larger. 

The most outstanding feature of the book is the author’s style of writing (often through stories), and his lucid explanations, which together make the book an almost effortless read.  

The book is largely concerned with the new features in SQL Server 2022, but also includes details of some perhaps less familiar areas (e.g. SQL Server on Linux, VMs). If you’re familiar with Azure SQL PaaS database offerings, you may recognise many of the new features. With new features, there are often functional limitations, and these are typically described, and will surely be eased in future editions. 

Many of the author’s insights are based on customer feedback and his extensive experience in using the product. Reading this book will allow you to take advantage of his experience, to give direction to your own projects. 

The book is aimed at DBAs and developers. SQL Server’s new features are often enhancement to existing features, so some experience of SQL Server (perhaps a few years) is advantageous.  

Using Azure Extension for SQL Server for on-prem, and IaaS extension agent for Azure VMs, shows Microsoft is keen to integrate your SQL Servers into the Azure environment, where you can take advantage of many automatic features. 

If you want to get up to speed with the latest SQL Server functionality, I can highly recommend this book.

For more book recommendations on this topic see Pick Of The Shelf - SQL Server on our Programmer's Bookshelf.

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.