数据库变更管理 - 初始创建脚本、后续迁移脚本的设置
我已经制定了数据库变更管理工作流程。它基于 SQL 脚本（因此，它不是基于托管代码的解决方案）。
基本设置如下所示：
Initial/
Generate Initial Schema.sql
Generate Initial Required Data.sql
Generate Initial Test Data.sql
Migration
0001_MigrationScriptForChangeOne.sql
0002_MigrationScriptForChangeTwo.sql
...
启动数据库的过程是运行所有初始脚本，然后运行顺序迁移脚本。一个工具可以满足版本控制要求等。
我的问题是，在这种设置中，维护这个是否有用：
Current/
Stored Procedures/
dbo.MyStoredProcedureCreateScript.sql
...
Tables/
dbo.MyTableCreateScript.sql
...
...
“这个”是指代表创建脚本的脚本目录（按对象类型分隔）用于启动数据库的当前/最新版本。
出于某种原因，我真的很喜欢这个想法，但我无法具体证明它的必要性。我错过了什么吗？
优点是：
- 对于开发和源代码控制，我们将具有与我们习惯的相同的每个文件对象设置
- 对于部署，我们可以通过运行 Initial+Migrate 将新的数据库实例启动到最新版本，或者通过从 Current/ For dev 运行脚本
- ，我们不需要运行数据库实例来进行开发。我们可以对Current/文件夹进行“离线”开发。
缺点是：
- 对于每次更改，我们都需要更新 Current/ 文件夹中的脚本，并创建一个迁移脚本（在 Migration/ 文件夹中）
提前感谢您的任何输入！
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!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
其实，这是最好的办法。尽管听起来很麻烦，但它比使用 SQL Compare 之类的工具或 VSDB .schema 文件部署的替代方案要好。一段时间以来，我一直在争论 smae 方法： 版本控制和您的数据库。我的应用程序从初始脚本部署 v1 架构，然后为每个版本运行升级脚本。每个脚本都知道如何从版本 N-1 升级到 N，仅此而已。最终结果是当前版本。
最大的缺点是缺乏权威的 .sql 文件，无法查找任何对象（过程、表、视图等）的当前版本。但是，与任何以前的版本相比，能够部署应用程序的优势以及通过良好控制和测试的脚本进行部署的优势远远超过了缺点。
如果您对使用此部署过程感到不好（部署 v1 的脚本，然后应用 v1.1，然后应用 v1.2 ...直到最后应用 v4.5，当前版本），请记住这一点：使用完全相同的过程由 SQL Server 在内部在版本之间升级数据库。当您附加较旧的数据库时，您会看到著名的“数据库正在运行从版本 611 到 612 的升级”，并且您会看到升级是一步一步进行的，不会直接升级到当前版本 651（或您目前的情况）。升级也不会运行 diff 工具来部署 v 651 而非 v. 611。这是因为最佳方法就是您刚刚使用的方法，一次升级一个步骤。
在发布了相当隐晦的咆哮之后，为您的问题添加一个实际的答案（我对此有强烈的看法，您能告诉我吗？）：我认为拥有当前版本的脚本版本很有价值，但我认为它应该是一个连续的集成构建过程可交付成果。换句话说，您的构建服务器应该构建当前数据库（使用升级脚本），然后作为构建步骤，编写数据库脚本并使用当前版本架构脚本生成构建删除。但这些应该仅用作搜索和代码检查的参考，而不是作为部署可交付成果，我的 2C。
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.
我认为从长远来看，这只会让事情变得更加复杂。整个版本需要存在于单个脚本中，以便您可以在一个上下文中测试该脚本并知道它可以在另一个上下文（例如生产）中正常工作。
I think it will just make things more complex in the long run. Whole versions need to live in a single script so that you can test that script in one context and know it will work correctly in another context like production.
马丁，
如果您在现实世界中，那么您的生产数据库只接受更新 - 您永远不会从头开始“创建”它。所以对你来说存储、观看、回顾等最重要的就是更新脚本集。这些是将其投入制作的脚本，因此这些是唯一真正重要的脚本。
通过将它们作为主要内容，您正在做正确的事情。但开发人员需要能够了解架构的“当前情况”。 DBA 也喜欢这样做，尽管他们（太）频繁地通过登录生产服务器并启动某种 GUI 工具来做到这一点。 （哎呀！）
我对你的方法唯一的保留是当前/以前的架构按对象类型。这些脚本应该通过转储数据库本身自动生成。如果您可以自动按类型对它们进行分类，那就太好了！如果没有，请尽您所能使它们易于导航，但指导规则应始终“从实时数据库自动生成”。
Martin,
If you're in the real world, then your production database only accepts updates - you never "create" it from scratch. So the most important thing for you to be storing, watching, reviewing, etc. is the set of update scripts. Those are the scripts that will make it to production, so those are the only ones of real importance.
You're doing the right thing by making them primary. But developers needs to be able to get a "current picture" of what the schema looks like. DBAs like to do this, too, although (too) frequently they do it by logging in to the production servers and firing up some kind of gui tool. (yikes!)
The only reservation I have about your approach is the current/previous schema by object type. Those scripts should be generated automatically, from dumping the database itself. If you can automatically categorize them by type, then great! If not, do what you can to make them easy to navigate, but the guiding rule should always be "generated automatically from a live database."