Microsoft has officially released its highly-anticipated SQL Server 2022 and, at first glance, it might look like more of a pitch for Azure. With improvements to security and performance features, as well as several Azure-friendly functionalities, SQL Server 2022 users can certainly expect a renewed experience from their SQL Server instances.
SEE: The Essential Microsoft SQL Server Training Bundle (TechRepublic Academy)
In this report, we’ll cover more details of the SQL Server 2022 release along with exclusive commentary from Asad Khan, Vice President of SQL Products and Services at Microsoft.
Jump to:
- Fast facts on Microsoft SQL Server 2022
- Why so much focus on Azure and the cloud?
- Creating an optimal SQL Server experience for all users
- Making cloud data recovery available to cloud and on-prem users
- SQL Server integrations with analytics at the forefront
- How can I get started with SQL Server 2022?
Fast facts on Microsoft SQL Server 2022
Microsoft SQL Server 2022 offers users a number of new features with a cloud-friendly emphasis. The new solution offers improvements in query performance with Query Store on by default. It also has simpler availability options for multi-write locations, enabled by automating the “last-writer wins” rule. Additionally, the immutable, tamper-evident SQL Ledger supports the scenarios that blockchain has promised but rarely delivered.
SEE: The Microsoft SQL Server and T-SQL Course for Beginners (TechRepublic Academy)
Like the new T-SQL capabilities that add JSON support, these features are all available on-premises. But much of the emphasis with this release is on integrating with the following cloud services that are part of Microsoft’s Intelligent Data Platform:
- Azure SQL Managed Instance: Primarily used for disaster recovery and replicated data failover.
- Azure Synapse Analytics: For monitoring and managing data changes.
- Microsoft Purview: Used to scan, classify, track and control data on your own SQL Servers for compliance and data governance.
- Microsoft Defender for SQL: Useful for security and authentication, even with on-premises servers
- Azure Active Directory: Allows you to use the Azure portal to manage SQL Server wherever you run it.
Why so much focus on Azure and the cloud?
With such a strong focus on cloud compatibility and Microsoft Azure in particular, it’s easy to believe that SQL Server 2022 is only right for current Azure users. However, this new release is not about inducing organizations to move to Azure, Asad Khan told TechRepublic: It’s about giving everyone the benefits of innovations in the cloud without having to migrate.
“What we wanted to do with SQL 2022 was blur the line between whether you are running SQL in Azure or whether you are running outside. And when I say outside, it could be a customer data center, it could be AWS, it could be GCP. It could be anywhere.”
SEE: Top cloud and application migration tools (TechRepublic)
As an example of how SQL Server 2022 works beyond Azure environments, Defender for SQL can monitor your SQL Server workloads on-premises or in other clouds and warn you about various performance and security issues. There’s also integration with Azure Storage; the object storage support means you can connect to any S3-compatible storage provider, including AWS.
Creating an optimal SQL Server experience for all users
SQL Server is built from the same codebase regardless of how you use it. Whether you’re using Azure in your own data center, in a VM, in a container, at the edge with Azure SQL Edge’s subset features, through the Azure SQL Managed Instance, or through DB services, new and improved SQL Server features are showing up everywhere.
“If I do an improvement in the query processing, [or] if I improve the storage capability, or [if] I do something for Polybase or virtualization, it goes to all the deployment options,” Khan explained.
However, some features take more work to make available outside the cloud. Azure SQL Database had an immutable ledger some years ago, and Microsoft decided to bring a similar feature to SQL Server 2022. You can now create a cryptographically hashed, audited ledger table that makes it obvious if someone attempts to tamper with the data.
You can also make an append-only table if you need to prove to someone who might or might not trust you that the data hasn’t been altered. An airplane manufacturer considered using Ledger in SQL Edge on its planes to provide a trusted record for the FAA, Khan revealed to us. “We are able to solve 80% of the blockchain scenarios through this.”
Making cloud data recovery available to cloud and on-prem users
But there are also services in Azure, which SQL Server connects to natively, that aren’t going to be as easily available on other infrastructure. “We realized that unless we break this barrier between the cloud and on-prem, [then] all the innovation that we are doing in the cloud, our on-prem customers will never get,” said Khan.
The Microsoft team is continually looking for solutions to bridge the gap between Azure and on-premises SQL Server users. One integration that proved very popular in the preview version of SQL Server 2022 was using Azure SQL Managed Instance for DR (disaster recovery), which is now very simple to set up using a wizard and comes with no extra licensing cost.
SEE: Best data recovery software (TechRepublic)
Although you will be charged for Azure resources like storage for the databases you replicate to the cloud, you don’t need to budget for the usual per-core SQL license. That’s because Microsoft now treats this as a third DR option alongside the free read replica and free passive instances already included in the SQL Server license.
Even organizations that have decided to keep SQL Server on their own infrastructure rather than moving it to the cloud are interested in cloud DR because of the costs of standing up a failover server in a separate data center region; some might already use a hosting provider to do exactly that.
The link to Managed Instance uses the same distributed availability groups database admins will already be using for DR, so you can try out cloud DR alongside any existing DR infrastructure to see if you can replace that infrastructure when you’re ready, or when your contract runs out. “[If] you are using a PaaS service for the DR, it comes for free,” Khan pointed out: “Why would you not do it?”
Plus, the replica you get in Azure isn’t passive: It’s read-only, but it’s data you can read and use for analytics. “You can do queries against it; you can do Power BI against it because now you’re in the cloud,” added Khan.
SQL Server integrations with analytics at the forefront
If you want to go further with analytics — an area where Khan notes most of the innovation is happening in the cloud — you can also integrate with Azure Synapse, a service originally developed for Azure Cosmos DB, which continually streams the change feed from your database into a data lake.
Within the data lake, you can use your choice of cloud analytics tools to work with data without impacting your operational database or waiting for ETL batch jobs to run. Again, this uses existing SQL Server capabilities. In this case, the self-hosted integration runtime is already used by Azure Data Factory.
SEE: Best ETL tools and software (TechRepublic)
If you’re already replicating your database into Azure SQL Managed Instance for operational reasons, you can link that to Azure Synapse rather than moving data to the cloud twice, once in the proprietary SQL Server format and once in an open format that you can use with either SQL or Spark runtimes. On the other hand, you might want to enable both connections.
“There are fine-tuning knobs in each,” Khan pointed out. “For Synapse Link, you can choose which database and which tables you want the analytics on, whereas for a DR scenario, you typically pick it at a database level. We didn’t want to say ‘you can only do analytics on the databases you have picked for DR’”. Think of the connections as Lego blocks you can mix and match to build the system you need, he suggested.
SEE: Hiring kit: Microsoft Power BI developer (TechRepublic Premium)
That kind of flexibility makes the link to Managed Instance more than just a simple way to do DR. It also lets you test the performance of SQL Server on Azure and get familiar with working with a cloud service through your existing database tools like SQL Server Management Studio and the rest of the Azure portal.
If organizations find the ability to do Power BI and analytics queries against their data in the cloud useful enough, that might give them the confidence to move to the cloud. With the database replicated in Azure already, that’s as simple as failing over to Managed Instance.
How can I get started with SQL Server 2022?
SPLA, Microsoft Cloud Solution Providers and OEMs will get access to this tool in January 2023. According to Microsoft, this second group accounts for less than 20% of the SQL Server business.
For most organizations, SQL Server 2022 is available now, starting on November 16, 2022. You can access the new tool directly from the Microsoft website and get more information from their team there. For newer SQL Server users, we highly recommend investing in a Microsoft SQL Server Course for Beginners to get your initial footing with the platform.
Read next: Top data governance tools (TechRepublic)
