Use a Docker Container to Explore SQL Server 2022’s New Features

There’s a lot of hype surrounding the next version of SQL Server. And for good reason. SQL Server 2022 is packed with some long awaited niceties, improvements, and enhancements. Here are a few of the SQL Server 2022’s new features we are excited about.

• Contained Availability Groups
• Enhancements to Availability Groups and Distributed Availability Groups
• Query Store enhancements, including being available on secondary replicas
• Parameter Sensitive Plan (PSP) optimization
• Ledger to help assure data hasn’t been tampered with

You can learn more about SQL Server 2022 on the What’s new in SQL Server 2022 – SQL Server | Microsoft Docs site.

Now, how can you explore SQL Server 2022’s new features?

Test Driving SQL Server 2022

Containers are a great way to explore new versions of a product like SQL Server without having to actually install bits on your computer that you’ll later want to remove. Containers are lightweight, standalone, and have everything required to run SQL Server. There’s nothing to install directly on your host machine. And nothing to remove later, except, of course, the container and image file. But those won’t leave remnants of SQL Server in your registry or file system.

Prerequisites

To use SQL Server in a Docker container, you’ll need the Docker Desktop. There are versions for most every desktop platform, including Windows, Mac, and Linux. In Getting Started with SQL Server in a Docker Container – The SERO Group, I walk through the process of configuring Docker Desktop for a Windows 10 client. If you don’t already have the Docker Desktop installed, do that first.

Pulling the SQL Server 2022 container image

Once you have Docker Desktop in place, you can pull (download) the SQL Server 2022 image using the following command in the Windows command prompt.

docker pull mcr.microsoft.com/mssql/server:2022-latest

Starting the SQL Server 2022 container

Once the pull has completed, you can create the container from the image using the following command.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=iO1$6sJ&64YRCJAx" -p 1433:1433 --name sql2022ctp1 -d mcr.microsoft.com/mssql/server:2022-latest

The ACCEPT_EULA parameter indicates that you agree to Microsoft’s End User Licensing Agreement. SA_PASSWORD parameter is where you set the sa password for this instance. You’ll want to replace iO1$6sJ&64YRCJAx in the example above with a complex password of your choosing.

The -p parameter allows you to map a TCP port on the host environment to a port in the container. In this example, I’m mapping the default host environment port of 1433 to the container port of 1433.

Set the name for your container using the –name parameter. I’m naming this one sql2022ctp1.

And finally, the -d parameter is the name of the container image your want to run.

Listing your containers

You can use the following command to list all containers in your Docker environment.

docker ps -a

You can see that this Docker environment has two SQL Server 2019 containers, sql2019 and sql2019a, along with our newly created container, sql2022ctp1. The sql2022ctp1 container is running. The other two are not.

You can also see your containers in the Docker Desktop as shown below.

Querying the SQL Server 2022 instance

Using Azure Data Studio or SQL Server Management Studio, you can connect to your new SQL Server 2022 instance and query as normal from your host operating system. For example, running the following query in Azure Data Studio will return the SQL Server Edition, Version, and Product Level.

SELECT  SERVERPROPERTY('Edition') AS SQLEdition,
        SERVERPROPERTY('ProductVersion') AS ProductVersion,
        SERVERPROPERTY('ProductLevel') AS ProductLevel;

You can use the following query to review information about the host operating system.

SELECT  host_platform, 
        host_distribution, 
        host_release
FROM    sys.dm_os_host_info;

Exploring SQL Server 2022’s new features

Now that you have a working SQL Server 2022 instance, you can begin exploring its new features.

For example, I’d like to better understand the new Parameter Sensitivity Plan (PSP) optimization enhancements. So, I restored a copy of the BaseballData database that I sometimes use for testing and demos. See Getting Started with SQL Server in a Docker Container – The SERO Group for how to restore a backup to your container.

Next, I set the compatibility level of the BaseballData database to 160 and enable Query Store using the following t-sql commands.

ALTER DATABASE BaseballData  
SET COMPATIBILITY_LEVEL = 160;  

ALTER DATABASE BaseballData 
SET QUERY_STORE (QUERY_CAPTURE_MODE = auto);

To verify that setting the compatibility level to 160 enabled PSP optimization, I can run the following query.

SELECT name, value, is_value_default
FROM sys.database_scoped_configurations
WHERE name = 'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION'

Now I can begin experimenting how PSP optimization affects my queries. For more information about PSP optimization, see Parameter Sensitivity Plan optimization – SQL Server | Microsoft Docs.

Stopping and starting your Docker container

You can leave your container running all the time if you wish. However, if you like to stop it and start it again later, you can with the following command, where sql2022ctp1 is the name you gave to your container.

docker stop sql2022ctp1

To start the container later, use the following.

docker start sql2022ctp1

Want to work with The SERO Group?

Want to learn more about how SERO Group helps organizations take the guesswork out of managing their SQL Servers? It’s easy and there is no obligation. 

Schedule a free 30-minute call to see if our SQL Server Health Check or one of our SEROShield plans is right for you.

Schedule a call with us to get started.