用于Visual Studio 2010和SQL Server 2008脚本以及数据库更新的源代码控制工具? [英] Source Control tools for Visual Studio 2010 and SQL Server 2008 scripts and database updates?

问题描述

我们当前正在使用Visual Studio 2010和SQL Server 2008 R2-开发使用(多个)SQL Server数据库的Intranet ASP.NET应用程序.

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

我们一直在将SQL Server的脚本(用于数据库)存储在源代码管理中，与任何工具(例如SQL Server Management Studio(SSMS)或Visual Studio)分开存储.也就是说，我们有一个文本文件集合，其中包含表，存储过程等的脚本；并且我们会在更新它们之前(例如在Management Studio中)将它们检入和检出源代码控制，然后再检入它们(然后使用SSMS中的脚本来更新数据库).

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.

我已经阅读了与SQL Server相关的源代码控制中StackOverflow中的一些问题/答案(其中一些问题可以追溯到StackOverflow的几乎开始)，但是还没有找到任何出色的解决方案.另外，某些条目早于Visual Studio 2010或SQL Server 2008或现在可用的某些工具.

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.

我还阅读了有关该主题的其他文章，例如Troy Hunt的文章(一个示例是: http://odetocode .com/blogs/scott/archive/2008/02/02/versioning-databases-views-stored-procedures-and-the-like.aspx ).

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具有数据库项目"的概念，但这显然是已弃用的功能，不建议用于新开发.

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)在Visual Studio 2010中创建一个SQL Server 2008数据库项目，并将其连接到源代码管理(例如TFS或VSS). [此选项可能需要Visual Studio的Premium或Ultimate或Team Database版本.]

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

缺点是:没有用于表和其他项目的GUI设计器，可以通过对列进行一些更改来轻松丢失数据(因为"alter"脚本删除了该列并重新创建了该列)，缺少了"format document"命令(可用)在Visual Studio中用于Web项目，但不在数据库项目中.)

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)在SQL Server Management Studio中使用Red-gate SQL源代码控制(和SQL比较).

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

这种方法的主要优点是您可以使用所有的SSMS工具和设计器.

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

缺点是设计完整性检查较少，没有查找/替换或全局搜索(无需安装其他加载项)，并且使用SQL Source Control生成的脚本在语法上与SSMS或Visual Studio(最终结果是相同的).

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

=====

您对替代方法有何建议?是否使用/偏好使用特定工具或实用程序将SQL Server数据库的源代码控制集成到SQL Server Management Studio或Visual Studio 2010中，以及如何同时更新/同步这两种方法?具有变化的开发和生产数据库?

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?

为此，我还查看了其他一些软件实用程序/工具(其中一些已在其他StackOverflow主题中提及):

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(可能的，尽管它是一个完全独立的工具；未集成到SSMS或Visual Studio中)

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

LiquiBase(Apache/Java，尚无.NET)

LiquiBase (Apache/Java, no .NET yet)

NeXtep(尚不支持SQL Server)

NeXtep (no SQL Server support yet)

DBSourceTools(集成程度不够)

DBSourceTools (not integrated enough yet)

推荐答案

我的组织一直在两个不同的项目中同时使用这两种工具，而我对RedGate工具的使用也变得非常偏僻.正如您提到的要获得全部功能一样，您需要RedGate的整个工具集，但是您还可以获得很多其他功能(包括重构或全局搜索/替换).为了将DEV同步到TEST/QA/PROD环境，我使用其Compare产品来构建脚本集(通常需要进一步检查).我真的不相信有任何工具可以更新使用中的模式，除非我准备快速恢复它.

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.

相比之下，我发现MS工具过于复杂，这使它们有些不灵活.当数据库中有一些需要同步到项目中的更改时，我们发现自己很烦，但是项目中的更改不允许在没有数据库更改的情况下进行构建……最终导致整个catch 22场景需要大量的手动编辑(并导致测试和购买RedGate工具).

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

这篇关于用于Visual Studio 2010和SQL Server 2008脚本以及数据库更新的源代码控制工具?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！