| SQL Server 2022 Revealed |
Page 1 of 3 Author: Bob Ward 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:
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.
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 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:
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:
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 ) |
