How To Create A SQL Server 2022 Learning Sandbox In Few Easy Steps

Microsoft has recently released the public preview of SQL Server 2022. You can find info here.

Microsoft peddles SQL Server 2022 as “the most cloud enabled version Microsoft has ever released.” So, whether you’re hosting your SQL Server On-Premises or On Cloud (IaaS and/or PaaS), SQL Server 2022 might just be the perfect solution.

Below are some of the SQL Server 2022 features that I am most excited about:

• New permissions & roles (Info here)
• Query Store hints (Info here)
• Ledger (Info here)
• Parameter sensitive plan optimization (Info here)
• Degree of parallelism (DOP) feedback (Info here)

You can find the whole list of features here. The latest version is still on Public Preview as of this writing, expect for many changes as Microsoft nears the RTM release.

Maybe you want to get your hands dirty with the bells and whistles of the latest iteration of SQL Server, but you don’t have an extra bare metal or Azure or GCP based VM. Well, you’re in luck because Microsoft just released container images for SQL Server 2022 (check the container announcement here).

Here are the few steps to get started with the SQL Server 2022 version using a Docker container image for SQL Server 2022 on Linux:

1. Download and Install Docker Desktop in your laptop. Get started with Docker Desktop here.
2. Once you’re set up with Docker, the next step is the installation of container. For this example, I’m using the Linux version. Open Power Shell and enter the following docker command:
   
   docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=MySuperSecureSAPassword" -p 1433:1433 -v 'c:\docker\sql2022:/var/opt/mssql/data' -d mcr.microsoft.com/mssql/server:2022-latest
   
   That’s pretty much intuitive so I am not going to explain the parameters. The -p parameter (1433:1433) is there so I can access the SQL Server instance in the container via the SSMS installed in my laptop.
   
   At this point, you now have a container running a SQL Server 2022 instance (Click on the Play button if the container is not running).
   
   Note: Docker logs says, “This is an evaluation version.  There are [163] days left in the evaluation period.”
   
   Docker creates their containers with funny names (I got “fervent_almeida” in this case), to change that you can simply rename it by:
   
   docker rename <Container> <New Name>
docker rename fervent_almeida SQL2022
   
   
3. Start the container. Now, you can connect to the SQL Server 2022 instance in the Docker Container like this:
   
   
   
   And, we are connected…well, I was able to connect after 3 cycles of starting the container. For some reason, the container kept stopping. Then after the 4th cycle, the container status stabilized.
   
   
   
4. The SQL Server 2022 instance is unusable without a full-fledged database of course. Download a copy of WideWorldImporters-Standard.bak here. I downloaded the backup file to C:\temp.
   
   Note: I couldn’t make the WideWorldImporters-FULL.bak work. The RESTORE was barfing on the filestream file, and I just gave up and went with the standard backup which worked. Another thing is that you can only raise the compatibility level of the database from the standard backup file up to 2019.
   
   Create a backup directory in the SQL Server Container.
   
   docker exec -it SQL2022 mkdir /var/opt/mssql/backups
   
   Now copy the backup file from the local C:\temp to the backup folder in the container:
   
   docker cp C:\temp\WideWorldImporters-Standard.bak SQL2022:/var/opt/mssql/backups
   
   List the logical name of the data file, just so you know how to call them:
   
   docker exec -it SQL2022 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "MySuperSecurePassword" -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/WideWorldImporters-Standard.bak'"
   
5. Restore the WideWorldsImporters-Standard.bak. You may notice that I have added the REPLACE command. This was because the RESTORE was barfing about the system not finding the file specified.
   
   NOTE: As a workaround, after the first failed RESTORE, “touch” the files so that they have a modification time. You can then replace them later in the RESTORE command. (And I don’t know why this works, so don’t ask me why)
   
   Ref: Stackoverflow
   
   Touch the files like this:
   
   docker exec SQL2022 touch /var/opt/mssql/data/WideWorldImporters.mdf
docker exec SQL2022 touch /var/opt/mssql/data/WideWorldImporters_userdata.ndf
docker exec SQL2022 touch /var/opt/mssql/data/WideWorldImporters.ldf
   
   You can then RESTORE WITH REPLACE like this:
   
   docker exec -it SQL2022 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "MySuperSecurePassword" -Q "RESTORE DATABASE WideWorldImporters FROM DISK = '/var/opt/mssql/backups/WideWorldImporters-Standard.bak' WITH MOVE 'WWI_Primary' TO '/var/opt/mssql/data/WideWorldImporters.mdf', MOVE 'WWI_UserData' TO '/var/opt/mssql/data/WideWorldImporters_userdata.ndf', MOVE 'WWI_Log' TO '/var/opt/mssql/data/WideWorldImporters.ldf', REPLACE"
   
   And, there you go…
   
   

Disclaimer: This test is performed in my own personal laptop. No company laptop is involved in any way. This blog is provided “as is” without warranty of any kind.