SSIS, Docker, and Windows Containers, Part 0 – Getting Started

To begin tinkering with SSIS in containers, you first need to install Docker. There are some prerequisites. I will not exhaust the prerequisites here. I strongly suggest you familiarize yourself with the requirements for Docker prior to attempting to install the software.

Since I use a PC (a Lenovo P51S) running Windows 10 Pro, I chose to use Docker for Windows.

I Needed a HyperVisor

I chose to run Docker for Windows with Hyper-V:

One reason I upgraded to Windows 10 was to work with containers. I read posts and articles that stated I could use Docker for Windows with VirtualBox, and I had been using VirtualBox for a long time. When I started using VirtualBox, it was the only HyperVisor that was:

  1. Free; and
  2. Supported 64-bit guests.

I decided to switch to Hyper-V, though, and rebuilt my demo and development virtual machines in Hyper-V.

IWOMM; YMMV (It works on my machine; your mileage may vary…)  😉

Choose a Container OS Platform

Once Docker is installed you need to decide whether to work with Linux or Windows containers, but you can switch anytime:

One nice thing about switching is it’s fairly painless, as one may glean from the message that displays when I click “Switch to Linux containers…”:

Docker for Windows with Windows Containers

The cool kids are using Linux containers, especially the cool SQL Server kids. I’ve tinkered a little with SQL Server on Linux. I’m interested in SSIS, though, so I’ve been focusing on running Windows containers in Docker for Windows.

Getting Started

Containers are like lightweight virtual machines. They typically consume less disk space. Containers spin up relatively quickly. A developer can configure a container, persist it in some state, and then turn it off. It’s faster and lighter than working with a VM. There are other benefits that we will get to later in this series.

After installing Docker, your next step is pulling an image.

Pulling an Image

I can hear you thinking, “Umm, Andy… what’s an image?” I’m glad you asked. From the Docker Glossary:

An Image is an ordered collection of root filesystem changes and the corresponding execution parameters for use within a container runtime. An image typically contains a union of layered filesystems stacked on top of each other. An image does not have state and it never changes.

I can now hear you thinking, “Umm… what?” I think of an image as a pre-configured container. What’s configured in the pre-configuration? Well, the operating system and other software I may want to use.

For example, the hello-world image is relatively small and will test your installation of docker. In the animated gif below, I show how I:

  • Search for images named “hello-world”
  • Locate the name of an image labeled “Official” and named “hello-world”
  • Pull the “hello-world” image (sped up – my internet is not that fast out here in Farmville…)
  • Re-pull the “hello-world” image to show how things look when the image is up to date.
  • Run the image
(click to open, enlarged, in a new tab)

As stated earlier, the hello-world image is a test.

Searching, Pulling, and Starting a Windows Container with SQL Server Installed

To search for, pull, and start a Windows container with SQL Server installed and configured, execute the following Docker commands:

  1. docker search mssql
  2. docker pull microsoft/mssql-server-windows-developer
  3. docker run -d -p 1433:1433 -e sa_password=$up3r$3cr3t -e ACCEPT_EULA=Y –name mySqlServerContainer microsoft/mssql-server-windows-developer

If all goes well, you will see the container id – a hexadecimal number such as:

2d06a9a756b7b75bb0e388173bdd6925ba712e8843848610b5f5276a69f4bf19

Command Line Switches

#3 above has a lot of switches on that command line. Here’s what they mean:

  • -d == detach, which tells docker to run the container in the background and print the container id
  • -p == publish list, which publishes a container’s port(s) to the host
    • I use -p to map the container’s port 1433 (SQL Server uses port 1433) to my laptop’s port 1433
  • -e == env list, which sets a container’s environment variable(s) to a value (or values).
    • I use -e to set the sa_password and ACCEPT_EULA environment variables
  • –name == assigns a name to the container
  • The final argument is the name of the image

By the way, you can get help on any command in docker by typing docker <command> — help. To complete the list above, I typed:

docker run –help

Interacting with Your Newly-Started Container

If you saw that long hex number, your SQL Server Windows container started. It’s a brand new container – it still has that new container smell.

There are a few ways to interact with this container. Let’s look at one, PowerShell.

Connect to PowerShell in mySqlServerContainer using the following command:

docker exec -it mySqlServerContainer powershell

Once connected, you can use PowerShell to execute all sorts of commands, such as:

dir
(or ls):

Cool? I think so too.

Conclusion

I’m going to stop here because this post is long enough and this is enough to get you started using SQL Server in a Windows container. That was my goal today.

Enjoy!

It is Possible to Execute SSIS in a Container

As the title of this post suggests, it is possible to execute SSIS in a container. How? I’ll share more details later – promise. One short version is:

  1. Run a container with the microsoft/mssql-server-windows-developer image (assign lots of RAM).
  2. Add the SQL Server installation files to the container.
  3. Execute SQL Server setup from a command line configured to add Integration Services (this is why you need the RAM).
  4. Add SSIS.
  5. Execute from the command line (dtexec).

The results?

SSIS Catalog in a Container?

I can hear you thinking, “Yeah? But what about using the SSIS Catalog in a container, Andy? Huh? HUH?”

Calm down, will ya? I’m working on it.

I’ve benefited from some great advice and help from Ned Otter (nedotter.com | @NedOtter). I caught Ned’s presentation on containers at SQL Saturday Boston 22 Sep 2018. I’d seen other presentations on containers and learned a lot. Ned’s session put me over the top.

I can hear you thinking, ” Yeah-yeah. Have you made any progress, Andy?”

Well, some:

Above is a screenshot of an SSIS 2017 Catalog hanging out in a container. In this state I cannot do much with it because I am connected using a SQL Server login (even though said login is sa).

I was able to “create” a Catalog folder in this Catalog:

I write “create” in quotes because I “created” the folder in an unconventional manner (executing pieces of SSISDB stored procedures).

I may be able to deploy an SSIS project – again, via some creative combinations of pieces of SSISDB stored procedures. If so, I doubt I will be able to execute an SSIS package in this Catalog.

The only way I can think to accomplish this is to bypass some Catalog security checks which means there is a distinct possibility that important steps will be missed or skipped.

My gut tells me this would be bad.

The best path forward is to find some way to connect to SQL Server in the container using Windows Authentication. I’ve been communicating with my brother and friend, Brian Kelley (truthsolutions.wordpress.com | @kbriankelley) about this but have yet to solve it.

I plan to publish the image once I figure out how.

Conclusion

I am excited about executing SSIS in a container from the command line, though. Very excited.

More to come…

:{>