serving the solutions day and night


Thursday, January 30, 2020

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. 

            docker pull

  • 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:
  1. ACCEPT_EULA -> Accept the end-user license agreement.
  2. SA_PASSWORD -> sa username password
  3. MSSQL_PID -> <your_product_id | edition_name>: Specify edition, (Default : Developer)
  4. --name -> a custom name to the container to simplify the connection of the application with the database.
  5. -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

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=SaPwd123$' -e 'MSSQL_PID=Express' -p 1433:1433 --name sqlserver -d

  • 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

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,1433 or localhost,1433

  "ConnectionStrings": {

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


No comments: