A first look at SQL Server 2022 — the latest Azure-enabled database and data integration innovations. See what it means for your hybrid workloads, including first-time bi-directional high availability and disaster recovery between Azure SQL Managed Instance and SQL Server, Azure Synapse Link integration with SQL for ETL free near real-time reporting and analytics over your operational data, and new next-generation built-in query intelligence with parameter sensitive plan optimization. Bob Ward, SQL engineering leader, joins Jeremy Chapman to share the focus on this round of updates.
QUICK LINKS:
00:38 — Overview of updates
02:19 — Disaster recovery
04:26 — Failover and restore example
06:16 — Azure Synapse integration
09:04 — Built-in query intelligence
10:19 — See it in action
12:52 — Wrap up
Link References:
Learn more about SQL Server 2022 at https://aka.ms/SQLServer2022
Apply to join our private preview, and try it out at https://aka.ms/EAPSignUp
Unfamiliar with Microsoft Mechanics?
We are 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?sub_confirmation=1
- Join us on the Microsoft Tech Community: https://techcommunity.microsoft.com/t5/microsoft-mechanics-blog/bg-p/MicrosoftMechanicsBlog
- Watch or listen via podcast here: https://microsoftmechanics.libsyn.com/website
Keep getting this insider knowledge, join us on social:
- Follow us on Twitter: https://twitter.com/MSFTMechanics
- Follow us on LinkedIn: https://www.linkedin.com/company/microsoft-mechanics/
Video Transcript:
- Up next, we’re joined by SQL engineering leader Bob Ward, to tour the latest Azure-enabled database and data integration innovations with a first look at SQL Server 2022 and what it means for your hybrid workloads, including first-time, bi-directional high availability and disaster recovery between Azure SQL Managed Instance and SQL Server, Azure Synapse link integration with SQL for ETL-free, near real-time reporting and analytics over your operational data, and new, next-generation built-in query intelligence with parameter sensitive plan optimization. So Bob, it’s great to have you back on the show.
- I love being on the show, thanks for having me.
- So first, congrats to you and the team and big news today with the announcement of SQL Server 2022. What’s been the focus on this round of updates?
- So with every release we’re optimizing the core SQL engine to give you a consistent database experience from the cloud to on-premises, and even the Edge. As you know, most of the new innovations start in the cloud before coming to SQL Server. And this release really moves a ton of pain points in areas like disaster recovery, where for the first time you can do failover from SQL Server 2022 to Azure SQL Managed Instance in the regions you need. We’re also removing silos between your operational and analytic workloads. In the past, you’d have to ETL your operational data on a schedule to bring it into your analytics system in order to run analytics. That pain now goes away with Azure Synapse link integration for near real-time analytics and reporting. Additionally, you can now get visibility over your SQL Server data in the context of your entire data state with Azure Purview integration. Then for auditing scenarios, we’re introducing SQL Server Ledger, bringing the power of blockchain technology to verify changes by other parties, so that you can retain an immutable record of the data. And one of my favorites, we’re also bringing performance to the next level with built-in query intelligence, like parameter sensitive plan optimization that caches more than one plan for a stored procedure for consistent query performance.
- And these really seem like great updates, especially for hybrid data workloads. But this is Mechanics, though. So let’s unpack these capabilities a bit more and also see them in action.
- Sure, Jeremy, right. And as you saw, there’s a lot we can dig into with SQL Server 2022. So I’m really going to focus on my three favorites: disaster recovery, Azure Synapse integration, and built-in query intelligence.
- All right, let’s do it. So starting with disaster recovery, I know this has been traditionally a pain point for people. Today they have to spin up an Azure virtual machine or another SQL on-premises server to failover to. It’s hard to set up everything and really get it all configured and running, though, right?
- That’s true. So now it’s super easy to set up disaster recovery as a managed service with Azure SQL Managed Instance configured for you as your disaster recovery site. In fact, we’ll look at our demo company example, Wide World Importers, who want to implement disaster recovery in an easy way, especially for the email marketing campaign. I’m in SQL Server Management Studio, or SSMS. I’ve connected to my SQL Server, and you can see the Wide World Importers database. We’ve run a query to look for people that have opted into email comms. And you can see five subscribers that have opted in. In this case, I’ve already attached our Azure SQL Managed Instance to our SQL Server. All I had to do was right click the server, go to Azure SQL Managed Instance link and attach my database to the cloud. And by the way, if you’re setting this up for the first time, it will ask you to log into your Azure subscription and choose your target managed instance, and it will seed your database into the managed instance. Next, you can see an availability group and distributed availability group that was deployed and configured for me automatically. And since we’re linked to Azure SQL Managed Instance, we can also view it in the Azure portal. Now, not only is it easier to configure, but because this is a managed service, you no longer have to manage and maintain your DR infrastructure. So now with everything running, I’ll go back into SSMS and we can start to run queries against the managed instance as a read-only endpoint. I’ll set my screen up so SQL Server is in the middle and Azure SQL Managed Instance is on the right. Now, I’ll run the same query to see who’s opted in for emails. And in the results we’ll see that it’s a read-only database, and the same results that we see in SQL Server. And just to show you how quickly everything replicates, we can go back to SQL Server and insert a new subscriber, Isabella Rupp, and now I’ll run the initial query again, and we can see the results reflected in Managed Instance.
- Of course, what you just showed was a great side benefit for read replica. But that said, the main reason why we set this up was for failover. So what does the process then look like to move the active instance then to Azure SQL Managed Instance?
- That’s right, Jeremy. So now let’s take the example of an outage or a hardware upgrade where we need to failover. This time, I’ll navigate to migrate database to the cloud to initiate the failover. That takes me to a wizard to step me through the process. First, I have to sign into Azure and connect to the managed instance I want the failover to. Once I’m connected in cut-over type, I can choose planned or forced to failover. I’ll choose planned because I want to synchronize SQL Server with managed instance one last time before the failover. We’ll skip through our cleanup and summary. And when I click finish, we can monitor the steps. Now this can kind of take a few moments. Once the failover is complete, we can connect to the Azure SQL Managed Instance and add a new subscriber, in this case, Mandy Smith. We can now use managed instance for read and write workloads, whereas before it was read only. You can see from the result that a new row has been inserted.
- That’s great, the read and write capabilities make this pretty useful. Now everything’s up in the cloud, but can you restore everything then back to your SQL Server on prem?
- Jeremy, can you believe this? That’s the great thing. With SQL Server 2022 for the first time ever, you can restore a versionless Azure SQL Managed Instance database back to SQL Server. I’m going to back up the Wide World Importers managed instance database to an Azure blob storage account, using the backup URL. And now if I switch over to SQL Server 2022, I can use good old T-SQL to restore the backup file to my local SQL Server. I’ll give it a new name, like WideWorldImporters_back, so we don’t get confused. Now, when I refresh, I can see the new database has been restored. We can then query the stored database and confirm the new subscriber Mandy Smith was added, and there they are.
- So now disaster recovery is truly hybrid, but why don’t we move on to our next major update with Synapse Link, where SQL Server, now like Cosmos DB and Dataverse, can also be integrated with Azure Synapse.
- This makes a huge difference, by the way; as I mentioned, removing the need for ETL to run analytics over your operational data. So let’s go back to our Wide World Importers example. They’re using Azure Synapse Link with SQL Server to track their fleet of trucks, ETL free. Their trucks have onboard IoT sensors to track engine telemetry with time series data logs sent to Azure Data Lake. And anytime a truck loads or unloads, the onboard cargo data is updated to our SQL Server. As all of this is happening, their business analysts can connect to the analytical data in near real-time and use Power BI to track vehicle location and onboard cargo. Additionally, their data scientists can use the vehicle telemetry data with spark pools to train machine learning models to get ahead of potential issues in each truck that could affect the delivery of goods. Let me show you how this works. Starting in my Synapse workspace, you can see that I’ve established the Synapse Link relationship between the SQL pool in Synapse and my SQL Server. I’ve also already linked the specific tables that I want to link with Synapse. I could add more tables using the plus new here, but for now stick with my two vehicle tables.
- Cool, so now everything is wired up between your SQL Server and Synapse, but what else can you do now that your data is in Synapse?
- Yeah, so Azure Synapse opens up limitless data analytics, and with Synapse Link enabled, this lets me pull out insights in near real time. Now I’m back in my Synapse workspace and here are my two vehicle tables. Just to prove the data’s in sync, I’ll just run a basic top 100 rows query and you’ll see it returns almost instantly. And it gets better, because Power BI visualization is built in to the Synapse workspace, I can quickly visualize my data. And this vehicle data is tracking inventory, or cargo, on our trucks. In Power BI, all of these colored dots here in Texas represent our trucks with their location, and the diameter of each dot indicates the amount of cargo in each. And you can now see they’re all pretty similar. I’m back in SSMS and we just bought a new truck in Dallas and we finished loading our truck in Waco with more cargo. So I’ll write these updates to my SQL Server first. And because this is all linked for near real-time analytics in Synapse, if I head back to my Power BI report in my workspace and refresh it, you’ll see our new truck in Dallas with this green dot here and our Waco truck’s blue dot is larger to reflect all of its added cargo. Additionally, because the IoT devices on our trucks are logging up to our data lake and we’re an Azure Synapse, our data scientists can also build machine learning models using built-in spark pools to detect data anomalies that might put our shipments at risk, as you can see in this notebook.
- And this is really a great example of hybrid HTAP for SQL Server and really rounds out our hybrid updates. Now for all of our hardcore SQL fans who are watching, why don’t we go deeper on the changes that we made to the SQL engine itself?
- Jeremy, I’ve got to say as a longtime SQL professional, the built-in query intelligence is a game changer. It really ensures consistent performance without any code changes. For example, when you run a stored procedure, there are two types of plans possible depending on the amount of data that SQL needs to process and the parameter value pass to the stored procedure. An index seek is great for queries that return a small number of rows and an index scan is great if your query is going to return a lot more data. SQL will choose the best plan based on the scenario. That said, only the first planned run can be cached for the stored procedure. This plan will remain cached unless something evicts it from memory. So if you are the second person to run the store procedure and you need an index seat, but an index scan is in the cache, you were stuck with that plan, which usually means it takes longer to run your query. This problem is commonly known as parameter sniffing. So now instead of constantly tuning your queries, built-in query intelligence takes care of that for you. Instead of one cached plan per store procedure with parameter sensitive plan optimization, SQL can now cache multiple plans against the same stored procedure as multiple queries are run against it.
- It sounds good, but can we see it in action?
- Sure. In this example, I have a database that hosts real estate property listings with agent details in a single table. You can see here in SSMS in these two query windows, the one in the middle is best used with a seek, and the one on the right is best used with a scan. Let’s see an example of these queries in action, simulating a workload of many users. I’ll use Perfmon to track resource usage per CPU and batch requests per second, which is a common way to look at SQL query throughput. Now I’ll use the popular free tool, OStress, to run a workload for the first parameter, which uses an index seek. In fact, this is the script I’ll be running to execute the stored procedure. In Perfmon, we see a healthy use of CPU and a high throughput. You can see the workload only took a few seconds to run. Now let’s run the other workload to simulate a planned cache eviction, and an execution of the query that is best served by index scan. And while that runs, here’s my script. And you can see the first lines evicting the cache plan. Once that happens, and the second line runs, the new scan plan has taken over in cache. So let’s flip back to run our first query in this top window, which runs faster using a seek plan. But now in Perfmon, you’ll see a massive performance degradation compared to the first time we ran it. That’s because it’s forced to use the scan plan from cache. As you’ll remember, with the seek it took just like three seconds. We’re already way past that, so I’m going to cancel this. I’m going to fix this with SQL Server 2022. I don’t need to change any code, though. I’ll just enable DB compat 160, which is the new compat level for SQL Server 2022. Now I’m going to run the same commands to simulate planned cache eviction with the workload that requires a scan, like I did before, just to ensure the scan plan is cached. That’ll run for a few seconds. And normally, that would have crippled the query that prefers the seek plan. Let’s see if it’s still the case. I’ll run the query that prefers a seek plan again. You see it wasn’t slowed down. There was no performance degradation, because both plans were cached. I’m going to prove it. Let’s look behind the scenes using query store. You can see that now I have two plans for the same query statement with the same stored procedure. First, let’s look at the seek plan and then we’ll open up and look at the scan plan. Now each plan can provide the best performance for the same stored procedure based on what the parameter value needs. And the nice thing about these changes at the engine level is that SQL Server and Azure SQL, at this database compatibility level, all benefit from parameter sensitive plan optimization.
- Awesome, it’s so great to see all the updates, and really how they accrue to SQL, no matter where you’re running it. So how do you recommend people get started?
- You can learn more about SQL Server 2022 at aka.ms/SQLServer2022. And if you want to try it out, you can apply to join our private preview at aka.ms/EAPSignUp.
- Thanks Bob and of course, keep checking back to Microsoft Mechanics for all the latest updates. And be sure to subscribe to our channel if you haven’t already. And thanks for watching.
