1. Each Developer Should Have Their Own Database

The first thing to mention about our database environment is that every developer has SQL Server installed on their development machine and develops on a local copy of the database. This allows the developer to make changes without having to worry about how it will effect other developers. It also allows them to test using stable data (it's difficult to test your code if other people are making changes to data that you need).

2. Script Database Changes

When a developer needs to make a change to the database they make their changes to a script and check it into VSS. These scripts are named in a way to ensure that they run in order. We use the product version (<major>.<minor>.<maintenance>) followed by a script number and then a readable name (eg, 1.0.0.100 My Script.sql). The naming conventions allows us to just process the scripts alphabetically instead of having to create a build configuration file for the order. We leave gaps in the numbering so that we can add scripts without having to rename all of the scripts that come after it. For example, if we add another script to our build after 1.0.0.100 My Script.sql, we would call it 1.0.0-110 Another Script.sql (see screenshot below).

[image lost in blog update]

3. Scripts Should Only Run Once

In order to prevent a script from running multiple times, we keep track of what scripts have been run in the database. The table is fairly simple, the key field being the name of the script (the name of the file without the extension).

Our table looks something like this (we actually have more columns, but these are the most interesting ones):

[image lost in blog update]

Before a script does anything interesting we make sure that it has not been run yet by checking this table. If it has been run, it should just exit gracefully. The sql for this looks something like this:

DECLARE @ScriptName NVARCHAR(255) 
SET @ScriptName = '1.0.0.100 My Script' 
IF (SELECT COUNT(*) FROM VersionHistory WHERE ScriptName = @ScriptName) > 0
BEGIN
PRINT 'Script ' + @ScriptName + ' has already been run.'
RETURN
END 
PRINT 'Run script ' + @ScriptName + '.' 
INSERT INTO VersionHistory (ScriptName, StartTime)
VALUES (@ScriptName, GETDATE()) 
-- Your SQL here. 
UPDATE VersionHistory
SET EndTime = GETDATE()
WHERE ScriptName = @ScriptName

4. Script Objects Separately

There are some database objects that can easily be recreated without having an impact on data such as stored procedures, functions, views, etc. We place each one in a separate file so they can be easily maintained.

During the build process we place them all in a single file per type (eg, a single script for all stored procedures). This ensures that they are run in the correct order (we place them in order based on dependencies to other objects). It also makes it easier to manually upgrade databases when necessary (it's easier to run a single script then a hundred or more).

5. Create Your Database With Your Scheduled Build

We have a nightly build process that creates the database. The build basically grabs a copy of the database from the previous release to production and runs the latest version scripts on it. When we release a new version of the product, the scripts are removed from the current branch in source control so that the script directory only contains scripts for the current release.

We have recently released version 8.1 of our product and have been using this process for many years. Although there may be better ways of managing your database while in development, this process has worked well for us.

Jeff Atwood had a great post today about automating your build process (The F5 Key Is Not a Build Process) and it inspired me to finally write this post (I promised it a while ago - Making the Build).

As mentioned in my post Making the Build, an automated build process is very important. However, most software teams have a tight schedule and budget and find it difficult to justify spending time creating a proper build process, especially when nobody on the team has previous experience creating an automated build process.

Before I get to the benefits of an automated build, let me first describe the build for our product.

Our build process is fully automated. We have it scheduled to run on a dedicated machine every night. The build includes many different things, including compiling the source code, building the database from the previous version of our product using scripts, generating some source code, updating assembly attributes (such as version, company, etc), running unit tests, and more (too project specific to bother mentioning).

The benefits that we have found for our team are as follows:

1. Frequent builds -  Before we automated our build process, it was run manually (though we've always had scripts and utilities to help). Unfortunately it was sometimes weeks between builds. If there was a problem with the build it was very difficult at times to track it down.
2. Standard builds - Back when we ran our build process manually, it was not always run the same way. Often times steps were skipped because they took too long and didn't seem important (sometimes it wasn't, but when it was, it could take a lot of time to figure out what the problem was).
3. Build status visibility - Every developer gets an email every day about the status of the build. If the build fails due to something they checked in, it is usually easy to find the problem because it is only a single days worth of code and was checked in only yesterday - hopefully they still remember what they worked on yesterday :).
4. Easily get the latest build - The build process places the completed build (at least the parts of it the developer cares about) into a shared directory that all developers have access to. To run the build, they can simply copy the build onto their machine and run a simple utility to attach the database. This makes it very easy to debug problems with the build.

If you decide to create an automated build process, here are a few suggestions (some of the advice is specific to building .Net projects, but I think much of it should hold true for other technologies as well):

1. Fully automated - I can't seem to mention this enough :). The process should be able to run in the middle of the night on an unattended machine without any manual setup or teardown process.
2. Run regularly - The sooner a problem is found, the easier it is to fix. We build nightly, but many people build even more often than that.
3. Run in a clean environment - In order to prevent false positives with a build (the build succeeds but shouldn't have), the first task in your build should be to create a clean environment (create a new directory or clear out the directory that you are using). This is especially important in finding circular references.
4. Use proper tools - You don't want  We use FinalBuilder. The tool provides a visual interface for creating your build. I would not recommend using batch files, MSBuild (as your primary build tool anyway), or NAnt. These can be very difficult to maintain. You don't want to get into a situation where every time you want to make a tweak you have to relearn the whole build process. If your team has a dedicated build engineer, then MSBuild or NAnt are great tools.
5. K.I.S.S. - Keep It Simple, Stupid. The simpler you make the build process, the more likely it will be for people to use it correctly. For example, in our build process, the only thing developers need to do to integrate their project with the build is to check it into the correct location in VSS. We have a utility that will make sure all the projects get compiled in the correct order based on the references in the project file (Compiling Multiple Projects Without a Solution).
6. Don't underestimate the effort - It took me a couple of weeks full time to create our current build process. Of course once it's setup, and assuming you used the proper tools, it should only take a few minutes to make adjustments when necessary.

Many people also advocate that build should be able to be run by any developer on their machine. This hasn't been very realistic for our project due to the size of the project and the cost of the software we chose to use (FinalBuilder isn't exactly free). However, it is a goal that I approve of.

If your project doesn't currently have an automated build process, I hope this article has encouraged you to create one. Good luck.

I've posted a new article on Code Project. It is titled Compiling Multiple Projects Without a Solution. The article shows how to compile a multi-project application without using solution files (yes, it includes source code).

The code creates a MSBuild project file that will allow MSBuild to compile a list of VB.Net or C# projects in the correct order. The list can be loaded based on a root directory that contains .vbproj and/or .csproj files or a text file that can contain a combination of directories, project files, and/or other text files.

I am of the belief that an automated build process is perhaps the most essential element in producing a quality software application. The build process can be used to enforce good practices within the development team and also detect any issues within the application in a timely manner (the sooner you know about a problem, the less code you have to look at to determine what caused it). Even a small team with a simple application can benefit from an automated build process.

Recently I've gotten the opportunity to recreate the build process for the company I work for. The product is over 5 years old, is fairly large and complex (well over 200 separate .Net projects as well as database and legacy code), and has grown a fairly complicated build process using a combination of batch files and custom executables.

The main reason for recreating the build process is due to the difficulty of maintaining it. There were quite a few batch files and it wasn't easy to run. We averaged a build about once a week (builds should be run several times a day, nightly at most) and the unit tests weren't run very often and if they were, the results often times weren't published (it could be months before you find out a unit test no longer works and then who knows what changed that broke it).

I started building my own build software using Windows Workflow (see my April 28th post, Windows Workflow and Your Next Build System). Although it probably could work and is a great way to learn Windows Workflow, I soon remembered one of my life rules, always use the right tool for the job.

I decided to try FinalBuilder instead and boy am I glad I did. FinalBuilder (FB from here on) is a great build tool. They have dozens (maybe hundreds) of different actions (that's what they call activities or tasks) available to use, such as MSBuild, get from VSS, run SQL, update AssemblyInfo, NUnit, send email, etc. They also allow you to write script (either VBScript or JavaScript) that is run during the build. If that isn't enough for you, you can also create your own custom actions using .Net (or other supported languages).

I've looked at several of the build tools available out there today (especially the free ones :) such as MSBuild and NAnt and the thing that strikes me about these tools is that they seem to be designed for full time build engineers. I don't have time to figure out how to setup these tools let alone write a complete build process using them. Perhaps even more frustrating is that I actually did take the time to learn MSBuild at one point and had done some interesting things with it, however, I couldn't tell you the first thing about MSBuild anymore. I wouldn't use MSBuild to create an entire build process for the same reason I wouldn't use Perl to write an entire ERP system (write only code doesn't work well in a complex, constantly evolving product).

I am not sure if it would have taken longer to create the build process using another technology (such as MSBuild or batch files) or not, but the big benefit in using FB is in the ability to maintain it months later and, if I'm lucky, by somebody other than myself (I definitely don't want to be the "build" guy).

I'm sure there are other great build tools out there. If you know of one, feel free to leave a comment (I always love to find out about new tools :). I plan on writing another article within the next week or two that has more concrete tips for creating a build process (perhaps even some source code), so stay tuned.

Recently I've been messing around with the new workflow foundation developed by Microsoft and included in .Net 3.0. It is very cool! It has great design-time support, it's easy to extend with your own custom activities, and it seems to be very well documented.

At first I was a bit put off by the fact that it doesn't include a simple, run-time configuration process. They do give you the ability to host the designer in your application, but it appears that you have to write a lot of code to make that work (I didn't actually try it, but I did glance over the documentation). However, as I thought about it more, it seems that Visual Studio is a great way to reconfigure the workflow. It might be a bit of overkill for a non-technical person who just wants to reconfigure one or two of the activities, however, if they just go to the workflow designer, it seems pretty easy to use (but what would I know, I'm a developer :). On the other hand, if somebody that is more technically inclined wants to extend the workflow with code, they can do that easily as well.

If you are new to Windows Workflow, I think a build system is a great starter project. It's simple (as far as workflow is concerned anyway), it fits into the workflow concept very well, and it will make your build process easier to configure and extend (if your build process is anything like our current one, as long as it's working, the last thing anybody wants to do is touch it). Even if you don't use it for the build process for the entire product, it can make many tasks that you have to perform on a regular basis on your computer easier (such as preparing your machine for a new build).

Before you can start building a workflow, you will need to install .Net 3.0 as well as Visual Studio 2005 extensions for .NET Framework 3.0 (Windows Workflow Foundation). This will provide the necessary pieces that Visual Studio needs to create workflows.

For the build system I'm creating at work, I created a Workflow Activity Library (a project template available in the New Project dialog) that contains most of the activities that I want to use, such as GetFromVSS, CopyFiles, RenameFile, AttachDatabase, RunSQL, etc. Each activity has one or more properties that allow it to be configured (for example, RenameFile has a property for the path to the file and another property for the new name of the file). As you can tell, these are just building blocks for a build system. For the build itself, I create a Sequential Workflow Console Application (this is a new project type installed with the VS 2005 workflow extensions). If you have several different types of builds (such as a developer build, test build, and production build), you can create a separate project for each one.

So what makes Windows Workflow such a great tool for a build system? Other than making configuration easy, you can also disable activities. This is very useful, especially when testing. There are several parts to the build system that I'm working on (for example, compiling the code and building the database), each one is made up of several activities. I can simply create an activity that contains all the activities for that part of the build and, if I don't want to run it, I can simply click on the parent activity and set it's Enabled property to False.

The diagram below (image lost due to blog update) shows a very simple workflow diagram in the Visual Studio Workflow designer. It includes two activities, one of the activities has been disabled. As you can see, the Workflow designer is very similar to the WinForm designer. You start by picking the activity that you want to run from the toolbox (on the left) and dragging it onto the workflow. The workflow will show you the places that you are allowed to drop the new activity. Once you drop an activity onto the workflow, you can set the properties associated with it using the property window (on the right).

If there is interest, I might be willing to write a more detailed article for Code Project that includes sample code. So if you want to read more, make sure you leave a comment.