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 a 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.

Here are few steps to get you started with SQL Server 2022:

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:dockersql2022:/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:tempWideWorldImporters-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.

The post How To Create A SQL Server 2022 Learning Sandbox In Few Easy Steps appeared first on SQL, Code, Coffee, Etc..