Versioning SQL Server?

Question

My development group uses Visual Source Safe for version control; this choice was originally made due to cost and its tight integration with Visual Studio.

As our repository has grown Source Safe has really started to show its limitations and we are considering moving to another solution. Up for discussion are Team Foundation Server, Subversion, Git, and Mercurial.

We are largely a data shop, so another major factor for us is being able to easily version SQL Server 2005/2008 projects. This is one of the benefits of using Source Safe, and also of Team Foundation Server - the integration with Microsoft SQL Server Management Studio.

I'm wondering if anyone has had experience versioning SQL Server with Subversion, Git, or Mercurial and can provide some solid pros/cons for each of these systems, as well as how you went about implementing them.

Answer 1

My honest answer is don't do any integration with your database tooling and SCM if you can avoid it. Use the filesystem where possible. It's another layer of integration which is going to be a pain. Small separate tools are better than a behemoth.

We use Subversion and SQL 2005 together in the following manor:

• We use TortoiseSVN only. No VS/SSMS integration at all.
• We have a principle of "automate everything", so we never rely on GUI tools to do work.
• We keep all scripts inside SVN along with the code. The code, schema and scripts are versioned together.
• Schema changes are numbered in order of application i.e. 000-create-table-users.sql. We write down the maximum script number deployed in each environment. Each script performs a migration to the next database r number. When we deploy, we check out the source and run all scripts from the last version number to the highest number.
• Any non-schema scripts (sprocs/views) are idempotent (can be executed any number of times with the same result). They are applied via a nant plugin we wrote. These are replaced every time we deploy. Don't forget to refresh your views!
• We avoid any scripts where possible anyway as we use NHibernate so there are less problems with script versioning anyway.

From this structure, we can re-create the environment and database at any point in time on any machine which is important.

We do NOT use it for unit testing however - we rely on the NHibernate schema generation to do this on top of an SQLite database.

The only negative point we've encountered has been making sure that developers adhere to the process. Herding cats is a very appropriate description.

Answer 2

Visual Studio Team System 2008 Database Edition (codename "DataDude") is what you need.

It allows you to version your database objects in ways that will blow your mind. (eg upgrade a customer site to a specific version, or rollback to a previous version without destroying any data).

Check out the features at Gert Drapers' blog, starting with this post.

Or if you prefer a podcast, listen to DotNetRocks with Chris Sells in show 494.

I don't know whether you're limited to TFS for source control, when using DataDude -- but it is the undeservedly "underhyped" member of the Visual Studio family.

Answer 3

This might be a useful tool for you: http://www.liquibase.org/

It's designed so that it's easy to version control in any system, and manages your upgrade scripts in a sane way.

Answer 4

Git and Mercurial are the only ones you should consider IMHO, the other 2 are too old-fashioned. Modern SCMs should treat branches like git does.

For git vs. mercurial comparisons see: http://rg03.wordpress.com/2009/04/07/mercurial-vs-git/, http://www.russellbeattie.com/blog/distributed-revision-control-systems-git-vs-mercurial-vs-svn.

I have no past experience with SSMS SCM integration though, but AFAIK neither of the systems mentioned (except from TFS) have one. I wouldn't call it a disadvantage tho - git GUI for example is a pretty handy tool, which you'll find more enjoyable than such an integration. This is at least my case when moving from SVN (with VS integration using Ankh) to Git (with no integration at all)...

Answer 5

Mercurial has VS integration with VisualHG, if you think DVCS is the way to go. We use that for C++/C# projects in our shop, and it works well enough. (OTOH, I've never used any "full" integration, so I'm happy to work with the explorer extension and/or command-line for detailed VC work.)

Answer 6

We've now added VSS support to SQL Source Control, which integrates with SSMS to provide fully integrated source control for database development. To try this out, please visit: http://www.red-gate.com/MessageBoard/viewtopic.php?t=12265

Answer 7

TFS is missing a few features of VSS, notably keyword expansion. If you don't embed revision keyword info within your source files, then it should not be a concern.

Answer 8

There are potentially quite a number of alternatives - SQL Server Management Studio (SSMS) supports integration with any Microsoft Source Code Control Interface MSSCCI Provider. So you can broaden the search to source control systems that feature an MSSCCI compatible provider.

In SSMS, Check out Tools -> Options -> Source Control to see what provider plug-ins are installed on your system.

For example, Team Foundation Server's integration with SQL Management Studio is courtesy of the TFS MSSCCI Provider. I think there's a provider for CVS/Subversion ("Aigenta Unified SCC") and so on.

As to a pros/cons list, I think provided there's a compatible provider, you can open the question up to a wider audience. My main experience is with VSS, TFS and Subversion. It really comes down to your team, and environment. Can you elaborate more on your environment?

E.g.

• would you be interested in establishing CI (continuous integration)?
• automated builds/automated versioning?
• support for multiple environments?
• configuration management?
• what team size do you have? likely to have lots of merges/branching etc?
• do you have a bug tracking system in place already (you get work items/bug tracking as part of a TFS roll out)?