Using Microsoft SQL Server on macOS with Docker

Since the release of .NET Core 1.0 in 2014, .NET developers have no longer been confined to development on Windows. Yet I am still surprised by how many people don't realise that .NET Core is cross-platform!

I develop all my .NET code, from web apps to Azure Functions, using Microsoft SQL Server on macOS.

We will use a Docker container to host SQL server, which means this technique could also be used on Windows and Linux and not just macOS.

If you would like to see a full integration of Twilio APIs in a .NET Core application, then check out this free 5-part video series. It's separate from this blog post tutorial but will give you a full rundown of many APIs at once.

Microsoft SQL Server on macOS: Getting Started with Docker

To get started we will need to download Docker for Mac and follow the installation instructions.

Once installed, the first thing we will need to do is increase Docker's default memory allocation as SQL Server will require a bit more grunt.

Click on the Docker daemon icon in the top menu and select "Preferences".

Next, choose the "Resources" tab, note - on some versions of Docker you may find it under the "Advanced" tab, and adjust the memory slider to be at least 4GB. Then click "Apply and Restart"

How to Install SQL Server on Mac

We can install the SQL Server on the Mac and run the Server image all at once by running the following command in the terminal.

There are several parameters in the above command, so let's take a closer look at each one.

• -d will launch the container in "detached" mode and is optional. This means that containers will run in the background and you can close the terminal window.
• --name sql_server will assign a name to the container and is optional, but recommended for easier management!
• -e will allow you to set environment variables:
  
  • 'ACCEPT_EULA=Y'  SQL Server requires the user to accept the "End User Licence Agreement" or EULA. The Y here indicates acceptance.
  • 'SA_PASSWORD=someThingComplicated1234' is a required parameter for SQL Server. This is the System Administrator password.  See the note below on password strength.
• -p 1433:1433 will map the local port 1433 to port 1433 on the container. Port 1433 is the default TCP port that SQL Server will listen on.
• mcr.microsoft.com/mssql/server:2019-latest is the image we wish to run. I have used the latest version of 2019, however, if you need a different version you can check out the Microsoft SQL Server page on Docker Hub.

For more information on docker runcommands, check out the documentation.

Note on Password Strength

If you find your image starts but then immediately stops or you get an error such as setup failed with error code 1`, then it may be you haven't created a strong enough password. SQL Server really means it when it requests a strong password. Ensure good length with a mixture of upper and lower case, and a mix of alphanumeric characters. For more information on password requirements take a look at the Microsoft documentation.

Using the SQL CLI tool

The SQL CLI tool is a convenient command-line tool for querying SQL databases and is cross-platform. It's also really useful for checking to see if the above worked!

To install it, run the following command in the terminal:

Then, to connect to you database:

Where someThingComplicated1234 is your password.

You should receive a response similar to below:

Azure Data Studio

Another hurdle with using SQL Server on macOS is the lack of SQL Server Management Studio (SSMS), a software tool that is used for configuring, managing, and administering all components within Microsoft SQL Server. It includes scripting tools for creating and running queries and graphical tools for use with objects and features within SQL Server.

However, Azure Data Studio has got you covered.

Click the "New connection" link and enter localhost for the "Server" input, sa for the "User name" input, and then your password for the "Password" input.

The other inputs are optional.

Once you've logged in you should have a view similar to the one below, with all the databases on the server listed.

The connection string

To use the database within code, the connection string will look like this, with the database name substituted for DB_NAME.

Some useful Docker commands

Some useful Docker commands can be found below

• View downloaded images: docker images
• View all containers: docker container ls -a
• View all running containers: docker container ls -a
• Start a container: docker start CONTAINER_NAME
• Stop a container: docker stop CONTAINER_NAME
• Remove a container: docker rm CONTAINER_ID

Cross platform-ness

Although this tutorial focuses on using Microsoft SQL Server on macOS, there is no reason why you couldn't run your SQL Server instance within Docker on Windows or Linux.

If you have any tips and tricks for cross-platform development with .NET Core, then I would love to hear from you!

• Email: lporter@twilio.com
• Twitter: @LaylaCodesIt
• GitHub: layla-p
• Twitch: LaylaCodesIt