Whenever I ask friends who work in the IT industry, specifically .NET,  about application code source control, they always elaborate on how their web applications are set up, with every minute detail you can think of, which in turn I think is great, because I have learned a lot from others over the years about structuring projects.  But then I ask the question, “how about your database? How do you handle that?”, in which I get the response, “working on it., for now we use scripts” .

For some reason, database source control has always been the elephant in the room no one wants to address.  Microsoft technology gives you the ability to create a database project off your database, and will wrap the objects in a dacpac when you are ready to deploy.   Objects refers to tables, stored procedures, security, views, and functions. This post isn’t about setting up a database project, as that is a how-to in itself. This article is about giving you an option to set up your TFS source control for database projects, and what I have found in my experience to allow flexibility in development and deployment.

I have found that using a shared development database causes a lot of headaches, and doesn’t give you a true sandbox. If everyone is making their changes in the development database, even if they are checking their changes in through the database project, then, in my opinion the point of source control is moot. You will face issues when writing over each other’s changes at deploy time.  Every developer should have their own individual copy of the database, running a local database. This is something that will be painful in the beginning, but advantageous in the future. The problem with having your own local instance of the database is the size of your database, but fear not, I outlined some steps I took below.   These are the steps I took in order for the team to have their own instance.

1. Using your database project, publish it to your local instance.

2. Half the work is done. Your local instance now should contain all your database objects. The hardest part is to get your data over. I have not found an easy way to do this, as I spent a lot of time analyzing the tables that were needed for reference look ups. Any data tables, I didn’t bring over.  Data tables are any tables that grow as part of an automated/manual process, for example, an Orders Table would be classified as a data table, since customers place orders every day.  You don’t need to bring this over to your local instance, because you really would only need a subset of this, which can be done manually at a later time.  I wish there was a way to label your tables to identify which are reference and which are data.  Some would argue that in order to do a performance test, you would need this data, but that’s what your development region is for.

I used the Visual Studio SQL Server Object Explorer Data Comparison tool to do my imports. I don’t have a development environment, so I can’t go through the whole exercise, but what you would do is the following, the left would be your production database or UAT Database:



3. Step 2 will be a trial and error type of exercise, which is why I mentioned it would be painful. As I was doing this, I found myself going back and forth excluding the data tables I didn’t need.

4. After you are done bringing in all the needed data, you should create a backup of your local instance database, and then share with your team so they can restore. Trust me, your team will be happy you took the time to do it.  Only one person should do this exercise, because there is no sense in all your team trying to bring down the database.

After you complete the 4 steps, now everyone will have their own sandbox.  This now gives your team the ability to play in their own sandbox, and not trumph over each other’s changes. The structuring of your TFS Source Control is outlined below and explanation is outlined:

The following approach that I use must have a “Release Manager”. The Release Manager will be the person that will be in charge of keeping your branches clean.  Here is how I structure:

1. There are 3 repositories and 1 main branch. Development, Hotfix and Release are classified as a repository, and Main is a branch.

2. Your development repository will have all branches in development and being worked on. It’s the release manager’s responsibility to keep this up to date. Your Archive folder will have any development branches not currently in development.

3. Your Release repository will have a Release branch to release to your region.  Release branch will be used to merge into. You can have 2 development branches merge into one release branch.  Couple of examples I have outlined:

A. Bobby is developing on branch A. He is ready to release to Development. Bobby needs to merge to the Release Repository into Release Branch A. Release Branch A gets published to Development.

B. Sue is developing on  branch B, and wants to release her changes to Development.  She alerts the team she is ready to do a release, and Bobby says his testing is over, and she may release. Sue needs to merge to the Release Repository into Release Branch B.  Release Branch B gets published to Development. In this scenario, it is a matter of communication. Because now Sue’s changes will write over Bobby’s.

C. Lead Developer Lee realizes that Bobby and Sue’s releases should be tested simultaneously, and she asks them to merge their changes together.  Lee tells Sue and Bobby to merge their changes to Release Branch A_B.  Release Branch A_B will now be released to Development. Lee archives Release Branch A and Release Branch B.

Key point is that all releases will come from the Release Repository.

3. Main will always be your production code. Do not merge into main until your release has gone to production. A couple of case examples:

A. While Bobby and Sue are in development, there is a production issue.  Chris has been tasked to fix it.  Lee alerts the team to seize any releases, as Chris needs to do an emergency production release. Chris branches from Main to the HotFix repository and works on his changes there. When chris is ready to release his change, he branches to the Release Repository in a branch called “Release Hotfix DDMMYYYY”. The hotfix is released across all environments.

B.  Release Branch A_B has been released to production. Lee now merges from Release Branch A_B back to Main. The cycle starts all over again.

In a perfect world, hotfixes would have their own set of regions to release. Production issues should be released in another environment where you have a Test region and directly to Production region. This will save you the headache of having to halt all development for your team.

The key in running this environment is communication and having a release manager. Your release manager could be the lead developer or any developer on the team that wants to take ownership of coordinating releases.

Any other cases I have missed can be addressed by contacting me. Thanks for reading.  Your comments are welcome.

Structuring TFS Database Source Control

Leave a Reply

Your email address will not be published. Required fields are marked *