MySQL Workbench for Database Change Management

Managing database change is an incredibly important discipline that very few database professionals overtly talk or worry about until they're in the thick of things with a particular database - moving it from development to production, making changes to a newly installed production database, or implementing an updated version of the database (new tables, modifications to existing objects, etc.) in a SaaS application. It's at that point where change management becomes very important because if you don't do things right the first time, you can make a royal mess of things and even (in a small number of cases) reach the point of no return where you've completely torched your database.

This is why I've always been a big fan of using good tools when building and managing databases. Yes, some say that tools aren't for real men (or women), but the fact is that the good ones can save you a lot of time, anxiety, and even make you into a hero on occasion. They are also a godsend to novices or non-database pro's who get dragged into being the project DBA for a database-driven project.

With the recent release of MySQL Workbench, you now have at your disposal one of those tools that can really make a difference in how you design and manage databases. You may have thought that data modeling was the only thing that MySQL Workbench could do, and certainly that is the core thrust of the tool. But one of the reasons that I used my data modeling tool so much as a DBA was not for its logical modeling abilities, but for what it could do for me in the area of change management. MySQL Workbench contains a number of database change control features that you may not have noticed at first, so let me introduce them to you and show you why they can be a big help when it comes to managing change in your databases.

What's Different?

A developer lead in charge of implementing a new version of a major ERP application came to me once and said he was in need of some major help. Because of some special app changes they'd made, he needed to know if the new, incoming database design was different in any way from the existing production database. Of course, there were about 3,000 tables alone in the database so manually doing checks was out of the question.  What to do?

Fortunately, I pulled out my modeling tool that had a built-in differentiation utility and I was able to get him the information he needed in about 10 minutes.

MySQL Workbench has just such a differentiation tool - it's labeled "Generate Schema Diff Report" under the Database menu. You can generate a difference report between a model and a database, between two live databases (same or different physical servers), between two SQL scripts, or between a SQL script and a live database or model.

So for example, let's say I've reverse-engineered a particular database into a MySQL Workbench design/model and have made a simple change to my model - I've changed the name of one of the column names in a table. I close my model and go on vacation for a week. I come back and can't remember exactly what I've changed in my model - but I do know the model and my live database aren't the same anymore.

By selecting the Generate Schema Diff Report option, I can quickly do a compare against my live database and get back a simple report telling me what the differences are. Of course, the utility serves many more use cases than just this simple scenario - investigating differences between development, QA, and production databases is a very common use case as is looking into the dissimilarities between new iterations of packaged applications that ship with databases.

What's the Process?

A major database re-design is something only people who enjoy a trip to the oral surgeon like to go through. If you have a database that's got lots of complex relationships, many objects, and stored procedures/triggers that reference it all, then doing a serious overhaul can be a challenge. Figuring out the process of making all the necessary changes to a big database in the order they need to be done isn't something that the faint of heart normally attempt.

Again, though, this is where my modeling tool always came to the rescue. Because I kept everything in my model and used is as my change control repository as it were, I knew that I could pretty much start any serious redesign work and go from top to bottom, left to right, and my modeling tool would keep everything in sync for me.  Then, when it came time to apply my redesign to an existing physical database, the synchronization functions of the modeling tool would make all the changes for me, in the right order, and do so without error. That's a dose of confidence that everyone can use.

MySQL Workbench has a nice synchronization utility that does just what I've described. You can make one to many changes in a model that's been reverse-engineered from an existing database or used to previously forward engineer a new database, and then synchronize that model to the target database. MySQL Workbench will first preview all of the changes that it found between the model and the database and let you preview the DDL it will use to make all the changes so you can eyeball what's about to happen to your database.

Keep in mind that you can use your models as a form of version control for your database. Perhaps you want to try out different indexing schemes and go through many iterations of testing which scenarios are best.  After about the 10th or so go around, you want to revert back to a scheme that you used early out of the chute that you now know was best - only problem is, what exactly was it?  By going back to a version of the model that had that particular indexing scheme in it, you can aim your model at your database, do a synch, and you're where you want to be.

Of course, MySQL Workbench also lets you synch a database to a model and so update your model if some changes have happened in the database that you want to capture and retain in your model.

What's it Cost?

Now the only real fly in the ointment with using modeling tools for the things I've just described is they can be costly to buy. It's not unusual to pay up to $4,000 on a serious modeling tool that does change management and more. But the good news is that MySQL Workbench Community Edition can handle all the core data modeling functions you'll likely need, and the Schema differentiation and synchronization functions so you can do compares and synchs.

You can download MySQL Workbench from the MySQL web site at http://dev.mysql.com/downloads/workbench/.

MySQL Workbench is available on Mac, Linux, and Windows. Give MySQL Workbench a try and let us know what you think - your feedback and comments help ensure we continue to build high-quality tools at MySQL. You can also visit the MySQL Workbench forums and both get help and post feedback at http://forums.mysql.com/index.php?151.

And as always, thanks for using MySQL!