Lead Image © Dmitriy Shpilko, 123RF.com

Lead Image © Dmitriy Shpilko, 123RF.com

SQL Server 2022 and Azure

Cloud Power

Article from ADMIN 71/2022
By
SQL Server 2022 focuses on even closer collaboration between on-premises SQL servers and SQL functions in Azure, including availability and data analysis. We highlight the innovations of the database server and the interaction with versatile and powerful Azure services.

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.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • The Azure Arc multicloud and on-premises management platform
    The Azure Arc cloud service supports centralized management of Windows and Linux servers, Kubernetes clusters, and SQL servers that are not themselves running in Azure, extending Azure management capabilities to servers in traditional data centers or any other cloud environment. We show you how to get Azure Arc up and running and look at its key features.
  • Migrating to Azure Monitor Agent
    The replacement for the Log Analytics Agent has improved security and cost efficiency, better manageability, and greater reliability – and you must migrate to this new solution by the end of 2024.
  • Azure AD and AD Domain Services for SMEs
    Azure Active Directory Domain Services is a Microsoft product, distinct from Active Directory and Azure Active Directory, that offers centralized directory services in the cloud in place of an often convoluted on-premises operation.
  • Server update with Azure Update Management
    Microsoft Azure Update Management automatically patches servers in on-premises data centers, virtual servers on Azure and other cloud services, and even Linux servers.
  • Private cloud with Microsoft Azure Stack
    Azure Stack is an Azure extension that implements an on-premises data center for consistent hybrid cloud deployments.
comments powered by Disqus