Build a SQL Server Instance in a Hurry (aka SQL Server on Docker)

If you're a developer working in the .NET stack, you no doubt have had to install SQL Server - a royal pain if you don't have the installation media handy, or have memorised which options to select just to get something ready to restore a database to. Once it's installed, you also have to dedicate a port and part of your every-day memory to running it. It can be quite the hog if you have a couple of reasonable sized databases running. In the last 5 or so years, I've liked to keep a small footprint on my devices. As it stands, my muck-about laptop is a Lenovo ThinkPad T440s - rocking an i7 4600u with 12GB of RAM (4GB built in + 8GB Module). A good sturdy machine with good battery life. Unfortunately, the memory is max'd out and that doesn't leave much room for running things like SQL Server to run in the background.

If you haven't heard, SQL Server is available as a docker container and has been since ~2018. As such, I was happy to remove SQL Server from my laptop and just spin up SQL Server when needed. There's not a whole lot to it - you specify the image and a few environment variables and volumes, and you in effect have a portable version of SQL Server. Indeed - SQL Server on Docker is currently powering this website.

Per below, create the following docker-compose.yaml file in a directory. I'd recommend you creating a directory for each instance, as the script creates a directory structure relative to where the docker-compose.yaml file is run from.

version: "3"

services:

  mssql:

    image: mcr.microsoft.com/mssql/server:2019-latest

    ports:
      - 6000:1433

    user: root

    volumes:
      - ./_db/data:/var/opt/mssql/data:rw
      - ./_db/log:/var/opt/mssql/log:rw
      - ./_db/secrets:/var/opt/mssql/secrets:rw
      - ./_db/backups:/var/opt/mssql/backups:rw
      
    restart: always

    environment:
      ACCEPT_EULA: Y
      SA_PASSWORD: Password1234
      MSSQL_PID: Developer # change this to Express if you're not licensed, Standard and Enterprise are also available here

Because we're providing our own volumes such that we can keep this setup portable, the user: root line is (or at least was) a cheat way to allow all write access to /var/opt/mssql otherwise you'd need to run your chown / chmod scripts to set the directory permissions. For volumes, I like to separate my data and log DBs, so those are listed separately. Maintaining your secrets directory will enable portability and you'll probably want somewhere to store your backups. I usually set restart: always as I context-switch by running my docker-compose up / down commands. Finally - the environment variables are specified in the dockerhub link. The three key ones are to accept the EULA automatically (otherwise the startup will hang), set a password and set the product ID to match what you're licensed for.

You'll also note that I override the port. I usually set a particular port range per application I run - such that I can store all my configurations in SSMS under the port. Let's say I have 5 applications, I might do 3000-3999 for application 1, 4000-4999 for application 2 and so-on. This allows me to save a configuration as "localhost,3000" and "localhost,4000" with the SA password already set.

It really is that simple*. Except for one bug when mounting your volumes this way. That is, if you try to restore a backup you'll need to have the file created at your /_db/data directory. You can do this any way you want. The easiest options for me have been:-

• Create the database the normal way in SSMS, then run your Restore script.
• Run "touch /var/opt/mssql/data/file.mdf" and "touch /var/opt/mssql/log/file_log.ldf" so that there's an empty file to write to avoiding the need to create the database in the first place.
• Create the files in /_db/data and /_db/log yourself.

These avoid the: The operating system returned the error '2(The system cannot find the file specified.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on '/var/opt/mssql/data/ error you'll see in your docker outputs.

So... after you've copied and pasted the script above, and saved it in a directory called docker-compose.yaml and you want to run your own instance? Simples. Once you have docker installed (and therefore docker-compose), the next step is to execute the following commands:

• To start: docker-compose up -d (nb - if you omit the -d your command window will be attached to logs)
• To stop: docker-compose down

That's it. A docker-compose file with Docker installed and you've got yourself a SQL Server instance in seconds (as long as you have the image downloaded already otherwise you'll need to wait for those couple of GB's to come down).