Modern Database Development Practices

Back in November 2012 I presented on this topic at Entelect's Dev Days.

I've also done a blog post previously on setting up Flyway.

I keep referring back to the same links and concepts and this post is here just for that, as a pointer to good resources and brief points about this topic.

I may revisit it and put down my own consolidated content, but for now this will do.

• Reading
• Evolutionary Database Design
  Martin Fowler, 2003
• Agile Database Techniques: Effective Strategies for the Agile Software Developer
  Scott Ambler, 2003
• Refactoring Databases: Evolutionary Database Design
  Scott Amber and Pramod Sadalage, 2006
• Catalog of Database Refactorings
  Scott Ambler
• Three Rules for Database Work
  K. Scott Allen, 2008
• The Baseline
  K. Scott Allen, 2008
• Change Scripts
  K. Scott Allen, 2008
• Versioning Databases - Views, Stored Procedures, and the Like
  K. Scott Allen, 2008
• Versioning Databases - Branching and Merging
  K. Scott Allen, 2008
• Scott Ambler's Articles
  Scott Ambler
• Stairway to Database Source Control
  SQL Server Central
• Martin Fowler's Practices
• DBAs collaborate closely with developers
• Everybody gets their own database instance
• Developers frequently integrate into a shared master
• A database consists of schema and test data
• All changes are database refactorings
• Automate the refactorings
• Automatically Update all Database Developers
• Clearly separate all database access code
• K. Scott Allen's 3 Rules
• Never use a shared database for development work
• Always have an authoritative source for your schema
• Always version your database
• Tools
• Redgate Developer Tools
• SQL Server Data Tools (SSDT)
• Entity Framework Code First Migrations
• DbUp
• Flyway
• Open DBDiff
• sqlcmd
• tSQLt
• dbdeploy

Refactoring Databases

On Friday I did a presentation at Entelect's internal dev days regarding Modern Database Development, and how best practices of Agile Development are widely known and regarded in the community, but how we rarely see the same rigour with database development.

And it's not new. Martin Fowler, Scott Ambler and Pramod Sadalage have written books and blog posts on Evolutionary Database Design and Refactoring Databases, going back to at least 2003. But we don't always see people employing the techniques and tools that have been discussed in the past ten years.

I didn't even know about dbdeploy nor the Red Gate developer tools until this year. I've only been on one real life project that used a custom developed database patching tool to roll out database changes and a base for integration testing. But I definitely believe these tools and techniques must be employed more regularly.

I have to say, the presentation went well. I received a lot of positive comments from people and I think that the audience may have got something out of my talk, which is great news. In fact, I was even asked straight afterwards to help someone out in how to help them compare the data in two production databases.

Look up the tools I mentioned, dbdeploy, Red Gate Source Control, SQL Compare and SQL Data Compare, as well as Microsoft's SQL Server Data Tools. dbdeploy is open source, the Red Gate Developer Tools comes with a trial version, and SSDT is free if you have Visual Studio.

Let's all get better at what we do.

Cheers,

James