将单客户端 SQL Server 数据库转换为单数据库多租户
我们目前有一个系统，每个用户都可以获得一个数据库。我们现在正在转向单数据库多租户模式，以便一个数据库可以容纳许多客户。
几个问题：
是否存在多租户转换工具？或者这只是创建
Tenant表并向每个其他表添加TenantID的过程？有没有一种简单的方法可以实现多租户，而无需重构与数据库通信的代码？
我们有一个
Odata.svc来完成与数据库的所有通信（我们的前端客户端范围从 .net 前端到 iOS 设备）。我读了一些有关使用 Federation 对 TenantID 谓词执行过滤的内容，因此根本不需要更改代码。这可能吗？对于数据库中的租户数量是否有建议的限制？
我认为这是一个愚蠢的问题（一根绳子有多长）。我们很可能会在 Azure 上托管最终解决方案。
期待任何人能给我的任何建议。我们正在对我们的流程进行根本性的改变，所以我想在我们陷入困境之前先占上风。
We currently have a system where each of our users gets a database. We are now moving to a one database multi-tenant schema so one database can house many customers.
A few questions:
Is the a multi-tenant conversion tool in existence? Or is it just the process of creating a
Tenanttable and adding aTenantIDto every other table?Is there an easy way to implement multi-tenant without having to refactor our code that communicates with the database?
We have an
Odata.svcthat does all the talking to the database (our front end clients range from .net frontends to iOS devices). I read a little about using Federation to perform filtering on thetenantIDpredicate so the code does not have to be changed at all. Is this possible?Is there a recommended limit on how many tenants should be in a database?
I'm gathering this is a stupid question (how long is a piece of string). We will most likely be hosting the end solution up on Azure.
Look forward to any advice anyone can give me. We are making a fundamental change to our processes so I want to be on top of it before we are under it.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
自动化？
理论上，应该可以设计一个工具，让执行这项艰巨的操作（从单租户到多租户）变得更加容易。然而，鉴于此类产品的受众有限，我认为这样的工具不存在。如果有人出现的话那就太好了。
有关手动转换的想法
首先设计一个新的多租户数据库架构。 （这意味着将所有单租户数据库模式与您可能拥有的任何共享模式合并。）我想让它像在设计时没有考虑遗留问题一样。
显然，您需要一个
Tenant表，许多现有的单租户表需要使用Tenant_id列来引用该表。例如，包含用户的表需要将用户与租户唯一关联。对于简单的
Products表（以Product_id作为主键），应该可以添加Tenant_id列，从而生成一个表使用复合键（Tenant_id和Product_id）。但是，如果您从头开始编写应用程序，我相信没有租户引用的Product表是正确的方法。这也让租户可以共享产品，而不是添加重复的产品。由于一个租户可能拥有Product_id1,2,3 和另一个 1,2 的产品，因此您不能简单地合并表，因为您不能两次使用相同的 ID - 您需要唯一的主键值。解决此问题的一种方法是编写一个程序（使用 Java 或其他高级语言），将租户数据库中的所有数据读取到内存对象中，然后将数据写入多租户模式。对于下一个租户数据库重复该过程，依此类推。这样您的
Product_id值就会为 1,2,3,4,5。一种快速而肮脏的方法是向每个模式中的所有 ID 值添加一个数字，例如 1,000、2,000 等，然后祈祷不会发生冲突。与数据库通信的代码
您将需要重写大多数数据库查询，以考虑数据库现在是多租户的事实。这是一项艰巨的任务，特别是考虑到引入一个让一个租户篡改另一个租户数据的错误所带来的影响。然而，一些技术可以使这项任务变得更容易。例如，租户视图筛选器可以减少工作量实质上需要。
限制租户数量
我从未见过限制多租户结构中租户数量的建议。相反，多租户方法的优势它的可扩展性。如今，您可以轻松创建数据库服务器集群，或使用基于云的解决方案根据需要无缝添加更多硬件功能。
感兴趣的链接
Automation?
In theory, it should be possible to craft a tool that makes it much easier to perform this daunting operation (going from single-tenant to multiple-tenant). However, I don't think such a tool is in existence, given the limited audience for such a product. It would be very nice if one surfaced.
Ideas about manual conversion
Start by designing a new multi-tenant database schema. (This means merging all single-tenant databases schemas with any shared schemas you possibly have.) I'd like to make it like it would be if it was designed with no legacy considerations.
You obviously need a
Tenanttable, which will need to be referenced by many of your existing single-tenant tables with aTenant_idcolumn. For instance, a table with users will require this to uniquely associate users with a tenant.In the case of a simple
Productstable (withProduct_idas primary key), it should be possible to add aTenant_idcolumn, yielding a table with a composite key (Tenant_idandProduct_id). But if you'd written the application from scratch I believe aProducttable with no tenant referencing is the proper way. This also lets tenants share products, instead of adding duplicates. Since one tenant may have products withProduct_id1,2,3 and another 1,2 you cannot simply merge the tables, because you cannot use the same ID twice -- you need unique primary key values.One way to solve this problem is to write a program (in Java or another high-level language) that reads all data from a tenant database into in-memory objects, then writes the data to the multi-tenant schema. The process repeats for the next tenant database, and so forth. That way you would have
Product_idvalues 1,2,3,4,5. A quick-and-dirty way would be to add a number, say 1,000, 2,000 and so on, to all ID values in each schema and simply cross your fingers that no conflicts arise.Code that communicates with database
You will need to rewrite most database queries to account for the fact that the database is now multi-tenant. This is a daunting task, especially considering the implications of introducing a bug which lets one tenant fiddle with another tenant's data. However, some techniques could make this task easier. For instance, a Tenant View Filter could reduce the amount of work required substantially.
Limit on number of tenants
I have never seen a recommendation to limit the number of tenants in a multi-tenant structure. On the contrary, a strength of the multi-tenant approach is its scalability. Today you can easily create clusters of database servers or use cloud-based solutions to add more hardware power seamlessly, as needed.
Links of interest
老实说，根据我的经验，你无法将其自动化。您正在将非常重要的数据从基础设施转移到数据模型中。每个查询都是在假设租户已经建立的情况下编写的。每个查询&因此，SP 将更改为引用回您的租户表并进行参数化。
您在 Q1 中询问是否只需将tenantID 添加到每个表中。这是一种方法，但我不提倡。它会导致您很容易获得不正确的数据（没有强制要求子级与父级具有相同的tenantID，甚至它们都相同！）您需要确保添加一个租户表，然后仔细选择需要添加哪些表参考它。不会是每一个人。有些人需要它，有些人出于性能原因可能会选择将其放在那里。如果您决定后者，毫无疑问您将需要额外的检查机制来保持数据的意义。
如果您在 Oracle 中，您可能能够做的是将每个表重新加工成一个视图（仍然执行上述所有操作），然后将tenantID填充到会话中并对其进行一些细粒度访问对客户隐藏大部分细节。但很难做好，而且我不确定 SQL Server 的等价物是什么。可能值得一些研究。
合并数据库的原因是什么？您需要一些跨数据库报告什么的吗？否则，单租户有很多优点（多个升级和停机时间计划、性能可能会更好，具体取决于您的标准化程度、单租户数据提取/报告的简易性、失去租户时的轻松删除）。云解决方案&单一租户可能会对您有利。
To be honest, in my experience you can't automate this. You are moving very important data from your infrastructure into your data model. Every query has been written on the assumption the tenant has already been established. Every query & SP will therefore be changed to reference back to your tenant table and parameterised.
You ask in Q1 if you just add the tenantID to each table. That would be one approach, but not one I'd advocate. It leads you wide open to having incorrect data (no enforcement that the children have the same tenantIDs as the parent, or even that they are all the same!) You need to add a Tenant table for sure and then carefully choose which tables need to reference it. It will not be every one. Some will require it, some you might choose to put it there for performance reasons. If you decide on the latter, you will no doubt require extra checking mechanisms to keep your data meaningful.
If you were in Oracle, what you may be able to do is rework each table into a view (still doing all of the above) then stuff the tenantID into the session and do some Fine Grained Access on it to hide most of the detail from the client. Hard to do well though and I'm not sure what the SQL Server equivilent is. Could be worth some research.
What's the reason behind merging the DBs? Do you need some cross-DB report or something? Otherwise single-tenant has many advantages (multiple upgrade & downtime schedules, performance can be better depending on how [de]normalised you go, ease of single tenant data extract/reporting, ease of removal when you lose a tenant). The cloud solution & single tenant could potentially work in your favour here.