SQL Server on Linux container with Docker

September 30, 2018 by rdagumampan

Container technology is relentlessly changing the way we construct software. The release of docker support for SQL Server and Linux version is a leap forward on database devops evolution. In this lighweight demo, you can create or compose a container running SQL Server on Linux host and throw away when you’re done. The possibilities are yours to explore.

For full source code available here.

Motivation

  • Allows your regression tests to freely drop and re-create database during execution
  • Explore new releases and versions of SQL Server
  • Keeps you away from ivory tower of DBAs during development
  • A step toward Infrastructure as a Code (IaaC)

Tools

  • Windows 10 or Windows Server 2016
  • Docker for Windows (Switched to Linux container)
  • VS Code

Build & Run

1. Create docker file

A docker file is your recipe to instruct docker engine how to build and run your container. This is Dockerfile.txt.

FROM microsoft/mssql-server-linux:latest

ENV SA_PASSWORD=MyD3f@ultP@ssw0rd1357!
ENV ACCEPT_EULA=Y
ENV MSSQL_PID=Express

EXPOSE 1433

2. Build

docker build . -t "rdagumampan/mssql-sandbox" -f dockerfile.txt
docker images --all

docker-images

3. Run

docker run -d -p 1433:1433 --name mssql-sandbox rdagumampan/mssql-sandbox
docker ps --all

Note your container id like this 8cd0b350ccb5.

docker-images

docker inspect rdagumampan/mssql-sandbox
docker inspect -format='{{range.NetworkSettings.Networks}}{{.IPAddress}}{{end}}' 8cd0b350ccb5

4. Test

docker exec -it 8cd0b350ccb5 "bash"
/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'MyD3f@ultP@ssw0rd1357!'
> SELECT @@SERVERNAME, @@VERSION;
> GO
> QUIT
> CREATE DATABASE SqlServerLinux;
> GO
> CREATE TABLE [dbo].[Users] (Id INT IDENTITY(1,1) PRIMARY KEY, FirstName NVARCHAR(32), LastName NVARCHAR(255), Age INT);
> GO
> QUIT

4. Access outside your host

  • On Windows 10, open up port 1433 IN/OUT
  • On Windows Server, open up port 1433 IN/OUT
  • On Azure NSG, open up port 1433 IN/OUT

docker-images

5. Cleanup

docker ps --all
docker stop 8cd0b350ccb5
docker rm 8cd0b350ccb5
docker rmi rdagumampan/mssql-sandbox

Compose & Run

A compose file allows you provision a complete infrastructure for your application. You can create a db server, web app server, and api app server and contain them in dedicated virtual network. Refer to docker docs for more details.

1. Create docker compose file

version: '3'

services:
  sql2017:
    image: rdagumampan/mssql-sandbox
    hostname: 'dockersql2017exp'
    container_name: dockersql2017exp
    build:
      context: .
      dockerfile: Dockerfile
    ports:
      - "1433:1433"
    tty: true

2. Run (Run as administrator mode)

docker-compose -f docker-compose.yml up

Push

If you want to keep the base image, you can upload to DockerHub or to your private repository. For this, you need an account on http://hub.docker.com

docker login --username=rdagumampan
docker push rdagumampan/mssql-sandbox

References

© 2017 | About | Contact | Follow me on Twitter | Powerered by Hucore & Hugo