Next week I’m speaking at the Agile Alliance technical conference, Deliver Agile in Nashville Tennessee. My talk is titled “Dev and Test Agility for Databases with Docker”. I had a conversation with track chair Richard Seroter about the topic.
Julie Lerman deliver:Agile 2019 Interview with Richard Seroter from Agile Alliance on Vimeo.
If you are used to pulling the mssql-server images from the microsoft repository, e.g.,
docker pull microsoft/mssql-server
that won’t work for the 2019 CTP.
I was able to repull (aka update) using the former repository, but that wasn’t working for the CTP whose tag is vNext-CTP2.0-ubuntu.
I finally noticed the new docker pull command on the docker hub page for the image
It says: docker pull mcr.microsoft.com/mssql/server
So the command for pulling the CTP using it’s tag is as follows:
docker pull mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu
My latest course on Pluralsight, Cross-platform SQL Server Management for Developers using VS Code, went live earlier this month (just as I was about to hop on a plane for 2 weeks of conference travel!)
This is a course on a Visual Studio Code extension that I enjoy using so much that I wanted to share it with you. It is the mssql extension which lets you interact with SQL Server in a fairly rich way that belies the lightness of the IDE which it extends. Because VS Code is cross-platform, so are all of its extensions. So you can use this while you are coding on Windows, Mac or Linux and want to do some basic interaction with a SQL Server database.
As SQL Server examples, I used SQL Server LocalDb on Windows, SQL Server for Linux in a Docker container on a Mac and Azure SQL in the cloud. The course starts not only y showing you how to install VS Code (and some VS Code basics) and the extension but also by walking you through how to set up each of the database servers. That means it also has a lesson on Docker , installing and running an image as well as a quick start on creating a new SQL database in the Azure portal.
Once everything is set up, I dig through the features and functionality of the mssql extension. And I turned over ever possible stone to make sure you don’t miss helpful features which is the norm if you just start using such a tool without any preparation.
The course is mostly demos and very light on Powerpoint slides and I do work in Windows, on my Macbook and even in a Linux virtual machine.
What I’m also proud about this course is that if you’ve never used VS Code before, you’ll learn how to get around this amazing editor. If you’ve never used Docker before, I provide a really helpful and gentle introduction and you’ll be able to work with it. I had some great support from the team responsible for this extension as they were so happy to have this kind of attention paid to it.
So whatever language you code in, whatever O/S you work on, if you are using SQL Server (or interested in using it), this course should be a great help in mastering this very handy extension!
Below is the Table of Contents for the course.
If you need a 30-day free trial to the Pluralsight library so that you can watch this, send me a note!
In this module you’ll get a short introduction to the cross-platform and free developer IDE, Visual Studio Code and its mssql extension, The extension allow you to perform some key interactions with a SQL Server database without leaving the IDE. You’ll also walk through installing both the IDE and the extension on Windows and macOS
In this module, you’ll learn how to set up a variety of SQL Servers. All of them are quick to install. You’ll learn to install SQL Server LocalDb on Windows, create an Azure SQL Database in the cloud and use a Docker image of SQL Server for Linux to quickly spin up SQL Server in a container on macOS. This will ensure that you have a SQL Server database to interact with in the rest of the course.
In this module, you’ll learn the ins and outs of connecting to a variety of local, cloud and containerized SQL Servers with the mssql extension. You’ll learn how to use the commands and shortcuts, how connection profiles and passwords are stored and even how to create a handy shortcut for getting mssql started.
In this module you’ll start interacting with the database, executing queries and commands, and exploring result sets. You’ll learn about the snippets and also learn about attaching to existing database files and creating new databases from scratch. Most importantly you’ll learn about the great SQL editor and result view support that the mssql extension brings to you.
This module will dig deeper into mssql and provide tips that take advantage not just of mssql features, but also capabilities of Visual Studio Code to make using mssql easier.
Update June 3, 2017: The team has revised the docker image and the bash file is gone, presumably with its logic broken up in to various locations. Still I’m glad I grabbed this when I did to satisfy my curiosity!
Microsoft has created 4 official Docker images for SQL Server: SQL Server for Linux, SQL Server Developer Edition, SQL Server Express and (windows) SQL Server vNext) . They can be found on the Docker hub (e.g. https://hub.docker.com/r/microsoft/mssql-server-linux/) and there is also a Github repository for them at github.com/Microsoft/mssql-docker. Some of the files that go along with that image are not on Github. The Dockerfile files for each image run some type of startup script. The Windows images have a PowerShell script called start.ps1. You can see those in the Github repo. The Linux image runs a bash file called sqlservr.sh. That’s not included in the repo though and I was curious what it did.
Note: I wrote a blog post about using the SQL Server for Linux container (Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code) and I’m also writing an article about using the containers for my July MSDN Magazine Data Points column (watch this space).
Still a bit of a bash noob, I learned how to read a file from a docker container on ..you guessed it…StackOverflow. Following those instructions, I created a snapshot of my running container
➜ MySqlServerLinuImage git:(master) docker commit juliesqllinux mysnapshot sha256:9b552a1e24df7652af0c6c265ae5e2d7cb7832586c431d4b480c30663ab713f0
and ran the snapshot with bash:
➜ MySqlServerLinuImage git:(master) docker run -t -i mysnapshot bin/bash root@2a6f950face2:/#
Then at the new prompt (#), used ls to get the listing
root@2a6f950face2:/# ls SqlCmdScript.sql SqlCmdStartup.sh bin boot dev entrypoint.sh etc home install.sh lib lib64 media mnt opt proc root run sbin srv sys tmp usr var
then navigated to folder where the bash file is and listed its contents:
root@2a6f950face2:/opt/mssql/bin# ls compress-dump.sh generate-core.sh mssql-conf paldumper sqlpackage sqlservr sqlservr.sh
Once I was there I used the cat command to list out the contents of the sqlservr.sh file and see what it does. Here is the secret sauce in case, like me, you NEED to know what’s going on under the covers!
root@2a6f950face2:/opt/mssql/bin# cat sqlservr.sh #!/bin/bash # # Microsoft(R) SQL Server(R) launch script for Docker # ACCEPT_EULA=${ACCEPT_EULA:-} SA_PASSWORD=${SA_PASSWORD:-} #COLLATION=${COLLATION:-SQL_Latin1_General_CP1_CI_AS} have_sa_password="" #have_collation="" sqlservr_setup_prefix="" configure="" reconfigure="" # Check system memory # let system_memory="$(awk '/MemTotal/ {print $2}' /proc/meminfo) / 1024" if [ $system_memory -lt 3250 ]; then echo "ERROR: This machine must have at least 3.25 gigabytes of memory to install Microsoft(R) SQL Server(R)." exit 1 fi # Create system directories # mkdir -p /var/opt/mssql/data mkdir -p /var/opt/mssql/etc mkdir -p /var/opt/mssql/log # Check the EULA # if [ "$ACCEPT_EULA" != "Y" ] && [ "$ACCEPT_EULA" != "y" ]; then echo "ERROR: You must accept the End User License Agreement before this container" > /dev/stderr echo "can start. The End User License Agreement can be found at " > /dev/stderr echo "http://go.microsoft.com/fwlink/?LinkId=746388." > /dev/stderr echo "" echo "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement." > /dev/stderr exit 1 fi # Configure SQL engine # if [ ! -f /var/opt/mssql/data/master.mdf ]; then configure=1 if [ ! -z "$SA_PASSWORD" ] || [ -f /var/opt/mssql/etc/sa_password ]; then have_sa_password=1 fi # if [ ! -z "$COLLATION" ] || [ -f /var/opt/mssql/etc/collation ]; then # have_collation=1 # fi if [ -z "$have_sa_password" ]; then echo "ERROR: The system administrator password is not configured. You can set the" > /dev/stderr echo "password via environment variable (SA_PASSWORD) or configuration file" > /dev/stderr echo "(/var/opt/mssql/etc/sa_password)." > /dev/stderr exit 1 fi fi # If user wants to reconfigure, set reconfigure flag # if [ -f /var/opt/mssql/etc/reconfigure ]; then reconfigure=1 fi # If we need to configure or reconfigure, run through configuration # logic # if [ "$configure" == "1" ] || [ "$reconfigure" == "1" ]; then sqlservr_setup_options="" # if [ -f /var/opt/mssql/etc/collation ]; then # sqlservr_setup_options+="-q $(cat /var/opt/mssql/etc/collation)" # else # if [ ! -z "$COLLATION" ]; then # sqlservr_setup_options+="-q $COLLATION " # fi # fi set +e cd /var/opt/mssql echo 'Configuring Microsoft(R) SQL Server(R)...' if [ -f /var/opt/mssql/etc/sa_password ]; then SQLSERVR_SA_PASSWORD_FILE=/var/opt/mssql/etc/sa_password /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log elif [ ! -z "$SA_PASSWORD" ]; then SQLSERVR_SA_PASSWORD_FILE=<(echo -n "$SA_PASSWORD") /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log else if [ ! -z '$sqlservr_setup_options' ]; then /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log fi fi retcode=$? if [ $retcode != 0 ]; then echo "Microsoft(R) SQL Server(R) setup failed with error code $retcode. Please check the setup log in /var/opt/mssql/log for more information." > /dev/stderr exit 1 fi set -e rm -f /var/opt/mssql/etc/reconfigure rm -f /var/opt/mssql/etc/sa_password echo "Configuration complete." fi # Start SQL Server # exec /opt/mssql/bin/sqlservr $*
I’ve been having a lot of fun with the new mssql extension for Visual Studio Code. I have an article coming in MSDN Magazine and am planning more fun as well. My latest experiment was doing a big mashup taking advantage of the fact that there is now a Linux version of SQL Server. So we are no longer limited to hosting it on Windows or Azure. The most lightweight way to host SQL Server on Linux is in a Docker container. While I am sitting in front of a MacBook typing this I’m by no means working towards abandoning my Windows development or Windows machines. I’m just happy to have more options at my disposal as well as have the ability to share what I am learning work beyond the world of Windows developers.
Containers are not stateful. There are ways around that (I’ll show you below) but I only know enough to be dangerous here. This is a great way to use SQL Server at design time. Using this for production is a totally different story and you need to do a lot more research and soul-searching before using that option.
On the other hand, there are those who do have that particular goal:
@julielerman @code I’ve been doing the same. SQL Server on a Mac was the missing link in being able to fully move away from windows.
— Liam Blizard (@LiamBlizard) April 7, 2017
I had to go through a number of documents to do this and of course I got stuck even with those resources at my disposal. So I will share the full path of how I got this setup working.
I already have Docker for Mac installed on my MacBook. Here is the installation link if you need to perform that step. Keep in mind that you can’t do this on a VM. I tried as I wanted to repeat this with a clean setup.
Be sure that Docker is set to use at least 4GB of memory.
This is what makes the whole thing so easy! Microsoft has created an official docker image with SQL Server for Linux already on it.
In the terminal window, you can pull and install the official image with
sudo docker pull microsoft/mssql-server-linux
Once it’s installed, the ‘docker images’ command will show you that the image is now available on your machine.
Although I just installed it today, you can see that the image I’m using — which is the latest version — was created by Microsoft 3 weeks ago.
Now that Docker is aware of the image, you can create a container from it — which is running instance of the image.
Because we’re on a Mac and awaiting for a “bug” to get fixed, we will actually create two containers.
Depending on your familiarity with Docker, you may or may not be aware that containers are not stateful. Once you delete a container, it’s all gone! If you have persisted data in that container, it, too, is all gone. However, Docker has a feature called “Volumes” which are a way to retain state between docker instances. So when one instance is shut down, the state is stored in a Volume. When another container instance is spun up, that volume provides the container with the state from the previous instance. This is how it’s possible to use containers for databases.
Here’s a great tutorial on volumes: https://rominirani.com/docker-tutorial-series-part-7-data-volumes-93073a1b5b72
And the official docker doc: https://docs.docker.com/engine/tutorials/dockervolumes/#/creating-and-mounting-a-data-volume-container
However there’s an issue (which looks like the resolution is around the corner) with Docker on Mac hosting the sql-server-linux image. This prevents us from using a volume for persistence in the simple way. So instead, we’ll create a separate container that is a “data volume container”, then we will point the container that will run SQL Server to the data volume container.
I’ll name mine mssqldata. Here’s the command to create it. (Don’t miss the full length of the command!)
docker create -v /var/opt/mssql --name mssqldata microsoft/mssql-server-linux /bin/true
This volume container still uses the image as its base. But we won’t be running SQL Server from this instance.
Now you can create an container where you will run SQL Server and that container will use the data volume container for the persisted data.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' -p 1433:1433 --volumes-from mssqldata -d --name sql-server microsoft/mssql-server-linux
The two environment variables (accept_eula and sa_password) are required. The userid is (gulp) ‘sa’. The password requirements are: “At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.”. Mine’s really fancy!
Once these exist,
docker ps
will only show you the regular container. The volume container is hidden so you need
docker ps -a
to see it.
Notice that the container I will run SQL Server on is on a port whereas the data volume container has a different status “Created”, and is not exposed on a port.
You don’t have to do this but it made me happy and it was fun. Did you know that you can interact with SQL Server from the command line? The tools are installed inside the container but that’s a messy way to use them. It’s easier to just install them on your computer directly. The command lines tools for Mac are sql-cli.
Note: On May 16th, Microsoft released the macOS version of sqlcmd and bcp (bulk copy). Good to know if you are already familiar with sqlcmd. Here’s their blog post with more details.
You can install that with:
npm install -g sql-cli
Start it up with the mssql command.
To minimum you need connect is to identify the server (which is at localhost by default, you don’t need to specify the port) and the password. It will presume sa for userid.
mssql -s localhost -p Passw0rd
If it’s successful, you’ll get some information followed by a new prompt, mssql.
Connecting to localhost...done sql-cli version 0.4.14 Enter ".help" for usage hints. mssql>
Enough of that. Now I get to use my new favorite tool. The mssql extension in Visual Studio Code.
Connect in Visual Studio Code
Once you have mssql installed in VS Code, you can begin by creating a new sql file. Either via the commands (F1 for the command pallete, MS SQL to see the commands and then New Query. This will prompt you for a connection – one parameter at a time. YOu can also start with the MS SQL Connect command (⇧⌘C). The extension will prompt you for each parameter.
Server name: localhost Database name: (enter) User name: sa Password: Passw0rd Save Password (yes or no) Profile name: [your choice]
If you enter everything correctly, not only will it connect, but the details will get stored as a connection profile in the VS Code settings and be available for subsequent connections.
You can see the connection status as it is connecting and then when connected in the lower right hand corner of the IDE.
In the SQL file open in the editor, you can type SQL and see some existing snippets as well as get help from Intellisense whicih has read the schema of the data on the server. So far there’s not much.
Selecting the sqlListDatabases snippet and then executing it (right click for Execute Query on the context menu or ⇧⌘E) displays the databases:
Now you can use TSQL to create databases, database objects, query data. In the results pane you will see data as a grid similar to what you might see in SSMS. You can also export results to CSV or JSON. I’ve recently written an article about all of the cool things you can do with mssql which will be in teh June 2017 MSDN Magazine. But that connects to a SQL Azure database. In the meantime you can just go to the docs for the extension (aka.ms/mssql-marketplace).
I let some more snippets help me to create a database and a table.
The first was the sqlCreateDatabase where I changed the snippet’s database name placholder to create a new database called LinuxReally then executed that with ⇧⌘E.
Re-running the select name from sys.databases command showed that the new database was now in the list.
Next I leveraged the sqlCreateTable snippet to help me create a new table. I named teh table DatabasesIKnow and gave it three columns
CREATE TABLE dbo.DatabasesIKnow ( Id INT NOT NULL PRIMARY KEY, -- primary key column DatabaseName [NVARCHAR](50) NOT NULL, KnowIt [Bit] );
For some reason, the intellisense cache did not automatically refresh when I created this. Possibly becuse it was a new database. Even the mssql extension’s Refresh Intellisense Cache command did not kick it in. I got it working by disconnecting and reconnecting this time choosing the LinuxReally database rather than letting the extension connect to master by default. When I did that, I could see the message “Updating Intellisense…” in the status bar. After I had done this, the intellisense did auto refresh any time I modified the database schema.
Once i had the new database, I could execute “select * from dbo.DatabasesIKnow” and see the proper results. In my case, since I haven’t added data, there were no rows. But clearly it was reading from my table.
Now come the big docker volume tests. I first disconnected from the database inside of VS Code with⇧⌘E. Then I stopped and removed the sql-server container with the two commands:
docker stop sql-server docker rm sql-server
But I left the data volume container (mssqldata) running.
I then created a new container instance using the same command as earlier:
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' -p 1433:1433 --volumes-from mssqldata -d --name sql-server microsoft/mssql-server-linux
In VS Code, I think reconnect to the server and my new database which was easy since it had been stored in the connection profiles . It’s the first one, localhost: LinuxReally.
The connection was successful and listing databases showed my new DatabasesIKnow database . Select * from that database showed the schema. So my database was persisted in the data volume container even though I had killed and recreated the sql server container.
Now it was time to see what happens when I stop and remove both containers!
docker stop sql-server docker rm sql-server docker stop mssqldata docker rm mssqldata
Next I use the same command I used previously to restart the sql-server container with the parameter to use the (not running) mssqldata volume container.
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' -p 1433:1433 --volumes-from mssqldata -d --name sql-server microsoft/mssql-server-linux
That fails. I need to first run the data volume container. Unfortunately, I re-ran the create command for that and overwrote the existing container, so all was lost. GOOD LESSON THERE! 🙂
So I started from scratch. In the brand new data volume container I recreated my database and table.
After more experiments, I realized that I had misunderstood the Docker documentation on volumes. You can create copies of data volume containers and remove those. But you need to be more of a docker ninja. While you can make all kinds of copies of the container, once you RM all of the related volumes, it is gone gone. This blog post does clarify some of the info I was still confused about wrt creating/backing up/restoring data volume containers: tricksofthetrades.net/2016/03/14/docker-data-volumes/.
So the bottom line is you need to leave the Data Volume Container running in some format (the original or some flavor of copy). (I’m still finding it hard to believe that it doesn’t somehow get stored as a file you can re-run so I will update this as soon as someone corrects me!)
And if you think this is only somehting to do on OS X (because that’s where I’m doing it) .. no no no! Did you know there is now Docker for Windows? And that VS Code is cross platform? This is such a great way to quickly get SQL Server up and running in your development environment.
And as I said on twitter, comparing the experience of pulling the docker image and spinning up a container to the experience of installing SQL Server on windows is something like this:
creating a docker container that hosts sql server for linux vs doing a sql server install on windows
— Julie Lerman (@julielerman) April 7, 2017
