多个数据库VS与逻辑分区数据的单一数据库 [英] Multiple Databases Vs Single Database with logically partitioned data

问题描述

我琢磨了一个数据库设计问题。任何帮助将是非常美联社preciated。

I am pondering over a database design issue. Any help would be highly appreciated.

我们正在设计有20桌（新功能的开发过程中可能增长到约30最大值）的应用程序

We are designing an application which has 20 tables (which may grow to about 30 maximum during new feature development)

的技术堆栈

MVC4，.NET 4.X，实体框架5，SQL Server 2012中，ASP.NET框架成员

MVC4,.NET 4.X, Entity Framework 5, SQL Server 2012, ASP.NET membership framework

否用户

我们打算去迎合大约1000个客户端谁也平均有20个用户。

We intend to cater to about 1000 clients who would have on average 20 users.

问题

我们应该设计数据库和以这样的方式，该表被逻辑分区，即所有客户端使用相同的表与一个分区GUID到数据分离应用

Should we design the database and the application in such a way that the tables are logically partitioned, i.e all clients use the same tables with a partition guid to separate the data.

或

去多个数据库可能被证明是在新功能发布和bug修复困难。但可能允许缩放？

Go for multiple databases which could prove to be difficult during new feature launch and bug fixing. BUT could potentially allow for scaling?

注意事项：其中一个表具有一个存储文件（每个记录最大5MB）的二进制列

Caveats: one of the tables has a binary column which stores files (maximum 5MB per record)

除了这一点，我们需要考虑的会员框架表，我们将延伸至其他自定义表和逻辑映射用户分区GUID。

In addition to this we need to consider the Membership framework tables, which we will be extending to another custom table and logically mapping users to a partition guid.

推荐答案

您会希望你曾经使用独立的数据库：

You'll wish you had used separate databases:

• 如果你想将权限授予数据库本身给客户，或超级用户。


• 如果你想在不影响其他人的数据恢复只是一个客户的数据库。


• 如果有管理您的数据和数据泄露监管问题，你姗姗来迟发现，这些规定只能由具有单独的数据库来满足。


• 如果你想轻松地将客户数据迁移到多个数据库服务器或以其他方式向外扩展，或将更大/更重要客户到不同的硬件。在世界的不同部分。


• 如果你想轻松地存档和淘汰旧的客户数据。


• 如果您的客户关心被孤立他们的数据，他们发现，你没有其他。


• 如果您的数据被传唤，你必须制作整个数据库，而不是只为一台客户机的数据。


• 当你忘记保持警惕，只是一个查询通过滑不包括键，客户id = @CustomerID 。提示：使用脚本权限的工具，或模式，或包裹中的所有表与视图，包括 WHERE客户id = SomeUserReturningFunction（），或它们的组合
。

• 当你错的权限在应用层和客户数据暴露在错误的客户。


• 当你想有不同程度的备份和恢复保护为不同的客户的。


• 一旦你意识到，建设基础设施，以创造，提供，配置，部署，否则自旋向上/下新数据库是值得投资的，因为它迫使你取得好它。


• 当你没有允许一些阶层的人需要访问多个客户的数据的可能性，你需要一个抽象层上客户，因为顶部 WHERE客户id = @CustomerID 现在不会削减它。

• If you ever want to grant permissions to the databases themselves to clients or superusers.
• If you ever want to restore just one client's database without affecting the data of the others.
• If there are regulatory concerns governing your data and data breaches, and you belatedly discover that these regulations can only be met by having separate databases.
• If you ever want to easily move your customer data to multiple database servers or otherwise scale out, or move larger/more important customers to different hardware. In a different part of the world.
• If you ever want to easily archive and decommission old customer data.
• If your customers care about their data being siloed, and they find out that you did otherwise.
• If your data is subpoenaed and you have to produce the entire database instead of just the data for the one client.
• When you forget to maintain vigilance and just one query slips through that didn't include AND CustomerID = @CustomerID. Hint: use a scripted permissions tool, or schemas, or wrap all tables with views that include WHERE CustomerID = SomeUserReturningFunction(), or some combination of these.
• When you get permissions wrong at the application level and customer data is exposed to the wrong customer.
• When you want to have different levels of backup and recovery protection for different clients.
• Once you realize that building an infrastructure to create, provision, configure, deploy, and otherwise spin up/down new databases is worth the investment because it forces you to get good at it.
• When you didn't allow for the possibility of some class of people needing access to multiple customers' data, and you need a layer of abstraction on top of Customer because WHERE CustomerID = @CustomerID won't cut it now.

您会希望你曾经使用一个单一的数据库：

You'll wish you had used a single database:

• 当你发现影响所有1000个客户和部署修复至1000数据库是很难的错误。


• 当你错的权限在数据库级别和客户数据暴露在错误的客户。


• 当你没有允许一些阶层的人需要访问所有数据库（也许是两个客户合并）的一个子集的可能性。


• 当您尝试在一台服务器上增长近32,767客户/数据库，并发现这是SQL Server 2012中的最大值。


• 当你意识到，管理逾1,000个数据库，比你想象一个更大的噩梦。

只是因为我列出的原因，一个不意味着它是更好的。

Just because I listed more reasons for one doesn't mean it is better.

这篇关于多个数据库VS与逻辑分区数据的单一数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！