Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
1  
What is it that you really want to control? What do you want to see the tool do? A better place to ask this question is at: dba.stackexchange.com/?as=1 –  Emmad Kareem Mar 28 '12 at 9:01
    
@EmmadKareem I would basically like to "check in" SQL changes in much the same way we currently check in source code changes. I'd like to keep a list of changes made, and have the ability to revert to a previous version if necessary. This would include schema and database object changes. –  msigman Mar 28 '12 at 17:40

7 Answers 7

up vote 6 down vote accepted

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.

share|improve this answer
    
+1 as far as I know, Database Projects are the "official" MS solution to this. Definitely give yourself some time to read about it and play around with it though, as not all of its features are obvious up front, and it has quite a bit of functionality. As you read about it and work with it, keep in mind that even though you're working in Solution Explorer, the model/strategy for working with database projects is a little different than for class libraries and the like. –  nlawalker Mar 28 '12 at 21:21
    
I have tried this approach in two locations. One location failed because the majority of users had not used Visual Studio or source control in general and only used with SQL Management Studio. This location created a homebrew SQL script based solution instead. The approach worked great in the second location where everyone was familiar with Visual Studio and source control concepts. –  NeoModulus Apr 2 '12 at 13:27

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.

share|improve this answer

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 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.

share|improve this answer

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!

share|improve this answer

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

share|improve this answer
    
Why are you talking about a solution which is already in the question? Seems like you doesn't answer to the question, nor bring anything the OP doesn't know already. –  MainMa Mar 28 '12 at 18:40
    
@MainMa, I did not see that this product is listed in the question text or in the associated link, so I listed it in my answer. –  Emmad Kareem Mar 28 '12 at 20:41

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.