Manage The Workflow Of Database Modifications

Why we do not have tools to ManageTheWorkflowOfDatabaseModifications? I mean, of course Oracle and SqlServer give you tool to manage the database, but they make it work for experts in the field (DBA), and provide almost no help for the expert:

• Most (all?) tools to manage a database do not help you with refactoring of your data model without loosing data.
• Most (all?) tools to manage a database do not help you with version control of your data model
• Most (all?) allow you to configure security in an all or nothing approach, but do not include integrated support for a modification process.

What do I mean by integrated support for a modification process?

Well I mean a tool that deals with database management as something that most be controlled over time, not something that plain has to be restricted to privileged few. Database management tools a great for projects where you have a really small number of DBAs and you don't want anyone modifying your database, but.. is that really the way the world works? do databases really need to evolve that slow? or is it because we have really bad tools to deal with database model refactoring and change control? Managing a database is really a job for only one person? shouln't we create tools to make it easier for a team to evolve a database? why none of the big database vendors (or database tool vendors) offer a tool like this?

I have seen tools for precisely this, though they've never achieved much popularity. Perhaps this is because database development is traditionally a single-person effort for all but the largest projects, and works quite well that way. I've personally managed a 170 table schema deployed in 70 locations, in addition to developing client-side software at the same time.

Yes, and imagine the nightmare it could have been for another DBA/Developer, if, for example you would have gotten sick (or resigned) when things were only 50% done.

• I never left a version change in a partial state, individual version changes were kept small for just this reason, and others did take over as DBA when I was away.

Managing the database installations was the least of my challenges, and I personally find database administration tools of any kind more of a hindrance than a help. I usually wind up writing little utilities to do admin tasks, change tracking, update deployment, etc., as needed. Change tracking and deployment, for example, is almost trivial.

I guess it was a almost trivial because you didn't work with a team.Even with relatively small teams (around 3 persons) it becomes IMO a hindrance to keep in sync with the changes everyone makes. (It is just too easy to overwrite someone else's stored procedure, change to a table, or trigger, and unless you keep a lot of discipline by manually backuping before every modification you will have conflicts between the changes of the people of your team (or wasted time because of excessive serialization)

• I worked in a team. I was the primary DBA and also worked on application development. Others worked solely on application development and generally requested database changes through me except when someone else temporarily took over as DBA. This is a traditional approach, but it works well.
  • And how much time the other team members wasted waiting for you to make the changes? I mean, I agree it may work well under certain circumstances, but the question that remains, wouldn't it work better, under a wider range of circumstances if we had some some software to ManageTheWorkflowOfDatabaseModifications
  • I doubt it. I suspect much more time would be wasted in sorting out the mess resulting from uncoordinated changes to a database that must be coordinated in order to be correct, avoid redundancy, ensure constraints are in place, etc.
  • Same with SVN. It would just take too long to train people to use SVN so I don't think it is useful today. Or CVS. People know how to use file systems on computers and know how to make backups so there is no real use in SVN --DevilsAdvocate
  • Changes to the database are performed by one DBA at a time and logged to support deployment. Thus, database changes are inherently controlled. SCM makes sense because multiple developers will be making multiple, otherwise uncontrolled changes to a potentially diverse source base, which works because application development can be effectively partitioned. Database administration can not. Having multiple developers simultaneously updating a single database schema doesn't work unless you can guarantee that every developer has sufficient knowledge of the schema to ensure that new tables aren't redundant, and that new columns are appropriately validated including foreign key constraints, cascades, etc. This is almost never the case, especially when a single DBA can effectively manage even very large schemas, but few application developers are aware of any aspect of the schema beyond the portion(s) they're using in their respective applications or portions thereof.

Once you've written a splot of SQL to implement a schema change, it's only a small step to plunk it into a deployment tool that invokes the appropriate SQL statements based on a current version number.

[And what prevents you from making a mistake when writing the splot of SQL to implement a schema change? Wouldn't it be better if the tool could do that for you? It is easy for errors to happen, specially if you need to merge the changes of several developers]

The SQL is tested before being deployed, as with all components of the application, and there was no need to merge database changes from several developers because database changes were made by the DBA. While I can see some negligible gain in having a tool automatically record 'ALTER TABLE Customers ADD Blah VARCHAR2(20)' when I make mouse clicks, as opposed to just typing 'ALTER TABLE Customers ADD Blah VARCHAR(20)', I'm not sure it would warrant the training/maintenance/licensing/etc. costs of such a tool considering such changes are a very small percentage of the overall application development/maintenance effort. Feel free to write such a tool, however. I'll be happy to test it for you.

Hmm we should KeepAnOpenMind.. databases become corrupted and if you keep backing up the database every day, all your backups may be corrupted... a version control system of some form and better management tools could be useful.. maybe even storing the binary database storage files (or whatever medium they use) on SVN or a similar source control itself.

---

MarchZeroEight