Integrate SQL Server 2017 Linux with ASP.NET Core using Docker

In the previous post, we saw how to initialize a SQL Server 2017 on Linux database running inside Docker container. This post is continuation of my exploration about Docker containers for stateful applications. In this post we will explore how to integrate the SQ Server 2017 on Linux container with ASP.Net Core Web API also running inside a Docker container. We will perform following activities

• Update Docker compose file to build the SQL server image
• Update Web API project to use SQL Server Database instead of In memory database
• Build and run the application containers using Docker compose commands
• Query Data from SQL container using the MVC web front end

Update Docker compose file to build the SQL server image

Docker provides Docker Compose as a tool for defining and running multi-container Docker applications. I already have an existing docker compose file which I have been using for my series of post on implementing continuous deployment of multi container apps. In a compose file we define a set of services which are part of our application. The compose file is used build different images required for running the application.

Pay attention to lines from number 4 to 13. This is the SQL Server 2017 service we are adding to the existing MVC & Web API services of the application. These lines are the replica of the docker build and docker run commands that we used in the previous post. Note that we are using container_name attribute to specify the name of the container as sql2017. This has the same effect as –name flag in the docker run command. We will see shortly how this name helps us to resolve the container using the built in service discovery feature of Docker.

Update Web API project to use SQL Server Database instead of In memory database

Currently the Web API project uses in memory database to store the key value pairs. We will modify this by changing the code to use SQL Server database. Lets add a reference to the Entity Framework core SQL Server to our CoreWebAPI.csproj file.

Refer to line 19 where we are adding the package reference to Microsoft.EntityFrameworkCore.SqlServer package with version 2.0.0. Next step is to add a connection string in appsettings.json file.

We add a ConnectionStrings section containing DefaultConnection as the connection string. This can be named to any valid name as per your project convention. The values specified as part of connection string are Data Source=sql2017;Initial Catalog=TechTalksDB;User Id=SA;Password=January2018;MultipleActiveResultSets=True

Pay special attention to the Data Source attribute. In usual scenario, this is the name of the server or the IP address of the machine where the SQL Server service is running. In our case it is running inside the container. So instead of the machine name or IP, we specify the container name. This has to exactly match the conatainer_name specified inside the docker compose file. It is this magic of Docker networking which allows us to connect one container to another using the container name. The last part is to use the connection string from the code instead of in memory data source. This requires a change in the Startup code.

Refer to lines 28-30. We have commented the UseInMemoryDatabase call and replaced it with UseSqlServer method. We are also passing the DefaultConnection as the connection string. That is all the hardwork we need to do in order to switch from in memory database to persistent one.

Build and run the application containers using Docker compose commands

Building docker containers based on docker compose is very simple. We execute the docker compose build command as shown below

docker-compose -f docker-compose-build.yml build

Usually the convention is to name the compose file as docker-compose. If the file name is something different we can override the convention by passing the –f flag with the filename which happens to be docker-compose-build.yml in our case. The build command builds all the images for the services which have the build context specified for it. Note that we have defined a dependency between the webapi and sql2017 services using depends_on attribute.

If everything goes fine, we should have 3 images built for the services defined inside the docker compose file. Lets run the application using the docker compose command again. This time instead of build, we will be using the up command to run the services.

docker-compose -f docker-compose-build.yml up

If everything goes fine, we should get output similar to below screenshots.

Docker creates a network using default driver named dotnet2017_default. It also creates 3 containers named sql2017, dotnet2017_corewebapi_1, dotnet2017_coremvc_1. We can also see that the TechTalksDB database is initialized correctly.

Query Data from SQL container using the MVC web front end

We now have all the pieces of the puzzle arranged together. Lets browse the MVC frontend and query the web API which will return us the results from the SQL server table. Hit the url http:localhost using your favorite browser.  In the service url textbox put the web API url. In my case this is http://docker.for.mack.localhost:8080/api/keyvalue. Search for key MS. The result is the corresponding value for MS returned from the database from container instance. You can test the other keys by cloning the repository and running the docker compose up command.

Conclusion

We saw how easy it is to integrate the database container with the ASP.NET Core. With minor modification to the existing code, we have been able to switch from in memory data source to containerized database. Docker makes it very easy for us to quickly test features. By using docker-compose we are able to describe in a declarative manner the various services which make up our application. One single command is used to build all the docker images for an application and we can run all the containers using a single command.

This is a great way to quickly build and test the applications quickly. You can imagine the huge productivity gains we can get from this kind of development process. We can use the same process to test the application on local machine as well as in dedicated test or UAT environment. Once again I would stress that you should explore Docekr for your needs. If you are not using Docker probably you are missing something.

The complete source code used for this demo is available on Github. In the next post we will see how to persist data outside of container to a persistent storage so that the state of the application is preserver even between the container restarts. Hope you enjoyed this post. Until next time code with passion and strive for excellence.

Share: