I had presented this session on our Local SQL community in Delhi and it was fun to see how quickly, we could spin up multiple SQL instances within a second. I am going to publish my demo scripts here so that, it could be leveraged by the readers:
I did my demo on Windows 10 machine:
Step 1 – Enable container role on Windows:
Step 2 – Download Docker Engine for Windows 10 from https://docs.docker.com/docker-for-windows/install/
![clip_image001[7]](https://dbcouncil.files.wordpress.com/2017/09/clip_image0017.png "clip_image001[7]")
Step 3 – Change the mode to Windows containers – right click on the Docker icon in the windows tray:
![clip_image001[9]](https://dbcouncil.files.wordpress.com/2017/09/clip_image0019.png "clip_image001[9]")
Step 4 – Pull the Docker image from the Docker hub – https://hub.docker.com/r/microsoft/mssql-server-windows/
Command to be run – docker pull microsoft/mssql-server-windows-developer
Step 5 – Once the image is downloaded, run this command to check if the image is downloaded :
Docker images
![clip_image001[11]](https://dbcouncil.files.wordpress.com/2017/09/clip_image00111.png "clip_image001[11]")
Step 6 – To check how many components have been patched in the image, run this command:
Docker history microsoft/mssql-server-windows
Step 7 – Spin up the container using this command :
docker run -d -p 1433:1500 -e sa_password=test123@ -e ACCEPT_EULA=Y microsoft/mssql-server-windows
To read about the switches used in the above command check this link – https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker
Step 8 – Check the status of the container – run this command
Docker ps –a
If you see in the picture above, it shows the status and id of the container and status which is up for 31 hours. To confirm if the SQL inside the container is up and running or check the SQL errorlog, run this command:
Docker logs <Container_id>
Step 9 – Let’s connect to SQL server intance: This command is to connect from within the container
docker exec -it <DOCKER_CONTAINER_ID> sqlcmd -S. –Usa
Step 10 – Let’s connect from SQL Server management Studio:
1) Run this command to get the ip address of the container:
docker inspect -format='{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ <Docker_id>’
Step 11 – Connect to SQL server instance this IP and the password used to spin up the container:
Step 12 – Let’s connect inside the container and check the files and folders:
docker exec –it <Container ID> “powershell”
After the above command is executed, you will see the powershell gettting opened, just run the command tasklist to see the processes which are running inside the container. You can run any command on the powershell to operate inside the container.
Step 13 – The storage and files inside the containers will remain until you remove the container. However, if you want to expand your files or create new databases on the disk attached to the local host, you will need to create volume and then attach those volumes to the disks of the host OS.
Persisting the data
Docker volume ls
docker volume create data2
Docker volume ls
docker run -d -p 1433:1500 -e sa_password=test123@ -e ACCEPT_EULA=Y -v c:/data:c:/data2 microsoft/mssql-server-windows
Note – In step 4, we have mapped the local storage c:\data to the c:\data2 inside the container. Let’s check it out:
1) Let’s get inside the container into powershell
2) Run the command to check if the data2 directory has been created:
3) Create the database inside and data2 folder and then let’s check how it looks like in the host OS:
If you see, we can see data2 and there is already a folder created:
Let’s create the database and place the files in data2 folder.
let’s see how c:\data looks like in the host OS. The files of the database inside the container were persisted on the OS drive:
HTH!
