What is the preferred tool/approach to putting a SQL Server database under source control?

Question

I've evaluated RedGate SQL Source Control tool (http://www.red-gate.com/products/sql-development/sql-source-control/), and I believe that Team Foundation Server 2010 offers a way to do this as well (as touched on here http://blog.discountasp.net/using-team-foundation-server-2010-source-control-from-sql-server-management-studio/).

Are there alternatives, or is one of these considered the preferred/standard solution?

Answer 1

If you are using Visual Studio and creating a new project, having an "SQL Server 2008 Server Project" (see Add New Project → Installed Templates → Database → SQL Server) would be the easiest way to get everything under source control.

Strong points:

• You don't have to use a third party tool. Everything is already in Visual Studio.

• Applying changes to the database cannot be easier. Visual Studio does it for you.

• The source control is the same as the one you use for other types of projects: ankhsvn, TFS, etc.

Issues:

• The support from both Microsoft and the community is pretty weak. I imagine that this type of project is not very used, so when you have a problem, be ready to spend hours or days searching for a solution on your own.

• If, like me, you are using Change Data Capture to track inserts, updates and deletes in some or all tables of the database, you will encounter a cryptic error when applying the changes to the schema to the database. You can solve it, but the problem still exists in the first place.

• I'm not sure if you can manage an SQL Server 2008 Server Project created with Visual Studio from SSMS, and if you will, probably the UX will be slightly different. This is ok if you're using Visual Studio in your daily work. This is not ok if the project must be changed by a DBA who has only SSMS on his machine, and doesn't know how to use (and don't want to) Visual Studio.

Answer 2

You could use Entity Framework. Using the Model to Database approach, Entity Framework will create the sql scripts necessary to build your database. These scripts can then be added to any source control such like your other project files.

Answer 3

There is no single preferred tool/approach because - as with everything else - there is a large lump of "it depends".

Conceptually however I think its fairly simple - script the changes, version control the scripts, automate running the scripts in order.

In terms of "script the changes" - this doesn't necessarily mean that you have to hand code them, SQL Server (for example) will let you save a script instead of run the changes if you use the designer in the management tools.

For version control the scripts (and automate running in order) I have a pattern that involves sticking the SQL into application code a whole series of methods called "DoUpdateNNN" that do the updates - a former colleague did some nice code that iterates over those by reflection. Each schema update sits in a transaction so it can be rolled back if it fails. The nice thing about this approach (wrapping it all in code) is that if you do need to do something that is challenging in SQL then you have the full capabilities of the language to play with as a means to an end.

To make sure we only do what is needed, I have a table in the database that tells me what version it is (its actually a list of all the schema changes with the date they were run).

Given all of this, my update code can:

• Look for a database and if its not there will create it with just the version table
• Identify what version of the code the schema is at
• Run any the necessary schema updates, in sequence.

I do this because it is relatively simple and very effective i.e. it works - in the real world, really quite a lot. The only thing I don't cover is rolling the schema back - ideally one would do a full backup before a schema update (and this has not yet - in 15 years - been an issue for me).

Really need to get this opensourced and nuget-ed!

Answer 4

There is another solution out there, that takes a different approach:

http://nobhillsoft.com/randolph.aspx

this one is more of a 'fix it and forget it' type thing: a background service that checks what has changed in your database and automatically updates TFS. you just do your work, the history builds up on its own

Answer 5

This product may be what you are look for: RedGate SQL Server Source Control.

This post may give you some insight on problems you want to avoid when using automation of sc of db: programmers-hotfixes-of-db

Answer 6

The proper approach is a collection of sql scripts and a database migration tool to apply them. Any tool that is working in visual studio probably isn't effective here.

I am personally a bit partial to RoundhousE these days but there are other options out there.

Answer 7

One possibilty is to create an empty VS project and add your scripts to it. There are some minor things you have to do to make it compile (if there are other projects in your solution) but I think that is the simpliest without resorting to some 3rd party tool.

Figure 1 - SQL Server Source Control Project

At the top level is the schema (in this example we have four schemas), and then we have a pre-defined folder structure for the database. For example, Indexes folder has the index script files. We use this structure as well to create and deploy the instance and all the scripts. We use SMO (Sql Management Objects) to create the database and deploy the schema and wrote a small utility to call all the scripts and run them and log the results. Most of the scripts are re-runnable and we have folders in place for exceptions (RunOnce) or table updates because you can't drop a table that already has data in it.

Everything is pretty standardized and all the database stuff is in one project. So far, over 1500 scripts being maintained in source control.

I am not sure if this is the best, but it is a solution.