In a previous post I’ve covered how to use the new sqlcmd to spin up a SQL Server Developer Edition container, in this post I cover how to achieve that just by using Podman.
Prerequisites
Make sure you have WSL and Podman installed. You can use the same steps from my Create a SQL Server Developer edition container using sqlcmd post.
Note, if you already have Docker set up, you can follow the steps from this Microsoft Learn article.
Create the SQL Server container in Podman
I’ll be using the SQL Server 2022 Docker container image as an example, but the commands apply to 2019 as well.
The following command pulls, if not already in your local cache, the latest SQL Server 2022 container image and spins up a container with the following configuration:
- container name – sql2022
- hostname – sql2022
- sa password – S0MeP4sS!
- the instance in the container will listen to port 1433 which will be mapped to your machines port 1433
| 1 2 3 | podman run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=S0MeP4sS!" ` -p 1433:1433 --name sql2022 --hostname sql2022 ` -d mcr.microsoft.com/mssql/server:2022-latest |
| 1 | podman ps --all |
Connect to and interact with the container
To connect to the container and run commands from inside it you can use the podman exec command together with the container name or ID.
Connect as root
I recommend doing this especially if you want to create symlinks in order to fix the issues caused by the new path used for the new version of the SQL Server command-line tools for Linux. You can read more about that in this blog post.
| 1 | podman exec -u 0 -it sql2022 "bash" |
And to create the symlinks, while you’re connected as root.
| 1 2 | ln -s /opt/mssql-tools18/bin/sqlcmd /usr/local/bin/sqlcmd ln -s /opt/mssql-tools18/bin/bcp /usr/local/bin/bcp |
Might as well upgrade and install curl, because it will come in handy later.
Note that you’ll be prompted twice to confirm (press Y and Enter).
| 1 | apt update && apt upgrade && apt install curl |
Connect as a non-root user
For anything else you should interact with the container as the non-root user named mssql.
| 1 | podman exec -it sql2022 "bash" |
Connecting to the SQL Server instance hosted in the container
From inside the container
Note that the -C option is needed to avoid getting the error:0A000086:SSL error message.
| 1 | sqlcmd -S localhost -U sa -P S0MeP4sS! -C |
From the container’s host using SSMS
To connect from SSMS you can use the local IP 127.0.0.1 or you can use the hosts file (C:\Windows\System32\drivers\etc\hosts) to map the container name to the local port.
You should normally be able to also use localhost as the server name, but for some reason that doesn’t work for me in SSMS.
I’ve added the following entry in my hosts file:
| 1 | 127.0.0.1 SQL2022Container |
And connected from SSMS to both the local IP and to SQL2022Container and this is how the connections look in Object Explorer.
Restore a sample database
It’s all kinda pointless if the instance just sits there empty with no database to play with.
So, I also restore the AdventureWorks2022 database.
The instance root directory doesn’t contain a backup directory.
So, first I have to create the backup directory.
| 1 | mkdir /var/opt/mssql/backup |
And then proceed to download the backup file in the previously created directory.
| 1 2 | curl -L -o /var/opt/mssql/backup/aw2022.bak \ 'https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak' |
Beyond this point I show how this can be done via the terminal, but this can also be done via SSMS without any issues or extra requirements.
Before I can restore the backup, I have to see the logical file names.
| 1 2 3 | sqlcmd -S localhost -U sa -P S0MeP4sS! -C \ -Q "RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/aw2022.bak';" | \ tr -s '-' | cut -d ' ' -f 1-2 |
Note that I’m running the query like this so that I can pipe it to tr and cut in order to make the result easier to read.
Looks like the data file’s logical name is AdventureWorks2022 and the log file’s is AdventureWorks2022_log.
Resulting in the following restore command.
| 1 2 3 4 5 | RESTORE DATABASE [AdventureWorks2022] FROM DISK = N'/var/opt/mssql/backup/aw2022.bak' WITH MOVE N'AdventureWorks2022' TO N'/var/opt/mssql/data/AdventureWorks2022.mdf', MOVE N'AdventureWorks2022_log' TO N'/var/opt/mssql/data/AdventureWorks2022_log.ldf', STATS = 20; GO |
And now I can freely interact with the AdventureWorks2022 database from both sqlcmd and SSMS.
Stop and Start the SQL Server container
If you want to stop the container until the next time you need it you can simply stop it.
| 1 | podman stop sql2022 |
If the stop was successful, the status of the container, when checking with pdoman ps --all, should be Exited.
The status is also shown in the Podman Desktop GUI.
Next time you need the container you can start it from the GUI, using the Start/Play button, or via the start command.
| 1 | podman start sql2022 |
Removing the SQL Server container
When the container has outlived its usefulness you can simply delete it from the GUI, using the delete button, or via the following command:
| 1 | podman rm --force sql2022 |
Conclusion
Once you get the hang of it, it’s a fairly simple task to spin up, use, and dispose of a SQL Server container using Podman.