数据库更改管理 - 初始创建脚本，后续迁移脚本的设置 [英] Database Change Management - Setup for Initial Create Scripts, Subsequent Migration Scripts

问题描述

我有一个数据库变更管理工作流程。它基于SQL脚本（因此，它不是托管的基于代码的解决方案）。

基本设置如下：

 初始化/ 
生成初始Schema.sql 
生成初始必需Data.sql 
生成初始测试Data.sql 
迁移
 0001_MigrationScriptForChangeOne.sql 
 0002_MigrationScriptForChangeTwo.sql 
 ... 
  

启动数据库的过程是运行所有Initlal脚本，然后运行顺序Migration脚本。一个工具会考虑版本控制要求等。

我的问题是，在这种设置中，还要保持这一点非常有用：

 当前/ 
存储过程/ 
 dbo.MyStoredProcedureCreateScript.sql 
 ... 
表/ 
 dbo.MyTableCreateScript.sql 
 ... 
 ... 
  

this我的意思是一个脚本目录（由对象类型分隔），代表用于启动数据库的当前/最新版本的创建脚本。

出于某种原因，我真的很喜欢这个想法，但我不能具体地证明它的需要。我缺少某些东西？

优点是：

• 源控件，我们将使用与用于
相同的每个文件的对象设置

• 对于部署，我们可以通过运行初始+迁移或通过运行当前/

下的脚本
• 对于dev，我们不需要运行数据库实例来进行开发。我们可以在当前/文件夹上进行离线开发。

缺点是：

• 对于每个更改，我们需要更新当前/文件夹中的脚本，以及创建迁移脚本（在Migration /文件夹中）
$ b $

解决方案

实际上，这是最好的办法。由于它可能听起来麻烦，它比使用SQL比较工具或VSDB .schema文件部署的替代方法更好。我已经争论了一段时间的smae方法：版本控制和您的数据库。我的应用程序从初始脚本部署v1模式，然后为每个版本运行升级脚本。每个脚本知道如何从版本N-1升级到N，而只有这一点。最终结果是当前版本。





最大的缺点是缺乏权威的.sql文件，以查找任何当前版本的 对象（过程，表，视图等）。但是，通过任何之前的版本部署您的应用程序的优势，以及通过良好控制和测试的脚本部署的优势远远超过了缺点。





如果你觉得使用这个部署过程感觉不好（脚本部署v1，然后应用v1.1，然后v1.2 ...直到最后你应用v4.5，当前），然后保持这一点：SQL Server内部使用完全相同的进程在版本之间升级数据库。当您附加较旧的数据库时，您会看到着名的数据库正在从版本611升级到612，并且您看到升级是按步骤 ，不会直接升级到当前版本651或任何当前在你的情况下）。升级也没有运行差异工具来在v.611上部署v 651.这是因为最好的方法是你刚才使用的方法，请及时升级一步。





为了给你的问题添加一个真正的答案，发布一个相当倾斜的rant（是一个主题我有强烈的意见，你能告诉吗？）：我认为是有价值的有一个脚本版本的当前版本，但我认为应该是一个连续的集成构建过程可交付。换句话说，构建服务器应构建当前数据库（使用升级脚本），然后，作为构建步骤，脚本化数据库并使用当前版本模式脚本生成构建文件。但是那些应该只用作搜索和代码检查的参考，而不是作为部署可交付的，我的2C。




I've got a database change management workflow in place. It's based on SQL scripts (so, it's not a managed code-based solution).

The basic setup looks like this:

Initial/
    Generate Initial Schema.sql
    Generate Initial Required Data.sql
    Generate Initial Test Data.sql
Migration
     0001_MigrationScriptForChangeOne.sql
     0002_MigrationScriptForChangeTwo.sql
     ...

The process to spin up a database is to then run all the Initlal scripts, and then run the sequential Migration scripts. A tool takes case of the versioning requirements, etc.

My question is, in this kind of setup, is it useful to also maintain this:

Current/
    Stored Procedures/
        dbo.MyStoredProcedureCreateScript.sql
        ...
    Tables/
        dbo.MyTableCreateScript.sql
        ...
    ...

By "this" I mean a directory of scripts (separated by object type) that represents the create scripts for spinning up the current/latest version of the database.

For some reason, I really like the idea, but I can't concretely justify it's need. Am I missing something?

The advantages would be:

• For dev and source control, we would have the same object-per-file setup that we're used to
• For deployment, we can spin up a new DB instance to the latest version either by running the Initial+Migrate, or by running the scripts from Current/
• For dev, we do not need a DB instance running in order to do development. We can do "offline" development on the Current/ folder.

The disadvantages would be:

• For each change, we need to update the scripts in the Current/ folder, as well as create a Migration script (in the Migration/ folder)

Thanks in advance for any input!

解决方案

Actually, this is the best way. As cumbersome as it may sound, it is better than the alternatives of using SQL Compare like tools or VSDB .schema file deployment. I have argued for exactly the smae approach for some time now: Version Control and your Database. My apps deploy the v1 schema from the initial script, then run upgrade script for each version. Each script know how to upgrade from version N-1 to N, and only that. Final result is the current version.

The biggest draw back is lack of an authoritative .sql file too look to find the current version of any object (procedure, table, view etc). But the advantages of being able to deploy your app over any previous version, and the advantage of deploying by well controlled and tested scripts far outweigh the drawback.

If you feel bad for using this deployment process (script to deploy v1. then apply v1.1, then v1.2 ... until finally you apply v4.5, current) then keep this in mind: exactly the same process is used by SQL Server internally to upgrade the database between releases. When you attach an older database, you see the famous 'database is running the upgrade from version 611 to 612' and you see that the upgrade goes step by step, does not upgrade straight to current version 651 (or whatever is current in your case). Nor does the upgrade runs a diff tool to deploy v 651 over v. 611. That is because the best approach is the one you just use, upgrade one step at at time.

And to add an actual answer to your question, after posting a rather oblique rant (Is a topic I have strong opinions about, can you tell?): I think is valuable to have a scripted version of the current version, but I think it should be a contiguous integration build process deliverable. In other words, your build server should build the current database (using the upgrade scripts) and then, as a build step, script out the database and produce a build drop with the current version schema script. But those should be only used as a reference for searching and code inspection, not as a deployment deliverable, my 2C.

这篇关于数据库更改管理 - 初始创建脚本，后续迁移脚本的设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！