If you are working with a group that wants to use an Agile methodology, there are some things you are going to need to do in order to keep up as a DBA. Personally, I like Agile better than other software development lifecycles (SDLC). I think that’s the reason I come to this topic a lot, from a lot of different angles. However, that doesn’t mean it is the only way to create success.
Because Agile is different, there are things you have to do differently in order be successful as an Agile DBA. The process is very different, and there are a LOT of pitfalls, if you try to use the same processes found in other SDLC methodologies. I’m going to do a series of processes you need to do to be successful as a DBA in an Agile world.
The first practice you MUST master is database change migration. Because your database evolves using Agile techniques, you don’t have a phase where you design the entire database. Moreover, you are probably going to experience in one cycle or another, the alteration of a table schema. Once you have data in a table, some modifications become very difficult to do without data loss. Data loss is often not acceptable. When migrating to a production system, data loss is probably not even an option.
In order for different programmers to work on different features, they most likely will have their own sandbox database. That way their database can be modified without impacting others. This is another reason for having solid database migrations. Using database migrations, a developer can introduce to others their changes to the database only after the supporting application code is complete, and not break the application for others who are not working in the same area. Shared database components benefit from this technique more than anything.
It doesn’t matter what migration tool you use. What is most important is that your migrations are bullet proof. You need to practice them many times before you go live. So, the best technique I have found is to modify your database in two phases. First, create a migration that becomes a part of your release code. Key to this is choosing a method that is repeatable over and over, regardless of the state of the database against which the migrations are being applied. They need to be able to run multiple times against the same database without failure. It is even better if you can roll back migrations. What is not acceptable is to have a surprise when you run a set of migrations when you deploy to production.
Your migrations must handle both schema change and data change. Reference tables are specifically important. Many times there are tables for which there is not user maintenance interface. They have to be populated with database migrations. Rather than doing a schema comparison, or data comparison to maintain these migrations, it is a better practice to design those migrations first. Test for the necessary conditions, and do the modifications based on the result of the tests. Test for data existence; add new data if it doesn’t exist; delete data if it is no longer needed; update data if it exists, but doesn’t match your new data.
The same is true of schema modifications. If you’re adding a new column to a table, test for the column existence first, and then add the new column if it does not exist.
The point is, if your migration tests before making database modifications to the schema or data, they can be repeated over and over against the same database without damage. If you don’t have database migrations working smoothly, you will fail as an Agile DBA. There is no reliable way to propagate database changes to others outside of database migrations. As the size of a team increases, the importance of migrations increases.
Cheers,
Ben