Containers are one of the big things at the moment, Microsoft has recently announced that they will be bringing official support to Windows. While this is great news it is unlikely to be something most IT departments can use in the near term mainly because it takes time for everyone to get up to date with the latest version of windows, which in this case, is still to be written.
Containers provide a number of real benefits and while there is not yet official support for them it does not mean that we cannot use them to help us, especially in development and test environments. We could also potentially use containers in other cases such as where support has ended and they are not officially supported anyway but each organisation will have different criteria.
What are the use cases for SQL Server and containers today?
Containers let you run multiple copies of processes in their own sandbox so they cannot affect other processes but they do not have the same security boundary that a process has running on its own physical or virtual machine. I think until Microsoft provides official support we should just rule out, in the most part, any production instances of SQL Server. This for now at least, makes the discussion about using containers without official support easier.
Continuous Integration
This is where I started looking at running SQL Server in a container. What we can do is for every build that runs, spin up a new instance of SQL Server with a copy of the target database or databases in the state of the last production deployment, deploy the new code, run the tests, gather the results and destroy the new instance of SQL Server.
This sounds a bit wasteful but it is actually really useful in a number of ways:
- If we use the compare/deploy method of deploying our code we run the same upgrade that we would run in production instead of either restoring a backup before every build or in the CI environment running incremental changes but larger bulk changes in production. I would much rather test the changes from the CI environment upwards.
- We do not need a separate SQL instance or set of databases for each consecutive build
- If there is code that makes cross database calls or writes to system databases like msdb or master, every build gets its own instance so again you do not need to have multiple instances hanging around to do consecutive builds
- If you do not control the build environment such as Visual Studio online, you can spin up your own database instance on the build server and use that to test your code
Development Instances
Each developer should have their own instance of SQL Server. If you work in an environment where you have different versions of SQL Server or different settings (server collation!) for each application then having lots of different versions of SQL Server installed can be a pain. I understand that installs can be scripted but it isn't a great experience.
Instead if you use containers a developer or tester could simply start whichever version of SQL Server they needed for whatever they were working on at the time and not have to worry about whether they had SQL 2008 R2 with binary collation etc.
It also helps with the "Joel on software" test, can you checkout and build in one step? Yes but it takes an hour to get SQL Server installed to test it!
Relocating instances
When you run a container from an image you do not need to install anything so if you wanted to move to a different O/S you could pick it up and move it. That seems pretty cool and I can see this being used more when production instances are supported.
Recreating complex environments
If you have a test environment that includes replication and other fun things then you may need a number of instances to re-create a test environment, using containers you can run them on a single host but still re-create the actual environment. Think about this if you have instance A that replicates data to instance B, both of them are the default instance and you have apps hard coded to connect to the server ".", you also connect to a database with a hard coded name - typically you would have to use two different machines to reproduce this but using containers you can start them both and make them both think they are listening on port 1433, w o w, sorry let me repeat that in capital letters W O W!
Running apps without having to use the installer
There are lots of apps you can run directly via without having to install them, think of Chocolatey without the install step. Start chrome, Firefox, IE, MySQL, SQL Server, anything. You can run them without having to install them which is really useful for just about everyone.
Can we use containers on Windows today?
Yes, but lets look at the technology first and how it can be applied to run applications within containers on windows.
What is a process?
Code that runs on a computer, on Windows a process:
- Reads and writes files
- Reads and writes to the registry (which is actually stored in a file)
- Makes Win32 API calls - whether the app is .net or whatever is irrelevant, under the hood Win32 APIs are called on Windows (forget that kernel drivers call the nt subsystem, we are talking apps here)
- Accepts and creates network connections and reads and writes data from them
Whether your application is notepad or SQL Server, in this context, they do the same thing.
What is an image
An image is the installed state of an application stored in a reusable format. On Windows they are often created by taking a snapshot of the files and registry, installing an application and then taking a further snapshot to see what has changed. The changes are then gathered up and made available as an image that can be downloaded and run on another machine.
What is a container?
A container is where an image or set of images are downloaded onto the machine running the container and the applications inside the image are run. Typically when they run they believe that they have access to the entire machine and are situated in "\Program Files\Whatever" but actually they are normally in a users temporary folder. It is this deception that allows containers to be kept within their sandbox. To enable this sandbox approach to running images, we can use app virtualization technology.
What is App Virtualization?
App Virtualization is where you run a process and redirect file, registry and other API calls to somewhere else so an application may make a call to the Win32 API "CreateFile" and ask to read the "c:\program files\microsoft sql server\120\MSSQLSERVER\binn\sqlservr.exe" file but actually the path is changed to "c:\Users\ed\AppData\Blah\sqlservr.exe" - this means that using the image that was created on another machine I can run SQL Server without ever having to install it - or run it alongside another version whether they normally read and write to the same locations or not.
There are a number of app virtualization providers on the market including Microsoft, Spoon and Symantec. To see a fuller list of vendors see "Vendor-specific implementations" on http://en.wikipedia.org/wiki/Application_streaming.
Using Spoon
So Spoon.net has been been operating since the mid-2000's previously as Xenocode and where they had the virtualization technology to run processes in sandboxes and their Spoon Studio software which creates the images they have now added the ability to publish images and store them in a private or public repository which can be run on any windows machine and it is this extra support which allows us to use containers on windows today.
This is beginning to sound like marketing, do you work for Spoon?
No, I don't and I am not affiliated with Spoon in any way. I have used their products before and started discussing how we could use app virtualization to run SQL Server in containers with the CEO on twitter, this by the way is one of the reasons I love twitter! I tested it out and like it, I wouldn't recommend something unless
- it was something everyone could afford - plans start at free
- it fundamentally worked - tick
- their support is responsive and resolved any issues - I had one issue which has been resolved quickly, I am actually glad I had an issue as it is only when you use support you find out what they are like. I found them to be polite knowledgeable and interested in fixing my problem.
Sounds expensive
No, a basic plan which includes everything you need to start creating public images and running those is free. To have a private repository of images you need to pay and the cost is more expensive to self host your repository but it really isn't a lot and if it means you can run less Windows instances then you are more than likely going to save money on licenses.
Enough talk, lets see this in action
I like your style.
To use the Spoon containers you will need to get a Spoon.net account, go to:
Click the "Try Free" button to create an account, no credit card needed etc.
When you have created an account make sure you are on the "Dashboard" page and click on "New" and then "Spoon Shell", this will download the Spoon shell which you can install. This is a small application that lets you control the images and containers, if you want to run a container as part of a CI process you do not need to install it but it makes our demo easier for now.
If you go ahead and install the "spoon-plugin.exe" which will let you run Spoon commands from a cmd window. You should install it on a machine, real or virtual that you want to start a new instance of SQL Server on.
Note, that after the Spoon shell is installed, we will download the express edition of SQL Server 2014 and start it on your machine, if you have policies against running strange code on your machine run it on a VM or somewhere you are allowed to. If you already have an instance of SQL Server on your machine, fear not this will start a new instance and use the Spoon app virtualization technology to make sure they exist side by side.
When the shell is installed we simply need to create a new container based on an existing image, spoon have already created images of SQL express 2012 and 2014 either with or without SSMS included. There is nothing stopping you from creating your own images so if you need SQL 2000 Enterprise edition you can do that but you should keep it rather than sharing the image due to the way SQL is licensed.
If you now start a cmd.exe prompt, there is no need to create an elevated prompt we can create a container from the SQL express image. In the cmd window, use the spoon command:
spoon run --route-block=tcp spoon try sqlserver/sqlserver2014-express
This will download the image onto your machine it will be cached so the first time an image is started it will take as long as it takes to download ~1 gb over the internet, subsequent starts will be significantly quicker. Spoon are working on a new system that will significantly reduce download times but it is not available yet.
This will download the SQL express image, start the sqlservr.exe service and also start a version of cmd that can “see into” the container, if you run sqlcmd (sqlcmd.exe -S 127.0.0.1,1433) you can run the normal SQL Server commands. Do something cool like "select @@servername; GO" it will literally knock your socks off!
OK so I have a new SQL Server instance, now what?
Now you have the plug-in installed you can do all sorts of cool things, if you use sqlcmd.exe to make some changes, create a database for instance then from a cmd window do "spoon containers", you should see the container listed so do "spoon stop [containerid]" - this should stop the instance, do "spoon start [containerid]" you will see that your new database still exists. Now let’s pretend that we want another instance of SQL so do, "spoon run sqlserver/sqlserver2014-express " look, two separate instances which both think they are listening on the same port, two instances of sqlcmd which can both connect to their own instances which are completely independent:
Wait WAT?
Have a play around, to start and stop containers use the commands "spoon start" and "spoon stop" when you want to throw your containers away just do "spoon rm --all" to throw them all or "spoon rm [containerid]" to just get rid of one.
Additionally, instead of "spoon run" you can do "spoon try" which creates a temporary container where you don't need to do a "spoon rm" to get rid of it, just shutdown SQL Server (sqlcmd.exe --> “shutdown” is the easiest) or "spoon stop" on the container id.
Summary
If you followed the quick demo and got a SQL Server instance running on your machine, then well done but this is literally only the surface of what Spoon can do.
In summary:
- Run images of SQL Server wherever (windows) and whenever you want = cool
- Really useful for development and test
- Free to use public images, although if you use it you will want to have private repositories
- Running things you download of strangers off the internet is risky, do what you will with that warning
- My use of Spoon is as a SQL Server developer but spoon isn’t limited it SQL Server it works with almost every application even your own in-house apps and services
