In my previous post on running SQL Server in a container I showed how you could use the Spoon (https://spoon.net) products to start an instance of SQL Server without having to install it: https://the.agilesql.club/Blog/Ed-Elliott/Containers/Run-SQL-Server-In-A…

In this post I am going to talk about images and how to layer them together and why SQL Server is perfect for this sort of container technology!

What is an image

An image is the files and registry keys that make up an application, the sqlserver/sqlserver2012-express image was created by taking taking all of the files and registry keys that were added to a machine during the SQL Server install. This normally happens using Spoon Studio which takes a snapshot of the system, you then do your install and configure SQL Server (or whatever app it is) however you like and then you take a post snapshot, Spoon Studio gathers all the changes and creates an image.

When you do this:

spoon run sqlserver/sqlserver2012-express

The image is downloaded to your machine, to list all the local images do “spoon images”:

You can remove images etc.

If we look at the sqlserver2012-express image as an example, it has sqlservr.exe, sqlcmd.exe, dll’s etc as well as the registry keys for the SQLEXPRESS instance along with a master database that tells it what user databases to load. Because it was a fresh install the master database doesn’t try to load any user databases which is fine in some scenarios but what if we wanted to have a user database or a choice of user databases?

Layering images

What we can do is to create a container which is an instance of an image, connect to SQL Server and create our database or configure SQL as we want it and then capture the difference between the original image and our new state. We can capture this into a new image, either with the original image in or a new image by itself. We can then create a new container which is the original sqlserver2012-express image and our new second image and hey presto we will have a running instance of SQL Server including our database!

Walkthrough

The first thing to do is to launch the base SQL Server instance:

spoon run sqlserver/sqlserver2012-express

The next thing is to connect to SQL Server using sqlcmd and do some stuff, I will create a database and a table but you could just as well deploy a million (well maybe not a million) databases using scripts or dacpacs etc. As an aside I normally start the instances without SSMS installed, if you want to use SSMS and don’t already have it installed on the machine you can run “sqlserver/ssms-2014-express”.

sqlcmd -S 127.0.00,1433

Then we shutdown our container and create a new image that just contains the differences, which in this case is the new Awesome mdf and ldf and the changes to master:

spoon commit –no-base 359da0d2 eddebug/awesome-database

359da0d2 is the id of the container, you can see it in the spoon shell output.

If you then do:

spoon images

you see the new image of the awesome database:

Because I did –no-base the size is 80 mb, rather than including the original base image which would be about 1gb and it means I can have the original image cached on my machine and now share it with different databases wihtout having to redownload the same bits over and over again.

To then start the new image, we simply chain together the base and new instance such as:

spoon run sqlserver/sqlserver2012-express,eddebug/awesome-database

You can layer as many images as you like, think about adding in ssms or sqlpackage or a specific release and you can begin to see how powerful this stuff is.

Why is SQL Server so perfect for containers?

There are a couple of reasons, the first is that SQL already has instance support so it doesn’t create global objects which mean you can’t run multiple copies and secondly you can swap in different copies of master to configure a SQL instance exactly as you want, there are no wierd config files or binary blogs of data hidden in registry keys - everything is pretty well documented and straight forward, at least for the core SQL Server engine - who knows about MSAS or MSRS!

Have a play!

If you like the look of running instances of SQL without installing them or without having to have lots of VM’s hanging around then I would encourage you to have a play - drop me a line with any questions or reach out to spoon.

I have published the awesome database as a demo so if you want to see how the layers work, start a container with just SQL and also one with the awesome database and have a play around:

spoon run sqlserver/sqlserver2014-express

spoon run sqlserver/sqlserver2014-express,eddebug/awesome

To launch the awesome database from the spoon web site go to: https://spoon.net/hub/eddebug/awesome

Enjoy!

---

Comments:

Anonymous

July 9, 2015 - 02:40

typo

great article as always…i think there’s a little typo. the image is fine but the text is off slightly.
sqlcmd -S 127.0.00=.1,1433

Ed Elliott

July 9, 2015 - 08:02

typo

Hi - thank you, I have fixed it!

Anonymous

July 13, 2015 - 21:24

Great article!

Appreciate it!

Minor typo: last sentence “form” the spoon web site

Ed Elliott

July 13, 2015 - 21:27

Thank you for your kind words

Thank you for your kind words, I have fixed it now :)

ed