I've been in different IT shops with many different ways of releasing code to the database. Some have been very loose in their security and processes. Simply pushing code thru a sql tool straight into production, without even testing it in other environments. Sometimes it was written by the DBA or by a developer.
Other IT shops have had much more strict release management processes. Change control boards meet to discuss the changes and approve or disapprove of them. Actions occur based on these decisions.
Still other IT shops have had many layers of systems in the which sql code was released, tested, approved. Different groups pushing code to different servers along the way. Production pushes being the most complex, with a lot of people involved on conference calls to ensure proper execution of planned steps.
I have heard of the perfect world, where everything had been taken care of for us, and at the push of a button, code is released in a controlled fashion, with no problems whatsoever. none of us live there. So lets talk about the world we live in.
For production releases, we have instituted a process that forces as little human interactions with the code as possible. A tool had been created that executes the sql code. This code is stored in a source control system. There is a single document that describes the actual files to be pushed, with filenames. The files are all listed out in this file, within specific groupings and the tool can parse this data and manage it for us. We simply load a production file, and the tool pulls in all the needed files. We can select specific ones to be released, or release them all at once. We simply indicate the server to be released too, and hit a button.
This sounds easy, but it still has some complexities. With certain types of code that will be pushed, we have to do it in a specific fashion. For example, data that needs to be released should only go to certain servers. Pushing ad-hoc sql is not allowed. We've instituted a process for data changes to be included in a stored procedure. This proc is released, executed and dropped. I know what you are thinking, isn't this the same as a human executing the sql? Remember, this is one of the goals, removing the human interactions. The old way we'd do it was to execute the code in a sql tool. But we could select the wrong portion of code, or miss typed the proc name we released, or even put in the wrong parameters.
So, putting the SQL into a tool that performs the action removes that human interactions, the possibility of the DBA miss selecting portions of code. Putting the SQL code into a proc, releasing this to the appropriate database, executing it and dropping it, all from the release tool, makes it even more removed from the human interactions. By forcing this release to be performed and tested in multiple environments prior to production, we can be ensured that it should succeed, in production.
So far, so good. We've been able to successfully release code into all environments in the fashion with few goofups. Where previously we had instances that we executed code in a tool and caused issues. Performing a release is stressful enough. Why introduce more layers of problems with possible code issues.
Well, like it or not, thats the process we are supporting currently. There are parts i like about it, and parts i would like to alter, enhance, and so on. What I'd like to know is how others do their code pushes, mainly to production. Specifically, how you release data changes. There are few permutations to releasing a schema change, new objects, or security. Those are fairly cut and dried. But how we push data changes, I;d like to know how you do it.
Comment on your process, what you like, what you dislike, how you would do it in a perfect world. Lets all learn from each other.