Page 20 - MSDN Magazine, July 2017

them in the docker run command. I’ll again use the -d parameter so the container will run in the background and I can continue to use the same terminal window to work with the container:
docker run -d -p 1433:1433 --name juliesqllinux julielinuximage
This command specifies the port to run this on, the name to apply to the running container (--name juliesqllinux) and the name of the image to use (julielinuximage).
The docker ps command lets me confirm that it’s now running. I’m also running the Kitematic UI (included with the Docker client application installer), which shows the state of Docker con- tainers and also displays logs. This means I can see not only all of the tasks accomplished by the sqlservr.sh script, but evidence of my juliedb database, tables and data being inserted. I can even see the results of the query at the end of my sql script to display the data from the people table.
Figure 2 shows the end of the logs after running this container.
Now, just like before, I can interact with the container using the command-line tools installed directly on my computer.
I have to start sqlcmd again, then I call a few commands to check that I have access to the juliedb database my image created. Figure 3 shows my entire interaction, with my commands in bold and the response in standard font. The commands shown are starting up the sqlcmd utility and connecting to the database server, listing the databases, using juliedb, listing its tables, querying the people table and then quitting out of the sqlcmd utility.
Skip the Script, Just Provide the Database File
If you have a large database (or multiple databases) to share, you might prefer not to include all of the scripts for the schema and data. Another approach is to pre-create the database files, include
Figure 3 Interacting with Data in the Container That Pre-Created a Database
those mdf files with the image, and in the Dockerfile be sure to copy the files into the container and then run TSQL to attach the files to the server. SQL Server DBA Andrew Pruski wrote a great blog post about this approach, which you can find at bit.ly/2pUxQdP. I’ve also done this myself in the Pluralsight course I’m currently building about the mssql extension.
There’s certainly a lot more to learn and benefit from providing SQL Server in Linux and Windows containers.
For Devs or DevOps
With these two approaches, you now have a simple means of shar- ing a pre-configured database along with a SQL Server across your team, allowing everyone to have a locally running server and a local copy of the database. But thanks to the container, nobody needs to install SQL Server on their computer or execute any scripts to get the necessary database set up for use. It’s frighteningly easy and fast.
I also mentioned using this for DevOps scenarios, perhaps with automated tests that require a database. I hope it’s not a big leap from what you’ve seen here to imagine having your build or test process spin up a container that has everything needed for your operation to quickly and easily interact with a SQL Server database. One exam- ple of this is in the MSSQL-Node-Docker demo app (bit.ly/2qT6RgK) created by Travis Wright, a program manager on the SQL Server engineering team. The demo was “created to show how SQL Server can operate in a DevOps scenario where an application developer can check in code to GitHub and then trigger a build in Red Hat OpenShift to deploy the changes automatically as pods (containers).”
There’s certainly a lot more to learn and benefit from providing SQL Server in Linux and Windows containers. It’s amazing to me that you can now “install” SQL Server almost anywhere and you don’t have to be a Microsoft platform developer to benefit from what is one of the most powerful relational databases on the planet. As somewhat of a newbie to Docker, I was curious about the ability to create an image that included my own database. And I’m always happy to share the results of my explorations. I learned a lot and hope that your curiosity isn’t just sated, but piqued enough to explore the possibilities even further. n
Julie lerman is a Microsoft Regional Director, Microsoft MVP, software team men- tor and consultant who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at thedatafarm.com/blog and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at juliel.me/PS-Videos.
Thanks to the following Microsoft technical experts for reviewing this article: Sanjay Nagamangalam and Travis Wright
→ sqlcmd -S localhost -U sa -P Passw0rd 1> select name from sys.databases
2> go
name
-------------------- master
tempdb
model
msdb juliedb
(5 rows affected)
1> use juliedb
2> go
Changed database context to 'juliedb'. 1> select name from sys.tables
2> go
name ----------------- people
(1 rows affected)
1> select * from people
2> go
PersonId Name ----------- ----------------
1 julie
2 giantpuppy
(2 rows affected) 1> quit
→
16 msdn magazine
Data Points