SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04

In this article, we will be looking at how to use SQL Server 2017 Docker image for Web API development on Ubuntu 18.04.

Introduction

 
This article will discuss the following,
  1. How to locate SQL Server 2017 image and download it locally on Ubuntu 18.04
  2. Execute the SQL Server 2017 docker container locally on Ubuntu 18.04
  3. Use the SQL Server 2017 hosted inside the Docker container for local .NET Core Web API development in Ubuntu 18.04
Prerequisites
  • Ubuntu 18.04 LTS
  • Visual Studio Code
  • Azure Data Studio

    SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04

  • Docker on Ubuntu 18.04
  • Client: Docker Engine - Community
    • Version: 03.1
    • API version: 40
  • Server: Docker Engine - Community
  • Engine
    • Version: 03.1
    • API version: 40 (minimum version 1.12)
It would be good to have prior knowledge of,
  • Docker
  • EF Core 2.1
  • Web API using .Net Core 2.1
  • Azure Data Studio
 
Assumption
 
This article assumes that you have a basic knowledge of Docker, Azure Data Studio, Visual Studio Code, EF Core 2.1, and Web API using .NET Core 2.1.
 

Motivation

 
Cross-platform
 
Microsoft SQL Server 2017 is now available on multiple platforms: Windows, Linux, and Docker.
 
Fast installation
 
Getting SQL Server’s docker image is as simple as running a docker image pull.
 
Cost-effective
 
Containers are much cheaper.
 
Different versions/Multiple instances
 
We can start as many instances on an On-premise Server/Laptop as we want. Each container will be independent (fresh and clean) and tear them back down when we are done.
 
Speed
 
The speed and efficiency benefits of Docker and containerizing apps are available to SQL Server 2017 Docker container too.
 
Persistence
 
We can use volume mounts to store .mdf and .ldf files outside the container. That way those .mdf and .ldf files will be stored on the persistence hard disk. Even when the container is removed that data will be safe as it is hosted outside the container.
 

Locating the SQL Server 2017 image and pulling it locally

 
We can visit here to find the image we would like to pull to our local laptop. In this article, we will be using “mcr.microsoft.com/mssql/server:2017-latest”.
 
First, let’s verify the list of available images on our laptop by executing the below-mentioned command using the terminal.
 
docker images
 
From the terminal, please execute the below-mentioned command to pull the SQL Server 2017 image locally. We need to wait for a couple of minutes for the image to be pulled locally.
 
docker pull mcr.microsoft.com/mssql/server:2017-latest
 
Let’s verify that the SQL Server 2017 image was successfully pulled locally. We can see that the image was pulled, and its size is 1.33 GB. Please refer to the image below.
 
docker images
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 

Executing SQL Server 2017 container locally without Volume mount

 
We will be executing the SQL Server 2017 container without volume mount. The effect of this will be, whenever the container is deleted all the data stored inside the SQL Server database(s) will be lost. Execute the below-mentioned command inside the command line. Ensure that port 1433 is available.
 
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Sample123$" -p 1433:1433 --name sqlserver2017withoutmount -d mcr.microsoft.com/mssql/server:2017-latest
  • -e 'ACCEPT_EULA
    Sets the ACCEPT_EULA variable to ‘Y’ to confirm the acceptance of the End-User Licensing Agreement.
  • -e 'SA_PASSWORD
    Password for login into SQL instance using the sa username.
  • -p 1433:1433
    Map a TCP port on the host environment (first value) with a TCP port in the container (second value). In this example, SQL Server is listening on TCP 1433 in the container and this is exposed to the port, 1433, on the host.
  • --name
    Specifies a name for the container rather than a randomly generated one.
Also, execute docker ps -a to verify that the container was successfully created and running.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 

Connecting to SQL Server 2017 running inside the container using Azure Data Studio

 
As we can see, SQL Server is being executed on port 1433. Please specify the Server name “localhost,1433”, Login “sa”, and Password “Sample123$” to login into the SQL Server running inside the container.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Once we are successfully logged into SQL Server running inside Docker Container from Azure Data Studio, we should be able to see the Container Id as Computer Name. Please refer to the image below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Once we are successfully logged in, click on File -> Open File …. It will display the Open Dialog, please select Create_Database.sql from Scripts Folder inside the project. Please refer to the images below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
It will display the Create_Database.sql file. Please ensure that you are connected to SQL Server instance running inside the container. If it does not show as connected, please click on the connect icon and specify the credentials to log in. Please click on the “Run” icon, it will create a database. Please refer to the image below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Click File -> Open File…, to open mentioned 3 files from the SqlScripts folder. Execute only 1Create_Professors.sql, 2Create_Students.sql and 3Insert_Professors.sql file. It should create two tables and populate the Professors table. Please ensure that you are selecting the “webapidemodb” database before executing the .sql file.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Once we have successfully executed the 3 script files, we can perform the select * from both Professors and Students table. Please review the image below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Let’s verify the database and log files exist inside the container. Please execute the “docker exec -it sqlserver2017withoutmount bash” command. It should take us inside the container. We can execute ls (listing) for directory listing. As we know the path of the files is “/var/opt/mssql/data”, let's navigate and verify that our webapidemodb.mdf and webapidemodb_log.ldf files exist.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 

Connecting to the database created in SQL Server 2017 running inside the container in Web API

 
Let’s use the database which we have created inside the SQL instance running in Docker container in our Web API solution. Please open SQLServer2017DockerWebApi inside Visual Studio Code. Please open appsettings.json and modify the “CollegeDBConnectionString” inside “ConnectionStrings”.
 
"CollegeDBConnectionString": "Server=tcp:localhost,1433;Database=webapidemodb;User Id=sa;Password=Sample123$;". Please refer to the image below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Please open the Terminal in Visual Studio Code and Navigate to the folder where College.Services.csproj exists and execute the dotnet run command. It will host the application inside the Kestrel Server. Please refer to the image below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Please open up the browser and navigate to the URL in our case it is (http://localhost:5000/api/professors) and we should be able to see 3 professors' information inside the browser.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 

Verifying, deleting and re-creating SQL Server 2017 container to ensure that database (webapidemodb) we created is lost

 
Verify the SQL Server container exists (docker ps -a). Then stop and delete the container using docker stop ContainerId and docker rm ContainerId. Also, verify that the SQL Server container is not available (docker ps -a). Please refer to the image below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Execute the below-mentioned command to create the SQL Server 2017 container without volume mount and verify that our webapidemodb files are lost.
 
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Sample123$" -p 1433:1433 --name sqlserver2017withoutmount -d mcr.microsoft.com/mssql/server:2017-latest
 
Let’s verify the database and log files inside the container are lost. Please execute the “docker exec -it sqlserver2017withoutmount bash” command. It should take us inside the container. We can execute ls for directory listing. As we know the path of the files is “/var/opt/mssql/data”, let's navigate and verify that our webapidemodb.mdf and webapidemodb_log.ldf files are lost. Please refer to the image below.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 

Re-creating SQL Server 2017 container with volume mount to store the database and log files outside the container

 
With the above experiment we understand that if we create SQL Server container without volume mount and have the database and log files inside the container, they will be lost if we delete the container. Now let’s delete and re-create the container with volume mount. Verify the SQL Server container exists (docker ps -a). Then stop and delete the container using “docker stop ContainerId and docker rm ContainerId”. Also, verify that the SQL Server container is not available (docker ps -a).
 
Let’s execute the below-mentioned command to create SQL Server 2017 container with volume mount. “~/DockerVolumes” is the path from our local Laptop, and /var/opt/mssql/data the folder inside the container. That will create the database and log files outside the container inside (~/DockerVolumes). Please refer to the image below.
 
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Sample123$" -p 1433:1433 --name sqlserver2017withmount -v ~/DockerVolumes:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2017-latest
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 
Let’s create the “webapidemodb” database and execute all the 4 scripts from the SqlScripts folder. Execute the Web API and we should see the Professor(s) and Student(s) information in the browser. Assuming we have created the same database name, the tables are like we did in our first exercise.
 
SQL Server 2017 Docker Container And Web API In .NET Core In Ubuntu 18.04
 

Summary

 
Using the SQL Server 2017 container will help the developer to quickly set up, develop, and deliver.