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.

7 thoughts on “Standing up a local Sql Server development DB w/ Bullseye, Docker, and Roundhouse

  1. erikejcloudeon

    Great post, but a couple of questions:

    >> Sql Server Docker image embeds some of the Sql Server command line tools in the image,

    I see no use of command line tools in that code snippet, but some C# code ??

    >> How long DOES it take????

    Reply
    1. jeremydmiller Post author

      I added the missing code sample for reaching into the container to use sqlcmd, sorry about that.

      If the Docker image is already in your local branch, it’s taking maybe about 15 seconds from scratch, but most of that is in the Roundhouse migrations.

      Reply
      1. erikejcloudeon

        Thanks, looking forward to try this – just so I understand – the Bullseye “script” is run how? As a test or console app – by the dev?

      2. jeremydmiller Post author

        Bullseye is run as a small .Net Core app. Most folks I’ve seen add a tiny “build.cmd/build.sh” wrapper around the call to “dotnet run”. So in normal usage, that task shown up above for me would be “./build.sh init-db” on OSX or just “build init-db” from Windows.

        I think the choice of build scripting tool is mostly just a preference thing, and most of what the Bullseye script is doing here is just delegating to the command line.

      3. erikejcloudeon

        You could also just connect with SqlClient in order to create the database, I assume?

  2. Jeff Putz

    This isn’t durable, right? How would you make this a durable database instance? I assume you have to map the data to a local drive.

    Reply
    1. jeremydmiller Post author

      So far we’ve been able to keep the test database dataset small enough that that hasn’t been necessary. Just with the setup above, I’m able to stop and start the container and maintain state.

      Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s