SQL Server 2022 updates for query performance and database failover

QUICK LINKS: 

00:00 — Introduction 

01:01 — Backup and disaster recovery 

02:31 — Failover between SQL Server and Managed Instance 

05:24 — Snapshot backups 

07:57 — Restore a snapshot backup 

08:59 — SQL Server management: Pay-as-you-go 

10:40 — SQL Server performance: Query optimizations 

13:21 — Wrap up

Link References: 

Start a free version of SQL Server 2022 at https://aka.ms/getsqlserver2022 

Check out our free online workshop at https://aka.ms/sql2022workshop 

Unfamiliar with Microsoft Mechanics? 

As Microsoft’s official video series for IT, you can watch and share valuable content and demos of current and upcoming tech from the people who build it at Microsoft. 

• Subscribe to our YouTube: https://www.youtube.com/c/MicrosoftMechanicsSeries 
• Talk with other IT Pros, join us on the Microsoft Tech Community: https://techcommunity.microsoft.com/t5/microsoft-mechanics-blog/bg-p/MicrosoftMechanicsBlog 
• Watch or listen from anywhere, subscribe to our podcast: https://microsoftmechanics.libsyn.com/podcast

Keep getting this insider knowledge, join us on social: 

• Follow us on Twitter: https://twitter.com/MSFTMechanics
• Share knowledge on LinkedIn: https://www.linkedin.com/company/microsoft-mechanics/
• Enjoy us on Instagram: https://www.instagram.com/msftmechanics/
• Loosen up with us on TikTok: https://www.tiktok.com/@msftmechanics 

Video Transcript:

- Up next we’ll take a deeper look at recent updates to SQL Server 2022 and what it means for your hybrid workloads, including online bidirectional disaster recovery between your local SQL Servers and Azure. Differential snapshot-based backup and restore to achieve massive speedups, as well as anywhere management of your SQL Servers with Azure Arc-enabled SQL provisioning, including new pay-as-you-go licensing. And if raw performance is your thing, we’ll demonstrate intelligent Degree of Parallelism feedback where SQL optimizes the thread count of your queries automatically. And to walk us through all the updates I’m joined once again by Bob Ward. Happy to be back. It’s really great to have you back on the show to go deep on all the SQL Server updates. There’s a lot of updates there to talk about. SQL Server 2022 is now generally available, and there’s a bunch of incremental improvements around resilience, even deeper Azure integration. And as always, we’re incorporating optimizations to reduce costs for both licensing and compute. Whether that’s local or in the cloud, this includes new ways to improve query performance.

- And this is going to be an action packed show. And when you mentioned, by the way, Azure integration, something that I always think about coming from an IT background on prem is that that means like a one-way road to Azure when we configure hybrid going to the cloud.

- I’m glad you brought that up, that’s not exactly the case. At the end of the day, on-prem SQL Server and hybrid configurations are a reality for so many reasons today. Of course we’ve done a ton of work to integrate SQL Server with Azure services for analytics, policy, directory services, security, and anywhere management with Azure Arc. One of the areas Azure integrations makes a ton of difference is in backup and disaster recovery. You can now link your local SQL Servers to Azure SQL Managed Instances with either serving as a primary or secondary. And it’s bidirectional, so you can swap their roles. So data can move up to the cloud and back on-prem as needed, so you’re always in control. Let me show you. Here I’ve got SQL Server 2022 installed. There’s a Wide World Importers database and I’ve replicated it to a Managed Instance as you can see here. I’ve also created a couple of custom tables called vehicles and vehicle stock items. The data’s been synchronized to Managed Instance for these tables. To prove everything is synchronized, I’ll run this query to show the vehicle cargo count per city. Now the tab on my left is my local SQL Server, you can see it set to read_write with database version number of 957. The right tab is our Managed Instance. It’s read_only with the same database version number 957, so we’re compatible and synced. Now we’re going to failover online from SQL Server 2022 to Managed Instance here in Management Studio. So I’ll go to this menu, pick failover database, and use a new option called Failover between SQL Server and Managed Instance. That’s because I’d like to keep SQL Server as part of the distributed availability group, so I can fail back at any point. Now I’ll failover, and you can see that I’ll be reversing roles from SQL Server as the primary and Managed Instance as the secondary. So I’ll confirm I understand and click next. Now I have to use my Azure account so it can connect to my Managed Instance. So I’ll hit next again. And then I’m going to choose, in this case, a planned manual failover. So I’ll choose I understand. And now SSMS will go through a process of synchronizing the two systems and switching roles.

- Okay, so at the moment now your primary is Azure Managed Instance, but you’ve got a secondary then as your local server.

- That’s right. And depending on how much data this can kind of take a few moments. We’ve swapped the roles temporarily because the thing I really want to show you is how we can also fail back to the local SQL Server. Now with the process finished I can check the state of both systems. I’ll run the same query as I did before. You’ll see the local SQL Server is now read_only. So I’ll run the same query for Managed Instance and it’s now read_write. And now that Managed Instance is our primary, let’s make a change in that Managed Instance to make sure changes are reflected back in SQL Server when I fail back. So I’m going to change all the vehicle cargoes and move my cargo vehicles to North Richland Hills by setting the city to all the same value. I’ll run this query here in Managed Instance and it’s affected in all those rows. Okay, so now here comes the moment of proof. We’re ready to fail back to our local SQL Server and switch roles once again. I’ll go back to my SQL Server, and again I’ll select Azure SQL Managed Instance link failover database. Next I’ll choose failover between SQL Server and Managed Instance. And I’ll confirm that I understand. Now I’ve already signed into Azure, so what I’ll do is I’ll keep the planned manual failover option confirmed and make sure my workload has stopped. So let’s start the failover. This normally kind of takes a few minutes. But all right now we’ve switched back. You’ll see the local SQL Server is back to read_write, and the Managed Instance is now to read_only. Now to prove our data is synchronized and we didn’t lose anything, I’ll run this query to see if all the vehicles were changed to North Richland Hills on our primary local SQL Server. And as expected, they have been. And in case you’re wondering, using Azure SQL Managed Instance as a disaster recovery site can be done without a SQL Server license.

- And this is great because we’re all trying to find efficiencies where we can to save costs. Instead of buying expensive metal for things like backup and failover, you can use the cloud for that instead and your data can easily flow as we saw, back from the cloud to your local server.

- It’s so super flexible, and now just a few clicks. And speaking of efficiencies that can lead to more cost savings, we’ve made huge improvements to further decouple storage from compute. This opens up some new exciting capabilities for snapshotting backups, where you’ll be able to do point-in-time restores for massive multi terabyte databases in seconds, while also saving on compute and bandwidth costs. I have a database here that’s about 2.5 terabytes in size that we’re going to be backing up. Let’s first see how long it takes to back up our database to an Azure Blob Storage container. Now right here we’ll create a credential as an access key to the container. And then we’ll kick off this backup and come back and see how long it takes.

- Okay, and that’s probably going to take some time. So how long are we talking? How long does it really take?

- Well maybe as long as your average movie but not quite as entertaining. That said, to save time, we ran this in advance. And as you can see here at the bottom, it took over like 5,000 seconds, which is about an hour-and-a-half. Now we can do much better than that. Let’s see how long it takes to do a snapshot backup just to gauge how much faster it is. I’ll snapshot this disk. This is the F drive which holds all of our data for SQL Server. In the Azure Portal you can see for this virtual machine’s data disk, we already have a full snapshot of this drive. So we can create an incremental snapshot and back up everything super quickly. Okay, so here’s how we’re doing our snapshot backup. Everything described in the green commented lines in the code here. We first suspend I/O for the database using T SQL. We’ll perform the actual incremental snapshot in Azure. Finally, once that’s finished, backup this metadata of the database so we can resume I/O. All this being done using scripts to make this very quick, but I kind of want to show each step manually. I’ll suspend I/O for the database, and here’s that confirmation. Now back in the Azure portal, I’ll create the snapshot from this data disk as an incremental snapshot. Let me give it a name here and I’ll say snapshot from data disk zero incremental. Okay now I’ll scroll down and mark this as an incremental snapshot, then review it and create it. And this time we’ll let it run without speeding it up, because instead of taking more than an hour it will just take a few seconds. Now this is using Azure storage to capture incremental changes to the disk, and that’s how we had to stop our I/O. So we can do this at the disk level. Now it’s finished, it only took a fraction of the time. Then we simply run this command to back up our metadata, resume I/O, and we’re done. Something that took over an hour-and-a-half before literally now takes 17 seconds.

- Wow, math was never my strongest subject but if I’m doing it correctly that’s like one third of 1% of the time. That said though, how long does it take to restore from the snapshot?

- Well depending on your configuration your results can vary, but it’s so much faster than incrementals. Let’s try that out. Now to restore from the backup, we first need to attach or create a new disk inside our VM based on that storage snapshot. Then we can run this restore command to restore the database from that disk. Back in the Azure portal, this new data disk you’re seeing was created based on the incremental snapshot, and I attached it to the VM. Now go inside the VM, this shows up as the G drive with our data. Now we need to restore to a new database. Pointing to that metadata backup with the original database name and the set of files that are now on the G drive. So I’ll run it, and look at that, in only five seconds. I’ve been able to successfully restore a snapshot backup compared to potentially several hours to restore a stream backup from Azure storage.

- So that means, by the way if you’re keeping score at home, if you would’ve scripted the entire procedure it would’ve taken just 22 seconds for two-and-a-half terabytes. So those are some pretty good updates in terms of resiliency, but you also mentioned that we’re also improving the management aspects for SQL Server. So what’s new there?

- That’s where Azure Arc comes in. This gives you a unified control plane with the same types of controls you’d use to manage SQL servers in Azure. And this opens up a lot of additional options for deployment, management, and security, just to name a few. It all starts with SQL Server 2022 setup, which now offers an integrated option to use pay as you go billing through Microsoft Azure. You could even pick Standard Edition to save costs. So I just need to log in with my Azure tenant, then you’ll enter the standard fields for things like subscription, a resource group, and region. Now in the Azure portal, because I’m connecting to Azure, my servers automatically rolled into Azure Arc as you can see here. Notice in the overview screen I’ve got some pretty interesting information. For example, I can tell that I’m running standard edition, and using that new pay as you go licensing. And there are other benefits to being on Azure too. I can use Azure Active Director authentication. You can see at the top of the screen I’m set up as an Azure admin using a AAD. Now optionally I can set up Microsoft Purview access policies to push authentication and authorization down to my SQL server. And I can see all my databases here that have been deployed as well on SQL Server. You know I can even use Microsoft Defender for cloud to get configuration recommendations and security alerts. You can see my server is at risk for potential SQL injection attack. And the vulnerability assessments also point out common SQL Server best practice configurations.

- And by the way, you’re just kind of scratching the surface in terms of what Azure Arc can really do, where you can literally manage SQL Servers wherever they are, even across clouds. Why don’t we switch gears though to performance. I got to say, being a former DBA and IT pro, one of my favorite parts of having you on the show is really going deep on all the different query optimizations that we’re making with SQL.

- I don’t want to disappoint. Last time my showed parameter sensitive plan optimizations. Now we have built-in query intelligence with Degree of Parallelism feedback, where we monitor queries that use parallelism and server. Then the engine manages resource consumption automatically based on query duration and CPU usage. Here I’ve got a stored procedure I’m running and it’s using what’s called a parallel plan with an index scan. Now the problem is the max Degree of Parallelism, or DOP, is configured for auto or zero, which means it’s going to use up to 32 threads to run this query. Now we can use built-in intelligence in SQL server to find out if I can use less threads to achieve the same query performance. Now you enable it using SET DOP_FEEDBACK=ON, and this works on any SQL query running at compat level 160. Now here I’m looking at extended events to show overtime how DOP feedback can reduce the time it takes to give the same query performance but reduce the overall CPU consumption of the query. That’s called a feedback cycle. Doing validation test and stabilization, as you can see in the query DOP column across a set of continually lower DOP values. And I can go in as well, finally I’ve got a stabilization point now. I start at 32 and I’m down to 12. 12 Here is the minimum DOP needed to run the query for the same duration. And you see it here through what’s called feedback DOP. And a stabilization point here on this bottom row. Now inside the query store we store persistent feedback so that way it’s persisted anytime you restart SQL Server. So we can always go back and make sure that we run this DOP at 12, which is our new parallel efficiency or minimum DOP. You can also see we’ve persisted memory grant feedback which is another capability for intelligent performance. Now if you really want to see the performance differences, the way to do this is you can go into query store reports to easily see this over time. I can look at the average duration and check out the plan summary view on the right as SQL lowered the DOP value over time. Look here, the query gets actually faster until it gets down to that stabilization point. Now I’ll change the view one more time because what’s really telling is the average CPU time. Again on the right you can see kind of a staircase fashion over time. I start up here at the top and I’ve achieved lower CPU values at the very bottom where I’ve reached that stabilization point.

- And it almost seems counterintuitive. You’ve gone from 32 threads to just 12 threads. You got civil performance, even faster performance in your case. And it’s also meaning that your CPU cycles required to run it are going to be reduced as well. So love going deep on all the updates with you today for SQL Server. For anyone who’s watching right now, what do you recommend they do to get started?

- Well try it out for yourself. You can get started with a free version of SQL Server 2022 today at aka.ms/getsqlserver2022. And also to learn more, check out our free online workshop at aka.ms/sql2022workshop.

- Thanks so much again for joining us today Bob. And of course, keep checking back to Microsoft Mechanics for all the updates. Be sure to subscribe to our channel if you haven’t already. And as always, thank you for watching.