SQL/Schema 比较和升级
我的情况很简单。一个大型组织正在使用某些（桌面）应用程序的多个不同版本，每个版本都有自己的数据库结构。大约有 200 个办公室，每个办公室都有自己的版本，可以是 7 个不同版本之一。该公司希望将所有应用程序升级到最新版本，即版本 8。
问题是他们没有每个版本的单独数据库。他们也没有为每个办事处提供单独的数据库。他们拥有一个由专用服务器处理的数据库，从而使管理和备份等工作变得更加容易。每个办公室都有自己的数据库模式，并且在模式内有其特定应用程序版本的整个数据库结构。因此，我正在处理 200 个需要升级的不同模式，每个模式都有 7 个可能的版本。幸运的是，每个模式都知道正确的版本，因此检查版本并不困难。
但我的问题是，我需要创建可以从版本 1 升级到版本 2、版本 3 等等的升级脚本...基本上，所有模式都需要升级一个版本，直到它们都是版本 8。编写代码这样做没问题。挑战是如何创建从一个版本到另一个版本的升级脚本？最好使用一些自动化工具。我已经研究过 RedGate 的 SQL Compare 和 Altova 的 DatabaseSpy，但它们并不实用。阿尔托瓦太慢了。 RedGate 之后需要太多处理，因为生成的 SQL 脚本仍然有一些错误，并且它引用了模式名称。此外，代码需要成为存储过程的一部分，而 RedGate 生成的代码并不真正适合单个过程。 （另外，它做了太多的事务处理，而我需要一个事务中的所有内容。
我一直在考虑使用另一个 SQL 比较工具，但在我看来，我的情况与标准工具可以提供的情况有很大不同。所以我我将编写我自己的比较工具。为此，我将使用 ADOX 和 Delphi 来读取数据库中每个架构版本的目录，然后使用它来编写需要将这些架构升级到的 SQL 语句。 （比较 1 与 2、2 与 3、3 与 4 等）我对生成 SQL 脚本生成器并不陌生，所以我预计不会有太多问题，而且我只会升级。表结构，而不是任何其他数据库对象
那么，在进行这种比较时是否有一些好的提示和技巧可以应用？需要注意的事项? 提高速度的实用技巧？
I have a simple situation. A large organisation is using several different versions of some (desktop) application and each version has it's own database structure. There are about 200 offices and each office will have it's own version, which can be one of 7 different ones. The company wants to upgrade all applications to the latest versions, which will be version 8.
The problem is that they don't have a separate database for each version. Nor do they have a separate database for each office. They have one single database which is handled by a dedicated server, thus keeping things like management and backups easier. Every office has it's own database schema and within the schema there's the whole database structure for their specific application version. As a result, I'm dealing with 200 different schema's which need to be upgraded, each with 7 possible versions. Fortunately, every schema knows the proper version so checking the version isn't difficult.
But my problem is that I need to create upgrade scripts which can upgrade from version 1 to version 2 to version 3 to etc... Basically, all schema's need to be bumped up one version until they're all version 8. Writing the code that will do this is no problem. the challenge is how to create the upgrade script from one version to the other? Preferably with some automated tool. I've examined RedGate's SQL Compare and Altova's DatabaseSpy but they're not practical. Altova is way too slow. RedGate requires too much processing afterwards, since the generated SQL Script still has a few errors and it refers to the schema name. Furthermore, the code needs to become part of a stored procedure and the code generated by RedGate doesn't really fit inside a single procedure. (Plus, it's doing too much transaction-handling, while I need everything within a single transaction.
I have been considering using another SQL Comparison tool but it seems to me that my case is just too different from what standard tools can deliver. So I'm going to write my own comparison tool. To do this, I'll be using ADOX with Delphi to read the catalogues for every schema version in the database, then use this to write the SQL Statements that will need to upgrade these schema's to their next version. (Comparing 1 with 2, 2 with 3, 3 with 4, etc.) I'm not unfamiliar with generating SQL-Script-Generators so I don't expect too many problems. And I'll only be upgrading the table structures, not any of the other database objects.
So, does anyone have some good tips and tricks to apply when doing this kind of comparisons? Things to be aware of? Practical tips to increase speed?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我仍然认为 RedGate 是最佳选择。确实，它并不总是捕获所有依赖项，并且您可能需要对其进行一些修改，但它可以让您完成 95% 的工作，并且在我看来，这将节省大量时间。
生成脚本后，您可以轻松地破解错误处理和事务完成的方式，输出有很好的文档记录，因此查看发生的情况很简单。
一种可能性是，不要修改每个数据库，而是执行以下操作：
DB_NEW)DB) 迁移所有数据>)（为此，您最多需要 7 个不同的数据迁移脚本）DB重命名为DB_OLD并重命名DB_NEW到数据库I still think RedGate is the way to go. It is true that it does not always catch all the dependencies, and you may need to hack on it a bit, but it gets you 95% of the way there, and would be a huge timesaver IMO.
Once you have the script generated, you can easily hack on the way error handling and transactions are done, the output is very well documented, so it is trivial to see what is going on.
One possibility would be, rather than modify each database in place, do this:
DB_NEW)DB) (you will need up to 7 different data migration scripts for this)DBtoDB_OLDand renameDB_NEWtoDB创建新数据库然后迁移数据是最好的方法。也许您需要创建许多数据转换脚本，但我认为数据结构之间的差异并不大。迁移后，我建议使用任何允许比较 sql 查询结果的数据比较工具来验证迁移是否成功。
Creating new database then migrating data is the best way. Probably you will need to create number of data transformation scripts, but I assume that differences between data structure are not huge. After migration I recommend to use any data comparison tool which allows sql-query results comparing to verify migration success.
Redgate就是答案，您可以比较不同的架构，并且还会根据差异为您生成脚本。
Redgate is the answer, you can compare the different schemas and will also generate scripts for you based on the difference.