Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Run SQL Server vNext (CTP1) as a Docker Container on a Mac


By:   |   Read Comments (2)   |   Related Tips: More > SQL Server on Linux

Problem

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.

Solution

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.

docker install

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).

docker preferences

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.

docker memory

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.

mac terminal window

mac terminal window

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.

Managing Docker

You can issue the below commands in a Terminal Window.

Command Description
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:

mac terminal window

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!'

mac terminal window

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.

mac terminal window

To list all the databases type .databases at the prompt.

mac terminal window

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.

mac terminal window

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.

sql server vnext via 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.

Next Steps


Last Update:






About the author
MSSQLTips author Susantha Bathige Susantha Bathige currently works at Pearson North America as a Production DBA. He has over ten years of experience in SQL Server as a Database Engineer, Developer, Analyst and Production DBA.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, December 14, 2016 - 1:12:28 PM - Susantha Bathige Back To Top

Hi Stephane

Good point. Thanks for mentioning it.  

 

 


Wednesday, December 14, 2016 - 11:09:32 AM - Stephane Frechette Back To Top

Hi Susantha

Just want to point out that for the following commands:

docker start start a particular Docker image
docker stop stop a particular Docker image

You can also use the 'container name' (instead of copying and pasting the container id) and that these commands 'docker start' and 'docker stop' is for a Container and not an image.

Great post

 

 


Learn more about SQL Server tools