Running SQL Server + ASP.Net Core in a container on Linux in Azure Container Service on Docker Swarm - Part 1

Too many ins and ons in the title. Welcome to the world of containers!

This is a multi-part blog series. In this, I'll cover -

1. Part 1 (This post): Create and run a SQL Server Container in Linux.
2. Part 2: Create and run an ASP.Net Core application that can interact with SQL Server Container.
3. Part 3: Deploy ASP.Net Core application and SQL Server as a multi-container app to Azure.

Let's get started.

SQL Server has announced support for Linux. Developers (especially .Net developers) can now fully embrace the Linux platform.

Earlier, even though it was possible to run ASP.Net Core apps on Linux, because its better half, off course SQL Server!, would still be running on Windows. This ended up being a hybrid model against most developers wishes and convenience. But that is past now.

In this post I'll cover how to run ASP.Net Core and SQL Server on Linux. I'll do it using containers because sooner or later you will have too!

I have already talked about why and how of running applications using container technology. While that discussion focused more on ASP.Net Core application, in this post I will briefly discuss why it is a good idea to run SQL Server as a container. For a lengthy discussion, I will recommend to listen to this very good discussion on the topic. It covers almost all the benefits of running SQL Server as a container. Personally, I find following 2 reasons to be very compelling to get started with SQL Server Container.

1. Consistent Data Model across environments: Days of database schema mismatch between dev. and other environments are not too far behind us. It is a struggle to ensure that changes made in dev. environment actually make it to other environments. Most conventional approach is to create 2 scripts, 1 for database creation and other for master data import (mostly a SSIS package script). Developers will source control these 2 scripts and ensure that same scripts are used for database deployments. My observation, however is that, for DBAs and Database Teams adopting to a source controlled scripts is not very common. There are changes done which are not checked-in and teams still end up having different database schema in different environments. Containers are a better approach than source controlled scripts and we'll see how so shortly.
2. Multi-tenant applications: More and more applications these days run as tenants in some shared environments. This results is better resource utilization of capacity and most importantly lower costs. Containers are great for packaging such applications. SQL Containers take this model further and allow such a very common database engine to participate in this exciting ecosystem.

Now that I have covered why, let's start with how.

First, SQL Server. It is easy to run SQL Server as a container by creating it from base image microsoft/mssql-server-linux. Once docker environment is set up,  base image can be pulled down by running following command.

At the time of writing this, image size is > 1GB so it may take some time to download. Once the image is available, a SQL Server container can be created by running following command.

Lets look at some of the switches used in the above command -

-d switch indicates that command runs in detached mode. This is a good choice for troubleshooting purposes  or even to know what's going in the background. Otherwise, it can be skipped. Ensure that at least 3.25GB RAM is available on server or local machine when using "Docker for Windows". . This setting should look something like below.

-p switch is for port. Use standard 1433 port for SQL connectivity on container host as well as container.

-e switch is used for defining environment variables. Command above uses them for accepting EULA and specifying sa password. Please follow password strength policy of your organization when setting password :-)

Finally, specify the base image, microsoft/mssql-server-linux.

A container ID is returned back after successful execution of the command. It is easy to verify if SQL Server Container is created or not by using SQL Server Management Studio (SSMS).

Note that Server name is 127.0.0.1, 1433. This is the local Linux Container Host. SA password should match the one entered while creating container. Clicking "Connect" should take to the full SSMS experience. All system database like master, msdb, tempdb and model are already populated.

SQL Server is now running as a container. Next step is to create a new custom database in this container. Process of creating database is still the same. A database creation (and an additional table creation script if needed) script is executed. This script will look something like below.

New database should start appearing along with other databases.

Now let's take a snapshot of this container and create an image so that there is no need to run this script every time. To create such an image, run following command.

Command above stops the running container. 2c here are first 2 letters of the container id that uniquely identifies container. Since the container is now stopped, a snapshot of it can be taken by running following command.

Command above will create an image in the format <my-id>/<imagename> in local environment. Syntax is merely a convention. This image has the database structure embedded into it. So from next time on, this image can be used to create a container and it will have custom database pre-populated.

Let's test that. First, execute following command to see if new image is available locally or not.

Sure it is there(the one that ends with db!). It appears along side the original microsoft/mssql-server-linux image. The original container created from the base image can be now stopped and removed. A new container can be created using new image. Use following command to create this new container.

So a new container is created using custom database image. Let's connect to it from SSMS and see if the database is pre-populated. As before, connect to container by specifying 127.0.0.1, 1433 as server name and providing sa password that was used while creating the container. As can be seen below, custom database starts appearing alongside system databases.

This marks a very important point. There is an image ready from which new containers can be created. These containers will be pre-populated with custom database. This ties back to the earlier point on consistency in data model/schema. This image can be used to create containers in different environments (dev., qa, uat, etc.) and the data model remains same. If any change in the data model is required, a new image or a new version of the same image can now be created. This is giving full change tracking capabilities on the data model/design.

Now let's push this image to Docker Hub, a central repository from where it is possible to pull this custom database image. This is same place from where original database image, microsoft/mssql-server-linux came from. To push an image to Docker Hub, use following 2 commands -

A Docker Hub account is required to be able to run above command. Once above command succeeds, run following command.

When this command completes, this image should appear in Docker Hub repository.

A SQL Server Container with custom database is running locally using Linux simulator of Docker for Windows. An image for this container to be created when needed is also created and pushed to docker hub. In next post, we'll see how to write an ASP.Net Core application that will interact with this SQL Server Container.