Source Control tools for Visual Studio 2010 and SQL Server 2008 scripts and database updates?

Question

We are currently using Visual Studio 2010 and SQL Server 2008 R2 - developing intranet ASP.NET applications that use (several) SQL Server databases.

We have been storing scripts (for the databases) for SQL Server in source control, separately from any tools such as SQL Server Management Studio (SSMS) or Visual Studio. That is, we have a collection of text files containing scripts for tables, stored procedures, etc; and we check these in and out of source control directly before updating them (such as in Management Studio) and checking them back in (and then using the scripts in SSMS to update the database).

We would now like to move to a more integrated approach.

I have read several of the questions/answers in StackOverflow on source control related to SQL Server (some of them dating back to almost the beginning of StackOverflow), but haven't found any excellent solutions. Also, some of the entries pre-date Visual Studio 2010 or SQL Server 2008 or some of the tools that are now available.

I have also read other articles on this topic, such as Troy Hunt's articles (one example is: http://www.troyhunt.com/2011/02/automated-database-releases-with.html) and K. Scott Allen's articles (for example: http://odetocode.com/blogs/scott/archive/2008/02/02/versioning-databases-views-stored-procedures-and-the-like.aspx).

SQL Server Management Studio has the concept of a "database project", but this is apparently a deprecated feature, and not recommended for new development.

The approaches that we are considering at the moment are:

(1) Create a SQL Server 2008 Database Project in Visual Studio 2010 and connect this to source control (TFS or VSS, for example). [This option may require Premium or Ultimate or Team Database Edition of Visual Studio.]

This approach considers the script to be the "master" and the database is created/updated from that to synchronize with the script version.

The desirable features with this approach are: global search, find/replace, refactoring, warnings about missing indexes or invalidly-referenced items, etc.

The disadvantages are: no GUI designer for tables and other items, can easily lose data with some changes to columns (because the "alter" script drops the column and re-creates it), missing "format document" command (available in Visual Studio for web projects, but not for database projects).

(2) Using Red-gate SQL Source Control (and SQL Compare) in SQL Server Management Studio.

This approach essentially considers the database to be the "master", and the scripts are updated based on changes to the database design. In many ways, this is a closer match to the way that many smaller development teams work.

The major advantage of this approach is that you have available all the SSMS tools and designers.

The disadvantages are that there is less design integrity checking, no find/replace or global search (without installing other add-ons), and the scripts generated using SQL Source Control are syntactically different from the scripts generated natively by SSMS or by Visual Studio (the end result is the same).

=====

Do you have suggestions for alternative approaches, are there specific tools or utilities that you use/prefer for integrating source control for SQL Server databases into either SQL Server Management Studio or Visual Studio 2010, and how about approaches for updating/synchronizing both the development and production databases with changes?

I have also looked at a few other software utilities/tools for this purpose (some of these have been mentioned in other StackOverflow topics):

SQL Examiner (a possibility, although it's a completely separate tool; not integrated into SSMS or Visual Studio)

LiquiBase (Apache/Java, no .NET yet)

NeXtep (no SQL Server support yet)

DBSourceTools (not integrated enough yet)

Answer 1

My Org has been using both for two different projects, and I have become very partial to the RedGate tools. As you mention to get the full functionality you need the whole toolset from RedGate, but you get a lot of extras with that as well (including refactoring, or global search/replacing). For syncing of DEV to TEST/QA/PROD environments I use their Compare products to build out sets of scripts (which generally require further review). I don't really trust any tool to update an in-use schema unless I'm prepared to quickly restore it.

In contrast I found the MS tools to be overly complicated, which made them somewhat inflexible. We found ourselves in a pickle when we had some changes in the DB that needed to be synced to the project, but changes in the project that wouldn't allow it to be built without the changes from the DB... it ended up in a whole catch-22 scenario that required a LOT of manual editing (and resulted in testing and purchase of the RedGate tools).

Answer 2

One additional factor or criteria you may want to consider is where you and your team are most comfortable writing your scripts \ stored procedures, etc.

We've evaluated Visual Studio Team Database Edition (or whatever it is currently called these days) in the past and came to the unfortunate conclusion that we just were not comfortable and happy writing SQL from within Visual Studio. We're much more productive as a team writing SQL using SSMS. You of course might find the opposite to be true.

We're also in the middle of evaluating Red-Gate's SQL Source Control tool. The biggest plus for us is that it works within SSMS and is relatively frictionless. For whatever reason, their model maps a bit better to how we tend to do SQL development work.

Regarding deployment, both products appear to skew slightly more towards development work where you are deploying changes to a database that is on your servers. From an ISV standpoint this makes it a bit more challenging to generate deployment scripts to be executed on a database outside of you environment (e.g. database hosted on a client's server).

For that reason, I personally like the approach Microsoft has taken where you can generate a schema file which describes the schema of the database. I'm probably leaving something out, but I recall that this schema file is what is used to generate the scripts to update a target database. The beauty of this solution is that the change scripts could be different depending upon the state of the target database.

Unfortunately, if you don't have "DBPro" available (again, think client site for a moment) you're left with using VSDBCMD to generate the change scripts from the schema file. While this works, it would be nice if Microsoft exposed an API for VSDBCMD so one could create a GUI to make it easier for non-developer types to execute rather than having to use a command line tool.

It's difficult to imagine that VS Team Database Edition won't gain additional features in the future, so betting on MS probably isn't really a gamble assuming of course you can live with the present shortcomings of the tool.