Run SQL Server vNext (CTP1) as a Docker Container on a Mac
Microsoft released SQL Server vNext which allows you to install SQL Server on Linux. In this tip, we will look at how to install SQL Server vNext CTP1 as a Docker container on Mac OS.
SQL Server enthusiasts must have been super excited to hear about the first release of SQL Server vNext at Microsoft Connect() 2016 and in this tip we will walk through how to get this installed.
There are few infrastructure configurations that need to be completed before getting SQL Server to run on your Mac. It would be fair to assume that many SQL Server DBAs are not very familiar with Linux, because up until now all SQL Server releases were only supported on Windows (except very early releases of SQL Server that supported UNIX based OS called OS/2). The software development strategies have been changed with the release of the DevOps model and the Docker container concept is one such new development strategy.
The SQL Server vNext CTP1 is available as a Docker container for Linux and Windows as a result you can get SQL Server up and running on your machine in few minutes regardless of the OS.
How to Install Docker on a Mac
This is one of the pre-requisites to install SQL Server vNext on a Mac. Docker provides a virtualization layer to run a piece of software in a complete file system that contains everything needed to run the software program including code, system tools, configurations, system libraries, etc. So when you get the particular Docker container for SQL Server, it has everything needed to run SQL Server on top of the Docker service.
Installing Docker on a Mac is nothing different than installing any other program on a Mac. You just need to download Docker.dmg and follow the instructions.
Below are some screenshots captured during the installation.
Once the installation is complete, you can see the Docker icon in the Taskbar of the Mac. You can also notice the status as "Docker is running". You can view additional settings of Docker by using Preferences (this is like Properties for an object in Windows).
By default Docker for Mac has 2GB of memory allocated. However to run SQL Server vNext on Mac, it needs a minimum of 4GB of memory. By clicking on Preferences you can configure the minimum memory allocation as shown below.
Click the "Apply & Restart" button for the changes to take effect. You will notice the status changes to "Docker restarting..." and after a while it shows as "Docker is running". With that done, the infrastructure is ready to run SQL Server vNext as a Docker container.
You should also be familiar with some Docker commands to manage Docker.
Download and Install SQL Server vNext Docker Container on Mac
For this task, we need to open Mac Terminal and issue the following commands.
Pull the Docker image from the Docker Hub. Sudo is required since it needs admin privileges.
sudo docker pull microsoft/mssql-server-linux
Run the Docker image you just downloaded above.
docker run –e 'ACCEPT_EULA=Y' –e 'SA_PASSWORD=P@$$w0rd!' -p 1433:1433 -d microsoft/mssql-server-linux
Note: You can specify the password of your choice.
See the below images for the Docker installation.
Note: Sometimes copy and paste commands in a Terminal Window do weird things to the formatting. After copying, hyphens and quotation marks change 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(s) in the Terminal Window, then all should work with no issues.
Once this is done, you have installed SQL Server and it's running as a Docker image. Now you need to know a few Docker commands to see the status of Docker. Again you need to run these commands in a Mac Terminal.
You can issue the below commands in a Terminal Window.
|docker ps||List of containers which are running at the moment.|
|docker ps -a||To list all containers that are attached to the Docker. The 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.|
|docker start <container id>||start a particular Docker image|
|docker stop <container id>||stop a particular Docker image|
What About SQL Server Client Tools
The most favorite tool for SQL Server DBAs is SQL Server Management Studio (SSMS). The next question is how to install SSMS in Linux. Unfortunately a native build of SSMS for Linux is NOT YET available as of today. However you can use command line tools such as PowerShell or SQL-CLI.
Install SQL-CLI on Mac
First you need to install node.js on your Mac using the link mentioned under the Next Steps section below. It's a package and you can install it the same way you did Docker. After that you need to run the below command in a Terminal to install the sql-cli tool.
npm install -g sql-cli
See the below image for the output of the above command:
With that you have a client tool (command line) to connect to the SQL Server on a Mac.
If you just type MSSQL in the terminal, you will get a login failure that's 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 via SQL-CLI
Type the below command in a Terminal Window.
mssql -u sa -p 'P@$$w0rd!'
You will notice that you've successfully connected to the SQL Server vNext CTP1 which is running on your Mac as a Docker container. Now you can execute regular T-SQL commands.
Executing SQL Commands
To get version of SQL Server just type SELECT @@VERSION at the prompt.
To list all the databases type .databases at the prompt.
List of all the tables in msdb, first type USE msdb and enter. Then type .tables to see the list of all the tables in msdb.
Connecting to SQL Server vNext via SSMS
At this moment, there is no native SSMS tool for Mac. However you can connect to the SQL Server vNext from any remote SSMS installation elsewhere on your network as you do to connect to any other SQL Server instance. You just need to provide the local IP of your Mac and then connect as you do for any other SQL Server instance. The below image shows that I connected to SQL Server vNext from SSMS.
You can also notice there is no SQL Server Agent in SQL Server vNext CTP1. Maybe this will be released with a future version. Now you can work with SQL Server vNext as you would work with any other SQL Server instance.
- Take a closer look at SQL Server vNext
- Run the SQL Server Docker image on Linux, Mac, or Windows
- Download sql-cli
- Basic Docker commands
- Download node.js
About the author
View all my tips