14

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)

PhilipD
  • 419
  • 6
  • 13

3 Answers3

8

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

Kevin Dahl
  • 752
  • 5
  • 11
  • Thanks. Have you been able to develop a good workflow with the red-gate tools, or have you encountered any problems? For example, in my initial brief testing, it appears to require several steps to update a production database after working on changes in the development database - running SQL Compare as a separate application to generate the update scripts, checking (and changing) the scripts, then running the scripts in SSMS connected to the production database. Also, it appears to want to update some database items that I wouldn't normally update in production - users, roles, etc. – PhilipD Apr 14 '11 at 12:28
  • Once I was familiar with the tools the workflow was pretty smooth, but there has definitely been no getting away from manual edits and multi-step operations. For us, working on a growing data warehouse, this is desirable - I actually found it difficult to do such things with the MS tools. You can configure the Compare projects to ignore things like users and roles, and a multitude of other options: [Setting project options](http://www.red-gate.com/supportcenter/Content?c=SQL_Compare%5chelp%5c9.0%5csc_project_options.htm&p=SQL%20Compare) – Kevin Dahl Apr 14 '11 at 15:02
  • @PhilipD - You can configure a filter in SQL Compare to remove objects that you don't care about. Can you tell me more about the changes you need to make to the scripts? – David Atkinson Apr 14 '11 at 17:26
  • @Kevin - We're in the process of improving SQL Compare and SQL Source Control, particularly the management of migration scripts. If you're interested in sharing your thoughts on our early designs, please contact me at David.Atkinson at red-gate.com – David Atkinson Apr 14 '11 at 17:28
  • @Kevin and @David - Thanks, I was looking for options to exclude items in SQL Source Control, but now I see, as you have explained, that options to exclude users, roles, etc and to ignore collation, etc are set in SQL Compare (and not in SQL Source Control). – PhilipD Apr 14 '11 at 18:02
  • The filter feature in SQL Compare will be making its way into SQL Source Control soon. If you want to vote for 'ignore collation', there's already a request here: http://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/484227-ignore-collation-pk-name-fk-name?ref=title – David Atkinson Apr 14 '11 at 22:01
6

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.

Tim Lentine
  • 7,782
  • 5
  • 35
  • 40
  • 1
    We're relatively comfortable in either environment. However, SSMS does provide some GUI design tools that don't exist in Visual Studio. One aspect of red-gate's SQL Source Control that I like is that it essentially treats the database as the "master", and the scripts are updated after changes to the database - I think this matches more closely the way that small development teams often work. – PhilipD Apr 14 '11 at 12:20
  • I agree, though I would probably not characterize each developer's db as the master but rather a sandbox for development. Your source control repository becomes the master that changes are published to. – Tim Lentine Apr 14 '11 at 14:22
4

You don't integrate source control into database as such.

It isn't file based. Your database consists of tables, code, data, indexes, statistics, security etc: these all live in system tables.

Rather the copy+paste, see my previous answers...

We follow Martin Fowler's "Evolutionary Database Design" more or less

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • I'm not sure I understand. I'm not trying to integrate source control directly into the database, or keep the entire database in source control. I want to keep the SQL scripts (that can be used to generate and/or update a database) in source control, and I want to integrate this process as much as is reasonable into one of the development tools (either SQL Server Management Studio or Visual Studio). – PhilipD Apr 14 '11 at 12:15
  • Thanks for the links to your previous answers - I read both of them. And, thanks for the link to Martin Fowler's article - it is still useful and interesting even though it's almost a decade old. – PhilipD Apr 14 '11 at 12:17