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)
Best Answer
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).