SQL Server 2022 Revealed
Article Index
SQL Server 2022 Revealed
Chapters 5 - 9
Chapters 10 - 11; Conclusion

Author: Bob Ward
Publisher: Apress
Pages: 506
ISBN: 978-1484288931
Kindle: B0BLB4VJL9
Audience: DBAs & SQL devs
Rating: 5
Reviewer: Ian Stirk

This book aims to explain the new features in SQL Server 2022, how does it fare?

This book aims to explain the new features in SQL Server 2022. Many of these features are already present in the (version-less) Azure SQL database offerings. The book is targeted at DBAs and SQL developers. Since SQL Server’s new features are often enhancements to existing features, some experience of SQL Server (perhaps a few years) is advantageous. 

Below is a chapter-by-chapter exploration of the topics covered.

Chapter 1:​ Project Dallas Becomes SQL Server 2022

This book opens with a little history, describing how work on SQL Server 2022 was started before SQL Server 2019 was released. A list of proposed features was discussed, and work commenced. Next, there’s an overview of the salient features in SQL Server 2022, including: 

  • Cloud connected (between on-prem and Azure)

  • Built-in Query Intelligence (e.g. feedback to create better execution plans)

  • Improved Database Engine (e.g. Ledger blockchain technology, contained AGs)

  • Data Virtualization (e.g. connect to data via REST protocol)

  • Enhanced T-SQL 

The chapter ends with a look at getting started with SQL Server 2022, including: installation, learning about the new features, pricing, licensing, and training. 

This chapter provides a gentle introduction to SQL Server 2022, how it came about, an outline of its major features, and how to get started with it. It is easy to read, with useful discussions, diagrams, inter-chapter links, helpful example code (in other chapters), and a vast number of website links for further information. These traits apply to the whole of the book. 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 easy read. 

SQL Server 22 Rev cover

Chapter 2:​ Install and Upgrade

In terms of installation, not much has changed in SQL Server 2022. This chapter focuses on Windows installs, and references other installs in later chapters. 

Outline details are provided on how to install SQL Server 2022, with an emphasis on what’s new. Perhaps the biggest change is you can now choose an Azure connection on install, so an Azure subscription is needed. Some former features have been removed from installation (e.g. R, Machine Learning server), many of these missing features now require a separate install. 

Noticeably, the amount of memory recommended on install has changed to 75% of the available memory. The author says he feels this is too conservative (I would agree, we would typically leave 10% or 4GB of the available memory, whichever is larger, to software outside SQL Server). 

Next, details are provided on how to set up the Azure Extension for SQL Server. This registers your on-prem SQL Server with Azure, allowing you to take advantage of many useful Azure features, including the use of: 

  • Azure portal

  • Best Practices Assessment

  • MS Defender for SQL

  • Azure Active Directory 

Azure Extension for SQL Server doesn’t support Azure VM SQL, but similar functionality is available there using the IaaS Agent Extension.

Side-by-side and multi-instance installs are discussed, and require much the same thinking as in previous versions of SQL Server, with both methods having pros and cons. The importance of dbcompat (compatibility_level) in testing and giving reassurance in upgrading is noted. The SQL Server Configuration manager now includes stop/start of the Azure Extension for SQL Server.

This chapter provides a useful reminder of the install process. Details of what functionality has been removed (e.g. python) and where it can be downloaded should prove useful. Registering on-prem SQL Servers in Azure looks to provide some very useful features.

Chapter 3:​ Connect Your Database to the Cloud

Increasingly, businesses are moving and connecting their systems to the cloud, to take advantage of its capabilities (e.g. scalability). Various past SQL Server hybrid offerings are discussed, including backup to Azure blob storage, and creating a linked server between on-prem and Azure SQL Database. The SQL Server 2022 hybrid line-up is discussed with reference to a helpful diagram showing its cloud connected capabilities, notably:  

  • Azure Managed Instance Distributed Availability Group (DAG)

  • Azure Synapse Analytics (near real-time OLAP processing of OLTP data)

  • Microsoft Purview (policy management)

  • Azure Extension for SQL Server 

There’s a useful discussion on Managed Disaster Recovery (DR) between on-prem and Azure SQL Managed Instance, with a step-by-step walkthrough on how to set it up. I’m sure this will prove a very useful fallback solution for DR.  

Another useful feature is Azure Synapse Link for SQL Server, where data is moved from on-prem to Synapse without complex and time-consuming ETL. Again, a useful diagram is discussed, and a step-by-step walkthrough provided. 

The chapter ends with a look at Microsoft Purview for centralised policy management. For example, granting a temporary worker permission to systems for a given period.

This chapter provides a very useful look at connecting your on-prem SQL Server to the cloud, and then looks at the many advantageous features that follow. Throughout, useful diagrams and step-by-step walkthrough are provided to make the understanding almost effortless. There’s a useful exercise in restoring an on-prem database from an Azure SQL Managed Instance.    

Chapter 4:​ Built-In Query Intelligence

Increasingly, Microsoft is adding features to SQL Server that can detect and automatically correct performance problems. This is the idea behind Intelligent Query Processing (IQP). Each of the more recent versions of SQL Server has added additional IQP functionality. Query Store has been enhanced to support some of these new changes. This chapter looks at IQP features that can be used in some earlier versions of SQL Server, and the next chapter relates to IQP that require SQL Server 2022 (or more correctly, a dbcompat level of 160). 

The chapter opens with a brief overview of the SQL Server 2022 IQP features, including a diagram that separates the features requiring dbcompat 140+ (i.e. SQL Server 2017), or dbcompat 160 (SQL Server 2022). 

To enable many of the newer IQP features, Query Store has been enhanced. These enhancements include: 

  • Query Store is now enabled by default

  • Query Store Hints (add hint to query indirectly, via Query Store)

  • Query Store Support for Secondary Replicas

  • Store for IQP (Query Store survives restarts, so is ideal for storing IQP info)  

Next, some new IQP features that require SQL Server 2016 and higher are discussed. These features include Approximate Percentiles (get rough percentages very quickly), and Optimized Plan Forcing (shorter time for compilation of forced query plans). 

Moving on, IQP features can be used on SQL Server 2017 and above are discussed. These include Memory Grant Feedback Persistence, where a more optimal Memory Grant value, calculated from previous runs of the query, is obtained from Query Store. 

This chapter provides a useful overview of some of the newer IQP features that can be used in older versions of SQL Server to give more reliable and improved performance. 


Last Updated ( Tuesday, 04 April 2023 )