Containers | Building a local TSQL development environment

Docker-Compose, Flyway and a T-SQL runner to help you get up and running with a local development environment.

In my introductory posts on docker and docker-compose. We managed to set up an empty SQL Server container, but in the real world, that is not very useful.

Okay great - we can create SQL Server containers now let us build on that to create a ready-to-go development environment with docker-compose up.

Let us take a moment to talk about the objective. We need a SQL Server, a database and something to deploy code to it (build). We will define a service (an instance of a container) in our compose file for each of these tasks.

Containers used:

SQL Server using image mcr.microsoft.com/mssql/serverA simple T-SQL Script runner aletasystems/tsqlrunnerFlyway for Build/Deployment boxfuse/flyway

Flyway cannot create a database it expects the database to exist before deploying the code. This is why we use aletasystems/tsqlrunner which is a custom-image based on mcr.microsoft.com/mssql-tools.

SQL Server

	db:
	image: mcr.microsoft.com/mssql/server
	environment:
	SA_PASSWORD: ${SQL_SERVER_PASSWORD}
	ACCEPT_EULA: Y
	ports:
	- '14333:1433'

view raw db-docker-compose.yml hosted with ❤ by GitHub

We have configured our SQL-Server as follows.

• Running on port 14333
• Username sa
• Password is set in a .env file

Initialise

We now need to initialise SQL server using the container aletasystems/tsqlrunner.Our initialisation process is really simple, execute a create database script against the master database.

First a quick tour on how aletasystems/tsqlrunner works.

1. In your compose file, you map a local directory containing TSQL files to the containers /tsqlscripts
2. The local directory follows the convention of databasename\tsql-filename.sql
3. The runner will first run the scripts in master\filename (in alphabetical order)
4. Then process each database and script (again in alphabetical order)

With this information, we place a create database TSQL script file (create-db.sql) in ./path2initsqlfiles/master

	-- File Name : ./path2initsqlfiles/master/create-db.sql
	--
	-- Create a database named `Datawarehouse` and set it to `SIMPLE` recovery model.
	CREATE DATABASE Datawarehouse

view raw create-db.sql hosted with ❤ by GitHub

We now add inittools service to our docker-compose file, we configure it as below.

• The SQL Server it connects to is the db service we defined earlier
• It users the username sa and password (again it is taken from our .env file).
• We give it the path to the SQL Scripts to run.

  	inittools:
  	image: aletasystems/tsqlrunner
  	volumes:
  	- ./path2initsqlfiles:/tsqlscripts
  	environment:
  	SQLCMDSERVER: db
  	SQLCMDUSER: sa
  	SQLCMDPASSWORD: ${SQL_SERVER_PASSWORD}
  	command: '/tooling/execute-sql-scripts.sh'

  view raw inittools-docker-compose.yml hosted with ❤ by GitHub

Deployment

We will use flyway to deploy our code, but you can plugin an alternative that suits your need. I found flyway the easiest to work with, it is a fantastic tool and I hope to write/talk about it in the future.

I won’t get into too much detail about how flyway works as that deserves it own post, but if you are interested you can read How Flyway works

The basics:

• volumes shows flyway where the SQL & conf folder are

• command tells it what we want to do along with the various parameters
  

  	flyway:
  	image: 'boxfuse/flyway:5.2.4'
  	command: '-user=sa -password=${SQL_SERVER_PASSWORD} -url="jdbc:sqlserver://db;databaseName=DataWarehouse" -connectRetries=60 migrate'
  	volumes:
  	- './db_Datawarehouse/sql:/flyway/sql'
  	- './db_Datawarehouse/conf:/flyway/conf'
  	depends_on:
  	- inittools
  	- db

  view raw flyway-docker-compose.yml hosted with ❤ by GitHub
  Some useful commands:

• To re-run flyway with the latest changes to source code docker-compose restart flyway

• To view the flyway log docker-compose logs -f flyway

How did we control order?

We used the depends_on property to tell each service what it depends on. This is not exactly true, as it is (and from what I understand) the moment a service comes online, docker believes it is ready - which can be problematic as SQL Server takes a few seconds to become accessible.

It is therefore important that your services have a retry/delay mechanism built-in. Flyway has an additional parameter -connectRetries=60 and aletasystems/tsqlrunner has a modest retry mechanism