Page 16 - MSDN Magazine, July 2017

Data Points JULIE LERMAN On-the-Fly SQL Servers with Docker
In last month’s column (msdn.com/magazine/mt809115), I used the Visual Studio Code mssql extension to interact with an Azure SQL Database—from my MacBook Pro! I love the cross-platform capa- bility of Visual Studio Code, but I was still depending on Azure to provide me with some flavor of SQL Server. I also mentioned that it is indeed possible to run SQL Server without depending on the cloud or Windows, thanks to SQL Server for Linux and a Docker image that’s readily available to drop onto your machine. I’ll pause if the existence of SQL Server for Linux is new information for you and you just need a moment.
OK, I hope you’ve recovered. The Linux version of SQL Server is able to run in a Linux-based Docker container and you can use that container anywhere that Docker is supported.
Running SQL Server in a container is really useful for devel- opers because it makes it easy and quick to spin up a SQL Server instance. You can run different instances of SQL Server that are differentversionssidebyside.Youcaneasilyrunthecontainerfor a while and then when you stop/delete the container, the data all goes away and you can start another clean one. If you want, you can also choose to persist the database files, but still start/stop the container only as you actually need SQL Server to be running.
There are two flavors of SQL Server container images— Linux-based images and Windows-based images. Microsoft provides a Linux-based image for SQL Server 2017 Developer Edition and three Windows-based images: SQL Server 2016 SP1 Express Edition, SQL Server 2016 SP1 Developer Edition and SQL Server 2017 Evaluation Edition. All of these images are available on Docker Hub for free (dockr.ly/2rmSiWs). You can pull and run the Linux-based SQL Server image to create Linux-based containers wherever there’s a Docker Engine running, which could be on Linux, Windows or macOS. But you can run Windows-based containers only on Windows 10 Anniversary Edition or higher or Windows Server 2016—they can’t run on Linux or macOS. Keep in mind that Docker also runs on Azure and AWS, so you can move from development to production in the cloud, as well.
Although I’ve written a blog post about my first experiments with running SQL Server for Linux in Docker on a Mac (bit.ly/2pZ7dDb), I want to approach this from a different angle—the scenario where you want to share a pre-configured image along with a database. This can allow developers to very quickly get SQL Server and the needed databases on their machines, or even to be used as part of
an automated testing environment. I knew it could be done, but I was curious as to how, so I’ve worked through the basic process and will share it with you here.
I’ll begin by explaining how to get the base image of SQL Server for Linux up and running on your computer under Docker. My example will use Docker for Mac, but you can do the same with the other versions of Docker, as well. Be sure you have the correct version of Docker already installed and running on your computer, and that you set it to use at least 4GB of RAM from the host system. You can find more detailed setup information on my blog post referenced earlier.
Running SQL Server in a container is really useful for developers because it makes it easy and quick to spin up a SQL Server instance.
In the command or terminal window, you can get the official image by executing:
Mac: sudo docker pull microsoft/mssql-server-linux Windows: docker pull microsoft/mssql-server-windows
Once it’s installed, you can run the docker images command to see that Docker is aware of this image:
REPOSITORY TAG IMAGE ID microsoft/mssql-server-linux late 7b1c26822d
CREATED 13 days a
SIZE 1.35 GB
Note that if you already have the base image, you can visit dockr.ly/ 2qTavYr to see if a newer one has been released. If so, you should pull it again. Next, I’ll use the docker run command to spin up this image as a container in order to interact with it:
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' -p 1433:1433 -d --name juliesqllinux microsoft/mssql-server-linux
When starting up an mssql-server-linux container the first time, you’re required to accept its license agreement, which you do using an environment variable:
-e 'ACCEPT_EULA=Y'
You also must include an environment variable for a password to accompany the default sa user. The password you create must consist of “at least 8 characters including uppercase, lowercase let- ters, base-10 digits and/or non-alphanumeric symbols.” Optionally,
Code download available at msdn.com/magazine/0717magcode.
12 msdn magazine