如何版本化SQL Server数据库?

I need to put versions onto a SQL Server 2005 database and have these accessible from a .NET Application. What I was thinking is using an Extended Properties on the Database with a name of 'version' and of course the value would be the version of the database. I can then use SQL to get at this. My question is does this sound like a good plan or is there a better way for adding versions to a SQL Server database?

我需要将版本放到SQL Server 2005数据库中，并让. net应用程序可以访问这些版本。我想的是在数据库上使用一个名为“version”的扩展属性，当然，这个值就是数据库的版本。然后我可以使用SQL来达到这个目的。我的问题是，这听起来像一个好的计划吗?还是有更好的方法将版本添加到SQL Server数据库中?

Lets assume I am unable to use a table for holding the Metadata.

假设我无法使用表来保存元数据。

5 个解决方案

#1

I do this:

我这样做:

Create a schema table:

创建一个模式表:

CREATE TABLE [dbo].[SchemaVersion](
    [Major] [int] NOT NULL,
    [Minor] [int] NOT NULL,
    [Build] [int] NOT NULL,
    [Revision] [int] NOT NULL,
    [Applied] [datetime] NOT NULL,
    [Comment] [text] NULL)

Update Schema:

更新模式:

INSERT INTO SchemaVersion(Major, Minor, Build, Revision, Applied, Comment)
VALUES (1, 9, 1, 0, getdate(), 'Add Table to track pay status')

Get database Schema Version:

得到数据库模式的版本:

SELECT TOP 1 Major, Minor, Build from SchemaVersion
ORDER BY Major DESC, Minor DESC, Build DESC, Revision DESC

Adapted from what I read on Coding Horror

改编自我读的关于编码恐怖的文章。

#2

We use the Extended Properties as you described it and it works really well.

我们使用你描述的扩展属性，它工作得很好。

I think having a table is overkill. If I want to track the differences in my databases I use source control and keep all the db generation scripts in it.

我认为有一张桌子是多余的。如果我想跟踪数据库中的差异，我使用源代码控制，并将所有的db生成脚本保存在其中。

I've also used some ER diagram tools to help me keep track of changes in DB versions. This was outside the actual application but it allowed me to quickly see what changed.

我还使用了一些ER图工具来帮助我跟踪DB版本中的更改。这在实际的应用程序之外，但是它允许我快速地看到发生了什么变化。

I think it was CASEStudio, or something like that.

我想是CASEStudio，或者类似的东西。

#3

If I understand your question right (differentiating between internal database versions, like application build numbers), you could have some sort of SYSVERSION table that held a single row of data with this info.

如果我正确理解您的问题(区分内部数据库版本，如应用程序构建号)，您可以有某种SYSVERSION表，其中包含一行数据。

Easier to query.

容易查询。

Could also contain multiple columns of useful info, or multiple rows that represent different times that copy of the database was upgraded.

还可以包含多个有用信息的列，或者表示升级数据库副本的不同时间的多个行。

Update: Well, if you can't use a table to hold the metadata, then either external info of some sort (an INFO file on the hard drive?) or extended properties would be the way to go.

更新:如果你不能使用表来保存元数据，那么要么是外部信息(硬盘上的信息文件?)要么是扩展属性。

I still like the table idea, though :) You could always use security to only make it accessable through a custom stored proc get_ db_version or something.

我仍然喜欢表的想法，但是:)您总是可以使用安全性来使它只能通过自定义存储的proc get_db_version或其他东西访问。

#4

The best way to do is to have 2 procedures: one header to control what is being inserted and validations a footer to insert the data if the release is good or not. The body will contain your scripts.

最好的方法是有两个过程:一个头来控制插入的内容，如果发行版好或不好，则验证脚来插入数据。正文将包含您的脚本。

You need a wrapper that will encapsulate your script and record all the info: as far release, script number been applied, applyby, applydate date, release outcome "failed or succeeded".

您需要一个封装器来封装您的脚本并记录所有信息:到目前为止，已经应用了脚本编号、applyby、applydate日期、发布结果“失败或成功”。

#5

I am using dedicated table similar to Matt's solution. In addition to that, database alters must check current version before applying any changes to the schema. If current version is smaller than expected, then the script terminates with fatal error. If current version is larger than expected, then script skips current step because that step has already been performed sometimes in the past.

我使用的专用表类似于Matt的解决方案。除此之外，数据库修改器必须在对模式应用任何更改之前检查当前版本。如果当前版本小于预期，则脚本将终止致命错误。如果当前版本比预期的要大，那么脚本将跳过当前步骤，因为该步骤在过去有时已经执行过。

Here is the complete solution with examples and conventions in writing database alter scripts: How to Maintain SQL Server Database Schema Version

下面是完整的解决方案，其中包含了编写数据库修改脚本的示例和约定:如何维护SQL Server数据库模式版本

#1