A SQL Server docker container with full-text search

Writing a dockerfile that adds features to an existing image

I wrote containerized SQL Server on Docker a few months ago when I migrated my company from a “developer desktop setup tutorial”-style environment to a “docker compose up”.

Using SQL Server on Docker, we were able to end manual installations of SQL Server which wasted developer time and made it easy to commit a mistake that would prevent the rest of our setup scripts from succeeding. Perhaps someday Microsoft’s SQL Server team will phase out complex UI-based installers; but by then we’ll all have migrated to Postgres.

Now that we’ve committed to Docker, we have decided to invest time in new techniques to make more use of containers. In today’s short blog post I will walk us through installing full-text search on SQL Server in docker using a Dockerfile.

Photo by Micah Boswell on Unsplash

What’s the latest version of SQL Server on Docker?

If you visit the Docker Catalog and search for sqlserver, you’ll find a lot of images — and, as of the writing of this article, none of them are published by Microsoft.

On the other hand, Microsoft does publish an official reference image of sqlserver, but you have to do some searching for it. Microsoft’s official Docker tutorial says that they have an instance of SQL Server 2022 available:

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

If you keep clicking through documentation, you can find this Microsoft SQL Server Docker hub page which lists a bunch of different versions — but none of them include full-text search. So how do you install that?

Our original solution was to just manually install full-text search using the Docker Desktop terminal. That’s nice, but let’s go further and create a proper build script.

Using a Dockerfile to set up SQL Server with full-text search

A Dockerfile is a script that explains to Docker how to start with a known state and then transform it into another state. If you use your Dockerfile correctly, you can benefit from caching: each intermediate transformation can be cached to save you the time when you need to rebuild it again.

If you just want to fast-forward to the end, take a look at my resulting Dockerfile for SQL Server with full-text search. Otherwise, let’s walk through how we get there.

Start with a known image using FROM

The first step in a Dockerfile is to define our starting point. We’ll do that by using Microsoft’s official 2022 SQL Server image as a FROM statement:

# As of August 2023, I believe this is the current version of SQL Server available for use
FROM mcr.microsoft.com/mssql/server:2022-latest

Install CURL as user ‘root’

The next step in our script is to switch to a user with permission to run apt-get. If you don’t use this step, you won’t be able to run any installations.

# Switch to root to install fulltext - apt-get won't work unless you switch users!
USER root

We will now install CURL, a fantastic program which lets us retrieve files from remote Internet servers. Since Microsoft publishes a package for SQL Server full-text search for Ubuntu, this is what we’ll need to do to install it.

# Install dependencies - these are required to make changes to apt-get below
RUN apt-get update
RUN apt-get install -yq gnupg gnupg2 gnupg1 curl apt-transport-https

Add full-text search via apt-get

Although Microsoft does publish packages for Ubuntu, you can’t install them by default. Instead, you have to tell Ubuntu that it’s allowed to use Microsoft package sources first.

You do that by fetching a Microsoft package key and adding it via apt-key . Once this is done, you can then tell Ubuntu to use the Microsoft package source as one place to find new packages to install.

After both these steps are complete, you can then do the real action — you can apt-get install -y mssql-server-fts.

# Install SQL Server package links - why aren't these already embedded in the image?  How weird.
RUN curl https://packages.microsoft.com/keys/microsoft.asc -o /var/opt/mssql/ms-key.cer
RUN apt-key add /var/opt/mssql/ms-key.cer
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list -o /etc/apt/sources.list.d/mssql-server-2022.list
RUN apt-get update

# Install SQL Server full-text-search - this only works if you add the packages references into apt-get above
RUN apt-get install -y mssql-server-fts

Clean up and set an entrypoint

The final step in a Dockerfile is to do whatever cleanup is necessary — in our case, we want to remove any unnecessary apt-get leftovers remain from our installation work. Finally, we’ll set an entrypoint to make sure someone can use this SQL Server.

# Cleanup
RUN apt-get clean
RUN rm -rf /var/lib/apt/lists

# Run SQL Server process
ENTRYPOINT [ "/opt/mssql/bin/sqlservr" ]

Creating a docker compose script for this file

To begin using this script you should save this file to sqlserver-fulltext.Dockerfile and write a docker-compose.yaml file that references it. You’ll need to specify two environment variables: SA_PASSWORD and ACCEPT_EULA.

Here’s what a working docker compose YAML file looks like:

version: "3.2"
services:

  sqlserver:
    container_name: sqlserver
    build:
      dockerfile: sqlserver-fulltext.Dockerfile
    ports:
      - "1433:1433"
    environment:
      SA_PASSWORD: "Some4Complex#Password"
      ACCEPT_EULA: "Y"

If you’d prefer, you can just download this directly from my Github repository.

Ted Spence heads engineering at ProjectManager.com and teaches at Bellevue College. If you’re interested in software engineering and business analysis, I’d love to hear from you on Mastodon or LinkedIn.