SQL Server 2022 on Windows Server 2022 Core Edition

Today I’m going to install SQL Server 2022 on Windows Server 2022 Core Azure Edition. In a previous blog I installed ADDS on Windows Server Core edition.

Part 1: Install and configure ADDS on Windows Server Core in Azure
Part 2: Install and configure ADDS on Windows Server Core in Azure

I have done a large number of SQL server implementations in recent years, both in on-prem datacenter and in Azure (IaaS). Now that SQL 2022 is available, I’ve taken a look at the differences compared to previous versions. Installing on Windows Server Core edition is also supported, so I take that scenario as a starting point.

Basic Infrastructure

As a basis, it is of course important that an Active Directory environment is available. In my demo environment I ran a domain controller in Azure based on Windows Server 2022 Azure Edition. This server provides the other servers with DNS. The domain is called ‘demo.lab’.

SQL server preparations

As already mentioned, I performed the SQL Server 2022 installation on a server with Windwos Server 2022 Core edition Azure Edition. For a complete list of available versions of Windows Server 2022, see the link below.
https://learn.microsoft.com/en-us/windows-server/get-started/editions-comparison-windows-server-2022?tabs=full-comparison

The new SQL server has the following resources:

  • 2 vCPU
  • 8 gb memory
  • OS disk 127 GB
  • Application disk 100 GB
  • SQL DB & Log disk 20 GB

This is a demo environment, so the sizing of the server is not according to the recommendations. Always make sure you’re making the right sizing for your production environment and workloads.

For an overview of the prerequisites for installing SQL Server 2022, see the link below.
https://learn.microsoft.com/en-us/sql/sql-server/install/hardware-and-software-requirements-for-installing-sql-server-2022?view=sql-server-ver16

I used Terraform to roll out the server. In the variable file (.tfvars) Iused the latest version of the SKU ‘2022-datacenter-azure-edition-core’.


After a few minutes, the new Windows Server 2022 Azure Edition Core is up and running. The first step is to join the server to the Active Directory environment.

## to enable Remote Event Management.
Enable-NetFirewallRule -Displaygroup "Remote Event*" 


## to enable Remote Scheduled Tasks.
Enable-NetFirewallRule -Displaygroup "Remote Scheduled*" 

## to enable Remote Service Management.
Enable-NetFirewallRule -Displaygroup "Remote Service*" 

## to enable Remote Firewall Management
Enable-NetFirewallRule -DisplayGroup "Windows Defender Firewall Remote Management"


## to enable remote Disk Management
Enable-NetFirewallRule -Displaygroup "Remote Volume*" to enable Remote Volume Management


From my management server, I configured the disks, file system type, and partitions through Remote Disk Management (Server Manager)

SQL Server 2022 installation

Download the SQL Server 2022 installation files and copy them to the local drive of your SQL server. In my example ‘C:\_Install’.
Normally, you would start the setup using the ‘.\setup.exe’ command, but on Windows Server Core edition, you get an error.

You can use the ‘.\setup.exe /UIMODE=EnableUIOnServerCore‘ command to enable the graphical user interface, but this didn’t work for me either. I was not able to click on some links within the window.

I performed the installation via the command line. The link below shows all the available installation switches.
https://learn.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-ver16

This is a demo environment, so the installation is just an example. Always make sure you’re using the right installation switches and values for your production environment.

First, I’ve created a new service account ‘svc_SQL’ and a new security group ‘SQL_Admins’ within my Active Directory environment.

.\setup.exe /q /ACTION=Install /IACCEPTSQLSERVERLICENSETERMS="True" /FEATURES=SQL /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="demo.lab\svc_SQL" /SQLSVCPASSWORD="BM&mdi#hy78^%$#d" /SQLSYSADMINACCOUNTS="demo.lab\SQL_Admins" /AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE" /INSTALLSQLDATADIR="D:\Program Files\Microsoft SQL Server" /SQLUSERDBDIR="E:\DB" /SQLUSERDBLOGDIR="E:\DBLOGS" /SQLSVCINSTANTFILEINIT="True"

You can find the logging in the following directory.

C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log

Now it’s time to wait! The installation of SQL Server 2022 will take some time, it depends on what features will be installed.

What about the Windows firewall..?

A good hardened server has enabled the Windows firewall. It is important that the correct ports are allowed so that the newly installed SQL server is approachable and manageable.

I’ve created a Powershell script to enable the necessary ports within the Windows firewall configuration. You can download that script on my GitHub.

The script called ‘SQL Firewall Ports.ps1’.

SQL Server Management Tools

When the installation is completed, you can start installing the SQL Management Tools on your management server(s). In my environment, I’m using two management tools:

  • SQL Managed Studio (SSMS)
  • Azure Data Studio

In the link below you can download the latest version of Azure Data Studio. https://learn.microsoft.com/en-us/sql/azure-data-studio/download-azure-data-studio?view=sql-server-ver16&tabs=redhat-install%2Credhat-uninstall

After the installation has completed, the management tools are available.

We can successfully connect through SSMS (SQL Server Management Studio).

And we can also connect through the new Azure Data Studio.

When you want to migrate your SQL databases to Azure SQL or Azure SQL Managed Instance, you can install the ‘Azure SQL Migration’ extension within Azure Data Studio. From here, you can perform an database assessment to see what migration strategy is the best for your environment. After the assessment, you can migrate your databases to Azure.

Summary

When you are going to build a new SQL Server 2022 environment, it is definitely advisable to seriously look at Windows Server 2022 Core edition. This version of Windows Server 2022 has a small footprint, requires a restart less often, is optimally tuned for workloads and applications and has a smaller attack surface than the full-fledged Windows Server installation with GUI.

Thank you for reading and for supporting my blog.