Database Project Versioning

, 2014-01-30

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.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads