SQL Server Torque: November 2016

I wrote this blog post after Microsoft announced that SQL Server is going to support Linux. I had many questions in mind on how MS is going to design SQL Server to run on Linux. The major question I had was, Is it complete re-write to work on Linux kernel? Now Microsoft has released some details on, how they have designed SQL Server to run on Linux. It was not a complete rewrite of SQL Server instead Microsoft has used a virtualization technique called Drawbridge to run SQL Server on Linux. Drawbridge contains Windows OS Library versions which consists of a user-mode kernel of Windows NT as a process called picoprocess. Picoprocess is a container service and it communicates with Linux OS.

For more details refer below links;

Microsoft loves Linux?

Drawbridge

Cheers!

I'm super excited to hear about the news, the first release of CTP (Community Technology Preview) of SQL Server on Linux at Microsoft connect() 2016 on 11/16/2016. This is definitely an unforgettable day for SQL Server enthusiasts around the world and I could not wait to install it on my Mac and give it a try. I thought of writing a blog post about the installation steps that I used to setup SQL Server on Mac.

However there are few NEW things that you want to know before jump into the installation. As SQL Server DBAs, I believe many are not familiar much with Linux OS as well as new development methods like Docker containers. The CTP released for Linux is available as a Docker container and there are some prerequisites that you need to set up before the SQL Server. It is same for Windows as well. The CTP version of SQL Server is named as SQL Server v.Next.

Install Docker on Mac

What is Docker? Docker site defines it as: "Docker containers wrap a piece of software in a complete filesystem that contains everything needed to run: code, runtime, system tools, system libraries – anything that can be installed on a server. This guarantees that the software will always run the same, regardless of its environment."

Installing Docker as easy as other program in Mac. You just need to download the Docker.dmg and follow the instructions. Below are the screenshots that I took during my installation.

Once the installation is complete, you can see the Docker icon in Taskbar of the Mac. You can also see the status as "Docker is running". I presume this as a thin virtualization layer which you can host what is call, containers. You can view additional settings of Docker by getting its Preferences (like Properties Window in Windows OS).

By default Docker for Mac has 2GB memory allocated. However, to run SQL Server you need 4GB of minimum memory and by clicking on Preferences you can configure the memory allocation as mentioned below;

After that click on "Apply & Restart" button to take the changes in effect. You can notice that the status will change to "Docker restarting..." after a while it shows as "Docker is running".

With that you have completed the infrastructure necessary to run the SQL Server docker container.

You also need to familiar with some Docker terminal commands for some administrative work. Later part of the blog has those infomation.

Download and Install SQL Server Docker Container for Mac

For this task, we need to open Mac Terminal Window and enter the following commands.

1. Pull the Docker image from Docker hub.

sudo is required since this needs admin privileges.

sudo docker pull microsoft/mssql-server-linux

2. Run the Docker image.

docker run –e 'ACCEPT_EULA=Y' –e 'SA_PASSWORD=P@$$w0rd!' -p 1433:1433 -d microsoft/mssql-server-linux

Note: You can give any password for the SA account.

Once completed;

Note: Sometimes copy and paste commands to Terminal Window does weird things to formatting. After copying, hyphens and quotation marks changes to different characters. If that happens you will get errors stating some characters are NOT VALID. DO NOT panic, just delete them and re-type the correct character in the Terminal Window itself. Then all should work without any issues.

Once you come to this level, you have installed SQL Server and its running as Docker image. Now you need to know few Docker commands to see the status of Docker. Again you need to run the those commands in Mac Terminal.

Docker commands

1. docker ps

List of containers which are running at the moment.

2. docker ps -a

To list all containers that are attached to the Docker. Below screenshot shows the output of this command. You can see, the STATUS column is "Exited", meaning the SQL Server image is not running at the moment. So you need to start the SQL Server docker container.

3. docker start <container id>

You can see that now, after starting the particular container id for SQL Server docker image, the SQL Server is running.

4. docker stop <container id>

If you want to stop the docker container. I believe this is like you stop SQL Server service in Windows machine.

What About SQL Server Client Tools?

The most favorite tool for SQL Server DBAs is the SQL Server Management Studio (SSMS). The next question is how to install SSMS in Linux. Unfortunately native build of SSMS for Linux is NOT YET available as of today. However you can use some other command line tools such as Powershell or SQLCMD. I've installed SQL-CLI in Mac so that I can connect to SQL Server on Mac.

Install SQL-CLI on Mac

First you need to install node.js on your Mac using the link mentioned under References. It's a dmg package and you can install it same way you did in Docker. Its super easy. After that you need to run below command in Terminal to install the sql-cli tool.

npm install -g sql-cli

See below screenshot for the output.

With that you have a client tool (command line) to connect to the SQL Server on Mac.

If you just type MSSQL in the terminal, you will get a login failure that is because you've not specified the credentials. Let's see how you can connect to the SQL Server instance with proper credentials.

Connecting to SQL Server on Mac

Type the below command in Terminal.

> mssql -u sa -p 'P@$$w0rd!'