Page 18 - MSDN Magazine, July 2017

you can name the container instance. I’ll call mine juliesqllinux. I’ll also specify the port mapping for the host port to container port with -p, as well as a parameter to run this in the background: -d (for detach). If you don’t use the -d parameter, the instance will run in the foreground and you won’t get a prompt back at your terminal in order to continue executing commands. As usual, I learned this the hard way.
In response to the run command, Docker returns a unique container ID and returns your terminal back to a prompt. It does this in about one second. Think about how long it takes to install SQL Server onto your computer and get it configured. Then let me repeat: Spinning up the container took about one second.
Interacting Directly with the
SQL Server from the Command Line
While my last column focused on using the mssql extension for Visual Studio Code to interact with the database, here I’ll use a command-line tool to quickly create a simple database, a table and some data, and then run a query to prove it’s all working. Although the Microsoft sqlcmd command-line utility is part of the image, I find it easy enough to use it directly from my OS. Sqlcmd is available for Windows (bit.ly/2qKnrmh) and macOS (bit.ly/2qZBS6G). The macOS version is new. While I’ll use it in this article, I’m also a fan of the cross-platform sql-cli (bit.ly/2pYOzey).
When starting up sqlcmd, you need to specify the server name, user name and password as part of the command. Additionally, you can specify a particular database, although the utility will use master by default. See the sqlcmd for Windows link mentioned earlier for details on all of the command-line options.
From the command prompt, I’ll start up a sqlcmd command with the required parameters. This returns a numbered prompt so I can use it interactively:
→ ~ sqlcmd -S localhost -U sa -P Passw0rd 1>
At the prompt I can start entering lines of TSQL, then a final line, Go, to execute. For example, I’ll retrieve a list of databases that already exist on the server:
1> select name from sys.databases 2> go
name
------
master tempdb model msdb
(4 rows affected) 1>
Now, I’ll create a new database and execute that. Then I’ll use that new database, create a new table and add some data using the commands in Figure 1.
I find it a little clunky to work interactively with sqlcmd. Definitely have a look at the new cross-platform mssql-scripter tool (bit.ly/2pSNhoF) that was recently released as a preview.
The final command, select * from people, is there to perform a simple validation that the table and the data do indeed exist after running the other commands. Note that when you remove a Docker container, it’s gone completely and this database and data will disappear, as well. However, it’s possible to create separate data
volumes that can persist the data files even if you destroy the con- tainer that’s running your SQL Server instance. I walked through the first baby steps of creating persistent data containers in the blog post I mentioned earlier, so I won’t repeat that here.
Create a Custom Image
That Creates Its Own Database
What I’m more interested in is the idea of creating an image that includes not only a running instance of SQL Server, but also a pre-created database for development and testing, something developers can grab and use quickly. Of particular interest to testers is the ability to have this image accessible for automated testing, where the server and database can be instantly available for a test run, then destroyed and recreated on the fly as needed.
Consider that when I first created the container from the Docker image, the master database was created for me. If you don’t include the -d (detach) parameter in the docker run command, you can see the many steps that were performed on the container as it starts up. Creating the master database is only one of those steps. So what you can do is create your own image based on the base mssql-server-linux image (or any base image), then pro- vide additional commands to perform any steps you need in the dockerfile file that goes along with your image.
I’ll demonstrate by creating an image that will duplicate the steps I just ran in the terminal. These were the commands to create the new database and a new table and to insert a few rows of data.
I’ll need a folder to house the files I’ll be creating for my image. There will be a total of four because I’m going to separate specific tasks into different files to keep things organized:
1. SqlCmdScript.Sql: This file will hold the TSQL script with the commands for creating the new database, table and data. 2. SqlCmdStartup.sh: This is a bash file (like a batch file for Linux). It starts up the sqlcmd command-line tool and, as part of the command, runs the SqlCmdScript.Sql file.
Remember that sqlcmd is also part of the base image.
3. Entrypoint.sh: This is another bash file. It lists the non-Docker tasks that need to run, and its first task is to execute the Sql- CmdStartup.sh file. Then it will start the SQL Server process. 4. Dockerfile:Thisfile(there’snoextension)isthedefinitionofhow to build the image and how to run containers from the image.
Figure 1 Adding Data to a New Table
1> create database juliedb
2> go
1> create table dbo.people (PersonId int Primary Key, Name nvarchar(50)) 2> insert into people values (1,'julie')
3> insert into people values (2,'giantpuppy')
4> select * from people
5> go
(1 rows affected)
(1 rows affected)
PersonId Name
----------- --------------------------------------------------
1 julie
2 giantpuppy
(2 rows affected) 1>
14 msdn magazine
Data Points