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...
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:
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:
-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 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.
-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:
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
docker ps. In the
CONTAINER IDcolumn 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. (
stoptakes 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
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
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.
So What About Windows?
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:
(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: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