SSDT 2010, 2012 with TFS integration brought about a couple of different options when you are looking to version your database projects. Before deciding on how you should version your projects, you first have to ask what you are trying to accomplish through versioning. Is this simply an audit trail, are you forced to meet certain regulatory requirements or are you simply trying to keep a copy of the evolution of your code? Depending on the complexity of your situation, will sway your decision in the approach you take. The more complicated your scenario, actually the easier the decision will be on your approach. There are basically two options that I’ve used. 1) TFS Versioning 2) Database Snapshots

1) TFS Versioning: I have been at what you might consider a nightmare scenario development shop for branching database code. We had several versions of the application in development, while having hotfixes introduced in the middle of the multiple development cycles. In addition, a release was rescheduled to go in front of another. The amount of complexity the scenarios caused required lots of communication and coordination by the developers. The diagram below depicts the branching / versioning strategy that I’ve used to accommodate that nightmare scenario. This startegy was a version of Microsoft’s feature branch strategy. Microsoft has some excellent documentation on how to define your branching and merging strategy. In the end, do what works for you. If your developers already have a branching strategy, my recommendation is to use the same branching and versioning strategy as the rest of the developers in your IT shop.

2) Database Snapshot: This is much easier to implement than the previous versioning strategy. If you have a simple environment that doesn’t require significant versioning complexity, this is definitely the way to go. Database Snapshot is a topic that has been written about and demonstrated on many other blogs. What I want to outline, is the option of using database snapshot as a method of versioning your code.  Utilizing a snapshot, allows you to take a point in time snapshot of the code in your database. Using a good naming strategy, you are able to use snapshots as  a type of versioning that can be compared against previous snapshots or your current project work.

1. Create your project snapshot and give it a name like “<project name>_baseline.dacpac”

The baseline dacpac gives you something to start comparing against, before you start developing. Throughout your development cycle, you can continually compare your current project against the baseline snapshot to determine differences.

2. Right click on an existing dacpac or your project and select Schema Compare. This is a method of generating a difference script or simply looking at the differences between your project and the snapshot.

I hope I have given you something to think about when deciding which direction to go with on versioning your database project. There are many scenarios and strategies that can work for this issue. The best method is the one that will work best for your situation. There truly is no substitute for testing, experimenting and trying out different strategies.

Tip: If you want to evaluate TFS or your shop cannot afford to buy a full version of TFS, there is an express version available for free here. I’ve used this version to install on my laptop for presentations and play with.