Mini Data Lab – SQL Server 2019 on Docker Part 3

Back for more I see!! Well glad you’ve come back to round three where we are going to make a few more minor adjustments to our container and run our first shell script.

Make sure your container is running by executing the following command in your command prompt window: docker ps
If you don’t see the name of your container you can simple run this command docker start NAMEOFYOURCONTAINER in the command prompt.

Next we will need to run an elevated bash console on the container using the following command (the name of my container is sqldocker1). The -u flag is for the user and 0 is basically root (super admin); probably not a best practice. The -it flag opens it as an interactive session.

docker exec -u 0 -it sqldocker1 "bash"
or
docker exec -u 0 -it sqldocker1 /bin/bash

You’ll see in the screen shot below that my container is ready to take on some commands. The first one will be to run apt update and hit enter. This may take a few minutes to complete but is important to get the container ready to download other packages.

When it is complete we will execute the command apt install nano which will install a small text editor needed to create our first shell script.

Run nano in the SQLShare mount that we created in the previous post. Do this by navigating to the folder then type nano in the bash console and hit enter.

Optionally you can type nano and the name of the file you want to create. For example nano Test.sh will create the empty file with that name.

Enter the following script into the empty screen editor.

apt update
apt upgrade
apt-get install sudo -y
apt-get install iputils-ping -y
/opt/mssql/bin/mssql-conf set sqlagent.enabled true
/opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
/opt/mssql/bin/mssql-conf set memory.memorylimitmb 1024

Hit the control + o button to save the file (o the letter not 0 the number). Give it whatever name you like at this point, but make sure you remember it (I saved mine as Test.sh). Hit control + x to exit back to the bash terminal. Now all you have to do is run the script using it’s name proceeded by ./

If you get a permissions issue like mine, you can use the chmod 777 FILENAME to give it root privileges to run.

Now simply type exit and hit enter. This should put you back into the command prompt where you can type docker restart sqldocker1. When your container restarts you should see SQL Server Agent activated.

For those that are more curious in changing other Instance level properties here is a list of configurable properties for SQL Server on Linux via Bash.
https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-configure-mssql-conf?view=sql-server-ver15

Now we have a great foundation to create another container, so let us go and do that now. The code is similar to the previously created container with the exception of the file path for the data, log, and back files and the port number. The SQLShare path will be the same so that we can run our initialization script from there.

docker run -e "ACCEPT_EULA=Y" ^
-e "SA_PASSWORD=sqldocker@AymanPC123" ^
-e "MSSQL_DATA_DIR=/SQLMount/mssql/data" ^
-e "MSSQL_LOG_DIR=/SQLMount/mssql/logs" ^
-e "MSSQL_BACKUP_DIR=/SQLMount/mssql/backups" ^
-p 2433:1433 ^
-v S:\DockerMount\sqldocker2\mssql\system:/var/opt/mssql/data ^
-v S:\DockerMount\sqldocker2\mssql\data:/SQLMount/mssql/data ^
-v S:\DockerMount\sqldocker2\mssql\logs:/SQLMount/mssql/logs ^
-v S:\DockerMount\sqldocker2\mssql\backups:/SQLMount/mssql/backups ^
-v S:\DockerMount\sqldockershared:/SQLShare ^
--name sqldocker2 -h sqldocker2 -d mcr.microsoft.com/mssql/server:2019-latest 

We can use the same method as before to log into the container to run the bash script created earlier. Once that is done, you can restart the container and log into it using SSMS or even SQLCMD.

And there you have it, two containers with SQL Server running side by side with minimal effort and a good base configuration.