SQL Server 2017 Docker Container And Web API In .Net Core

https://github.com/vishipayyallore/speaker_series_csharp_dotnetcode/tree/master/SQLServer2017DockerWebApi/College.Services

Once we are successfully logged in, right mouse click on “Databases” -> “New Database …”. It will display a dialog, wherein you need to specify the “webapidemodb”. Also, observe that the database and log files are getting created inside “/var/opt/mssql/data” inside the container. Please refer to the image below.

Open a new query window (Right-click on “webapidemodb” -> New Query). 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.

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.

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 files is “/var/opt/mssql/data”, let's navigate and verify that our webapidemodb.mdf and webapidemodb_log.ldf exists.

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 Docker container in our Web API solution. Please open College.Services.sln solution inside Visual Studio 2017. 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.

Once that is done, please execute the Web API project and we should be able to see 3 professors' information inside the browser (in our case it is Chrome).

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.

Execute the 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

Please refer to the image below.

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 files is “/var/opt/mssql/data”, let's navigate and verify that our webapidemodb.mdf and webapidemodb_log.ldf are lost.

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. “C:\DockerVolumes\formssql” 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 (C:\DockerVolumes\formssql). Please refer to the image below.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Sample123$" -p 1433:1433 --name sql1 -v C:\LordKrishna\DockerVolumes\formssql:/var/opt/mssql/data -d mcr.microsoft.com/mssql/server:2017-latest

Let 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 as we did in our first exercise.

Summary

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