SQL Server 2017 on Windows Containers – Part 2 (Installation)

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:

image

 

Step 2  – Download Docker Engine for Windows 10 from https://docs.docker.com/docker-for-windows/install/

clip_image001[7]

 

Step 3 – Change the mode to Windows containers – right click on the Docker icon in the windows tray:

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

 

Step 5 – Once the image is downloaded,  run this command to check if the image is downloaded :

Docker images

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

image

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

image

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>

image

 

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

image

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>’

image

 

Step 11 – Connect to SQL server instance this IP and the password used to spin up the container:

image

 

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.

image

 

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

image

docker volume create data2

image

Docker volume ls

image

docker run -d -p 1433:1500 -e sa_password=test123@ -e ACCEPT_EULA=Y -v c:/data:c:/data2 microsoft/mssql-server-windows

image

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

image

2) Run the command to check if the data2 directory has been created:

image

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:

image

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:

image

 

 

HTH!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s