Here we go! We’ve just reached the last post of a series of four! In the previous posts, among a bunch of cool stuff, you were presented to Docker container with all the benefits it brings, learned how to set up the best software development environment ever and started playing with eShopOnWeb, a Microsoft sample web application using ASP.Net Core. To follow the instructions below, I recommend reading all previous posts. There are precious instructions and pre-requisites to follow some of the next steps.
Now let’s take eShopOnWeb to another level. We will build a SQL Server 2019 image and run a container so that eShopOnWeb can persist data.
First, to accomplish this task, we will choose an already built image of SQL Server 2019. We always recommend you use official docker images from all vendors or communities that support their products. Many of them publish and maintain their images updated. Go to dockerhub.com, fillup the search field with SQL Server and hit Enter.
Click on the Microsoft SQL Server link to take you to choose one of the Featured Tags. Scroll down a little, so you will see the tag 2019-latest, which is the one we are going to use. Spend a little time to read and explore the page to get used to all the tags and options in the Description tab and the Reviews tab and Resources tab with rich Support and Documentation content.
Now that you were formally presented to Docker Hub let’s run a docker container based on the Microsoft official Docker image of SQL Server 2019, which, by the way, runs in a Ubuntu 18.04.5 LTS distribution.
Go to Windows Terminal, open a Ubuntu shell prompt and run this command
docker volume create eShopOnWeb-data to create a docker volume to store SQL Server data.
In Docker’s world, there are two mechanisms to persist data: using bind mounts and the other is the use of volumes. In the docker official documentation, you will see that there many advantages to using volumes over bind mount as follows:
- Volumes are easier to back up or migrate than bind mounts.
- You can manage volumes using Docker CLI commands or the Docker API.
- Volumes work on both Linux and Windows containers.
- Volumes can be more safely shared among multiple containers.
- Volume drivers let you store volumes on remote hosts or cloud providers, encrypt the contents of volumes, or add other functionality.
- New volumes can have their content pre-populated by a container.
If you want to explore more about it, click here and find out.
A docker container for SQL Server 2019
For the next step, make sure port 1433 is not being used on your machine. For doing so, you can stop an eventual SQL Server service running locally.
Next step, we will run the container using this volume eShopOnWeb-data. Therefore copy this command below and hit Enter.
docker run `
-e "ACCEPT_EULA=Y" `
-e "SA_PASSWORD=MyP4ssw0rd" `
-p 1433:1433 `
--name eShopOnWeb_MSSQL2019 `
-u root `
--mount source=eShopWeb-data,target=/var/opt/mssql/data `
docker run command parameters:
-e "ACCEPT_EULA=Y": sets the required SQL Server image environment variable ACCEPT_EULA to Yes;
-e "SA_PASSWORD=MyP4ssw0rd": sets the required SQL Server image environment variable to set up a password for the user
-p 1433:1433: sets the port to be exposed to the host machine. The colon right-hand side is the container port that maps to the host port in the colon left-hand side;
-d : allows the container to run in detached mode;
--name eShopOnWeb_MSSQL2019 : gives a friendly name to the container;
-u root: grant the proper rights as
root user for mapping the database local container database directory to the newly created volume eShopOnWeb-data in the host;
--mount source=eShopWeb-data,target=/var/opt/mssql/data: mounts the database directory in the container to the volume in the host;
mcr.microsoft.com/mssql/server:2019-latest : the image and its tag docker will use to create the container.
Open eShopOnWeb in VSCode by typing
code . in the eShopOnWeb directory.
Click on the Reopen in Container button and wait a couple of seconds until it reopens into Dev Container.
Go to extensions, type SQL Server in the Search field, select SQL Server (mssql) 1.9.0 and click on Install in Dev Container: eShopOnWeb green button shown below.
It takes a few seconds to install it. When it finishes the installation, click on the SQL Server extension, which is the button right below the Docker button, to set up a connection.
Let’s connect to SQL Server. First of all, find out your machine IP address, which is the host of the SQL Server 2019 container, by typing
ipconfig command on Powershell or Windows Terminal.
My machine IP address is 192.168.2.217.
Click on the “Add Connection” button, type the IP address in the command palette and hit Enter.
Leave the default database connection blank and press Enter.
Select the SQL Login option for connection.
sa for User Name (SQL Login) and hit Enter.
Type the password defined on
docker run command, which is MyP4ssw0rd and hit Enter.
Select Yes to save the password.
And finally, give the connection profile the name eShopOnWeb-mssql2019 and hit Enter.
Now you got it! You’ve just connected to SQL Server 2019 database in another Linux docker container from within your Linux Dev Container using the host machine via 1433 port.
Let’s query the database to make sure it is working. Right-click on the server node, select the New Query menu option.
A new window will show up, type.
Highlight the command, right-click on it and select the Execute Query menu option.
And you will see the result on the right side.
Cool Software Development environment explained!
What we have here is your Dev Container talking to SQL Server 2019 container through host under port 1433. Check this typing
docker ps -a See the Linux containers up and running, and more importantly, in your Windows 10, cool, right?!
Now, let’s change the eShopOnWeb application to persist data in SQL Server 2019.
Search for the ConfigureDevelopmentServices method.
Startup.csConfigureDevelopmentServices method as follows:
Comment on the line below
"// use in-memory database"
Uncomment the line below
"// use a real database"
- Ensure your connection strings in
appsettings.json‘s on Web and PublicApi folders are pointing to the newly created SQL Server 2019 in Linux container. Remember to replace the IP address.
"IdentityConnection" keys in the
"CatalogConnection": "Server=192.168.2.217;Integrated Security=false;User ID=sa;Password=MyP4ssw0rd;Initial Catalog=Microsoft.eShopOnWeb.CatalogDb;",
"IdentityConnection": "Server=192.168.2.217;Integrated Security=false;User ID=sa;Password=MyP4ssw0rd;Initial Catalog=Microsoft.eShopOnWeb.Identity;"
Don’t forget to save the changes!
- Open an integrated terminal in the Web folder.
- Ensure the tool EF was already installed by typing this command
dotnet tool install --global dotnet-ef
- Execute the following commands:
dotnet tool restore
dotnet ef database update -c catalogcontext -p ../Infrastructure/Infrastructure.csproj -s Web.csproj
dotnet ef database update -c appidentitydbcontext -p ../Infrastructure/Infrastructure.csproj -s Web.csproj
These commands will create two separate databases, one for the store’s catalogue data and shopping cart information, and one for the app’s user credentials and identity data.
- Now run the application. Go to the menu Run and select Run Without Debugging.
The first time you run the application, it will seed both databases with data such that you should see products in the store, and you should be able to log in using the firstname.lastname@example.org account.
Now it’s running on SQL Server 2019. Click in Login and connect with email@example.com and password Pass@word1.
Add some products to the basket.
- Let’s check the SQL Server 2019 database. Go to SQL Server extension and open Database node, and you will see the
Identitydatabases created in the previous steps.
CatalogDb node and then
Tables node, right-click on
dbo.Baskets and choose the Select Top 1000 menu option.
See that there is one record in the
dbo.BasketItems and hit Select Top 1000 again to see the items you chose.
We’ve just reached our goal with this fourth and last post of this series. Now we have a versatile, tidy, rich and hybrid software development environment using cutting-edge technology.
To access the source code of this project on our GitHub, click here.
I am preparing exciting content for the following posts. We will set up Reverse Proxy using Nginx, automated tests with Jenkins CI/CD platform and more. Subscribe, make suggestions for the next posts!
Do you want to know more about us and our mission? Click here, and don’t forget to subscribe!
We also have pro bono projects just in case you are interested in learning more about them.
Be aware of why we started this project by clicking here.
Learn more about other posts here.
If you are a good reader like myself, I recommend the following readings:
- Docker Quick Start Guide: Learn Docker like a boss, and finally own your applications
- Docker for Developers: Develop and run your application with Docker containers using DevOps tools for continuous delivery
See you in the next post!