I just finished teaching an 8 hour pre-con at SQL Saturday Indianapolis on Essential Linux for the DBA. The focus of the class was on what the Microsoft DBA would need to know as more SQL Server environments begin to run on Linux.
The obstacles and demands of them will be a bit different than the Oracle crowd, as they may not have the Linux Admin or Unix expertise onsite for support and have to fend for themselves more than I did starting out on Linux.
Some folks asked me why I chose to use Docker with the SQL Server image to teach the class and I wanted to demonstrate why this, over VMWare or other options were my final choice.
Docker is available for Mac and Windows with a simple installation by the defaults.
- Download the correct installation for your OS type.
- Run the installer and keep all the defaults, choosing Linux containers, not Windows containers
- Reboot Windows workstations- Done.
Incredibly Simple MSSQL Container Install
Microsoft has done a great job of creating a very small, (maybe a bit too small, but we’ll get into that later…) image that can be used to create a running Linux container with SQL Server. This grants to student a great opportunity to simulate much of what it would be like to work on a real Linux server.
There are a few tips I’ll give you when working with Docker and containers.
- Don’t work from Power Shell or other advanced interfaces- Command Prompt is your friend. You’re in the Linux world now and the command line is your first choice and best choice to work with it.
- Remember password naming convention requirements. If you use too simple a password, your container will shut down immediately after starting. I automatically choose combinations of letters, numbers and special characters, making sure never to lead with a special character in mine. If I were to use the password “t3st1ngn0w”, the container would fail, but “t3st1ngn0w!” would be successful.
- Always choose 1433 for your port designation. If you look at the logs, the container will redirect to 1433 no matter what, so don’t think you can get fancy. It would take a lot of steps to change this and I haven’t see it successful yet. This is still in its infancy at this time.
- Because it does want to use 1433, make sure you don’t have a community edition or express edition on your PC already running on those ports, (or anything else…) Even though this is on a container, it’s still recognized by SSMS as a “localhost” so if you try to connect it, it will continue to fail.
- If you experience an error on the create command, you must run a remove command as a partial creation will have been done, entering the container metadata, (docker rm <container name>)
After the Docker installation is done, the command to create the container would look similar to the following, replacing anything inside the <> with your choice:
docker run -e “ACCEPT_EULA=Y” -e “MSSQL_SA_PASSWORD=<password>” -p 1403:1423 –name <container name> -d microsoft/mssql-server-linux:2017-latest
If I run this on my pc now, I’d see the following:
Running the “docker ps -a” command is something you will come to rely on to capture the status of the containers at any given time. It tells me the what, when, who and where of what I just created.
Also note the strange port numbers I set on my container. We’ll get back to that in a later post, but do note them, if you haven’t already… 🙂
From the command prompt again, I can then issue the next command:
docker exec -it sql2 "bash"
This will log me into the container as ROOT and into a BASH secure shell or to dissect the command:
docker – execute –interactive – container name – “application”
I’ve gone previously into my concerns with everything installed and logging into a server as root, so I’m not going to get on my soapbox, but for the case of teaching a Linux class, there are some benefits, because you can teach people what not to do and if they do destroy everything, they learn in a safe space and can recreate it quickly- no harm done.
Why Use For Teaching a Linux Class?
Let’s say, we have a student, Dan and Dan is learning how to remove some files from his test Linux server and he accidently created them in the /opt directory. He’s logged in a root, has God rights to create them, is new to Linux and in a rush, forgets the file name on his new fangled command of rm -rf he just learned:
rm -rf /opt
Oops, there goes the entire installation of SQL Server- all the bin files, tools, all of it, gone in a matter of a second or so. Proof is in the pudding and that can be seen when we try to log into the SQL Server:
What’s to be done? Containers allow you to make changes and build images from those changes. They are light and simple to deploy, including this class. Dan simply exits, runs the following to shut down and remove the container:
docker stop sql2 docker rm sql2
You can often just remove the container straight out, but if it complains, shut it down first and then remove- it’s just one added command. 🙂
Now recreate and log back in:
Notice the status says its been created and only been up for about a minute. If you wonder if it’s all there, here’s your proof:
And we’re back!
The Linux Docker container with SQL Server 2017 is one of the simplest, most powerful ways I know to give a working Linux environment without worry of damage, slow WiFi impact and simple recovery if something goes wrong.
Thank you for awesome Docker and thanks for the great image, Microsoft! Now I need to just talk to them about a few basic, (and tiny) packages I think need to be included to ease the transition. 🙂
Also published on Medium.