Home » Create a SQL Server Developer edition container using sqlcmd

Create a SQL Server Developer edition container using sqlcmd

by Vlad Drumea
0 comment

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:


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:

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:


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.


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:

WSL

Up next is Windows Subsystem for Linux, aka WSL.


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.


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.

Podman Settings Autostart Enabled Highlighted button: Next

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.

Podman Desktop Highlighted button: Run Podman

You can then validate if the Podman machine started successfully via both the GUI and PowerShell.

Podman Desktop Podman v5.0.3 showing as Running

Terminal window PS C:\Users\Vlad> podman ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES SQL Server developer container sqlcmd

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:

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:

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:

Specific version

And, if you want a specific version, you can use the --tag option:

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.

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.

Restore a sample database during container creation

You can restore backups of sample databases such as AdventureWorks during the creation of the container.

Delete the container

To delete a previously create container, just run the following command:

Or, if there are any user databases on the instance:

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:

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.

Terminal window the output of the container creation command Highlighted lines Created context "mssql" in "C:\Users\Vlad\.sqlcmd\sqlconfig", configuring user account... Disabled "sa" account (and rotated "sa" password). Creating user "Vlad"

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

Terminal window showing the contents of the sqlconfig file with the password being highlighted

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:

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:

Terminal window showing the above command and the resulting string (partially redacted)

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.

Object explorer showing the instance with the Linux logo running SQL Server 16.0.4125.3 and containing the AdventureWorks2022 SELECT @@SERVERNAME; returns SQL2022Container SQL Server developer container sqlcmd

*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:

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.

You may also like

Leave a Comment

* By using this form you agree with the storage and handling of your data by this website.

This site uses Akismet to reduce spam. Learn how your comment data is processed.