Installing MSSQL server using docker with full text search support

Question

I am using mssql express for ubuntu on docker. I just found that it does not support full text search. I believe the mssql express supports this, but found no way to enable this feature.

Here is the image I'm using.

image: mcr.microsoft.com/mssql/server:2017-latest-ubuntu

I am passing express edition as environment as MSSQL_PID: "Express".

How can I have full text search installed on the same image ?

Answer 1

Using this Docker.file with compose to build it on demand for SQL Server 2022 Full Text by extending Microsoft's SQL Server image.

# 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

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

# 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

# Install SQL Server package links
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/22.04/mssql-server-2022.list -o /etc/apt/sources.list.d/mssql-server->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

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

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

And the compose file (I use Dockge on Debian for this but it should work anywhere)

services:
  db:
    build:
      dockerfile: /opt/stacks/mssql/build/sqlserver-fulltext.Dockerfile
    user: root #Make sure you are comfortable with this or change it
    ports:
      - 1433:1433
    environment:
      - ACCEPT_EULA=Y
      - MSSQL_SA_PASSWORD=<PASSWORD>
      - MSSQL_RPC_PORT=135
    restart: always
    networks:
      - <NETWORK>
    volumes:
      - /opt/stacks/mssql/data:/var/opt/mssql/data
      - /opt/stacks/mssql/log:/var/opt/mssql/log
      - /opt/stacks/mssql/secrets:/var/opt/mssql/secrets
    deploy:
      resources:
        limits:
          cpus: "6" #set as needed
          memory: 16G #set as needed
networks:
  <NETWORK>:
    external: true

Answer 2

If you have an existing container and don`t want to build a custom image do the following steps in your cmd

docker exec -u 0 -it containername /bin/bash
apt-get update
apt-get install -yq curl apt-transport-https gnupg
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/mssql-server-2022.list | tee /etc/apt/sources.list.d/mssql-server-2022.list 
apt-get update
apt-get install -y mssql-server-fts
apt-get clean && rm -rf /var/lib/apt/lists/* && rm -rf /*.deb

And then Restart your container.

To check if it is working, run the following query(Should return 1)

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Answer 3

Referencing original answer.

For Docker linux container(s) if you do not want to with overhead of build scripts and just want a 1 second quick docker run example with everything configured then you can use the following command to run SQL Server Full Text Search (current image version is 2022 build) (Developer/Express/Enterprise) directly using this docker run command as shown below:

docker run --name sqlserver -e "ACCEPT_EULA=Y" -v SqlServer:/var/opt/mssql -e "MSSQL_SA_PASSWORD=your1#ComplexPassword" -e "MSSQL_PID=Express" -p 1433:1433 -d vibs2006/sql_server_fts:latest

Refer here for docker hub details of this SQL Server Full Text Search image.

Answer 4

Previous responses focus on the LINUX container of MSSQL. This is for windows, copy this PS into the image using docker file and run it:

    $global:ErrorActionPreference = 'Stop'

    Import-Module Sbs;

    # Download SQL Server ISO
    SbsDownloadFile -Url "https://download.microsoft.com/download/3/8/d/38de7036-2433-4207-8eae-06e247e17b25/SQLServer2022-x64-ENU-Dev.iso" -Path "C:\SQLServer2022-x64-ENU-Dev.iso";

    # Create a directory to extract the ISO contents
    New-Item -Path C:\SQLServerISO -ItemType Directory;

    # Use 7z to extract the ISO contents
    7z x C:\SQLServer2022-x64-ENU-Dev.iso -oC:\SQLServerISO;

    # Define directory paths
    $systemDbDir = 'C:\SQLSystemDB\Data';
    $systemDbLogDir = 'C:\SQLSystemDB\Log';
    $userDbDir = 'C:\SQLUserDB\Data';
    $userDbLogDir = 'C:\SQLUserDB\Log';
    $backupDir = 'C:\SQLBackup';
    $installDir = 'C:\Program Files\Microsoft SQL Server';

    # Ensure all required directories exist
    New-Item -Path $systemDbDir, $systemDbLogDir, $userDbDir, $userDbLogDir, $backupDir, $installDir -ItemType Directory -Force;

    # Install SQL Server from the extracted files
    $process = Start-Process -Wait -NoNewWindow -FilePath "C:\SQLServerISO\setup.exe" -ArgumentList "/Q",
        "/ACTION=install",
        "/SUPPRESSPRIVACYSTATEMENTNOTICE",
        "/INSTANCEID=MSSQLSERVER",
        "/INSTANCENAME=MSSQLSERVER",
        "/FEATURES=SqlEngine,FullText",
        "/INSTALLSQLDATADIR=`"$installDir`"",
        "/SQLUSERDBDIR=`"$userDbDir`"",
        "/SQLUSERDBLOGDIR=`"$userDbLogDir`"",
        "/SQLTEMPDBDIR=`"$systemDbDir`"",
        "/SQLTEMPDBLOGDIR=`"$systemDbLogDir`"",
        "/SQLBACKUPDIR=`"$backupDir`"",
        "/UpdateEnabled=0",
        "/TCPENABLED=1", 
        "/NPENABLED=0",
        "/IACCEPTSQLSERVERLICENSETERMS",
        "/SQLSYSADMINACCOUNTS=ContainerAdministrator" -PassThru;

    # Check the exit code
    if ($process.ExitCode -ne 0) {
        Write-Error "SQL Server installation failed with exit code $($process.ExitCode)."
        exit $process.ExitCode
    }

    # Cleanup: Remove the ISO and extracted files
    Remove-Item -Path C:\SQLServer2022-x64-ENU-Dev.iso -Force; `
    Remove-Item -Path C:\SQLServerISO -Recurse -Force;

Link to the repo that contains the docker files for the complete setup:

https://github.com/david-garcia-garcia/windowscontainers/tree/master/sqlserver2022base

Answer 5

Default mssql docker image does not support fts, i.e. full text search, therefore we need to create the custom sql image.

Here is the dockerfile for the same... based on the microsoft repo at https://github.com/Microsoft/mssql-docker/blob/master/linux/preview/examples/mssql-agent-fts-ha-tools/Dockerfile

# 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 && \
    apt-get install -yq curl apt-transport-https && \
    # Get official Microsoft repository configuration
    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