Looking for a system to manage and control database changes
I'm a DBA in a growing software company with multiple teams and projects.
Development of our products is highly dynamic and modifications to the database (not meaning data changes) are frequent. Monitoring and managing these changes has become quite a task, especially since they are applied to a variety of schemas and locations.
In the past, each developer with some database knowledge would have made database changes by himself. Come version deployment time, I would have created a changes-script of all changes, or used a full export/import to the target schema.
This method of managing the changes was full of holes overheads. We recently replaced it - requests for changes are now sent to me, and once I can spare the time, I perform these requested changes. When the time comes, copying of changes to target environments is performed as before (changes-script or full export/import).
This new method also brought new problems: Developers are now being delayed while waiting for me to perform their requested changes and I am also spending much valuable time in performing them. These delays and the complexity of this task are only expected to grow as projects will add and changes will become more frequent.
I need to form a system which will reduce our developers' waiting time for database changes, while keeping the different schemas' structure integrity high. I would very much like to hear from your experience about this. How do you manage database changes? What is the process flow between developers, the DBA(s) and the database itself? How are versions being deployed? I've been suggested a "Schema-Per-Developer" system – Is that applicable?
Any reference to articles on the subject or assisting tools would be greatly appreciated.
It sounds like you (not you personally, but your entire team) don't spend enough time developing your data model. I know, these days many people in IT don't even know what it means, but you, as a DBA, must. If the model is thoroughly designed you won't need to spend so much time changing it during the software construction phase.
In my view, all changes to the physical model must go through the hands of a DBA. It could mean more work for the DBA and may upset some of the (less disciplined) developers but with proper communication will always be beneficial to the project.
Does your software company have coding standards and a configuration system for managing code versions and migrations? If so, the same paradigm needs to be applied to your database standards. I would guess your database maintenance cost is probably pretty high.