Photo by The efforts to containerize all the things are underway, with many tech vendors pushing remote development as a viable development option. So in the future, some percentage of your development stack will be remote-capable, which means more command-line familiarity is necessary. I can hear the collective groans of “why can’t there just be a GUI!?!” well, there likely will be in most cases, but in some other cases, a GUI won’t be an option.
In this post, you’ll see the commands necessary to start a SQL Server container instance, connect to a shell instance within the container, and execute some SQL commands. I hope you find it helpful in your container journey.
Before getting started, you will need Docker running in your development environment. I’m also assuming you have command line access to your environment, whether through SSH or some editor.
Starting A SQL Server Container
The official SQL Server docker image is available on Docker Hub, and a single command can get you up and running. I highly recommend naming your running instances using the --name flag.
docker run -e 'ACCEPT_EULA=Y' -e "SA_PASSWORD=Pass123!" -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server:2019-latest
The previous command will call our container sqlserver, in addition to accepting the end-user license agreement, initializing the sa user account, and exposing the SQL Server port to 1433 so apps can access the database remotely. We also start our container detached (-d), which will keep your terminal free for further user inputs. Feel free to change any values to meet your specific needs.
Next, let’s attach to the running container.
Attaching to Our SQL Server Container
Our first goal is to attach or current command line session to our SQL Server container instance. We immediately connect to the sqlserver named container and start a bash session.
docker exec -it sqlserver "bash"
You can also start Shell instead of bash. Your choice really.
docker exec -it sqlserver /bin/sh
Both will get the job done.
Running SQLCMD in a Docker Container
Now that we’re in the context of our container, we can run the tools bundled with the SQL Server image.
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Pass123!'
Running the above command will start an instance of SQLCMD, letting us write queries into the command-line session. After the command, if everything goes correctly, you will see the following output.
$ /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Pass123!'
1>
Great! Let’s write a simple query.
SQL Queries in SQLCMD
The first query I typically write is to determine what databases are running in my container instance.
select physical_database_name from sys.databases
Enter the query, followed by a GO on the second line. Your output should look something similar to the following.
1> select physical_database_name from sys.databases
2> Go
physical_database_name
-------------------------------------------------------
master
tempdb
model
msdb
(4 rows affected)
You’ll need to remember to include the GO keyword when you’ve completed writing your SQL statements. Now you can write all the SQL queries you like!
Exiting SQL CMD and Docker Session
Hopefully, you’ve been able to write all the SQL Queries your heart desires, and now you need to get back out of the command line session. To exit the SQLCMD session, type the keyword exit. To exit the command-line session, you need to type exit again. Now you should be out of your Docker container and back in your remote environment.
1> exit
$ exit
(base) ~ via 🅒 base at ☸️ docker-desktop took 8m 20s
Conclusion
Containerization has made our lives managing dependencies more straightforward, but it also means we have to adapt to take advantage of the current technologies available to us. If you’re a .NET developer, like me, you’ll find yourself using the SQL Server container more than a few times a day.
I hope this short tutorial helps you jump-start your remote development journey. If you found this post helpful, please share it with others.
