Developers around the world are rejoicing as the highly anticipated release of SQL Server 2022 has made its way to public preview. With it, comes a slew of enhancements to the software many people have come to know and love, making it ready for meaningful utilization for years to come. However, if you learned about this announcement directly from Microsoft (SQL Server 2022 Overview), Microsoft made it clear that a lot of these enhancements have already been tried, tested, and implemented in many software stacks across the world thanks to how bleeding edge the Azure Cloud is made to be. With that in mind, in this blog we will briefly touch on what comes in the 2022 SQL Server upgrade, and what it would look like to get on SQL Server 2022 and utilize it for the foreseeable future. Then, to contrast, we will go over what there is to gain from migrating our SQL Server to the Azure stack, then leverage Azure SQL Database and what operations could look like there instead.
SQL Server 2022 New Feature Overview
SQL Server 2022’s new features fall in to 3 overarching categories: disaster recovery, Azure Synapse integration, and built-in query intelligence.
Provided below is a brief overview of each feature, with more details on each feature being available here: SQL Server 2022 Features.
Disaster Recovery
- Can now do failover from SQL Server 2022 to Azure SQL Managed Instance
- Disaster Recovery can be set up as a managed Service with an Azure SQL Managed Instance configured as the disaster recovery site
- Availability Groups and Distributed availability groups will be deployed and configured automatically for higher uptime through disaster
- Ability to restore a version-less Azure SQL Managed Instance Database back to SQL Server
Azure Synapse Integration
- Enablement of ETL-free, near real time reporting and analytics over operational data
- No longer need to Schedule ETL on operational data before running analytics on it
- Additional visibility over SQL Server Data in context of the entire data estate
- Able to create a link between SQL Pools in Synapse and SQL Server
- Power BI Visualization built in
- No need for repeated extraction from the Data Lake that hosts source data
Query Intelligence
- Core compute system powering SQL Server upgraded to provide built-in query intelligence and parameter sensitive plan optimization
- Multiple stored procedure execution plans can be cached
What does Upgrading to SQL Server 2022 look like?
We can see there are some clear advantages to upgrading to SQL Server 2022. Microsoft makes it easy for customers too, by providing simple instructions for multiple starting environment scenarios: Upgrade to different edition – SQL Server | Microsoft Docs. With the upgrade bringing advanced features after just following about 10 steps to upgrade, it seems like a simple choice forward – but let us quickly explore what the implications are of continuing with this implementation pattern.
Summary of SQL Server Upgrade
SQL Server receives constant updates. A google search will show you that SQL Server has almost yearly releases. As a result, each year owners can look forward to receiving upgrades. However, that implies that we are manually upgrading the software that hosts our business-critical data annually, which, even with the best of Database Administrators in charge, increases the possibility for error during the upgrades. Depending on how you upgrade, some downtime might come with this, and there is always a degree of corporate bureaucracy that will slow down the retrieval and implementation of new server licenses. There is no need to run from upgrading due to this point however, as most teams are mature enough today to know how to prevent any damage during an upgrade and understand common upgrade patterns to keep uptime for customers going while testing out the new changes.
Where things get a little more interesting, is that if you do not upgrade each year, which is a little more relatable to the modern company approach, the risk is ran that Microsoft will stop supporting your version of SQL Server after a few iterations. At the extreme level, Microsoft just recently cut support for SQL Server 2012 – but at a minor level, if you visit the SQL Docs area of Microsoft’s documentation pages, you can see that they only have detailed notes and information logged starting at SQL Server 2016! Each year it gets harder and harder to receive support for the dated versions we decide to stay on. So, by continuing down the SQL Server Path, you will see constant updates, but you are responsible for them. You are responsible for knowing the differences between versions, and you are responsible for making sure you can scale all the infrastructure that supports your SQL Server. The room for error increases over having reputable experts manage this for your company, and the conversation whether to migrate to a cloud solution will happen again the next time a SQL Version becomes deprecated and a new one gets released.
To see which SQL Server versions have the most compatibility with an upgrade to another version, you may search through official Microsoft documentation here: Which SQL Server Versions can be upgraded directly to SQL Server 2022.
The Alternative – Migrate to the Azure Stack
What is the alternative? As a SQL Server user, there is already a degree of integration with Azure occurring, so another option involves staging all our data inside of various storage services Microsoft Azure has to offer. For those unfamiliar, the “Azure Cloud platform is more than 200 products and cloud services designed to help you bring new solution to life – to solve today’s challenges and create the future. You can Build, run, and manage applications across multiple clouds, on-premises, and at the edge, with the tools and frameworks of your choice” (What is Azure—Microsoft Cloud Services | Microsoft Azure). What I find most intriguing about Azure, is that as Azure gets upgrades, Microsoft is responsible for making sure your data is on the newest version of their systems, providing seamless updates to the most modern features that you can utilize immediately.
Azure Stack Feature Overview
We started this blog by discussing features that SQL Server 2022 is receiving that make it a desirable release to upgrade to – but a point made during the discussion was that these features came second to SQL Server after their initial implementation in the Azure Stack. Microsoft makes it clear themselves, most, if not all features people come to know and love in SQL Server start on the Azure Cloud. Knowing this, there is no need to describe specific features around storage that Azure differs in from your SQL Server implementation, because the understanding is that you will have all the same features you use today, plus some that have not made it to SQL Server yet. The “some” being integrated security features that come default with living in the Azure ecosystem, automatic scalability features, and more automated and informative insights in to monitoring company data via integration with smart logging analytics services. All these advanced features have a layer of abstraction thanks to the Azure ecosystem that makes them simple to switch on, so our developers are delivering business value instead of rewriting features that have been written by others before.
What does Migrating to the Azure Stack look like?
To get on to the Azure platform, users can follow various Microsoft documentations that walk you through different scenarios you may be in as a database administrator. Microsoft provides the option to upgrade with downtime to keep costs down, or without downtime to maintain availability and reputation. This highlights an ideal concept in the Azure platform, the simplicity of choice alongside minimal work required. During migration, Microsoft will point you towards the Data Migration Assistant, which assesses your database for compatibility for the upgrade to Azure SQL Database. Microsoft notes that Azure SQL Database is approaching feature parity with SQL Server – meaning users should expect the same experience in Azure SQL Database after the upgrade with just a few known issues around server-level and cross database operations, you can read more about that here SQL Server database migration to Azure SQL Database – Azure SQL Database | Microsoft Docs.
Some companies who wish to upgrade may have a slew of scripted tasks built in SQL Server. While doing this in SQL Server, DB Administrators may have had to set up ODBC Drivers to have everything orchestrate together and work. When moving to a managed instance, ODBC Drivers are not required, and so it is worthwhile to note that you will have to remove these connectors after making a switch.
Summary of Azure Migration Upgrade
We can see how straight-forward it is to perform an Azure migration, now let’s summarize what upgrading to the Azure stack will look like in the short and long term like we did for SQL Server. Once migrated, you now have a foot in the Azure Door and can begin leveraging its features. Azure SQL Database will receive upgrades seamlessly, so that all your team must do is familiarize themselves with what new features they have access to. Azure ensures that our data is on the proper infrastructure setup that matches the needs we describe while building out our Azure solution and initial subscription. Developers no longer need to worry about scaling the SQL Server throughput capabilities during any times of spiked demand, thanks to the automatic scalability that Azure has with mostly every service. Leveraging Azure allows us to stage our data in multiple locations across the world closer to our customers with a simple click of the mouse. It all receives default Azure encryption, and we can follow well-documented pages if we ever want to increase the security poster of our implementation.
What is the common theme in all of this? Our developers can spend way less time making decisions on how to implement those features and instead spend more time cleaning data and helping customers which leads to immediate business value. Then, when next year comes, the decision does not need to be made again whether to upgrade, we will already be using the newest features!
Wrap-up
Now that we’ve touched on what new features teams can gain from an upgrade to SQL Server 2022, and what performing that upgrade would look like for our teams in both the short and long term, as well as what this looks like for switching to Azure, you hopefully have a more insight to which would be more suitable for your team. I encourage you to find more specific information relating to what Azure has to offer, as teams don’t typically just upgrade only their storage to Azure – though it is not abnormal, and you can see in greater detail of the passive benefits and analytic capabilities that are enabled by having your storage based in Azure. We can summarize the allure of Azure as providing massive amounts of new features after very minimal intervention thanks to straight-forward GUI programming interfaces that also have programmatic backends for those who prefer it. The reputation of Microsoft’s security, availability, and innovation gets thrown in to your company’s solution and will continue to automatically improve.
Here are some more resources to overview SQL Server and Azure services to help with your decision. Thank you for your time reading and I hope to see you in another blog soon!