Tips and Tricks for Creating Docker for SQLServer with FreeText
It’s one of those cases where AI is not super helpful in generating code that works for you. So reliant on tools like ChatGPT is not ideal (which applies to everything you code but is more specific to this example). What makes this one unique is that I am trying to install the FreeText search tool on Ubuntu image on Docker running on Mac with Intel Chip. So what went wrong:
- The original docker image provided by Microsoft does not support FreeText
- Docker images are more secure without the magic
sudoprivilige. - Not all columns support FreeText search by default
- Creating the index is reliant on existing keys in the table which might not exist in a database out of your control
- Ubuntu Images cache might require prune to free up
/var/cache/apt/archives/
At first, I was hoping that using the Microsoft image would be a quick win
FROM mcr.microsoft.com/mssql/server:latest
With a hack to install SQLServer shared libraries using the root user.
USER root
RUN wget http://http.us.debian.org/debian/pool/main/o/openldap/libldap-2.4-2_2.4.47+dfsg-3+deb10u7_amd64.debRUN apt install ./libldap-2.4-2_2.4.47+dfsg-3+deb10u7_amd64.deb
However, the following error caught me:/opt/mssql/bin/sqlservr: error while loading shared libraries: liblber-2.4.so.2: cannot open shared object file: No such file or directory
So this library wouldn’t install and that’s since Sudo and root is not allowed for security reasons. So I realised that relying on pre-built images isn’t going to take me far. So I decided to go with Ubuntu image and install SQLServer and FullText search via curl.
So the first step to get it is via:
FROM ubuntu:16.04# Install prerequisites since it is needed to get the repo config for the SQL serverRUN export DEBIAN_FRONTEND=noninteractiveRUN apt-get update
# Install curlRUN apt-get clean packagesRUN apt-get install -yq curl
This has not been installed for me due to this issue:
E: You don't have enough free space in /var/cache/apt/archives/
but there’s a trick to fix that without pruning your images, containers and volumes (the nuclear option that most people go to, when they’re super frustrated).
simply remove redundant build cache for the old images you no longer need
docker builder prune -a
as a bonus tip you can also consider removing any dangling images. You can find them with this command:
docker images -f "dangling=true"
Additionally, you might need to consider increasing the resources on your host via docker desktop preferences:
That’s it. You are ready to go.
Let’s build the docker file to install SqlServer and its dependencies with FreeText search. See this example.
# mssql-agent-fts-ha-tools
# Maintainers: Microsoft Corporation (twright-msft on GitHub)
# GitRepo: https://github.com/Microsoft/mssql-docker
# Base OS layer: Latest Ubuntu LTS
FROM ubuntu:16.04
# Install prerequistes since it is needed to get repo config for SQL server
RUN export DEBIAN_FRONTEND=noninteractive
# apt-get update && \
# Update package lists
RUN apt-get update
# Install curl
RUN apt-get clean packages
RUN apt-get install -yq curl
# Add debug output
RUN dpkg -l curl
#RUN apt-get install -yq curl && \
RUN apt-get install -yq apt-transport-https && \
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list | tee /etc/apt/sources.list.d/mssql-server.list && \
apt-get update && \
# Install SQL Server from apt
apt-get install -y mssql-server && \
# Install optional packages
apt-get install -y mssql-server-ha && \
apt-get install -y mssql-server-fts && \
# Cleanup the Dockerfile
apt-get clean && \
rm -rf /var/lib/apt/lists
# Run SQL Server process
CMD /opt/mssql/bin/sqlservr
Now you’re ready to build. Running the image requires accepting the license agreement which can be passed as an environment variable:
docker run -e "ACCEPT_EULA=Y"
to setup your own password use:
-e "MSSQL_SA_PASSWORD=pass"
and finally use -p for the port number as you need to expose 1433.
Additionally, you can use docker-compose to simplify your workflow. see below the code for docker-compose.yml
version: "3.2"
services:
sqlserver:
container_name: sqlserver_fulltext
build:
dockerfile: sqlserver.Dockerfile
ports:
- "1433:1433"
environment:
SA_PASSWORD: "pass"
ACCEPT_EULA: "Y"Job Done!
Any questions? let me know.
Let’s test it all. Create the database and a table with two simple columns Id, and Name. Make sure that the [name] column is of the type
VARCHAR(MAX)
and create the Index
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;CREATE FULLTEXT INDEX ON t(name) KEY INDEX PK__t__3213E83FE39EC819 on ftCatalog;DECLARE @counter INT
SET @counter = 1
WHILE @counter <= 20000
BEGIN
INSERT INTO table_x(id, name)
VALUES (@counter + 1, CAST(@counter AS VARCHAR) + CAST(NEWID() as varchar(255)))
SET @counter = @counter + 1
ENDLet’s look up something 🙂
select name from t where FREETEXT(name, 'Hello')
