我读过很多关于数据库版本控制重要性的文章。但是，我找不到一个简单的解决方案如何检查数据库是否处于应有的状态。
例如，我有一个数据库，其中有一个名为“版本”的表（版本号存储在那里）。但开发者可以在不改变版本号的情况下访问和编辑数据库。例如，如果开发人员更新存储过程但不更新版本数据库状态与版本值不同步。
如何跟踪这些变化？我不需要跟踪更改的内容，而只需要检查数据库表、视图、过程等是否与版本表中保存的数据库版本同步。
为什么我需要这个？在进行部署时，我需要检查数据库是否“正确”。此外，并非所有表或其他数据库对象都应该被跟踪。是否可以在不使用触发器的情况下进行检查？是否可以在没有第三方工具的情况下完成？数据库有校验和吗？
假设我们使用 SQL Server 2005。
编辑：
我想我应该提供有关我们当前环境的更多信息 - 我们有一个“基线”，其中包含创建基本版本所需的所有脚本（包括我们应用程序的数据对象和“元数据”） ）。然而，这个“基本”版本的许多安装都带有一些附加的数据库对象（附加的表、视图、过程等）。当我们对“base”版本进行一些更改时，我们还必须更新一些安装（不是全部） - 此时我们必须检查“base”是否处于正确状态。
谢谢
I have read lots of posts about the importance of database version control. However, I could not find a simple solution how to check if database is in state that it should be.
For example, I have a databases with a table called "Version" (version number is being stored there). But database can be accessed and edited by developers without changing version number. If for example developer updates stored procedure and does not update Version database state is not in sync with version value.
How to track those changes? I do not need to track what is changed but only need to check if database tables, views, procedures, etc. are in sync with database version that is saved in Version table.
Why I need this? When doing deployment I need to check that database is "correct". Also, not all tables or other database objects should be tracked. Is it possible to check without using triggers? Is it possible to be done without 3rd party tools? Do databases have checksums?
Lets say that we use SQL Server 2005.
Edited:
I think I should provide a bit more information about our current environment - we have a "baseline" with all scripts needed to create base version (includes data objects and "metadata" for our app). However, there are many installations of this "base" version with some additional database objects (additional tables, views, procedures, etc.). When we make some change in "base" version we also have to update some installations (not all) - at that time we have to check that "base" is in correct state.
Thanks
发布评论
评论（11）
您似乎违反了“三个规则的第一条和第二条规则用于数据库工作”。为每个开发人员使用一个数据库并为您的架构使用一个权威来源已经很有帮助。然后，我不确定您是否有 数据库的基线，更重要的是，您正在使用更改脚本。最后，您可能会在 视图、存储过程和点赞和分支和合并。
实际上，Jeff Atwood 的这篇精彩文章中提到了所有这些链接： 让您的数据库处于版本控制之下。恕我直言，必须阅读。
You seem to be breaking the first and second rule of "Three rules for database work". Using one database per developer and a single authoritative source for your schema would already help a lot. Then, I'm not sure that you have a Baseline for your database and, even more important, that you are using change scripts. Finally, you might find some other answers in Views, Stored Procedures and the Like and in Branching and Merging.
Actually, all these links are mentioned in this great article from Jeff Atwood: Get Your Database Under Version Control. A must read IMHO.
我们使用 DBGhost 对数据库进行版本控制。创建当前数据库的脚本（以及源代码）存储在 TFS 中，然后使用 DBGhost 生成增量脚本以将环境升级到当前版本。 DBGhost 还可以为任何静态/参考/代码数据创建增量脚本。
它需要改变传统方法的思维方式，但这是一个非常棒的解决方案，我强烈推荐。虽然它是第三方产品，但它可以无缝地融入我们的自动化构建和部署流程。
We use DBGhost to version control the database. The scripts to create the current database are stored in TFS (along with the source code) and then DBGhost is used to generate a delta script to upgrade an environment to the current version. DBGhost can also create delta scripts for any static/reference/code data.
It requires a mind shift from the traditional method but is a fantastic solution which I cannot recommend enough. Whilst it is a 3rd party product it fits seamlessly into our automated build and deployment process.
我使用一个基于这篇代码项目文章的简单 VBScript 文件来生成 drop /为所有数据库对象创建脚本。然后我将这些脚本置于版本控制之下。
因此，要检查数据库是否是最新的或是否有尚未纳入版本控制的更改，我这样做：
I'm using a simple VBScript file based on this codeproject article to generate drop/create scripts for all database objects. I then put these scripts under version control.
So to check whether a database is up-to-date or has changes which were not yet put into version control, I do this:
您必须限制对所有数据库的访问，并且只允许开发人员访问本地数据库（他们在其中进行开发）以及可以进行集成的开发服务器。最好的事情是他们只能在本地访问其开发区域并通过自动构建执行集成任务。您可以使用 redgates sql Compare 等工具对数据库进行比较。我建议您将所有更改保留在源代码控制（.sql 文件）下，以便您拥有有关谁在何时执行什么操作的运行历史记录，以便您可以在需要时恢复数据库更改。
我还希望能够让开发人员运行本地构建脚本来重新启动他们的本地开发盒。这样他们就可以随时回滚。更重要的是，他们可以创建集成测试，以自动方式测试应用程序的管道（存储库和数据访问）以及存储在存储过程中的逻辑。运行初始化（重置数据库），运行集成测试（在数据库中创建绒毛），重新初始化以将数据库恢复到干净状态等。
如果您是 SVN/nant 风格的用户（或类似用户），并且在然后您可以在 DotNetSlackers 上阅读我关于此主题的文章： http://dotnetslackers.com/articles/aspnet/Building-a-StackOverflow-inspired-Knowledge-Exchange-Build-automation-with-NAnt.aspx 和 < a href="http://dotnetslackers.com/articles/aspnet/Building-a-StackOverflow-inspired-Knowledge-Exchange-Continously-integration-with-CruiseControl-NET.aspx" rel="nofollow noreferrer">http:// /dotnetslackers.com/articles/aspnet/Building-a-StackOverflow-inspired-Knowledge-Exchange-Continously-integration-with-CruiseControl-NET.aspx。
如果您是一个强制多分支类型的构建大师，那么您将不得不等到我写一些关于这种自动化和配置管理的文章。
更新
@Sazug：“是的，当我们使用基本脚本+附加脚本时，我们使用某种多分支构建:)没有完整的文章，有关于这种自动化的基本技巧吗？”最常见的数据库有两种形式：
第一种设置要容易得多，并且可以完全自动化从开发到产品，并在需要时回滚产品。为此，您只需要一个脚本文件夹，其中对数据库的每次修改都可以保存在 .sql 文件中。我不建议您保留 tablename.sql 文件，然后像 .cs 文件一样对其进行版本控制，随着时间的推移，对该 sql 工件的更新实际上会在同一文件中进行修改。鉴于 sql 对象彼此严重依赖。当您从头开始构建数据库时，您的脚本可能会遇到重大更改。因此，我建议您为每次修改保留一个单独的新文件，并在文件名前面添加序列号。例如 000024-ModifiedAccountsTable.sql 之类的内容。然后，您可以使用自定义任务或 NAntContrib 中的某些内容，或者直接执行众多 SQL.exe 命令行工具之一，针对空数据库运行从 000001-fileName.sql 到最后一个文件的所有脚本在 updateScripts 文件夹中。然后，所有这些脚本都会签入您的版本控制中。而且由于您总是从干净的数据库开始，如果有人的新 sql 破坏了构建，您总是可以回滚。
在第二种环境中，考虑到可能影响生产，自动化并不总是最佳途径。如果您正在积极针对生产环境进行开发，那么您确实需要一个多分支/环境，以便您可以在实际推动生产环境之前测试您的自动化方式。您可以使用与上述相同的概念。但是，您无法真正在产品数据库上从头开始，并且回滚更加困难。出于这个原因，我建议在构建过程中使用类似的 RedGate SQL Compare。签入 .sql 脚本是为了更新目的，但您需要在运行更新之前自动比较临时数据库和生产数据库之间的差异。然后，您可以尝试同步更改并在出现问题时回滚产品。此外，在自动推送 SQL 更改之前应采取某种形式的备份。在生产过程中，在没有人眼注意的情况下做任何事情时都要小心！如果您在所有开发/质量/登台/性能环境中进行真正的持续集成，然后在推向生产时执行一些手动步骤……那真的还不错！
You have to restrict access to all databases and only give developers access to a local database (where they develop) and to the dev server where they can do integration. The best thing would be for them to only have access to their dev area locally and perform integration tasks with an automated build. You can use tools like redgates sql compare to do diffs on databases. I suggest that you keep all of your changes under source control (.sql files) so that you will have a running history of who did what when and so that you can revert db changes when needed.
I also like to be able to have the devs run a local build script to re initiate their local dev box. This way they can always roll back. More importantly they can create integration tests that tests the plumbing of their app (repository and data access) and logic stashed away in a stored procedure in an automated way. Initialization is ran (resetting db), integration tests are ran (creating fluff in the db), reinitialization to put db back to clean state, etc.
If you are an SVN/nant style user (or similar) with a single branch concept in your repository then you can read my articles on this topic over at DotNetSlackers: http://dotnetslackers.com/articles/aspnet/Building-a-StackOverflow-inspired-Knowledge-Exchange-Build-automation-with-NAnt.aspx and http://dotnetslackers.com/articles/aspnet/Building-a-StackOverflow-inspired-Knowledge-Exchange-Continuous-integration-with-CruiseControl-NET.aspx.
If you are a perforce multi branch sort of build master then you will have to wait till I write something about that sort of automation and configuration management.
UPDATE
@Sazug: "Yep, we use some sort of multi branch builds when we use base script + additional scripts :) Any basic tips for that sort of automation without full article?" There are most commonly two forms of databases:
The first set up is much easier and can be fully automated from dev to prod and to include rolling back prod if need be. For this you simply need a scripts folder where every modification to your database can be maintained in a .sql file. I don't suggest that you keep a tablename.sql file and then version it like you would a .cs file where updates to that sql artifact is actually modified in the same file over time. Given that sql objects are so heavily dependent on each other. When you build up your database from scratch your scripts may encounter a breaking change. For this reason I suggest that you keep a separate and new file for each modification with a sequence number at the front of the file name. For example something like 000024-ModifiedAccountsTable.sql. Then you can use a custom task or something out of NAntContrib or an direct execution of one of the many ??SQL.exe command line tools to run all of your scripts against an empty database from 000001-fileName.sql through to the last file in the updateScripts folder. All of these scripts are then checked in to your version control. And since you always start from a clean db you can always roll back if someones new sql breaks the build.
In the second environment automation is not always the best route given that you might impact production. If you are actively developing against/for a production environment then you really need a multi-branch/environment so that you can test your automation way before you actually push against a prod environment. You can use the same concepts as stated above. However, you can't really start from scratch on a prod db and rolling back is more difficult. For this reason I suggest using RedGate SQL Compare of similar in your build process. The .sql scripts are checked in for updating purposes but you need to automate a diff between your staging db and prod db prior to running the updates. You can then attempt to sync changes and roll back prod if problems occur. Also, some form of a back up should be taken prior to an automated push of sql changes. Be careful when doing anything without a watchful human eye in production! If you do true continuous integration in all of your dev/qual/staging/performance environments and then have a few manual steps when pushing to production...that really isn't that bad!
第一点：没有“规矩”就很难有条不紊。
或者对于你的例子 - 开发人员在没有通知的情况下更改任何内容会给你带来严重的问题。
无论如何 - 你说“不使用触发器”。
这有什么具体原因吗？
如果没有 - 请查看 DDL 触发器。此类触发器是检查是否发生某事的最简单方法。
您甚至可以记录发生了什么。
First point: it's hard to keep things in order without "regulations".
Or for your example - developers changing anything without a notice will bring you to serious problems.
Anyhow - you say "without using triggers".
Any specific reason for this?
If not - check out DDL Triggers. Such triggers are the easiest way to check if something happened.
And you can even log WHAT was going on.
希望有人有比这更好的解决方案，但我使用几种方法来做到这一点：
我们仍在构建自动化这部分的工具，但目标是有一个表来跟踪数据库所处的每个版本，以及是否应用了更改脚本。升级工具会查找最新的条目，然后逐一应用每个升级脚本，最终数据库处于最新版本。
我没有这个问题，但保护 _clean 数据库不被其他团队成员修改是微不足道的。此外，由于我在事后使用 SQL Compare 来生成更改脚本，因此开发人员无需随时跟踪它们。
比较的好处是它生成的脚本位于事务中 - 如果失败，它会将整个事情回滚。因此，如果生产数据库已以某种方式修改，升级将会失败，然后部署团队实际上可以在生产数据库上与 _clean 数据库使用 SQL Compare，并手动修复更改。我们只需要这样做一两次（该死的客户）。
.SQL 更改脚本（由 SQL Compare 生成）存储在我们的版本控制系统（subversion）中。
Hopefully someone has a better solution than this, but I do this using a couple methods:
We are still building the tool to automate this part, but the goal is that there is a table to track every version the database has been at, and if the change script was applied. The upgrade tool looks for the latest entry, then applies each upgrade script one-by-one and finally the DB is at the latest version.
I don't have this problem, but it would be trivial to protect the _clean databases from modification by other team members. Additionally, because I use SQL Compare after the fact to generate the change scripts, there is no need for developers to keep track of them as they go.
The nice thing about SQL compare is the script it generates is in a transaction -and it if fails, it rolls the whole thing back. So if the production DB has been modified in some way, the upgrade will fail, and then the deployment team can actually use SQL Compare on the production DB against the _clean db, and manually fix the changes. We've only had to do this once or twice (damn customers).
The .SQL change scripts (generated by SQL Compare) get stored in our version control system (subversion).
如果您有 Visual Studio（特别是数据库版本），则可以创建一个
数据库项目并将其指向 SQL Server 数据库。该项目将加载架构并基本上为您提供许多其他功能。它的行为就像一个代码项目。它还为您提供了编写整个表和内容脚本的优势，以便您可以将其保留在 Subversion 下。当您构建项目时，它会验证数据库的完整性。这是相当聪明的。
If you have Visual Studio (specifically the Database edition), there is a
Database Projectthat you can create and point it to a SQL Server database. The project will load the schema and basically offer you a lot of other features. It behaves just like a code project. It also offers you the advantage to script the entire table and contents so you can keep it under Subversion.When you build the project, it validates that the database has integrity. It's quite smart.
在我们的一个项目中，我们将数据库版本存储在数据库中。
对数据库结构的每个更改都被编写到单独的 sql 文件中，除了所有其他更改之外，该文件还增加了数据库版本。这是由更改数据库结构的开发人员完成的。
部署脚本根据当前数据库版本和最新更改脚本进行检查，并在必要时应用这些 SQL 脚本。
On one of our projects we had stored database version inside database.
Each change to database structure was scripted into separate sql file which incremented database version besides all other changes. This was done by developer who changed db structure.
Deployment script checked against current db version and latest changes script and applied these sql scripts if necessary.
首先，您的生产数据库不应该被开发人员访问，或者开发人员（以及其他所有人）应该受到严格指示，不得在变更控制系统之外对生产系统进行任何类型的更改。
变更控制对于您希望运行的任何系统都至关重要（整个系统中有超过 1 名工程师参与）。
每个开发者都应该有自己的测试系统；如果他们想对此进行更改，他们可以，但系统测试应该在更受控制的系统测试系统上完成，该系统具有与生产相同的更改 - 如果您不这样做，则不能依赖版本工作，因为它们正在不兼容的环境中进行测试。
进行更改时，应创建并测试适当的脚本，以确保它们干净地应用于当前版本，并且回滚有效*
*您正在编写回滚脚本，对吗？
Firstly, your production database should either not be accessible to developers, or the developers (and everyone else) should be under strict instructions that no changes of any kind are made to production systems outside of a change-control system.
Change-control is vital in any system that you expect to work (Where there is >1 engineer involved in the entire system).
Each developer should have their own test system; if they want to make changes to that, they can, but system tesing should be done on a more controlled, system test system which has the same changes applied as production - if you don't do this, you can't rely on releases working because they're being tested in an incompatible environment.
When a change is made, the appropriate scripts should be created and tested to ensure that they apply cleanly on top of the current version, and that the rollback works*
*you are writing rollback scripts, right?
我同意其他帖子的观点，即开发人员不应具有更改生产数据库的权限。开发人员要么应该共享一个通用的开发数据库（并且冒着互相触犯的风险），要么他们应该拥有自己的单独数据库。在前一种情况下，您可以使用 SQL Compare 等工具来部署到生产环境。在后一种情况下，您需要在开发生命周期中定期同步开发人员数据库，然后再升级到生产。
在 Red Gate，我们很快将发布一个新工具，SQL 源代码控制，旨在使这个过程变得更加容易。我们将集成到 SSMS 中，并通过单击按钮即可在源代码控制中添加和检索对象。如果您有兴趣了解更多信息或注册我们的早期访问计划，请访问此页面：
http://www.red-gate.com/Products/SQL_Source_Control/index.htm
I agree with other posts that developers should not have permissions to change the production database. Either the developers should be sharing a common development database (and risk treading on each others' toes) or they should have their own individual databases. In the former case you can use a tool like SQL Compare to deploy to production. In the latter case, you need to periodically sync up the developer databases during the development lifecycle before promoting to production.
Here at Red Gate we are shortly going to release a new tool, SQL Source Control, designed to make this process a lot easier. We will integrate into SSMS and enable the adding and retrieving objects to and from source control at the click of a button. If you're interested in finding out more or signing up to our Early Access Program, please visit this page:
http://www.red-gate.com/Products/SQL_Source_Control/index.htm
我必须同意帖子的其余部分。数据库访问限制将解决生产问题。然后使用 DBGhost 或 DVC 等版本控制工具会对您和其他人有所帮助维护数据库版本控制的团队
I have to agree with the rest of the post. Database access restrictions would solve the issue on production. Then using a versioning tool like DBGhost or DVC would help you and the rest of the team to maintain the database versioning