SQL Server 2022 Revealed
A Hybrid Data Platform Powered by Security, Performance, and Availability
The Apress logo.
This Apress imprint is published by the registered company APress Media, LLC, part of Springer Nature.
The registered company address is: 1 New York Plaza, New York, NY 10004, U.S.A.
I’ve said this before, but it is worth repeating because it matters. This book is dedicated to the #sqlfamily, the most dedicated and passionate and largest technical community I know of in the world. Without you we would not have a product.
Today’s modern business landscape is dynamic and fast moving – with data playing a fundamental role. Organizations need to quickly gain insights from data to inform customer experiences and empower their employees. Currently, many companies operate in a hybrid environment, with data in the cloud and on-premise. That’s why we are so excited about the upcoming release of SQL Server 2022, a key part of our evolving data and intelligence strategy. At Microsoft, we have made significant investments in SQL, creating the most connected SQL database ever with this newly launched product – and it’s incorporated into a Microsoft data ecosystem that gives organizations a seamless way to connect across their databases, analytics, and governance platforms.
I can think of no person more qualified to take you through a deep dive into SQL Server 2022 than our principal architect, and 29-year Microsoft veteran, Bob Ward. Bob’s depth of knowledge is second only to his enthusiasm and passion for the SQL community.
In his informational book, Bob delivers a closer look at new solutions that enable you to do more with your data and guide you to take advantage of new capabilities. Bob will introduce Azure Synapse Link for SQL Server 2022, which gets you from data to insights faster and easier than ever.
Bob will cover database engine innovations including built-in query intelligence, Ledger for SQL Server and Contained Availability Groups, as well as Azure Synapse Link for SQL, which combines data from on-prem and cloud data. This enables near-real-time analytics over your most important operational data, all without the need to build and manage complex packages and pipelines – or the need to write a single line of code for ETL movement. You’ll learn about SQL Managed Instance for managed disaster recovery and use the power of built-in Distributed Availability Groups (DAGs) to seed and link databases to Azure.
Additionally, Bob shares easy-to-consume information on Microsoft Purview, which you can use to create a policy for access rights and then publish across many SQL Servers, allowing you to have a central governance hub.
We hope you enjoy reading about, exploring, and deploying SQL Server 2022 as much as we enjoyed developing it. We want to extend our sincere thanks to our incredibly engaged SQL community. Your feedback over the years has helped us make a powerful platform that will reduce complexity, accelerate innovation, and deliver insights to inform business decisions. Thank you for giving us an opportunity to support your efforts and innovations!
Rohan Kumar
Microsoft Corporate Vice President, Azure Data
Like SQL Server 2019 Revealed, the manuscript for this book saw plenty of mileage. I was back on the road again in late 2021 and in 2022, and many of the chapters were written or polished in places like Las Vegas (multiple times); Orlando; London (including the London Underground); Charleston, South Carolina (where my son Troy lives); Greenville, South Carolina; Dallas (where my son Ryan lives); Poolville, Texas (where my wife’s family lives); Austin; Genesee, Colorado (where Ginger and I have a small mountain retreat); Siesta Key, Florida (thank you, Tom and Janet Grubish); Redmond, Washington (multiple times); Chicago; and Atlanta. This includes a number of times writing pages in hotels, airplanes, trains, and even the front seat on a road trip while Ginger drove. But I was always most effective finishing off a chapter or making edits in the confines of my home in North Richland Hills, Texas.
This book is a complete knowledge transfer from me to you on everything I learned, absorbed, tested, and exhaustively poured my brain into about SQL Server 2022 from its inception as project Dallas right up to the General Availability release of the product. And it is my honor and pleasure to do that. All those late nights and weekends of study, testing, looking at source code, talking to program managers and developers, and thinking of scenarios based on my 29 years of experience are all in this book. I tried to bring both the high-level “what and why” with deep “how it works” so you can learn SQL Server 2022 from all angles. The number of pages alone in the book is a testament toward that goal. I’ve also tried to provide a deep set of examples in almost every chapter. I want you to not only read about why SQL Server 2022 is special but try it out for yourself to see it in action. I think you will also love in almost every chapter quotes from our engineering team on their perspective on why they believe SQL Server 2022 is a great release.
If you like knowing the history and “behind the scenes” of SQL Server 2022, start with Chapter 1 where you will also get an insight into the overall story of SQL Server 2022. If you want to understand differences in installing SQL Server 2022 from previous releases including what has been removed and new Azure connectivity options, take a look at Chapter 2.
All of the other chapters can be read in succession or on their own as separate topics. If you want an immersion into how SQL Server is connected to Azure, Chapter 3 is a complete resource. There are plenty of screenshots in this chapter to show you the entire Azure experience. Even if you don’t have an Azure subscription, check out how we are connecting to Azure for disaster recovery (DR), analytics, and security.
Perhaps you want to dive right into the engine. Built-in query intelligence is so vast I needed two chapters to cover it all. Chapters 4 and 5 include very interesting exercises you can try yourself all on your own laptop with Evaluation or Developer Edition.
Chapter 6 is “all engine,” and I believe you will like the 25+ major features we have introduced in SQL Server 2022 including Ledger for SQL Server, “hands-free” tempdb, and Contained Availability Groups.
If you have heard of S3 object storage but are not sure how to use it, you will like Chapter 7. I’ll show you how to access parquet files and delta tables from SQL Server using our Polybase technology. Or perhaps you are looking for a new place to host your backups, so I’ll show you how to back up and restore native SQL backups to and from any S3-compatible storage provider.
I think many believe we have forgotten about the Transact-SQL (T-SQL) language. You might be surprised to see the new T-SQL functions and language enhancements we have poured into SQL Server 2022 in Chapter 8.
There are not many new features in SQL Server 2022 specific to Linux, but I want to make sure you know the fundamentals. In Chapter 9 I’ll show the basics of deploying and using SQL Server on Linux, containers, and Kubernetes.
I believe Azure is the best cloud for SQL Server, so Chapter 10 is a journey for you to learn how to deploy, optimize, and manage SQL Server 2022 on an Azure Virtual Machine.
And finally I wanted a way to close out the book with how SQL Server has become a force everywhere you need it. So Chapter 11 is how SQL Server exists “edge to cloud” in ways you have never dreamed with a story of consistency and flexibility.
If you have liked reading my books before or maybe this is for the first time, I hope you enjoy the book in all its aspects. I write in a very conversational style, which many have told me helps them “visualize me speaking to them” as they have seen me present in various events both in person and online. I also want you to have resources, so you will see plenty of online references throughout the book for you to dive in deeper.
Anytime your write a book like this, you want readers to have the latest information. So keep up to date with the latest examples and errata for the book at https://aka.ms/sql2022bookexamples. Or check out my free workshop materials at https://aka.ms/sql2022workshop.
I love to hear from readers, so you can either give the book a rating on Amazon or email me directly at [email protected]. I always want to know how you feel about the book or if you want to learn more about SQL Server 2022.
Bob Ward
North Richland Hills, TX
September 2022
I want to first thank God for sending his one and only son, Jesus, for the forgiveness of my sins, the immersion of the Holy Spirit, and the promise of everlasting life. Without my faith, I am nothing.
I want to thank my wife, Ginger, so much for her love and dedication to me for all of our years together but especially during the writing of this book. You patiently allowed me to work crazy hours and situations even on some of our vacation time to bring this book to life. I also want to thank my sons Troy and Ryan, and Ryan’s wife Blair, my daughter-in-law. You all give me hope for the future as I see you all grow and exhibit grace and truth in everything you do.
This is my fourth book with Apress, and I want to personally thank Jonathan Gennick from Apress. Jonathan gave me my first chance at authoring when many publication companies turned me down. Thank you, Jonathan, for always supporting me and letting me “write my way.” I also want to thank Jill Balzano from Apress whom I’ve never met in person but who always is so kind and professional despite all the crazy deadlines we try to meet.
I asked Erin Stellato to be my technical reviewer because she is one of the deepest experts on SQL and just an incredible person. Turns out it was a blessing when she joined Microsoft during the writing of the book as we could discuss confidential information. Erin, thank you for your thoroughness and great attitude as we all put immense pressure on you to review so many chapters late in the cycle.
There were so many people at Microsoft who supported my work on this book. But, first, I want to thank Joe Sack, Pedro Lopes, and James Rowland-Jones who no longer work at Microsoft but were a huge part of helping me craft the story of SQL Server 2022, which you see in this book.
At Microsoft I want to thank my leaders who support all of my efforts including Rohan Kumar, Peter Carlin, Asad Khan, and Sanjay Mishra. The true heroes of this book are all the people at Microsoft in the engineering team who helped me with all my questions and the review of complex topics and for giving me some great quotes. This list is long, but I have to call out everyone who helped because each of them helped in a way that made the entire book and the product worth the effort. Thank you, Ajay Jagannathan, David Pless, Kevin Farlee, Derek Wilson, Kate Smith, Kendal Van Dyke, Sasha Nosov, Travis Wright, Hanuma Kodavalla, Naveen Prakash, Joachim Hammer, Chuck Heinzelman, Mine Tokus, Hugo Queiroz, Tim Chen, Dani Ljepava, Mladen Andzic, Andreas Wolter, Srdan Bozovic, Vlad Rodriguez, Mirek Sztajno, Dylan Gray, Devin Rider, Robert Dorr, Peter Byrne, Jack Li, Mike Ray, Van To, Lee Woods, Sarah Kaufman, Conor Cunningham, Tejas Shah, Amit Khandelwal, Pam Lahoud, Purvi Shah, Aditya Badramraju, Taryn Pratt, Lani O'Brien, Nancy McGrory, Ebru Ersan, Umachandar Jayachandran (UC), Nicholas Simmons, Sharanya Bhat, Dilan Galindo Reyna, Ryan Stonecipher, Ravinder Vuppula, Thierry Fevrier, Aashna Bafna, Panagiotis Antonopoulos, Pieter Vanhove, Ankit Mahajan, Swaroop Moida, Fang Hou, Balmukund Lakhani, Parag Paul, Jakub Szymaszek, Yuqing Li, Perry Skountrianos, Pratim Dasgupta, Milos Vucic, Nikolas Ogg, David Liao, Chandrashekhar Kadiam, and Brian Carrig.
I also want to thank some of my colleagues on the engineering team who worked tirelessly to help me with workshops and events. Thank you, Marisa Mathews and Rie Merritt. You all work so hard behind the scenes that no one understands the effort you put in. And a special thank-you to my colleague Buck Woody. You are a dear friend and one of the most talented people I know. You always make traveling on the road a fun adventure.
Thank you also to the Microsoft Mechanics team for helping us highlight the amazing technology of SQL. Thank you, Jeremy Chapman and Celine Allee. Being on your show is always a privilege and fun experience.
I also want to thank members of our marketing team who were great partners during the launch and release of SQL Server 2022 including Matthew Burrows, Miwa Monji, Eric Hudson, Sonya Waitman, Guy Schoonmaker (thank you for your deck wizardry), and Emilija Dufresne.
Thank you, everyone, even for the smallest email response that helped make this book possible.
A photo of Bob Ward.
A photo of Erin Stellato.