如何在多租户应用程序中更新所有租户的所有架构？
我正在开发一个多租户应用程序。我选择了“共享数据库/单独模式”方法。
我的想法是拥有一个默认架构（dbo），并在部署此架构时，对租户的架构（tenantA、tenantB）进行更新>, 租户C);换句话说，创建同步模式。
如何将租户的架构与默认架构同步？
我正在使用 SQL Server 2008。
I am developing a multi-tenant app. I chose the "Shared Database/Separate Schemas" approach.
My idea is to have a default schema (dbo) and when deploying this schema, to do an update on the tenants' schemas (tenantA, tenantB, tenantC); in other words, to make synchronized schemas.
How can I synchronize the schemas of tenants with the default schema?
I am using SQL Server 2008.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您首先需要一个表或其他机制来存储模式的版本信息。如果没有别的事，您就可以将应用程序和架构绑定在一起。没有什么比应用程序版本针对错误模式更痛苦的了——失败、损坏数据等。
如果版本不正确，应用程序应该拒绝或关闭——当版本不正确时，您可能会受到一些打击，但可以保护您免受影响。当数据库损坏有价值的数据时，真是糟糕的一天。
您需要一种方法来跟踪更改，例如 Subversion 或其他工具 - 您可以从 SQL 导出初始架构。从这里开始，您将需要一种机制来使用 SQL 比较等不错的工具来跟踪更改，然后跟踪架构更改并匹配目标数据库中版本号的更新。
我们将每个增量保存在我们构建的升级实用程序下的单独文件夹中。该实用程序登录服务器，读取版本信息，然后应用数据库中下一个版本的转换脚本，直到在其子文件夹中找不到更多升级脚本。这使我们能够将数据库升级到当前版本，无论它有多旧。如果存在租户独有的数据转换，这些将会变得棘手。
当然，您应该始终对写入外部文件的数据库进行备份，最好使用人类可识别的版本号，以便您可以在脚本出错时找到它并恢复它。最终它只会计划弄清楚如何恢复和恢复。
我看到新的 VS 2010 中有某种模式升级工具，但我没有使用它。这可能对您也有用。
First thing you will need is a table or other mechanism to store the version information of the schema. If nothing else so that you can bind your application and schema together. There is nothing more painful than a version of the application against the wrong schema—failing, corrupting data, etc.
The application should reject or shutdown if its not the right version—you might get some blowback when its not right, but protects you from the really bad day when the database corrupts the valuable data.
You'll need a way to track changes such as Subversion or something else—from SQL you can export the initial schema. From here you will need a mechanism to track changes using a nice tool like SQL compare and then track the schema changes and match to an update in version number in the target database.
We keep each delta in a separate folder beneath the upgrade utility we built. This utility signs onto the server, reads the version info and then applies the transform scripts from the next version in the database until it can find no more upgrade scripts in its sub folder. This gives us the ability upgrade a database no matter how old it is to the current version. If there are data transforms unique the tenant, these are going to get tricky.
Of course you should always make a backup of the database that writes to an external file preferable with an human identifiable version number so you can find it and restore it when the script(s) go bad. And eventually it will so just plan on figuring out how to recover and restore.
I saw there is some sort of schema upgrader tool in the new VS 2010 but I haven't used it. That might also be useful to you.
据我所知，没有什么神奇的命令可以同步模式。您需要使用一个工具 - 内置或购买（查看 Red Gate 的 SQL Compare 和SQL Examiner - 您需要调整它们以比较不同的模式）。
然而，仅仅同步往往是一件棘手的事情。如果您添加了一列，是否还需要用数据填充该列？如果将一列拆分为两个新列，则必须有类似的转换代码。
我的建议是非常仔细地跟踪针对 dbo 架构运行的任何脚本，并确保它们在适当的时候也针对其他架构运行。然后，您可以使用 SQL Compare 等工具作为偶尔的健全性检查，以查找任何意外的差异。
There is no magic command to synchronize the schemas as far as I know. You would need to use a tool - either built in house or bought (Check out Red Gate's SQL Compare and SQL Examiner - you need to tweak them to compare different schemas).
Just synchronizing can often be tricky business though. If you added a column, do you need to also fill that column with data? If you split a column into two new columns there has to be conversion code for something like that.
My suggestion would be to very carefully track any scripts that you run against the dbo schema and make sure that they also get run against the other schemas when appropriate. You can then use a tool like SQL Compare as an occasional sanity check to look for any unexpected differences.