This article is part 2 of my series on version control.
- Part 1 - Dealing with Code
- Part 2 - Tracking Changes
- Part 3 - Migrating Objects to Production
- Part 4 - Rolling Back
Introduction
It's been a busy week/month/quarter and your development team is finally ready to push out a new release of your application.
In this article, I will examine how I track the changes that have occurred in some time frame of development. The first section of this article repeats a bit of background from the other articles. If you have read them, skip to the My Process section.
My Environment
Before I delve further, let me explain the type of environment that I have used in each of the last few jobs. Basically I have (at least) three systems setup for each application. These are not necessarily, but usually are three separate servers. These systems are defined as follows:
- Production - The "live" system that is actually being used by users. This is the data of record, the system that runs the database, accepts orders, etc. and runs the business.
- QA - A copy of the production system that is used for testing. This is usually the system that is most often updated (overwritten). Developers should not have access to this system, instead this should be exclusively given to the testing or Quality Assurance group. Integration and system testing occur here.
- Development - The "sandbox" or "playground" for the development group. This is where items are developed, tested, and verified by developers before being submitted to formal QA. This would be where unit testing and developer integration testing occurs.
- Beta (optional) - A copy of the production system where clients can perform testing. This is exactly like the QA environment. This is a playground for clients.
- Demo - The last couple companies have needed a "demo" system for the salespeople to use. This functions like the Beta system, except that it is rarely overwritten (from a data perspective).
Changes in the SQL environment are new and altered objects, data seeds, imports of data, etc. All of these changes are first made in the development environment. They are then applied to the QA environment where the intended effects can be verified by our QA environment before being migrated to the other environments.
It is my job to manage the changes as they move from one system to another, along with the QA person, though in actual practice, I "build" a package that migrates change from the development environment to the QA environment. The QA person is then responsible for running my "package" against the other systems to apply the changes. This article discusses packages.
My Process
In a previous article, I showed how I store items in our version control system, in our case Visual SourceSafe. I will refer to it as VSS here for short. In the followup to that article, I mentioned how I tracked the changes that need to be moved to the production systems. Now I will examine how I actually handle moving those changes among my environments.
I build units of deployments that I will call "packages". These packages contain a series of SQL scripts that are applied against a server. The scripts may create or alter tables, apply security, create stored procedures, etc. I have even included DTS packages in my packages.
How do I build a package? Well, there are a few things that I setup in VSS. First, I create a "Rollover" project that I use to organize my packages. Inside this project, there is an "In Flight" project as well as inidividual projects for the releases I need to organize. In my current job, the releases are divided up by date, since we release every week or so a new set of changes. In my last job, we had projects named by version (major and minor) since we were releasing shrink wrapped software to clients.
Now that you know how things are setup, how do I track changes? Well, the tracking occurs over time as I make changes or add new objects. Everytime I check something into VSS, I right click the file(s) and drag them to either the "In Flight" project or a specific project inside "Rollover". I then choose the "share" option. This way I have a "copy" that is linked. Since I do this incrementally as I check in objects, it automatically tracks them for me. The only issues that I run into are the "In Flight" items. Sometimes I am unsure of where these items will be released, so the reconciliation sometimes causes issues.
Does It Work?
Of Course!!!!
Well, most of the time. The "In Flight" items sometimes cause problems, but at least I know which items I have worked on.
The other issue occurs when I rev an object and we assume it will be released on xx date with other development based on that item. I then modify the object again for a future release, but since it is still shared, the current release version is rev'd as well. This doesn't happen often, usually developers catch this, but sometimes I have to then delete the share, and replace it with a previous version (from VSS of course).
One more thing that usually prevents most of these issues is that I "branch" all the items in a release project as soon as we have reached the development drop dead date. In other words, we usually stop all development on Monday at 10am. At that time, perhaps even first thing, I select all the files in the release project and "branch" them (in VSS 6, this is the SourceSafe | Branch menu option). This preserves the versions for that release. At this point, if I need to change an item, I delete it from the release project, check it out from the original folder and then do a "share and branch" back to the release project when I check it back in.
Conclusions
This is a basic process that builds on the ideas I presented in Part 1 of this series. I know this may seem cumbersome to some, but it is a habit, has proven very stable, even in a multi-DBA environment, and is easy to maintain. Some may wonder why I do not just check the Date-Time stamps in each object folder, because I often find myself working on 2 or more releases (plus In Flight items) at the same time. This allows me to keep the items separate.
As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article. If you know of a better method or tool, please let me know.
Steve Jones
©dkRanch.net November 2001
