Now that Docker is installed and working, we can investigate what containers we would like to deploy on this machine. The first port of call is Docker Hub (the official Docker repository), which contains several pre-created container images.

By running the docker search microsoft/mssql command, we request a list of images with the terms microsoft and mssql associated with them. The list of available images is constantly changing, with official images from Microsoft and unofficial/privately created images from other Dockers:

To create our first container on our test machine, we will issue a docker pull command and download the official SQL Server Express Edition image:

docker pull microsoft/mssql-server-windows-express

When we run the preceding command, the output is as follows:

Please note that here we have a list of GUID-style IDs with download progress reports. This clearly demonstrates the functionality behind Docker images. As mentioned at the beginning of the chapter, Docker packages together applications and binaries so they're portable. To further reduce overhead on a container host, images reuse portions of the data on multiple images on the same host. If we were to install five containers, each running the same version of SQL Server, Docker would be able to use a form of memory de-duplication to reuse the SQL Server binaries on these five containers while only having to download and host one set of those binaries. Equally, this reuse of data and binaries allows Docker to rapidly copy and deploy images on the same host. This very technology is what we, as developers, will want to use to rapidly spin up a SQL Server instance for testing purposes, and then we can dispose of the instance immediately after the tests have been carried out.

This approach to service usage is already widespread in other areas of IT and software development (web servers being the simplest service that this approach supports). The container in this situation is the perfect unit of control to allow the database engine to be managed and approached as a simpler service offering, like a web server. As with many of the recent advancements in technologies around SQL Server for high availability and system deployments, we receive these container benefits through the innovations made to implement the Azure services in the cloud. This technology trickle-down has been communicated by Microsoft over the last few years in most of their products and services with the Mobile First, Cloud First strategy.

Once the image has been downloaded and extracted, we can request a list of images available in the Docker host with this command:

We can now create our first container using this newly downloaded and provisioned image:

docker run -d -p 12345:1433 --env ACCEPT_EULA=Y --env sa_password=P4ssw0rd! --name MySQLContainer microsoft/mssql-server-windows-express

Let's deconstruct this command to understand the different portions of it:

docker run -d

This tells Docker that we wish to start a container in detached mode (run as a background service):

-p 12345:1433

This maps the port on the host machine (12345) to the port inside the Docker container (1433, the default SQL Server port). This is done to allow network access to SQL Server inside the container. The native container services inside Windows allow for maximum flexibility on the networking side. However, for simplicity, the Windows Container Service will implement a Virtual Network Interface Card (vNIC) and use Network Address Translation (NAT) to forward network traffic from the physical host to the container.

Defining environment variables inside a container:

--env ACCEPT_EULA --env sa_password=P$ssw0rd!

Two environment variables are defined. These are variables that are specifically passed into the container and not actually processed by Docker. In this case, we are informing the SQL Server image that we are accepting the licensing terms and conditions (as required when installing SQL Server) and providing the sa password:

--name MySQLContainer microsoft/mssql-server-windows-express

We assign a name to the container for future administration reference and supply the image that we want to use to base the container on.

Once the container has been created, we can show the status of the container with this command:

Here, we see an overview of the container, including network port assignment and which image was used to create the container.

To be able to connect to our SQL Server in the container, we need to know the real IP address that it has been assigned—0.0.0.0 is not an address we can communicate with.

We can investigate the container to find out the externally accessible IP address:

Now that we have a public IP Address, we can connect to the server using SQL Server Management Studio (SSMS). The server is no different from a normal SQL Server instance when connecting in SSMS: