Standing up a local Sql Server development DB w/ Bullseye, Docker, and Roundhouse

EDIT 3/26: I added the code that delegates to the Sql Server CLI tools in Docker

For one of our Calavista engagements we’re working with a client who has a deep technical investment in Sql Server with their database migrations authored in RoundHousE. The existing project automation depended on Sql Express for standing up local development and testing databases, with some manual set up steps in a Wiki page before you could successfully clone and run the application locally.

As we’ve started to introduce some newer technologies to this client’s web development ecosystem, there was an opportunity to improve what my former colleague Chad Myers used to call the “time to login screen” metric — how long does it take a new developer from making their first initial clone of a codebase to being able to run the system locally on their development box? Being somewhat selfish because I prefer to develop on OS X these days, I opted for running the local development database in Docker instead of Sql Express.

Fortunately, you can quickly stand up Sql Server quickly in a Linux container now. Here’s a sample docker-compose.yaml file we’re using:

version: '3'
services:
  sqlserver:
    image: "microsoft/mssql-server-linux:2017-latest"
    container_name: "Descriptive Container Name"
    ports:
     - "1433:1433"
    environment:
     - "ACCEPT_EULA=Y"
     - "SA_PASSWORD=P@55w0rd"
     - "MSSQL_PID=Developer"

That’s step 1, but there’s a little bit more we needed to do to stand up a local database (actually two databases):

1. Provision a new database server
2. Create two named databases
3. Run the RoundHousE database migrations to bring the database up to the current version

So now let’s step into the realm of project automation scripting. I unilaterally chose to use Bullseye for build scripting because of the positive experience the Marten team had when we migrated the Marten build from Rake to Bullseye. Using Bullseye where you’re just writing C#, we have this task:

Target("init-db", () =>
{
    // This verifies that the docker instance
    // defined in docker-compose.yaml is up
    // and running
    Run("docker-compose", "up -d");

    // The command above is asynchronous, so wait
    // until Sql Server is responsive
    WaitForSqlServerToBeReady();

    // Create the two databases
    CreateDatabase("Database Name #1");
    CreateDatabase("Database Name #2");

    // Run RoundHousE to apply the latest database migrations
    Run("dotnet", "tool update -g dotnet-roundhouse");
});

To flesh this out a little more, the Sql Server Docker image embeds some of the Sql Server command line tools in the image, so we were able to create the new named databases like this:

        // No points for style!!!
        private static void WaitForSqlServerToBeReady()
        {
            var attempt = 0;
            while (attempt < 10)
                try
                {
                    using (var conn = new SqlConnection(DockerConnectionString))
                    {
                        conn.Open();
                        Console.WriteLine("Sql Server is up and ready!");
                        break;
                    }
                }
                catch (Exception)
                {
                    Thread.Sleep(250);
                    attempt++;
                }
        }

The CreateDatabase() method just delegates to the sqlcmd tool within the Docker container like this (the Run() method comes from SimpleExec):

        private static void CreateDatabase(string databaseName)
        {
            try
            {
                Run("docker",
                    $"exec -it SurveySqlServer /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P \"{SqlServerPassword}\" -Q \"CREATE DATABASE {databaseName}\"");
            }
            catch (Exception e)
            {
                Console.WriteLine($"Could not create database '{databaseName}': {e.Message}");
            }
        }

It was a lot of Googling for very few lines of code, but once it was done, voilà, you’ve got a completely functional Sql Server database for local development and testing. Even better yet, it’s super easy to turn development database on and off when I switch between different projects by just stopping and starting Docker images.