Testing SQLServer and PHP locally in 2021

I’ve recently been working on overhauling the test database fixture system in CakePHP . My goals are to separate fixture schema management from fixture data management. By doing this applications will more easily be able to use their existing schema management to generate their test database. This project has entailed fixing many differences between the database servers that CakePHP supports. While I can easily install SQLite, MySQL and Postgres locally via apt SQLServer is a different animal.

Docker to the rescue

Thankfully we’re in 2021 and Microsoft has invested the time to make SQLServer functional on Linux and Docker makes running SQLServer much easier than ever before. The pdo_sqlsrv extension is complicated to install on linux, but thankfully Docker can make that easier too. So how does all this magic work? I wasn’t able to find a single reference guide after searching through google, stackoverflow and countless abandoned GitHub projects. From those sources, I was able to cobble together a solution. That I would like to share so other have a less confusing journey.

Getting SQLServer

First thing we need to do is get the SQLServer image. We can use docker pull to get it:

Show Plain Text
  1. docker pull mcr.microsoft.com/mssql/server:2017-latest

Before we run this container, we need to setup a docker ‘network’. This is required so that our database and PHP containers can talk to each other. While you could do this with docker-compose I didn’t want to use more tools:

Show Plain Text
  1. docker network create --driver bridge sqlserver-net

Now we can run our container to get SQLServer running in the background.

Show Plain Text
  1. docker run -e "ACCEPT_EULA=Y" \
  2.   -e "SA_PASSWORD=p@ssw0rd" \
  3.   -p 1433:1433 \
  4.   --name sql1 \
  5.   -h sql1 \
  6.   --network sqlserver-net \
  7.   -d mcr.microsoft.com/mssql/server:2017-latest

This will run the SQLServer container and attach it to the sqlserver-net network we created. We’re also giving this container a name so that we can kill it and restart it later more eaisly with docker kill sql1 and docker start sql1. Before our database is usable for testing, we’ll need to get a REPL in SQLServer so we can create a database or run ad-hoc queries:

Show Plain Text
  1. # Get in to the container.
  2. docker exec -it sql1 bash
  3.  
  4. # Get a REPL for SQLServer
  5. /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P "p@ssw0rd"

Getting PHP with pdo_sqlsrv

I chose to use PHP7.4 as I couldn’t get 7.2 to compile and I needed to test with one of the lower versions of PHP that CakePHP supports. I used the following docker image:

Show Plain Text
  1. FROM ubuntu:18.04
  2.  
  3. ARG DEBIAN_FRONTEND=noninteractive
  4.  
  5. # UPDATE PACKAGES
  6. RUN apt-get update
  7.  
  8. # INSTALL SYSTEM UTILITIES
  9. RUN apt-get install -y \
  10.     apt-utils \
  11.     curl \
  12.     git \
  13.     apt-transport-https \
  14.     software-properties-common \
  15.     g++ \
  16.     build-essential \
  17.     dialog
  18.  
  19. # INSTALL locales
  20. RUN apt-get install -qy language-pack-en-base \
  21.     && locale-gen en_US.UTF-8
  22. ENV LANG en_US.UTF-8
  23. ENV LC_ALL en_US.UTF-8
  24.  
  25. # INSTALL PHP & LIBRARIES
  26. RUN add-apt-repository -y ppa:ondrej/php
  27. RUN apt-get update
  28. RUN apt-get --no-install-recommends --no-install-suggests --yes --quiet install \
  29.     php-pear \
  30.     php7.4 \
  31.     php7.4-common \
  32.     php7.4-mbstring \
  33.     php7.4-dev \
  34.     php7.4-xml \
  35.     php7.4-cli \
  36.     php7.4-mbstring \
  37.     php7.4-curl \
  38.     php7.4-xml \
  39.     php7.4-zip \
  40.     php7.4-odbc \
  41.     php7.4-intl
  42.  
  43. # INSTALL ODBC DRIVER & TOOLS
  44. RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
  45. RUN curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
  46. RUN apt-get update
  47. RUN ACCEPT_EULA=Y apt-get install -y \
  48.     msodbcsql17 \
  49.     mssql-tools \
  50.     unixodbc \
  51.     unixodbc-dev
  52. RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
  53. RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
  54. RUN exec bash
  55.  
  56. # INSTALL & LOAD SQLSRV DRIVER & PDO
  57. RUN pecl install sqlsrv
  58. RUN echo ";priority=20\nextension=sqlsrv.so" > /etc/php/7.4/cli/conf.d/20-sqlsrv.ini
  59.  
  60. RUN pecl install pdo_sqlsrv
  61. RUN echo ";priority=30\nextension=pdo_sqlsrv.so" > /etc/php/7.4/cli/conf.d/30-pdo_sqlsrv.ini
  62.  
  63. WORKDIR /src
  64.  
  65. CMD ["/bash"]

You should be able to change the 7.4 into an 8.0 pretty easily and have everything work, but I haven’t tried. Once you build the image with:

Show Plain Text
  1. docker build -t php-sqlsrv -f sqlserver.Dockerfile .

You can run it with:

Show Plain Text
  1. docker run --rm -it --network sqlserver-net -v $(pwd):/src php-sqlsrv bash

You should now have a basic PHP installation able to communicate with a SQLServer database.

Comments

> The pdo_sqlsrv extension is complicated to install on linux

Did you try the guides from https://www.microsoft.com/en-us/sql-server/developer-get-started/php/ubuntu?rtc=1?

Marc Würth on 5/26/21

I didn’t use this exact guide, but I used one similar to it and ran into version conflicts between unixodbc and mssql-tools. It could be a problem related to using a 20.10 based distro.

mark story on 6/8/21

Have your say: