Add SQL Server 2017 Docker Container to ASP.NET Core MVC/Web API Project

SQL Server 2017 is available for both Windows and Linux Containers. 

• Pull SQL Server Docker Container image, Official images for Microsoft SQL Server on Linux for Docker Engine.

            docker pull mcr.microsoft.com/mssql/server

• Verify SQL Server 2017 image was successfully pulled locally using "docker images" cmd

Without Volume mount

• To store the database and log files insider the container, whenever the container is deleted all the data stored inside the SQL Server database(s) will be lost.
• To run SQL Server 2017 container locally without Volume mount, need to specify some parameters to configure the SQL Server instance correctly. use the -e option to specify an argument:

ACCEPT_EULA -> Accept the end-user license agreement. SA_PASSWORD -> sa username password MSSQL_PID -> <your_product_id | edition_name>: Specify edition, (Default : Developer) --name -> a custom name to the container to simplify the connection of the application with the database. -p 1433:1433 -> a TCP port on the host environment (1st value) with a TCP port in the container (2nd value). SQL Server is listening on TCP 1433 in the container and this is exposed to the port, 1433, on the host.

docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=SaPwd123$" -p 1433:1433 --name sss2017withoutmount -d mcr.microsoft.com/mssql/server:2017-latest

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=SaPwd123$' -e 'MSSQL_PID=Express' -p 1433:1433 --name sqlserver -d mcr.microsoft.com/mssql/server

• Verify SQL Server 2017 image container was successfully created and running using "docker ps -a" cmd

• Verify the database and log files exist inside the container. Execute the “docker exec -it ss2017withoutmount 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.

With Volume mount

• To store the database and log files outside the container
• Create SQL Server 2017 container with volume mount. “C:\Users\MK\DockerVolumes\sqlserver2017” is the path from PC, and /var/opt/mssql/data the folder inside the container. That will create the database and log files outside the container inside (C:\Users\MK\DockerVolumes\sqlserver2017).

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

Connecting to SQL Server container (inside or outside) using SQL Server Management Studio

Connecting to the SQL Server database (with or without volume) container in Web API

• appsetttings.json - not working 10.0.75.1,1433 or localhost,1433

  "ConnectionStrings": {

    "MyAppDbContext": "Server=sqlserver2017withoutmount;Database=demodb;User=sa;Password=Sample123$;MultipleActiveResultSets=true"

  },