Got Database Pain?

I'm doing free brown-bag talks at companies around the community.   I have a lot of really great material after dealing with DB struggles in lots of different environments, and learning a lot along the way.  We'll go through patterns of common mistakes and how to reduce them, and strategies for making mistakes less costly when they do happen.   If you're interested, feel free to send me an email and we can schedule it!

Database CI: Practical Strategies for Reducing Database Pain

It's a common challenge - the database gets in the wrong state, the release is delayed, and the entire team is blocked and waiting for the centrally shared resource to be fixed.  Recovering from database mistakes can be quite painful.  Most of the tools available don't really solve this problem either - migrations focus on automating the deployment of the scripts, but don't help much in developing scripts that work to begin with.

But how can we reduce the number of mistakes? And how can we detect our mistakes as early and cheaply as possible?

In this presentation, we'll discuss strategies for reducing database costs with mistake-prone systems.  By adapting continuous integration principles to database work, we can drastically reduce the costs involved with database changes.  With practical examples and patterns for organizing SQL and database build automation, we'll cover strategies for many challenging issues:

• Managing packages, procedures and views in the database
• Changes (or mistakes) that can't be rolled back
• High data volumes that make everything take longer
• Systems that are hard to keep in your head

To register for this free brown-bag lunch presentation at your company, send us an email at contact@newiron.com and schedule a date! 

Factors in DB Development Cost

I've been working on developing a new database CI tool suite, and was talking with a friend about his DB woes.  We talked about what problems he was having, where they occurred, and how long they were taking to resolve.   His database was really simple.  The scripts were pretty much always correct.  But some scripts might be forgotten, and his problems usually revolved around tracking down missing changes.

I realized my solution didn't fit his problems at all.  I had never worked in an environment that was all that simple.  There were always mistakes in the scripts.  And it was always painful to correct them.  When things went wrong, it was like the DB became the black hole of engineering hours, sucking away everyone's time.  We'd try to repair the mistake by hand.  Or if we couldn't figure out how to repair it, we'd have to restore from a snapshot of production.  And while all these repairs were going on, the engineering department was pretty much down.

Making mistakes was so painful.  And trying to use any of the database migration tools out there didn't seem to solve my problems.  In some cases, it even made them worse.  I always ended up resorting to rolling my own custom database tools.

So that got me thinking... how do you decide what kind of solution you need?

While there are many complex factors that drive DB development costs, there are two that seem to characterize the problem space quite well: frequency of mistakes, and cost of recovery.

When mistakes are rare, and recovery costs are cheap, existing migration tools are the perfect solution.  Most of the effort is spent in keeping databases up to date, and making sure all the scripts are deployed as the changes evolve.   Migrations are excellent at solving that problem, and handle it beautifully.

When mistakes are costly, migration tools can still be very helpful, but depending on how rarely mistakes occur, and how costly they are, migrations might not be enough.  Augmenting a simple migration tool might be a good option.

When mistakes are frequent, there's an entirely different kind of problem going on.  Developers aren't struggling with deploying the scripts, they're struggling to create correctly working scripts.  Migration tools tend to be intolerant of deploying and recovering from broken scripts.  And the tools can make recovery even more cumbersome by imposing additional constraints.

When frequent mistakes are also expensive to resolve... well, life is pain.   There's really only two strategies to a less painful life--figure out how to make fewer mistakes, or figure out how to make mistakes easier to recover from.  

I haven't found much help in this space in either the open source or commercial market, which is why I set out to try to fill that gap.  And I've built custom tools for solving similar problems several times over now, and had the opportunity to make a lot of mistakes.  Now I get to do something with all that learning, and hopefully help reduce some of the pain out there. :)

Wow how time flies...

Wow, has it ever been a while.  It was January, February, then suddenly it's August.  How the hell did that happen?

Some challenges came up at work that I had to jump in and get involved with, and most everything in my life started going on hold... including my book, and all the community stuff I wanted to do.  I got through writing chapter 4 back in February, and that's still where I'm at.   But I'm excited to announce having 2 uninterrupted days per week to focus just on going after making this happen.  I couldn't be more excited!

Since then the ideas started whizzing around in my head again.  After going to lunch with an old friend of mine, I was reminded of an idea I had been struggling with.  It had been at least a month, trying to make sense of something I didn't quite get, without words to describe what I thought was there.  But like a flash in my mind, he gave me a piece to the puzzle I was missing.  It was beautiful.

He was reading Thinking Fast and Slow, a book I read half of, and actually put down.  He said, "Association triggers from specific to general."  And that it had really struck him, "specific to general."

After reading On Intelligence, and working through the Art of Learning myself, I had been thinking about memory sequencing, and how it might impact our ability to recognize patterns.  Like when you see some messed up code, why is that sometimes an idea pops into your head about how to solve it, and other times it doesn't?  Why does this seem to happen more in some developer's heads than others?  Is this something that can be learned?  And can we learn how to do it faster?  This was the puzzle I was working on.

Specific to General.

We teach developers design patterns by handing them a book of design patterns.  A collection of "aha" moments from our predecessors.  But then armed with our new knowledge, we don't seem to have an ability to apply them.  When we see our own code, with our own problems, that flash of insight just doesn't happen.  Then for some, with experience, it happens.

Well, you could just say it's experience.  But couldn't we tailor the creation of the right experiences so that you would learn what you need to learn faster?

My friend had the key to my puzzle.  Specific to General.  The memory sequencing is critical, and the specific sequence of recognition is the opposite of what we teach.

If I want to have insight that leads to a design pattern, I need to experience specific problem instances that map to the more general pattern.  When I scan the code, the similarity of structural pattern to my specific memory should trigger recognition.

Going to have to pick up that book again I think. :)