Saturday, January 22, 2022

docker-compose with ASP.NET 6.0 & SQL Server

This article shows you how to create a docker-ized ASP.NET 6.0 web app that uses SQL Server. The end result will be a solution that you can run using "docker-compose up". I will, thereafter, show you how you can deploy your solution to Azure.

Source code:


  • .NET 6.0 is installed on your computer. 
  • Docker is installed on your computer. 

Getting Started

We will create an ASP.NET 6.0 razor pages application that uses individual authentication with the following command:

dotnet new razor -f net6.0 --auth individual -o AspMsSQL --use-local-db

Thereafter, go into the new folder with:

cd AspMsSQL 

To run the web application and see what it looks like, enter the following command:

dotnet run

You will see a message in the terminal window that resembles the following:

The above message indicates that the Kestrel web server is running and listening on port 7258 (https) and port 5231 (http). Start your browser and enter the appropriate URL. You should see a page that looks like this:

Close your browser and stop the web server in the terminal window by hitting CTRL C.

Let us configure our web application to use SQL Server with environment variables. Open the Program.cs file in your favorite editor and comment out (or delete) the following statement at around line 8:

// var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
// builder.Services.AddDbContext<ApplicationDbContext>(options =>
//     options.UseSqlServer(connectionString));

Replace the above code with the following:

var host = builder.Configuration["DBHOST"] ?? "localhost";
var port = builder.Configuration["DBPORT"] ?? "1444";
var user = builder.Configuration["DBUSER"] ?? "sa";
var pwd = builder.Configuration["DBPASSWORD"] ?? "SqlPassword!";
var db = builder.Configuration["DBNAME"] ?? "YellowDB";

var conStr = $"Server=tcp:{host},{port};Database={db};UID={user};PWD={pwd};";

builder.Services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(conStr));

Environment variables are used in the database connection string. These are: DBHOST, DBPORT, DBUSER, DBPASSWORD and DBNAME. If these environment variables are not found then they will take default values: localhost, 1444, sa, SqlPassword! and YellowDB respectively.

Next, add the following code, also in Program.cs, right before the last statement

using (var scope = app.Services.CreateScope()) {
    var services = scope.ServiceProvider;

    var context = services.GetRequiredService<ApplicationDbContext>();    

The above code will apply any outstanding database migrations.

There is, of course, something major that is missing I.E. we do not have a SQL Server database server yet. Let us have a Docker container for our SQL Server database server. Run the following command from any terminal window:

docker run --cap-add SYS_PTRACE -e ACCEPT_EULA=1 -e MSSQL_SA_PASSWORD=SqlPassword! -p 1444:1433 --name azsql -d

If you do not already have the docker image for SQL Server, it will be downloaded. Thereafter, a container will be made from that image and will run in background mode. To prove that the container was indeed created from the image and is active, run this command:

docker ps -a

You will see output that looks like this:

CONTAINER ID   IMAGE                              COMMAND                  CREATED          STATUS          PORTS                              NAMES
fde883706160   "/opt/mssql/bin/perm…"   25 seconds ago   Up 24 seconds   1401/tcp,>1433/tcp   azsql

Now, let us test our application and see whether or not it is able to talk to the containerized SQL Server database server. 

Run the web application with the following terminal command:

dotnet run

If all goes well, you will see a message that indicates that the web server is listening on a specified port. Point your browser to http://localhost:#### (where #### is the appropriate port number). The same web page will appear as before. Click on the Register link on the top right side.

I entered an Email, Password and Confirm password then clicked on the Register button. I was then presented with the “Register confirmation” page.

Click on the “Click here to confirm your account” link to make the app accept the login credentials that you had just entered. This is what you will see:

You can now login with the registered credentials:

I was then rewarded with the following confirmation that the credentials were saved in the SQL Server database server:

The message on the top right side confirmed that the user was saved and that communication between my ASP.NET application and SQL Server is working as expected.

Docker-izing an ASP.NET and SQL Server App

Stop the web server by hitting CTRL+C in the terminal window.

We will generate the release version of the application by executing the following command from a terminal window in the root directory of your ASP.NET project:

dotnet publish -o dist

The above command instructs the dotnet utility to produce the release version of the application in the dist directory. This results in output similar to the following:

Microsoft (R) Build Engine version 17.0.0+c9eb9dd64 for .NET
Copyright (C) Microsoft Corporation. All rights reserved.

  Determining projects to restore...
  All projects are up-to-date for restore.
  AspMsSQL -> E:\_playground\0000\AspMsSQL\bin\Debug\net6.0\AspMsSQL.dll
  AspMsSQL -> E:\_playground\0000\AspMsSQL\dist\

The highlighted file in the above screen-capture is my main DLL file that is the entry point into the web application.

Let us run the release version of your application. To do this, change directory to the dist directory with the following terminal instruction:

cd dist

You can then run your main DLL file. In my case, this file is AspMsSQL.dll. I executed the following command:

dotnet AspMsSQL.dll

This should run the web application just as it did before.

Stop & remove the SQL Server docker container with the following command:

docker rm -f azsql

We have a good idea about what ASP.NET artifacts need to be copied into a container. We will simply copy contents of the dist directory into a Docker container that has the dotnet core runtime.

Return to the root directory of your project by typing the following in a terminal window:

cd ..

We need to create a docker image that will contain the dotnet core 6.0 runtime. A suitable image for this purpose is:

Create a text file named Dockerfile and add to it the following content:

COPY dist /app
EXPOSE 80/tcp
ENTRYPOINT ["dotnet", "AspMsSQL.dll"]

Above are instructions to create a Docker image that will contain our ASP.NET 6.0 application. I describe each line below:

FROM Base image will be used
COPY dist /app Contents of the dist directory on the host computer will be copied to directory /app in the container
WORKDIR /app The working directory in the container is /app
EXPOSE 80/tcp Port 80 will be exposed in the container
ENTRYPOINT ["dotnet", "AspMsSQL.dll"] The main ASP.NET web application will be launched by executing "dotnet AspMsSQL.dll"

We will next compose a docker yml file that orchestrates the entire system which involves two containers: a SQL Server database server container and a container that holds our application. In the root folder of your application, create a text file named docker-compose.yml and add to it the following content:

version: '3.8'

      - sqlsystem:/var/opt/mssql/
      - sqldata:/var/opt/sqlserver/data
      - sqllog:/var/opt/sqlserver/log
      - sqlbackup:/var/opt/sqlserver/backup

      - "1433:1433"
    restart: always
      MSSQL_SA_PASSWORD: SqlPassword!

      context: .
      dockerfile: Dockerfile
      - db
      - "8888:80"
    restart: always
      - DBHOST=db
      - DBPORT=1433
      - DBUSER=sa
      - DBPASSWORD=SqlPassword!
      - DBNAME=YellowDB


What does the above do?

We will be having two containers. Each container is considered a service. The first service is named db and will host SQL Server. The second service is named webapp and will host our ASP.NET web app.

The most current version of docker-compose is version 3.8. This is the first line in our docker-compose file.

1) The SQL Server Container

Image will be used for the SQL Server container.

Volumes named sqlsystem, sqldata, sqllog and sqlbackup are declared that will host SQL Server data outside of the container. This ensures that even if the SQL Server container is decommissioned, data will not be lost.

restart: always is so that if the container stops, it will be automatically restarted.

The root password will be SqlPassword! when SQL Server is configured. This is set by the MSSQL_SA_PASSWORD environment variable.

2) The ASP.NET 6.0 Web Application Container

The container will be built using the instructions in the Dockerfile file and the context used is the current directory.

depends_on indicates that the web app relies on the SQL Server container (db) to properly function.
Port 80 in the webapp container is mapped to port 8888 on the host computer.

Just like in the db container, restart: always is so that if the container stops, it will be automatically restarted.

The environment variables needed by the web app are:

- DBHOST points to the SQL Server service
- DBPORT uses the port number that SQL Server is listening on
- DBUSER is sa
- DBPASSWORD is the SA password SqlPassword!
- DBNAME is the database name, set to YellowDB
- ASPNETCORE_ENVIRONMENT set to Development more. In reality, you should change this to Production one you determine that your web app container works as expected

Running the docker-compose.yml file

To find out if this all works, go to a terminal window and run the following command:

docker-compose up

Point your browser to http://localhost:8888/ and you should see the main web page. To ensure that the database works properly, register a user by clicking on the Register link in the top right corner, confirm the email address, then login.

In my case, I received confirmation that a user was indeed registered:

As you can see in the top-right corner, the user with email a@a.a has been successfully registered.


Let's shutdown and cleanup resources on our computer.

Inside of the terminal window that is running docker-compose, hit Ctrl C on your keyboard to stop the services. Thereafter, enter the following terminal command:

docker-compose down

To remove the webapp docker image, type:

docker rmi -f aspmssql_webapp

To remove all the volumes that were created on your computer, type:

docker volume rm aspmssql_sqlbackup
docker volume rm aspmssql_sqldata
docker volume rm aspmssql_sqllog
docker volume rm aspmssql_sqlsystem


The same concepts covered in this tutorial can help you create multiple containers that involve two or more services. For example, you may wish to create three containers comprising:
  1. a database
  2. a backend WebAPI application
  3. a frontend web app developed in React or Blazor
It is my hope that this opens up for you a new world in containerizing your applications.

No comments:

Post a Comment