This post covers step-by-step instructions on creating a SQL Server Developer edition container using the new Go-based sqlcmd, as well as getting the default login’s password and connecting via SSMS.
After a discussion on Davide Mauri’s LinkedIn post about SQL Server Developer edition and the ability to spin up a container using the new Go-based sqlcmd, I’ve realized that I haven’t really played with containers.
As a result, I ended up spending some time this past weekend playing with SQL Server containers and putting together this blog post.
There is already a guide on doing this on Microsoft’s Tech Community, but it skips a few steps that I had issues with.
I figured I could write more detailed instructions for people, like myself, who are just starting with this type of container.
What’s SQL Server Developer Edition?
SQL Server Developer Edition is Microsoft’s way of providing developers and data platform folks with the ability to learn, develop, test and demo on SQL Server, with 0 licensing costs on a platform whose features and capabilities match Enterprise Edition.
If you want to learn more about this, Bob Ward has a blog post that covers this in-depth and answers some questions that people tend to have about Developer Edition.
Why a container?
This is a great option if you want to do SQL Server related development and testing, but don’t need a full-fledged SQL Server instance.
The footprint is small, you can dispose of it as soon as you’re done with your work.
Prerequisites
Before you’re able to spin up a SQL Server container, you’ll need to ensure that the following prerequisites are met.
I’m running the latest build of Windows 11, but these commands should work on Windows 10 version 2004 and higher (Build 19041 and higher).
sqlcmd
This refers to the new Go-based sqlcmd, and not the ODBC one.
To install it, just open PowerShell as administrator and run the following command:
PowerShell
| 1 | winget install sqlcmd |
If, for some reason, you don’t have winget already installed, you can grab it from here and install it.
After the installation completes, you can refresh your PATH environment variable using the following command:
PowerShell
| 1 | $Env:Path = [System.Environment]::GetEnvironmentVariable("Path", "Machine") |
This way, you don’t have to open a new PowerShell window for the newly installed sqlcmd to be available.
Note that if you already have the ODBC-based sqlcmd on your machine, the Go-based sqlcmd will be placed in the PATH environment variable before the ODBC one.
This will mess with any existing processes or scripts that don’t call sqlcmd using its absolute path.
You can check this by running the following command:
PowerShell
| 1 | where.exe sqlcmd |
One thing to keep in mind about the Go-based sqlcmd is that it is unable to use SQL Server client aliases.
Enable virtualization
Next, enable platform support for virtual machines.
PowerShell
| 1 | dism.exe /online /enable-feature /featurename:VirtualMachinePlatform /all /norestart |
Note that enabling this feature may cause issues with L2 hypervisors such as Oracle VirtualBox – either VMs not starting or VM performance being impacted.
In case you have to disable it you can run the following:
PowerShell
| 1 | dism.exe /online /disable-feature /featurename:VirtualMachinePlatform /norestart |
WSL
Up next is Windows Subsystem for Linux, aka WSL.
PowerShell
| 1 | wsl --install |
Reboot after the install finishes.
Podman
Installation
The last requirement is Podman, a graphical tool for working with containers and Kubernetes.
You can either download the installer from their site, or install it via winget.
PowerShell
| 1 | winget install -e --id RedHat.Podman-Desktop |
Initial setup
Once installed, Podman needs to be set up, so open it from the desktop shortcut that the install process created and click on Set up.
You just have to follow the prompts here since they’re pretty straightforward.
First, Podman Desktop will prompt you to install Podman so click Next and, when asked if you want to install Podman, click Yes.
When prompted to begin the installation, you can leave “Install WSL if not present” checked since it won’t have any impact due to WSL already being installed.
Just click Install when ready.
When the installation completes, press Close.
I’ve set Podman to auto-start when Podman Desktop is launched.
No just click the Run Podman button to start up the the Podman machine.
You can then validate if the Podman machine started successfully via both the GUI and PowerShell.
PowerShell
| 1 | podman ps |
As long as you don’t get an error you should be good to proceed.
Create the SQL Server Developer edition container with sqlcmd
To view all the available versions of SQL Server that you can spin up container with, you can run the following command:
PowerShell
| 1 | sqlcmd create mssql get-tags |
Because the container is Linux-based, the oldest version available is SQL Server 2017 CU1, since that’s the first version of SQL Server to support Linux, and up to the newest version which currently is 2022 CU13.
Default configuration container
To spin up a container with the latest available version of SQL Server for Linux listening on the default port 1433, just run the following command:
PowerShell
| 1 | sqlcmd create mssql --accept-eula |
And you can then connect to the container by just executing sqlcmd without any other switches or parameters.
You can validate that the container is listening on TCP port 1433 using the podman ps command.
Custom port
If you want to use another port, 1436 for example, the command will look like this:
PowerShell
| 1 | sqlcmd create mssql --accept-eula --port 1436 |
Specific version
And, if you want a specific version, you can use the --tag option:
PowerShell
| 1 | sqlcmd create mssql --accept-eula --port 1436 --tag 2017-CU20 |
Specify a hostname
If you want to explicitly set the container hostname, instead of having it default to the container ID, you can use the --hostname option.
PowerShell
| 1 | sqlcmd create mssql --accept-eula --port 1436 --hostname SQL2022Container |
Non-default collation
By default, the container is created with SQL Server’s default collation – SQL_Latin1_General_CP1_CI_AS, but you can control that via the --collation option.
PowerShell
| 1 | sqlcmd create mssql --accept-eula --port 1436 --collation Romanian_CI_AS |
Restore a sample database during container creation
You can restore backups of sample databases such as AdventureWorks during the creation of the container.
PowerShell
| 1 2 | sqlcmd create mssql --accept-eula ` --using https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak |
Delete the container
To delete a previously create container, just run the following command:
PowerShell
| 1 | sqlcmd delete |
Or, if there are any user databases on the instance:
PowerShell
| 1 | sqlcmd delete --force |
Connecting to the container instance via SSMS
You’re not limited just to sqlcmd as a means of interacting with the container based instance, you can also use SSMS.
My current container is set up with the following command:
PowerShell
| 1 2 3 | sqlcmd create mssql --accept-eula ` --hostname SQL2022Container ` --using https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak |
Credentials
Now, during the container creation process, there is a message about the configuration of the current context, the sa login being disabled and another login being created.
Small detour
This hits on one of my 3 SQL Server pet peeves: not sure why the message refers to them as users since they are instance level logins. 🙂
The other two pet peeves are:
- (Oracle-native) people referring to SQL Server instance level logins and/or database level users as “schemas”
- and people confusing SQL Server and MySQL.
End detour
To get the password for the automatically created user, I need to look into the sqlconfig file stored in C:\Users\$User\.sqlcmd\.
PowerShell
| 1 | more C:\Users\Vlad\.sqlcmd\sqlconfig |
I’m a simple DBA, I see an alphanumeric string that ends in an equal sing, I instantly assume it’s Base64 encoded.
So, let’s decode it:
PowerShell
| 1 | [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String("NHdmQDVkKk4lQi[redacted]JjQydzQ=")) |
Note, if you want the decoded string to be sent right to your clipboard you can just pipe to clip (add | clip at the end of the above command).
Looks like a valid string:
Side note: I have to admit, my initial plan was to just enable sa and reset its password, but I was curios what the sqlconfig file stores and this turned out to be a bit more fun 🙂
Actually connecting
Now to connect via SSMS I just have to use 127.0.0.1,1433 as the server name – the port number obviously varies based on your configuration.
And provide the user name and password retrieved from the sqlconfig file.
And it does look like a full-fledged* SQL Server instance.
*SQL Server Agent can’t be enabled (or at least I wasn’t able to figure out how), and since this is SQL Server for Linux, you don’t have xp_cmdshell, among other things.
Also, keep in mind that the instance will be configured with the same silly defaults that we all know and love.
So, I recommend updating CTP to something more realistic like 50, MAXDOP to your number of cores up to 8 if you have a single NUMA node, and setting max memory to a value in MB that doesn’t look like someone’s phone number.
If you want to use the hostname of the container to connect to the instance, in my case SQL2022Container, you have to add the following line to your hosts file:
| 1 | 127.0.0.1SQL2022Container |
Afterwards, I’m able to connect via SSMS by using only SQL2022Container as the Server Name.
Bonus
You can still use PSBlitz to diagnose any performance problems you might have, even if it’s a container.
Just keep in mind that you might get some funky results in the Instance Health report page for the service account being LocalSystem.
Conclusion
Creating a SQL Server Developer edition container using sqlcmd is a nifty way of running SQL Server for free for development and testing purposes if you don’t want to go through the hassle of installing a full-fledged instance.
For anything involving heavier workloads and/or needing to restore specific databases such as the 180GB StackOverflow database, you’ll have to stick with non-container SQL Server Dev edition.
And, if you’re looking for a quick and automated way of setting up full-fledged SQL Server Developer edition instance (versions 2017 through 2022), you might want to check out this blog post.
containerlinuxPowerShellSQL ServerT-SQL
