Dotnet Core Webapi With Mssql in Docker Part 2

This blog post first appeared on my blog

In the previous post I showed how to setup a Dotnet Core WebApi project to run inside docker.

In part 2, I'll go over what is needed to containerize an SQL server and connect the WebApi to it.

What do you need?

This post assumes you have Docker Community Edition installed on your machine. If not, you can grab a copy from here.

Containerizing Microsoft SQL Server

Thankfully, Microsoft has created a base image for the SQL server which I will be using in this blog post. The image is available on the official docker registry. You can find it here.

We will start by creating a docker compose file. Docker Compose is a tool in docker used to define the configurations of your app's services and then create those services and start them up with a single command. More info can be found in Docker's official documentation page.

Name the file

docker-compose.yml

. The file should look like this:

version: "3"
services:
networks:

As you can see, we are using Version 3 of the docker compose file format.

Next, we need to define and configure the required services for out application. Here, Application encapsulates everything that must be up and running so the WebApi can return results as expected. In this case, we will have the following services:

• WebApi: the dotnet core web api project
• Sql-Server: an instance of Microsoft SQL server

Great! so now that we know what services to define, let's go ahead and define the first one in the docker compose file. We will start with the WebApi service.

We already have the DockerFile from the previous post which created the WebApi image. Let's see how to use that image to create the first service:

version: "3"
services:

  # webapi serves as the name of the service
  webapi:
    # The image name which this service will be based upon.
    image: MyWebApiProject

    # This defines the ports which should be exposed by this service's container to the outer world
    ports:
      - "5000:80"
      - "5001:443"

    # Make this service part of a network called webnet
    networks:
      - webnet
networks:
  webnet:

With this service, we are using the image name MyWebApiProject which was created by the WebApi DockerFile which we created in the previous post.
And now that we have defined the first service that will be created and launched, we move on to the next service to define: Microsoft SQL Server.

We don't have a DockerFile for MSSQL and we don't need one. We can specify an image hosted on Docker's official registry and docker compose will pull that image if it is not cached on the machine already. Let's see how we create the MSSQL service in docker compose:

version: "3"
services:

  # sql-server is the name of the service and will also be the name of the server on the network
  sql-server:

    # this is the image docker compose will pull to create a MSSQL service instance
    image: mcr.microsoft.com/mssql/server

    # this defines the ports to expose
    ports:
      - "1433:1433"
      - "1434:1433"

    # this put the MSSQL service instance on the same network as the WebApi service
    networks:
      - webnet

    # these are required by the image as per the MS documentions
    environment:

      # This is the password we will use for this server (passwords must pass the password rules of SQL server)
      SA_PASSWORD: "YG4ZkkGrn7QSz5&"

      # Required to accept the End User License Agreement
      ACCEPT_EULA: "Y"

      # This defines which edition of the server we want to run. We can also use "Express"
      MSSQL_PID: "Developer"

  # webapi serves as the name of the service
  webapi:
    # The image name which this service will be based upon.
    image: MyWebApiProject

    # This defines the ports which should be exposed by this service's container to the outer world
    ports:
      - "5000:80"
      - "5001:443"

    # Make this service part of a network called webnet
    networks: 
      - webnet
networks:
  webnet:

We gave the MSSQL service a name. In this case it is sql-server. This is what we will use in the WebApi connection string to connect to the containerized SQL server. So the connection string in the appsettings.json will look like this:

  "ConnectionStrings": {
    "Default": "Server=sql-server;Database=YourDatabaseName;User Id=sa;Password=YG4ZkkGrn7QSz5&"
  }

Also notice the user credentials. As per the MSSQL base image documentations, we will use the SA account to login to the server, and the environment variable we defined earlier in the docker compose file: SA_PASSWORD sets the password for that account which is what we are also using in the connection string.

Once you save the docker compose file and update the connection string in the WebApi configuration file, then re-build the WebApi image as shown in the previous post. This is so we have an up to date image of WebApi that uses the new connection string.

Next, we need to make the machine a manager of a docker swarm. To do this, in a command line, execute this command:

docker swarm init

This will create a docker swarm and make the current machine a manager for the swarm. In docker, a swarm is the cluster management feature which is required to deploy our multiple services. To read more about docker swarm, start here.

Once we have swarm created, we will then deploy our services. The command for this looks like:

docker stack deploy -c docker-compose.yml MyDockerApp

This might take a few minutes to finis if it is the first time running it as you probably don't have the MSSQL server image cached on your machine. But once done, you can run this command to see all your services created and running inside their containers:

docker stats

If you have SQL Server Management Studio on your machine, you can connect to the containerized sql server by using localhost,1434 as the host name and same credentials used in the connection string.

Go ahead and call your WebApi endpoints using localhost:5000 or localhost:5001.

Initializing a database

You might already be thinking that the SQL Server we created has no databases and that your WebApi won't work because the specific database you want to connect to does not yet exist.

Luckily, we can do something about that. We can have docker run a few commands for us as it is creating the MSSQL service. The commands we will write next will use 2 sql scripts to create the database and all of its tables.

How do we do that?

Let's start by creating an SQL file which will create the database:

CREATE DATABASE YourDatabaseNameHere;
go

This is a very simple script and will be the first to run against the SQL server.

Next, we will use Entity Framework Core to export the migrations as an SQL script. To do this, run the following command:

dotnet ef migrations script -o migrations.sql

Once that is done, create a Bash script file that will take care of executing those 2 scripts against the SQL server within the docker container:

#!/bin/bash
database="YourDatabaseNameHere"
username="sa"
password="YG4ZkkGrn7QSz5&"

# wait until the SQL server instance is ready
while ! /opt/mssql-tools/bin/sqlcmd -S 0.0.0.0 -U $username -P $password -d master -Q "select 'hello world'"
do
echo "Waiting for SQL server connection..."
sleep 1

done;

echo "SQL server ready!"

# this creates all databases specified in the databases.sql script
/opt/mssql-tools/bin/sqlcmd -S 0.0.0.0 -U $username -P $password -i databases.sql
# this runs the ef core migrations script
/opt/mssql-tools/bin/sqlcmd -S 0.0.0.0 -U $username -P $password -d $database -i migrations.sql

Place the .sh and the .sql files in a sub-directory called data. We will share this directory later with the sql server container.

Ok, now that we have everything ready, we need to get docker-compose to run the bash script for us. In the docker-compose.yml file, find the sql-server service definition and add the following to it:

volumes:
      - ./data:/init/scripts
    working_dir: /init/scripts
    command: sh -c 'chmod +x ./init-database.sh; ./init-database.sh & /opt/mssql/bin/sqlservr'

This is telling docker-compose to share the data folder with the container and place the content under this path: init/scripts. It then tells it to use the init/scripts as the working directory. The command part is overriding the default command defined by the base image. It is updating the file permissions for our bash script to allow execution, then it executes it and runs the sql server instance. While the sql server instance is being initialized and getting ready, the script would wait for it by attempting to login and query something and sleeping for 1 second.

Now, when we deploy the docker stack, we should have a database ready in the sql server instance.

You can deploy the stack again and then attempt to connect to the sql server using the SQL Server Management Studio. You should be able to see the database and the tables inside.

The Web Api should also be able to access that sql server instance.

Next steps

There is a lot more to docker than what I showed you in the last 2 parts and every project requires a different set of steps to containerize. You should familiarize yourself with the different things docker is capable of and read their documentations.

This ends this 2 part post series, but there will be more posts in the future with tips and gotcha's as I try more things with my own applications in docker.