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

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

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.

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
  

  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