Script First Change Management Gains Seal of Approval
Many have written in with positive experiences using a Script First change management approach.
David Writes:
I like the coverage of database change management. We also go with the script first methodology. Over time SQL skills have become a required skill for developers in our shop. The developers creating, fixing, and modifying applications understand the changes they’re making better than anyone else so they’re also the best people to be managing the database changes. Changes are done by script and have to be repeatable.
So, every script needs to check whether it’s already run or not and behave appropriately. The scripts are stored in our Subversion version control system and are checked in with other code changes. They go into a separate folder and do need to be run in the correct order, but we’ve worked out a system over time. Check-ins to the version control system trigger an automated build by our Team City continuous integration system which builds the source code and runs the database script each time.
Developers are either working with a branch for large projects with their own database that’s a clone of the production database or are working on bug fixes to the trunk which has its own database cloned from production. Developers were unaccustomed to this process when we first instituted this system, but now the scripting is just part of the development process and nobody really thinks about it anymore.
Builds are done regularly and problems with database scripts and code are discovered early in the development process and are pretty infrequent these days. Deployments happen often and are not a big deal for our developers or users and go very smoothly. We’ve made many changes to this process over the last eight years or so we’ve followed it, and have found and fixed several issues along the way, but it has been worth it. I can’t recommend this process enough!
Mark Writes:
I was intrigued by your approach to managing the "script first" change scripts for multiple developers in an agile environment. I struggle with "comparison" tools beyond just the time they take, as I find people can become complacent with them, particularly when it comes to required data transformations that result from DB object changes…And I don’t like the feeling that I have lost control on my projects ;-).
The ultimate change management system that I have experienced was with a "standard" software package I worked with on a project with CSC (thousands of implicated developers world wide). Large software providers for packages "à la SAP" have to manage branching on multiple levels through multiple teams (i.e. root/project/release/dev team/developer/etc.). Their proprietary system used a hierarchical publishing mechanism for managing DB object changes entirely automatically by managing all DB meta data within a development DB that was then used to maintain a separate DB version for each isolated code level and version…The DBA wasn’t even involved in change management. It was really nice to work with.
However this change management core was administered by a large team itself, and I find your approach very elegant for smaller development houses. Thanks again for revealing your solution…I can see real potential for it in my own development endeavours.
I had a design for completely managing scripts where each database instance and each change script were stored in a database, along with the change history for each database. Scripts were also associated with a story or feature that could be released. Then , when it was time to apply changes for that feature, the appropriate change scripts could be deployed from the system, or packaged into something that could be deployed remotely.
A command line interface to the system was created allowing it to easily be integrated into any continuous build process as well.
Thanks for all the great feedback on this topic. Tomorrow we’ll be moving on to something new. If you have a topic you’d like to see covered please drop me an email at btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Understanding SSIS Package Configurations
SQL Server Integration Services (SSIS) provides package configurations that help you to manage such configuration during deployment of the packages. In this article, you will learn about package configurations, and the types of package configurations.
Featured White Paper(s)
Encryption Key Management Simplified
Managing encryption keys is a fundamental step to securing encrypted data, meeting compliance requirements, and preventing da… (read more)
Featured Script
dba3_ExtendedProperty_level2type_demo
demo of ExtendedProperty level2type property use… (read more)