VS Code on Mac Meets SQL Server on Linux (in Docker)

Relationship status: It’s complicated.

After the release of the first public CTP of SQL Server v.Next for both Windows and Linux, I thought I would share how I set up a coding environment without Windows – Visual Studio Code, running on my Mac, connecting to SQL Server v.Next on Ubuntu, hosted within a Docker container.

I am anticipating one of the questions that will come up is, “Why a container?” A container is essentially just a more lightweight way to virtualize, and after fighting with several Ubuntu installations under Parallels, I decided to give it a try. (If you want a deeper answer from someone with more relevant experience than myself, see Docker containers vs. virtual machines: What’s the difference?)

Also, this is about using the free and open source version of Visual Studio Code, not the full-blown Visual Studio for Mac that was announced today at Connect(); // 2016.

I’m writing about this not because I expect a lot of people to intentionally use this exact stack for full-time development, or because I feel it is some kind of Utopian goal to develop without Windows (or without any virtualization at all). But rather because it was both interesting and satisfying to get up and running, and there were a few challenges along the way. So far I’ve talked to two people who got stuck somewhere in this process during the private preview, and hopefully this helps serve as a rudimentary step-by-step tutorial – if not for the whole thing for all of the people, maybe at least for some of the parts for some of the people. Including a future version of myself, should I need to do it again.

There’s a Hole in the Bucket…

The largest chunk of time I spent in this process was discovering the dependency chain to get this all running smoothly. It was like singing to Dear Liza:

1. In order for VS Code on the Mac to connect to SQL Server (on a Linux VM, in a Docker container, and possibly elsewhere), you need a current version of OpenSSL.
    
2. The easiest way I’ve found to install OpenSSL is using the package manager Homebrew, though if you search you can find more tedious and elaborate ways.
    
3. In order to install Homebrew, you’ll need Apple’s command line tools. For this we’ll install Xcode, though if you prefer, there are ways to avoid that.

We’ll work backwards through that list before moving on to Docker. First…

Install Xcode

As I mentioned, you don’t strictly need Xcode to get the command line tools installed, but it’s a pretty painless way to do it. Just search the App Store for Xcode, and click GET (if it says OPEN, you already have it).

You don’t have to ever actually open Xcode, but Homebrew does require that you accept the license terms. It is not entirely obvious when installation has finished; I kept coming back to the App Store and waiting for that button to cycle through DOWNLOADING, INSTALLING, and when it is installed, OPEN. Then you can just accept the license terms from the command line:

sudo xcodebuild -license

You’ll have to hit Enter, supply your password, type agree, and hit Enter again. Next up…

Install Homebrew

As I mentioned, there are probably other ways to install OpenSSL, but this one worked for me. You can get the code on GitHub, but Neil Gee has a good walk-through here: Installing Homebrew on macOS Sierra. The tutorial also elaborates on some of the details about Xcode. The TL;DR, though, is that once you have the command line tools installed, you can run the following from Terminal:

ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

==> Installation successful!

With Homebrew in place, you can use it to…

Install OpenSSL

This step is really easy:

brew install openssl

Again, you’ll have a whole bunch of output, but to gauge success, you’re looking for this message (or something very similar, depending on version):

==> Summary
🍺 /usr/local/Cellar/openssl/1.0.2j: 1,695 files, 12M

To avoid some other potential security-related errors, you need to create symbolic links for the crypto/ssl libraries:

ln -s /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib /usr/local/lib/
ln -s /usr/local/opt/openssl/lib/libssl.1.0.0.dylib /usr/local/lib/

Now you can…

Install Visual Studio Code

Download VSCode-darwin-stable.zip here. Open that archive, and copy Visual Studio Code.app to Applications. You can open the editor, put it in your dock, what have you. But we still have some work to do; you need the free mssql extension from the Visual Studio Code Marketplace. Go to Quick Open ( ⌘ + P) and type:

ext install mssql

Hit Enter, and you should see this option available in the extensions sidebar (in addition to an earlier, pre-pre-release version you should ignore):

Click Install, and restart VS Code. Next, you need to…

Install Docker

You can download Docker here. I used the stable release, but feel free to use the beta channel and live on the edge.

Run the DMG. It’s pretty simple; you just drag Docker.app into your Applications folder (there’s even a helpful visual guide). Then go to the Applications folder and double-click Docker.app. You’ll have a series of prompts, including one to enter your administrator password to let the application fiddle with your network settings. Then Docker will be up in your menubar – it’s a whale, but definitely not the fail whale.

To run SQL Server inside of a Docker container, you must have at least 3.25 GB allocated (Docker defaults to 2 GB, and if you leave this setting, SQL Server won’t run). So click on the whale, go to Preferences, and increase the memory (I recommend 4 GB at a minimum, but if you can afford more, go for it). Then hit Apply & Restart:

Running a SQL Server Container

Since the official release of SQL Server 2017, the process has changed a bit. I haven’t been through all of these steps yet, but Brent Ozar (@BrentO) told me that this is the process. You need to set up a Docker account and “buy” a free copy of SQL Server 2017 Developer Edition. You can do that here:

• Microsoft SQL Server 2017 Docker Image

Once you have an account and have acquired the Docker image, let’s head back to Terminal and play with getting a Docker container up and running. You need to bring down the SQL Server image from Docker Hub:

docker pull mcr.microsoft.com/mssql/server:2019-latest

You can determine success by looking for the following output:

Using default tag: latest
latest: Pulling from mcr.microsoft.com/mssql/server:2019-latest
…
Digest: sha256:238…
Status: Downloaded newer image for mcr.microsoft.com/mssql/server:2019-latest

Docker has a lot of commands; we’re going to be looking at run. In order to start up a a container that VS Code can talk to, we need these arguments:

• -v to mount a volume, so you can attach, restore, etc. using files from the host. Note that you have to specify -v first in order to avoid this error:

• [docker: Error response from daemon: invalid header field value "oci runtime error: container_linux.go:247: starting container process caused \"exec: \\\"-v\\\": executable file not found in $PATH\"\n".
  

   

• -i to specify interactive (which really means “attach STDIN and keep it open”). This seemed to eliminate at least one of the connection roadblocks I faced in the early going.
• -e (twice) to specify environment variables ACCEPT_EULA and SA_PASSWORD.

  For the EULA, this is pretty standard. You need to agree to the terms (even if this actually encourages you not to read them).

  About the password: Note that the sa password needs to be relatively complex. I suspect it’s based on the default AD implementation, but I don’t know that the actual complexity rules are documented. If your password is not complex enough, as Jeremiah noted, the container will just vanish without warning. Also, I recommend avoiding special characters like $, which require cumbersome escaping ( \$) ; sadly, this is where I spent quite easily the second-most amount of troubleshooting time.

• -p to let the host see the ports published by the container. I’ll use 1433 here, because I had problems connecting on other ports (I haven’t fully investigated that yet).
• -d to run the container in the background.
• The last argument is unnamed and tells Docker which image to use. (You can see the list of available images you have with docker images.)

Here is where I ended up after various attempts:

docker run -v /Users/aaronbertrand/Documents:/Documents -i -e ACCEPT_EULA=Y -e SA_PASSWORD=Turk3yT1m3 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

Once your container is up and running, you can…

Connect from Visual Studio Code

With the mssql extension installed, connecting to SQL Server is trivial, albeit there are several steps. Open a new file ( ⌘ + N) , which will default to plain text. Go to the Command Palette ( ⌘ + Shift + P) , type sql and then scroll to MS SQL: Connect:

Since the file was opened as plain text, the UI will prompt you to change the language mode to SQL:

Here’s that selection:

Now you’ll be asked to create a connection profile (if this isn’t your first time through, you can select an existing profile):

If you are creating a new profile, you need to enter the server name or IP address. You can likely use 127.0.0.1 here; I used 10.211.55.2 to match the next section about connecting from Windows:

Now you can enter your user name (you will also be prompted for your password, and whether or not to save it):

Finally, you will be asked to give the connection profile a name:

If the connection is successful, you will see this server / database / login info in the status bar at bottom right:

For some sanity checking, let’s check the new DMV sys.dm_os_host_info, and SERVERPROPERTY(N'Edition') (which reveals edition information, unlike @@VERSION):

Next, let’s prepare to restore a backup file from the host. I happened to have an old Northwind backup in my Documents folder, so I can check the logical file names from RESTORE FILELISTONLY, and see where I need to move the data and log files based on the results of sp_helpfile:

Now I can attempt to restore the database, and verify that it was successful by querying a catalog view. Note that, in spite of the output of sp_helpfile and catalog views like sys.database_files, I can use proper Linux paths like /var/opt/... instead of pseudo-Windows paths like C:\var\opt\... output:

Now persistence might be an issue for you – how I plan to use this is to make infrequent, minor incremental changes to my databases. So I may fire up a container, restore a backup, make a change, then back the new version of the database up:

After that, I can shut down the container, and do it all over again the next time I need to make a change (which might be next week, or 6 months from now). Not the most agile or source-control-safe way to do things, but it all depends on the importance of the database and your actual development goals. For a hockey card database you’re messing around with in your spare time, or a proof of concept you’re going to formalize later, this will often be more than good enough.

Aside from file locations Windows folks won’t be used to (no more C:\Program Files\...), it all just works the same. The backup file (and all the schema and code inside) is interchangeable with the Windows version of SQL Server, as long as it is an equivalent version and edition.

So What About Windows?

On the host, I could connect to a local Docker container using localhost or 127.0.0.1, as long as I use a port that Docker is publishing. You can also connect to the Docker container on the host from within a virtual machine (or from another machine), but you can’t use localhost or 127.0.0.1 there, because Windows has no way to know you mean the host, not the guest.

On this machine, I have Windows running inside a Parallels VM, and just needed to check my vnic0 interface on the host to see what IP address to connect to:

ifconfig vnic0

(You may need to check other interfaces aside from vnic0, depending on what virtualization platform you’re using and how you’ve configured the network. Also, firewall, TCP chimney, and other network issues are out of scope here, so you would need to troubleshoot those on your own.)

Now within Windows I can just create a SQL auth connection to that IP, and voila:

I could repeat the same exercises as above, and repeat again once connected to SQL Server on Windows, but in the interest of space you’ll just have to take my word that it all just works.

Follow-Up

Have questions about this type of setup? Want to know where the SQL Server team will go next? There’s an AMA this Friday:

Microsoft SQL Server team hosts Ask Me Anything session

Some other blog posts, videos, and resources that might interest you:

• What Am I Working On (RDORR): SQL Server On Linux (Bob Dorr)
• Announcing SQL Server on Linux (Scott Guthrie)
• SQL Server on Linux (Microsoft)
• SQL Server on Linux Sneak Peek (Channel 9)
• More with SQL Server on Linux (Channel 9)
• Official SQL Server on Linux Documentation
• Ops Team #024 – “DockerLove” (Channel 9)
• Visual Studio for Mac

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.