Microsoft is heading in the direction of the cloud. SQL Server 2022 is no exception, with the new database server offering enhanced functions for a closer tie-in to Azure, creating new opportunities for companies. However, I'll first look at SQL server licensing because, starting in version 2022 and following the abolition of the Open License program, SQL server will exclusively be available as a Cloud Solution Provider (CSP) purchase license or in the Open Value volume license program. You need to take this into account when planning licensing. Incidentally, as of this year, Windows Server is no longer available for commercial customers in the Open program for volume licensing, which also plays a role for the database server.

Azure Hybrid Advantage for SQL Server [1] lets you use licenses for locally operated SQL servers in Azure. Microsoft offers price reductions for this purpose, which is an important consideration in high-availability scenarios with SQL Server 2022 and Azure SQL Managed Instance. Therefore, it makes sense to look into server licensing in good time. A limited version of SQL Server 2022 will also be released for Linux. Microsoft continues to recommend the use of Linux containers for container installation. SQL Server 2022 cannot be operated in Windows containers.

SQL Server Used Locally

The connection between local SQL servers and Azure SQL instances will still be optional in the future, which means SQL servers can be operated locally without relying on functions from Azure. However, the cloud offers many advantages in terms of availability, replication, and data analysis. As the volume of data in the enterprise continues to increase, role owners need to deal with large quantities of structured and unstructured data. Additionally, you have the choice of centralized and decentralized storage approaches, numerous analysis options, and both relational and non-relational data. SQL Server 2022 is an obvious choice because analytics data can be comprehensively outsourced to the cloud.

Basically, migrating from SQL Server 2017 or 2019 only makes sense if you intend to use the new server features, especially Azure SQL Managed Instance. Windows Server 2022 is the operating system on which SQL Server 2022 resides. However, the expectation is that the database server will also run on Windows Server 2016 and 2019. You do not need a separate database server if SQL Server 2022 is used as an Azure SQL Managed Instance.

The focus of connecting on-premises SQL Server 2022 installations with Microsoft Azure is therefore likely to be availability and data analytics. For example, Azure SQL Managed Instance helps admins make databases available more quickly and in a more reliable way in case of disaster recovery (Figure 1). Managed Instance can therefore be viewed as an extension of SQL Server 2022.

Figure 1: Local SQL servers can implement disaster recovery scenarios with Azure SQL Managed Instance.

Managed SQL databases in Azure are also a good way to migrate on-premises databases to the cloud; after all, Azure SQL Managed Instance offers the features of the latest SQL Server version. The release of SQL Server 2022 also sees Microsoft aligning features between cloud and on-premises installations.

Establishing Reliability

In the event of a failover, Azure SQL Managed Instance can adopt all the databases of an on-premises SQL server, which means that corporations can establish disaster recovery strategies in the cloud without relying on their own hardware. The functionality of the cloud database is the same as that of a local installation, and on-board tools in SQL Server 2022 are used for the setup.

Previously, you could deploy cloud databases with Azure SQL, but you had to rely on a virtual database server. All you could save by migrating was the overhead of running a physical server on your own network: not so with the deployment of Managed SQL Instances in the context of SQL Server 2022. Managed Instances combine the benefits of Azure SQL databases with those of the on-premises data center. The service is ready for use out of the box and is updated and maintained by Microsoft. Moreover, you have, as mentioned previously, the benefit of high availability between local SQL servers and databases in the cloud. However, SQL Server 2022 licensing needs to be viewed separately from the pay-per-use model in Azure.

The data in the managed databases is backed up automatically, and subscribers can set the retention period themselves. In Azure, you can monitor databases in the same way as locally operated databases on SQL Server 2022. All applications that require access to a SQL database can easily use the cloud variant.

In this regard, the Managed Instances of an Azure SQL database, as a platform-as-a-service (PaaS) solution, differ from the other infrastructure-as-a-service (IaaS) offerings in Azure. Where an Azure SQL database runs as an IaaS service, the database runs on a virtual SQL server in Azure. This virtual server needs to be updated, backed up, and managed. If you use a managed PaaS version, you only need to manage the database yourself. The underlying server remains completely in Microsoft's hands. This organization significantly simplifies high availability with SQL Server 2022. If required, Microsoft offers additional options in the form of the Data Migration Service (DMS) [2] for importing databases from locally operated SQL servers into Azure.

An Azure SQL Managed Instance resides on a virtual network and connects to other PaaS services and virtual machines. Customers can also create private connections to the database with a site-to-site virtual private network (VPN) or Azure ExpressRoute, which is particularly important for a high-performance connection of SQL Server 2022. The data flowing through the network has Transport Layer Security (TLS) encryption. Dynamic data masking is also included and can automatically hide sensitive areas when displaying information in web applications. You can also import data from a backup into managed SQL instances and migrate that way. For example, a local SQL server can be set to back up to an Azure storage account (blob store). You can then create a managed SQL instance from this backup.

SQL Server 2022 relies on a new cloud feature for interaction with Azure SQL Managed Instance; consequently, on-premises SQL Server installations can be transferred to Azure for disaster recovery. This operation is handled by a Distributed Availability Group (DAG) that extends from the on-premises data center running SQL Server 2022 to an Azure SQL Managed Instance. The instance is on standby for this purpose and takes up the tasks of a local SQL server in the event of failure or maintenance. The setup is easily handled. Moreover, you could also set up extensive read scale-out scenarios in the same way. Queries that would overload the local SQL servers can be redirected to the Azure SQL Managed Instance, significantly reducing the load on the local servers.

Azure Services Data Analysis

In addition to a closer tie-in to Azure SQL Managed Instance, SQL Server 2022 also supports connectivity to Azure Synapse. Azure Synapse Analytics allows organizations to aggregate and prepare data queries from multiple sources in the cloud. This analysis also works in real time. For example, it can be used in Internet of things (IoT) environments, which often require rapid analysis of large volumes of data in a short time.

Previously, the data exchange between SQL servers and Azure Synapse was handled by an extract, transform, and load (ETL) pipeline. A configuration like this is tricky to set up and manage. In SQL Server 2022, Microsoft supports change feeds between the on-premises SQL Server and Azure Synapse, which enables near real-time analytics and hybrid processing with a minimal effect on local systems. The service combines decentralized data structures in a clear interface.

Toward that end, Azure Synapse Analytics brings together data integration, enterprise data warehousing, and Big Data analytics, and it lets organizations visualize their data with Power BI (Figure 2). Thanks to support for Azure Synapse Analytics, organizations can also benefit from Azure Machine Learning (ML) technologies when deploying SQL Server 2022. Additionally, data from Azure Data Lake Storage and from Spark pools can be used together with SQL Server 2022.

Figure 2: Connecting SQL Server 2022 with Azure Synapse Analytics opens up new possibilities for data analysis.

The new service sees Microsoft looking to expand its modern data warehouse strategy and to empower enterprises to analyze their data more effectively and quickly – including in the big data field. SQL Server 2022 at the local data center is the ideal component for making local data available in the cloud. The various functions are illustrated in a YouTube video [3]. One advantage of Azure Synapse Analytics is its scalability. Almost unlimited amounts of data can be loaded from external systems and analyzed, even in real time and including external data warehouses and data from big data systems.