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.
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:
- 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.
- 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.
- 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…
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:
You'll have to hit Enter, supply your password, type
agree, and hit Enter again. Next up…
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:
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:
With Homebrew in place, you can use it to…
This step is really easy:
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):
🍺 /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/libssl.1.0.0.dylib /usr/local/lib/
Now you can…
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
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…
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
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:
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:
You can determine success by looking for the following output:
latest: Pulling from microsoft/mssql-server-linux
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:
-vto mount a volume, so you can attach, restore, etc. using files from the host. Note that you have to specify
-vfirst 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".
interactive(which really means "attach
STDINand 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
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
sapassword 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.
-pto 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).
-dto 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
Here is where I ended up after various attempts:
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…
mssql extension installed, connecting to SQL Server is trivial, albeit there are several steps. Open a new file
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
SERVERPROPERTY(N'Edition') (which reveals edition information, unlike
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
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
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.
On the host, I could connect to a local Docker container using
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
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:
Here's the part of the output I was interested in:
(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.
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:
- 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