VS Code on Mac meets SQL Server on Linux (in Docker) - SentryOne

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

Relationship status: It's complicated.
Relationship status: It's complicated.

With this week's 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

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

You'll get at least a screen height's worth of output; toward the end, look for the following message to verify it was installed:

==> 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):

VS Code mssql extension

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

 

Install Docker

Docker Starting

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:

Docker preferences

 

Running a SQL Server container

Let's head back to Terminal and play with getting a Docker container up and running. First, we need to bring down the SQL Server image from Docker Hub:

docker pull microsoft/mssql-server-linux

You can determine success by looking for the following output:

Using default tag: latest
latest: Pulling from microsoft/mssql-server-linux

Digest: sha256:238…
Status: Downloaded newer image for microsoft/mssql-server-linux: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 Jemeriah 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 microsoft/mssql-server-linux

You can check for successful creation using docker ps. In the CONTAINER ID column you'll see a 12-character random string, like 8b6822996341 (which is just a subset of the container's 64-character identifier). You can use shortcuts referencing the first couple of characters of this string, assuming you don't have any conflicts. If you want to stop the container, for example, just issue docker stop 8b; to remove it, use docker rm 8b. (stop takes a bit longer than rm.) These shortcuts were quite handy while I was troubleshooting throughout this whole experiment, because I wanted to minimize the number of active containers I had up and running.

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:

MS SQL: Connect

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

Language Mode

Here's that selection:

Change Language Mode

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

Create Connection 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:

Enter Server Name

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

Enter User Name

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

Profile Name

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

Status Bar

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

sys.dm_os_host_info, SERVERPROPERTY(N'Edition'), and @@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:

Checking access to BAK file and destination data/log folder

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:

Restoring Northwind to /var/opt/...

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:

Backing up the database

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

Here's the part of the output I was interested in:

Checking vnic0 interface

(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:

Connecting to SQL Server in Docker, on Mac, from Windows VM

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:

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

Comments ( 4 )

    • Phil says:

      Hi Aaron,

      Thanks for the article. Just wondering how you managed to persist your databases in SQL Server in Docker?

      I've tried using the -v argument to mount a host directory to /var/opt/mssql, but this seems to cause an error on SQL startup opening file C:\var\opt\mssql\data\master.mdf.

      Checking the host directory I can see the master.mdf/lfd files have been created, so they're definitely there.

      Any ideas?

    • Aaron Bertrand says:

      Hey Phil, I haven't done anything to persist databases, I am not doing any long-term local development, I've simply automated restoring WideWorldImporters whenever I fire up a new container.

    • Johnny says:

      Thanks for this great article. If you want to use a different port other than 1433, you can still connect from VSCode. You just need to specify the port in your connection setting: https://github.com/sanagama/vscode-mssql/issues/6#issuecomment-235020678

    • Geoff says:

      Thanks very helpful Aaron. Following these instructions saved me so much time and pain. I did have VSCode already running. I tried to run scripts not from Documents subtree which did not work. But I was up and running in less than 30 mins.

    Leave A Comment

    Your email address will not be published.