Script First Change Mangement Hurdles
Yesterday I proposed the Script First methodology of database change management which I developed while working on an Agile project with multiple developers making concurrent changes to the same database. There were a few hurdles we had to overcome I wanted to share today.
The first hurdle we discovered was overwriting changes from others. This was resolved by maintaining and versioning master scripts of all database objects. This is quite easy to do with SQL Server in that it has scripting capability built into SQL Server Management Studio. Always working from a change script allowed us to have separate databases while still maintaining a central authority on what is current. Associating the master script modifications with a story allowed us to know why a change was made.
The second hurdle was different levels of SQL skills within the team. Many could work inside the GUI tools of SQL Server Management Studio, or Visual Studio. So, we created database triggers that captured all database object changes, and then created a change management tool allowing the developer to create a change script from the change, update the master script, and commit both to version control associating it with a story.
The third hurdle was modifications to data or table schema. Those pretty much had to be hand crafted by a DBA.
The final hurdle was making sure change scripts were executed in the correct order. This was done by sequentially assigning a number to each change script. A simple PowerShell script would then execute the selected scripts in the order of the number assigned to the file. A history of change scripts applied to a database was retained so a change script could not be applied more than once.
This process was not completely perfect. It would break down when only some of the code was released. Change scripts would be selected by story, leaving some change scripts out. This could cause issues of reverting code, or unexpected previous state. Change scripts were written to not require a specific state in most cases…yet there were issues.
The best part of this methodology was that it could be completely automated. All developers had their own sandbox (isolated database). They could pull latest changes from the change scripts and apply them using the script execution tool at any time. In this fashion, developers shared database changes just a freely as code modifications…all through version control.
Because the scripts could be executed only once, we were able to run all change scripts in version control as they were newly committed to version control. This allowed code changes and database changes on our build machine to be synchronized, and allowed integration tests to execute for each continuous build without DBA involvement. The result of this process was that we had no surprises when a production deployment was executed. We executed production deployments all day long.
The whole system used tools provided by Microsoft, with as little as one man week developing tools to automate the processes.
I have worked with many different software development teams using different techniques. Most prefer the post code database comparison methodology. I find that to be too cumbersome and time consuming at the end of the cycle. I know script first is not perfect, and there are other issues with the process such that it won’t work everywhere. But I have found that it reduces a lot of surprises because shared database changes are isolated when being made, shared when completed, and easily deployed to multiple locations.
There’s some food for thought. Do you have any ideas how to handle some of the script first hurdles? Drop a note to btaylor@sswug.org, or leave a comment below. Let’s see what you think.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
How to Quantify Goals with Intangible Duties
In previous articles, I discuss the importance of quantifying your performance with numbers and results; as well as aligning them with the company’s business goals. But what about employees in positions with less tangible monetary outcome; how do we convert these results into measurable results? What should be communicating?
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)