One of the first things you will want to do is to install SQL Server 2022 so you can explore all the goodness it has to offer. The great news for anyone familiar with SQL Server is that the installation experience for SQL Server 2022 is fairly identical to previous releases with a few minor changes.
If you are an experienced SQL Server user, in this chapter you can review the highlights of what is different for SQL Server 2022. If you are someone new to SQL Server, don’t worry. First, the installation process is amazingly simple, and our user experiences walk you through all of it. Second, if you need a “step-by-step” process, go straight to our documentation at https://aka.ms/deploysqlserver2022.
How to Install SQL Server 2022
You may be used to getting started with SQL Server by installing the Developer or Evaluation free edition of SQL Server and evaluating the product. SQL Server 2022 supports both of these editions, so if you just want to jump in, go to https://aka.ms/getsqlserver2022 and start testing!
I know that some people want to know more about the installation of SQL Server before they go “straight for the bits.” In this section of the chapter, I’ll review the prerequisites to install SQL Server and discuss the differences from previous versions and deployment options.
This section of the chapter focuses on installation of SQL Server on Windows. The section titled “Deploying on Other Platforms” later in this chapter discusses installation on Linux, containers, Kubernetes, and Azure. The details for deploying on these platforms can be found in Chapters 9 and 10 of the book.
Prerequisites
The prerequisites, including resources required, to install SQL Server 2022 on Windows have not changed from previous major versions of SQL Server, except the specific Windows operating system versions or Linux distributions we support. As in the past, we will support versions of Windows Server and Windows (client) that are “officially” supported, including any specific updates of Windows required to be supported. Consult the SQL Server 2022 release notes for any possible changes to prerequisites for SQL Server 2022 at https://docs.microsoft.com/sql/sql-server/sql-server-2022-release-notes.
One difference for prerequisites from previous versions of SQL Server is the requirement to be connected to Azure to support some of the new cloud-connected scenarios. The most important requirement is that if you choose to set up Azure-connected scenarios during installation, you will need an Azure subscription and a service principal.
Some cloud-connected scenarios like Managed Instance and Synapse Link do not require any special steps during setup. New capabilities like Azure Active Directory (AAD) and Microsoft Purview integration do require this configuration.
I’ll describe the exact steps for this requirement later in this chapter in the section titled “Setting Up the Azure Extension for SQL Server.” I’ll also go more into the specifics of how SQL Server is connected to Azure in Chapter 3 of the book.
To find all the exact resource requirements you need to install SQL Server 2022 on Windows, review the documentation at https://aka.ms/deploysqlserver2022.
What Is Different for SQL Server 2022?
Installing SQL Server 2022 on Windows using the “wizard” is remarkably similar to installing SQL Server from previous releases. Therefore, I won’t show you a “screen-by-screen” experience. See the complete set of steps at https://aka.ms/deploysqlserver2022.
I will make the boastful claim that I have probably installed and uninstalled SQL Server 2022 more than anyone in the world. I honestly lost count from November 2021 to November 2022 how many times I’ve installed and uninstalled SQL Server 2022. Maybe only beat by our automation tests at Microsoft!
Rather, I’ll discuss differences from previous releases. Let’s look at each of these differences from a perspective of running a SQL Server setup.
Azure Extension for SQL Server
The first option you will be presented with before choosing features is the Azure extension for SQL Server. The Azure extension for SQL Server feature represents software running on the computer or VM where SQL Server is installed to connect it to Azure. The capability for this extension is not new as it is found through Azure Arc–enabled SQL Server (you can read more at https://docs.microsoft.com/sql/sql-server/azure-arc/overview). You can still use the method in the documentation to connect SQL Server 2022 to Azure. We are just providing a method to do this as an integrated part of SQL Server setup.
This is a big topic to cover, so to learn more about the prerequisites and how to fill out the information for this feature, see the section later in this chapter titled “Setting Up the Azure Extension for SQL Server.”
Feature Differences
A screenshot of the S Q L server 2019. The instance features of python, java connector, machine learning server, and distributed replay client are highlighted.
SQL Server 2019 setup features with features removed highlighted
A screenshot of the S Q L server 2022. Under the feature selection file is a list of instance features selected for S Q L server 2022.
The SQL Server 2022 feature selection
Let’s look further at the features removed from SQL Server 2022.
Removal of R, Python, and Java
In SQL Server 2016, 2017, and 2019, we included options to install open source runtime packages for R, Python, and Java as part of machine learning services and the language extension feature. While this was convenient for many, we found a lot of our customers using this feature wanted to use their own runtime package. We also had to figure out how to update the open source packages we installed. Because of this, we felt it didn’t make sense to continue to install these packages as part of setup. In SQL Server 2022, we no longer offer these packages as an option during setup. If you want to use machine learning services or language extensions, you still must choose this feature during setup.
Instead you can install your own packages. See the documentation at https://docs.microsoft.com/sql/machine-learning/install/sql-machine-learning-services-windows-install to get started for R and Python. For the Java language extension, you can start at https://docs.microsoft.com/sql/language-extensions/install/windows-java.
Removal of Polybase Hadoop Connectivity with Java
In SQL Server 2016, we introduced the concept of Polybase, the ability to query data in different formats and storage “where they live” using T-SQL. The original design translated T-SQL statements into Java code to query files in Hadoop systems. It was revolutionary, but the specific connection to Hadoop systems never really took off as a popular feature among a lot of our customers.
In SQL Server 2022, we have discontinued providing this feature along with Polybase scale-out groups. You can read more about the retirement of Polybase to Hadoop with Java, scale-out groups, and Big Data Clusters at https://cloudblogs.microsoft.com/sqlserver/2022/02/25/the-path-forward-for-sql-server-analytics/. However, the concept of Polybase is still alive and well. You will see in this book in Chapter 7 a new implementation of Polybase using REST APIs.
In order to use Polybase REST APIs, you need to select the Polybase Query Service for External Data feature. This will install the Polybase services. The Polybase REST APIs do not use these services but are baked directly into the engine. However, for now you must select this feature to enable configuration options to support Polybase REST APIs. Polybase services for ODBC drivers also require you to select this feature and do use Polybase services.
Removal of Machine Learning Server
In the summer of 2022, we announced the retirement of Machine Learning Server and stated it would no longer be a feature in the next version of SQL Server. Therefore, it is not a feature option in SQL Server 2022 setup. You can read more about this retirement statement at https://docs.microsoft.com/lifecycle/announcements/microsoft-machine-learning-server-retiring.
Removal of Distributed Replay
Distributed replay is a tool to replay SQL Profiler traces and has been in the product for many years. We have decided to unbundle this feature from SQL Server installation and will make it available in a separable download package. I will be transparent with the readers of this book that we have not enhanced this capability for quite some time.
Memory Recommendations
In the past we introduced in setup the ability to configure “max server memory” for the instance of SQL Server, which includes recommended values. The recommend value for “max server memory” comes from our documentation at https://docs.microsoft.com/sql/database-engine/configure-windows/server-memory-server-configuration-options.
We have determined that we didn’t quite calculate this correctly, and we’ve updated the method in SQL Server 2022 setup. Be aware that this recommendation is very conservative in my opinion, so I would be careful about using it. The calculation for “max server memory” is ~75% of available free memory at the time setup is run. We built this to apply it across many different workloads and configurations. It is also important to note that this recommendation doesn’t consider other SQL instances, so if you run multiple SQL instances on the same server, you need to carefully consider the value for “max server memory” for each instance.
Other Installation Methods
SQL Server setup for Windows still supports options without a user interface through the command line. You can find examples of how to do this and see all the options at https://docs.microsoft.com/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt. The only differences for SQL Server 2022 are the removal of options for discontinued features as described earlier in this chapter and the addition of new parameters to support the Azure extension for SQL Server.
If you are new to setting up SQL Server, you should know that the SQL Server Evaluation and Developer Editions come with an “easy setup” mode with an installer that installs just the defaults without going through any screens. If you choose this method, the Azure extension for SQL Server feature is not selected. If you want the full setup experience with the Evaluation or Developer Edition, choose the Custom option from the initial setup screen.
Setting Up the Azure Extension for SQL Server
In 2020, we announced a new hybrid capability for SQL Server called Azure Arc–enabled SQL Server. The concept is to take an existing installation of SQL Server and connect to Azure to provide new capabilities such as viewing instance details in the Azure portal, Microsoft Defender, and best practices assessments. The process of setting up SQL Server with Azure Arc is done from the Azure portal or through scripts. SQL Server 2022 provides an integrated method to install this in the SQL Server setup process, through a setup feature called the Azure extension for SQL Server.
Microsoft Defender for SQL
SQL Assessment (which you can read more about at https://docs.microsoft.com/sql/sql-server/azure-arc/assess)
View of a connected SQL Server in the Azure portal along with other Azure SQL resources
Azure Active Directory (AAD) authentication
Access policies in Microsoft Purview (Purview integration requires AAD, which also requires the Azure extension for SQL Server)
You will learn more about these features in Chapter 3 of the book. I will describe more about the role of Azure Arc–enabled SQL Server in Chapter 11 of the book. Just connecting SQL Server to Azure does not cost you anything. It is only when you select certain services such as Microsoft Defender or Purview that you incur subscription costs.
The Azure extension for SQL Server is not supported when running SQL Server on an Azure Virtual Machine. SQL Server uses the Infrastructure-as-a-Service (IaaS) Agent Extension to provide capabilities like Defender, assessments, and AAD authentication. You can read more about the IaaS Agent Extension at https://docs.microsoft.com/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management.
A screenshot of the S Q L server 2022. It has options for azure service and subscription I D, resource group, region, tenant I D, and server U R L for azure extension.
Setting up the Azure extension for SQL Server
I’ll describe how to fill out these fields with information for Azure. I have some example files that can help you in this chapter in the ch2_install folder for the samples that come with the book.
What You Should Know First
The following information assumes you have an Azure subscription and your SQL Server is connected to the Internet directly or will be through a proxy. If you don’t have an Azure subscription, check with your organization. Or you can get started at https://azure.microsoft.com/get-started/.
Read up on the concept of Azure Role-Based Access Control (RBAC). RBAC is a permission system in Azure to give you rights to perform certain actions in Azure. You can learn more about Azure RBAC at https://docs.microsoft.com/azure/role-based-access-control/overview.
You need to check your permissions within your Azure subscription to ensure you can create a resource group and custom permissions for Azure Arc or the ability to create a custom role and a service principal. If these examples fail due to lack of permissions, check with the administrator of your Azure subscription. The permissions for your Azure account or a custom role to be used with a service principal can be found at https://docs.microsoft.com/sql/sql-server/azure-arc/overview#required-permissions.
A valid Azure Resource Group. You may have one already you want to use, or you can create a new one. You can use this documentation page to learn how to create a new resource group: https://docs.microsoft.com/azure/azure-resource-manager/management/manage-resource-groups-portal. When you create the resource group, choose an Azure region that is supported for Azure Arc–enabled servers, which is documented at https://docs.microsoft.com/sql/sql-server/azure-arc/overview#supported-azure-regions.
I recommend you install the az command line interface (CLI) from https://docs.microsoft.com/cli/azure/ or use the Azure Cloud Shell (which you can learn more about at https://docs.microsoft.com/azure/cloud-shell/overview).
If you decide to use a service principal, you will need to create a custom role in Azure that includes the following permissions: https://docs.microsoft.com/sql/sql-server/azure-arc/overview#required-permissions. To learn how to create a custom role, go to https://docs.microsoft.com/azure/active-directory/roles/custom-create.
For my tenant at Microsoft, I didn’t have permission to create a custom role in the Azure portal. However, I was able to create one using the az CLI as documented at https://docs.microsoft.com/azure/role-based-access-control/custom-roles-cli. You can see my example JSON file from the samples for this chapter called sqlazureext.json. The script to create the role using this JSON file is in the sample file createcustomrole.ps1.
If you decide to use a service principal, you can do this with the az command line interface (CLI) to create one for your subscription and resource group: https://docs.microsoft.com/cli/azure/create-an-azure-service-principal-azure-cli.
You will assign the service principal to the custom role and resource group you created. You can see an example of how to do this with the script sqlazureextsp.ps1.
When you create a service principal with the CLI, you will get a JSON result that will contain values for fields you will need for this screen. Be sure to save this result, especially the password. I’ll show you how each field from the output lines up with the fields on the feature screen for SQL Server setup.
Don’t include the quotation marks when using these values on the feature setup screen.
Service principals are highly privileged accounts in Azure. I created my service principal at the resource group scope, but the permissions assigned with the custom role are highly privileged. If you decide to disconnect and remove your SQL Server from Azure, make sure to remove the service principal.
Providing Values for the Feature Setup
With these resources created, you can fill out the fields from Figure 2-3 (all of these fields except Proxy Server URL are required).
Checking the Azure Extension for SQL Server
If you don’t want to install the Azure extension for SQL Server, you can uncheck this box. Leave it checked to install the extension. Remember that you don’t incur any costs just to set up the extension.
Login or Service Principal
Azure Tenant ID
This is an ID for your Azure Active Directory associated with your account.
Azure Subscription ID
This is the default subscription ID for your Azure account. We will register your SQL Server under this subscription. You can choose a different subscription from a drop-down list.
Azure Resource Group
We will choose a resource group under your subscription, which we will use to register your SQL Server. You can choose from a drop-down list a different resource group.
Azure Region
We will choose the Azure region from the resource group, but you can choose a different region from a drop-down list.
Proxy Server URL
You can optionally put in a proxy URL if your SQL Server cannot directly connect to the Internet. By default, the agents installed with the extension on the server will use outbound TCP port 443 to connect to Azure services over the Internet. You can set up a proxy server. If you use a proxy server, put in the URL of the proxy in this field. You can read more about this at https://docs.microsoft.com/azure/azure-arc/servers/manage-agent#update-or-remove-proxy-settings. Leave this field blank if you are not using a proxy.
Azure Service Principal ID
This is the value of appId from the JSON result of creating the service principal.
Azure Service Principal Secret
This is the value of password from the JSON result.
Azure Subscription ID
This is the subscription ID value you used as part of the –scopes parameter when you created the service principal.
Azure Resource Group
This is the name of the resource group you created as a prerequisite and used in the –scopes parameter to create the service principal.
Azure Region
The name of the Azure region where you created the resource group. The name of the region here should not contain spaces and should be lowercase. My region was East US, so the value should be eastus.
Azure Tenant ID
This is the value of the tenant from the JSON result when you created the service principal.
Proxy Server URL can also be used in this scenario.
What happens now?
A screenshot. 2 files titled azure connected machine agent and Microsoft S Q L server extension are highlighted with their publisher, size and version.
Azure Arc Agent and Azure extension for SQL Server
In addition, the Azure Connected SQL Server Onboarding role will be assigned automatically to the service principal. This role is required to connect SQL Server to Azure.
A screenshot of the azure portal. It has 2 files with their type and location for the resource group.
Azure Arc–enabled SQL Server in the portal
A screenshot of the main page of the azure portal. It has a list of essentials at the top and capabilities at the bottom.
Azure Arc–enabled SQL Server main portal page
Connect to Azure After Setup
If you decide not to select the Azure extension for SQL Server feature during SQL Server 2022 setup, you can connect your SQL Server 2022 instance with Azure at a later time following the steps at https://docs.microsoft.com/sql/sql-server/azure-arc/connect. Or you can go back and “add a feature” to the existing SQL Server 2022 instance, which provides you with the same screens as with the original setup. You can also connect many SQL instances at scale using steps found at https://docs.microsoft.com/sql/sql-server/azure-arc/connect-at-scale.
Removing the Azure Extension for SQL Server
You can disconnect your SQL Server 2022 instance at any time from Azure using the steps documented at https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/connect#delete-your-arc-enabled-sql-server-resource. If you decide to completely remove the connection to Azure, I recommend you also remove the Azure extension for SQL Server using SQL Server setup.
Deploying on Other Platforms
This chapter has focused more on the experience of installing SQL Server on Windows. SQL Server is supported on other platforms including Linux, containers, and Kubernetes. Chapter 9 of this book will cover those experiences, but if you want to jump right in, you can use these resources:
SQL Server on Linux – https://aka.ms/sqllinux
SQL Server on containers – https://aka.ms/sqlcontainers
SQL Server on Kubernetes – https://aka.ms/sqlk8s
This page contains instructions on deploying a SQL container on Azure Kubernetes Service (AKS). Consult your Kubernetes platform documentation on the proper method to deploy pods and containers.
Be sure to consult the latest release notes for SQL Server 2022 on Linux and containers at https://docs.microsoft.com/sql/linux/sql-server-linux-release-notes-2022.
In addition, SQL Server 2022 can be deployed on Azure Virtual Machines. Chapter 10 of this book will go through more details on this experience. To get more information on SQL Server on Azure Virtual Machines, consult our documentation at https://aka.ms/sqlazurevm.
Side-by-Side and Multi-instance Installations
Like previous releases of SQL Server, SQL Server 2022 supports side-by-side installations on the same computer with different versions (supported versions) and multiple instances (aka named instances). There are no differences in support for both side-by-side and multi-instance installations from previous SQL Server releases. For more information, consult our documentation at https://docs.microsoft.com/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server. SQL Server on Linux supports multiple instances by using containers.
How to Upgrade to SQL Server 2022
SQL Server 2022 supports upgrades from previous releases of SQL Server with the same methods as in previous versions. You can read more at https://docs.microsoft.com/sql/database-engine/install-windows/upgrade-sql-server for all the details.
At the time of the writing of this book, the plan was to support an “in-place” upgrade from SQL Server 2012 Service Pack 4 or any higher version and database restore upgrades from SQL Server 2008/2008R2 and higher.
The Importance of dbcompat
Over my career at Microsoft, it has become apparent that one of the reasons customers do not upgrade to a new major version of SQL Server is because the latest version is not compatible with their application.
For the past several SQL Server versions, we have been trying to convince customers and developers to test for compatibility with the database compatibility level (dbcompat) of user databases. If you upgrade a SQL Server database to a new version, we maintain the previous dbcompat. You can read more on why this could be a new strategy for compatibility for you at https://aka.ms/dbcompat. Note that using an older dbcompat gives you more time to explore and test the latest dbcompat levels, which enable new capabilities like Intelligent Query Processing (IQP), which is discussed more in Chapters 4 and 5 of this book.
Configuration
There are a few new server-level sp_configure options. They will be discussed throughout this book as they relate to new features for SQL Server 2022.
The SQL Server Configuration Manager has been enhanced to allow you to control the services (start and stop) for the Azure extension for SQL Server feature.
Other than that, there are no new instance-level configuration changes.
You will learn about new database-level configuration options as you explore new features throughout the book.
Easy to Install and Upgrade
SQL Server has been known for some time as a simple install-and-upgrade experience, and none of that changes in SQL Server 2022. As part of that experience, we have provided an integrated setup to get connected to Azure if that is part of your hybrid strategy.
If you read over all the details of this chapter, you are pretty much ready to dive in and learn about features. So let’s roll! The next chapter is about connecting SQL Server to the cloud in ways never before seen in the industry, which brings together SQL Server and Azure to provide disaster recovery, analytics, and security.