Creating a SQL Server test environment with Docker and data persistence.
Right now, I’m diving into .Net studies, and that led me to start a project where I needed to set up a testing environment with SQL Server. Because it’s easy to install and doesn’t hog too much computer power, I decided to use Docker for my study environment. In this article, I’ll walk you through the step-by-step process of creating and testing a Docker container with the SQL Server image.
1- Download Docker from the link below and install as follows:
1.1 – Download: https://www.docker.com/get-started/
1.2 – Install:
1.3 – After installation, this will be the Docker Desktop execution screen:
2- Now, download SQL Server Management Studio or a similar tool like DBeaver to interact with the database. In this case, I will use SQL Server Management Studio:
2.1 – Link to download SQL Server Management Studio:
https://learn.microsoft.com/pt-br/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16
2.2 – Install SQL Server Management. The steps are ‘next,’ ‘next,’ and ‘next.’
3- Access the Docker Hub website and download a stable SQL Server image for Docker or use the example link below.
3.1 – Open your terminal and run the command below:
docker pull mcr.microsoft.com/mssql/server:2019-latest
After downloading, you need to create your container with the necessary parameters for proper use. To start a Docker container with SQL Server and ensure that the data is persistent on your desktop, you can use a command similar to the following. This command creates and starts a Docker SQL Server container and mounts a volume to persist the data on your local system. This way, after restarting the computer, the database data and configurations will remain on your computer:
Usage instructions:
Make sure to replace YourPasswordHere with your desired password and ContainerName with a meaningful name for your container. The volume mounted at D:\Docker is used to persist SQL Server data on your local system, ensuring that data is not lost when the container is stopped or removed.
Remember that when using a volume, the data will be stored locally, and you can restart or create a new container with the same volume to maintain data persistence. Ensure that the provided local path (/local/path/for/data) exists and is suitable for storing SQL Server data.
If you are using an operating system other than Windows, you may need to adjust the volume path according to the host operating system’s path convention.
4- Open your terminal and run the command below to create the Docker container (Command to start the Docker container with data persistence):
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=suaSenhaAqui' -p 1433:1433 --name NomeDoContainer -v D:\Docker -d mcr.microsoft.com/mssql/server:2019-latest
5- After running the command from step 4, check that a new instance appears in Docker Desktop under “Images,” as shown below:
5.1 – Click on Play to run the container:
6- To check if the container is running, go to the “Containers” section on the left side of the page:
7- Running Container:
And now, how do I validate if it was successful?”
8- Open your database management tool, in my case, SQL Server Management Studio:
8.1 – Click on “Connect” to create a new database connection:
8.2 – Click on “Database Engine…”
8.3 – The following screen will appear:
8.4 – Enter the information below to connect to the database. Make sure your Docker container is still running.
Server name: “.” – Enter just a period, indicating that you want to connect to a local database.
Login: “sa” Default user
Password: Enter the password you used when creating the Docker container:
8.5 – After the connection is established, you will be directed to the following screen:
9- To execute SQL queries, right-click on your database and then select “New Query”:
9.1 – To execute SQL queries, right-click on your database and then select “New Query”:
