I need to design a windows application which represents multiple "customers" in SQL Server. Each customer has the same data model, but it's independent.
我需要设计一个代表SQL Server中多个“客户”的Windows应用程序。每个客户都有相同的数据模型，但它是独立的。
what will be the Pros/Cons Using multiple databases vs using single database.
什么是优点/缺点使用多个数据库与使用单个数据库。
which one is the best way to do this work. if going for an single database, what will the steps to do for that.
哪一个是完成这项工作的最佳方式。如果要使用单个数据库，那么将采取哪些措施。
Edited:
编辑：
One thing is database will be hosted in cloud(rackspace) account.
一件事是数据库将托管在云（rackspace）帐户中。
4 个解决方案
#1
13
Do not store data from multiple customers in the same database -- I have known companies that had to spend a lot of time/effort/money fixing this mistake. I have even known clients to balk at sharing a database computer even though the databases are separate - on the plus side, these clients are generally willing to pay for the extra hardware.
不要将来自多个客户的数据存储在同一个数据库中 - 我知道公司必须花费大量时间/精力/金钱来解决这个错误。即使数据库是分开的，我甚至已经知道客户不愿共享数据库计算机 - 从好的方面来说，这些客户通常愿意为额外的硬件付费。
-
The problems with security alone should prevent you from ever doing this. You will lose large customers because of this.
安全问题本身就会阻止你做这件事。因此，您将失去大客户。
-
If you have some customers that are unwilling to upgrade their software, it can be very difficult if you share a single database. Separate databases allow customers to continue using the old database structure until they are ready to upgrade.
如果您有一些客户不愿意升级他们的软件，那么如果您共享一个数据库可能会非常困难。单独的数据库允许客户继续使用旧的数据库结构，直到他们准备好升级。
-
You are artificially limiting a natural data partition that could provide significant scalability to your solution. Multiple small customers can still share a database server, they just see their own databases/catalogs, or they can run on separate database servers / instances.
您正在人为地限制可以为您的解决方案提供显着可伸缩性的自然数据分区。多个小客户仍然可以共享数据库服务器，他们只看到自己的数据库/目录，或者他们可以在不同的数据库服务器/实例上运行。
-
You are complicating your database design because you will have to distinguish customer data that would otherwise be naturally separated, i.e., having to supply CustomerID on each where clause.
您使数据库设计变得复杂，因为您必须区分本来会自然分离的客户数据，即必须在每个where子句上提供CustomerID。
-
You are making your database slower by having more rows in all tables. You will use up database memory more rapidly because CustomerID is now part of every index, and fewer records can be stored in each index node. Your database is also slower due to the loss of the inherent advantage of locality of reference.
通过在所有表中包含更多行，您使数据库变慢。您将更快地耗尽数据库内存，因为CustomerID现在是每个索引的一部分，并且每个索引节点中可以存储的记录更少。由于丧失了参考局部性的固有优势，您的数据库也会变慢。
-
Data rollback for 1 customer can be very difficult, maybe even essentially impossible as the database grows - you will need custom procedures to do this that are much slower and resource intensive than a simple and standard restore from backup.
对于1个客户的数据回滚可能非常困难，甚至可能在数据库增长时基本上不可能 - 您需要自定义过程来执行此操作，这比从备份进行的简单和标准还原要慢得多且资源密集。
-
Large databases can be very difficult to backup / restore in a timely manner, possibly requiring additional spending on hardware to make it fast enough.
大型数据库很难及时备份/恢复，可能需要额外花费在硬件上以使其足够快。
-
Your application(s) that use the database will be harder to maintain and test.
您使用数据库的应用程序将难以维护和测试。
-
Any mistakes can be much more destructive as you can mess up all of your clients by a single mistake.
任何错误都可能更具破坏性，因为你可以通过一个错误搞砸所有客户。
-
You prevent the possible performance enhancement of low-latency by forcing your database to a single location. E.g., overseas customer will be using slow, high-latency networks all the time.
通过将数据库强制到单个位置，可以防止低延迟的性能增强。例如，海外客户将始终使用缓慢，高延迟的网络。
-
You will be known as the stupid DBA, or the unemployed DBA, or maybe both.
您将被称为愚蠢的DBA，或失业的DBA，或两者兼而有之。
There are some advantages to a shared database design though.
但是，共享数据库设计有一些优点。
-
Common table schemas, code tables, stored procs, etc. need only be maintained and stored in 1 location.
公共表模式，代码表，存储过程等只需要维护并存储在一个位置。
-
Licensing costs may be reduced in some cases.
在某些情况下，许可成本可能会降低。
-
Some maintenance is easier, although almost certainly worse overall using a combined approach.
一些维护更容易，但使用组合方法几乎肯定更糟糕。
-
If all/most of your clients are very small, you can have a low resource utilization by not combining servers (i.e., a relatively high cost). You can mitigate the high cost by combining clients with their permission and explicit understanding, but still use separate databases for larger clients. You definitely need to be explicit and up-front with your clients in this situation.
如果所有/大多数客户端都非常小，则可以通过不组合服务器来获得较低的资源利用率（即，相对较高的成本）。您可以通过将客户端与其权限和明确的理解相结合来降低高成本，但仍然为较大的客户端使用单独的数据库。在这种情况下，您绝对需要明确地与您的客户保持联系。
Except for the server cost sharing, this is a very bad idea still - but cost can be a very important aspect too. This is really the only justification for this approach - avoid this if at all reasonable though. Maybe you would be better off to change a little more for you product, or just not be able to support tiny customers for a cheap price.
除了服务器成本分摊之外，这仍是一个非常糟糕的想法 - 但成本也是一个非常重要的方面。这实际上是这种方法的唯一理由 - 尽管如此，尽管如此，要避免这种情况。也许你会更好地为你的产品改变一点，或者只是不能以便宜的价格支持小客户。
#2
4
I'm assuming that by multiple customers you're not just storing customer information, you're hosting databases for an application for the customers, like CRM systems.
我假设多个客户不仅存储客户信息，而且还为客户的应用程序（如CRM系统）托管数据库。
If so, then I would absolutely not store everything in the same database.
如果是这样，那么我绝对不会将所有内容存储在同一个数据库中。
Reasons:
原因：
- Backup, when one customer calls and says that he needs to restore a backup because an intern managed to clean out the production database and not the test database, you do not want to have to deal with all the other customers at the same time
- 备份，当一个客户打电话并说他需要恢复备份因为实习生设法清理生产数据库而不是测试数据库时，您不希望同时处理所有其他客户
- Security, even with a bug in the application it won't be able to reach data for other customers. Also, consider if one customer is a bit too relaxed in their own security considerations and leaks passwords or whatnot to the system, if hackers discovers a way into that customers database, consider the fallout if that also includes all other customers you're hosting for.
- 安全性，即使应用程序中存在错误，也无法为其他客户访问数据。另外，考虑一个客户是否在他们自己的安全考虑因素中过于放松并且泄露了密码或其他信息，如果黑客发现进入该客户数据库的方式，如果还包括您托管的所有其他客户，请考虑其后果。
- Politics, some customers will not allow mixing their data with other customers even if you can 100% guarantee that access to their data won't be (accidentally) given to other customers
- 政治方面，即使您可以100％保证不会（意外地）将数
So bottom line: separate databases.
所以底线：单独的数据库。
#3
1
One day your developer will screw up something and one customer will access info of another customer. You will lose your customers as result. This alone should tell you that multiple customers can't be in one data base. no one will want to be your customer if they know this.
有一天，您的开发人员会搞砸一些东西，一个客户将访问另一个客户的信息。结果会导致您失去客户。仅这一点就应该告诉您多个客户不能在一个数据库中。如果他们知道这一点，没有人会想成为你的客户。
Do I have to really go over all issues that will eventually happen if this is the case? The answer is simple here - NO. You don't want to have information of multiple customers in the same database.
如果是这种情况，我是否必须真正解决最终会发生的所有问题？答案很简单 - 没有。您不希望在同一数据库中拥有多个客户的信息。
Only time that this happens is if you have multiplexer database to keep track of customer logons, sessions, etc. But data used and stored by customers should be in the dedicated database.
只有当您拥有多路复用器数据库以跟踪客户登录，会话等时，才会发生这种情况。但客户使用和存储的数据应位于专用数据库中。
#4
0
Some of the advantages to each approach to be considered are:
每种方法的一些优点是：
Single Database
单个数据库
- Relating data from different services can be bound together by foreign key constraints
- 关联来自不同服务的数据可以通过外键约束绑定在一起
- Analytic extracts are simpler to write and faster to execute
- 分析提取更易于编写，执行速度更快
- In the event of a disaster, restoring the platform to a consistent state is easier
- 在发生灾难时，将平台恢复到一致状态会更容易
- For data that is referenced by multiple services, data cached by one service is likely to be used soon after by another service
- 对于由多个服务引用的数据，一个服务缓存的数据很可能很快被另一个服务使用
- Administration and monitoring is simpler and cheaper up front
- 预先管理和监控更简单，更便宜
Multiple Databases
多个数据库
- Maintenance work, hardware problems, security breaches and so forth do not necessarily impact the whole platform
- 维护工作，硬件问题，安全漏洞等不一定会影响整个平台
- Assuming each database is on separate hardware, scaling up multiple machines yields more performance benefits than scaling up one big one
- 假设每个数据库都位于不同的硬件上，那么扩展多台计算机会比扩展一台大型计算机产生更多的性能优势
资源
#1
13
Do not store data from multiple customers in the same database -- I have known companies that had to spend a lot of time/effort/money fixing this mistake. I have even known clients to balk at sharing a database computer even though the databases are separate - on the plus side, these clients are generally willing to pay for the extra hardware.
不要将来自多个客户的数据存储在同一个数据库中 - 我知道公司必须花费大量时间/精力/金钱来解决这个错误。即使数据库是分开的，我甚至已经知道客户不愿共享数据库计算机 - 从好的方面来说，这些客户通常愿意为额外的硬件付费。
-
The problems with security alone should prevent you from ever doing this. You will lose large customers because of this.
安全问题本身就会阻止你做这件事。因此，您将失去大客户。
-
If you have some customers that are unwilling to upgrade their software, it can be very difficult if you share a single database. Separate databases allow customers to continue using the old database structure until they are ready to upgrade.
如果您有一些客户不愿意升级他们的软件，那么如果您共享一个数据库可能会非常困难。单独的数据库允许客户继续使用旧的数据库结构，直到他们准备好升级。
-
You are artificially limiting a natural data partition that could provide significant scalability to your solution. Multiple small customers can still share a database server, they just see their own databases/catalogs, or they can run on separate database servers / instances.
您正在人为地限制可以为您的解决方案提供显着可伸缩性的自然数据分区。多个小客户仍然可以共享数据库服务器，他们只看到自己的数据库/目录，或者他们可以在不同的数据库服务器/实例上运行。
-
You are complicating your database design because you will have to distinguish customer data that would otherwise be naturally separated, i.e., having to supply CustomerID on each where clause.
您使数据库设计变得复杂，因为您必须区分本来会自然分离的客户数据，即必须在每个where子句上提供CustomerID。
-
You are making your database slower by having more rows in all tables. You will use up database memory more rapidly because CustomerID is now part of every index, and fewer records can be stored in each index node. Your database is also slower due to the loss of the inherent advantage of locality of reference.
通过在所有表中包含更多行，您使数据库变慢。您将更快地耗尽数据库内存，因为CustomerID现在是每个索引的一部分，并且每个索引节点中可以存储的记录更少。由于丧失了参考局部性的固有优势，您的数据库也会变慢。
-
Data rollback for 1 customer can be very difficult, maybe even essentially impossible as the database grows - you will need custom procedures to do this that are much slower and resource intensive than a simple and standard restore from backup.
对于1个客户的数据回滚可能非常困难，甚至可能在数据库增长时基本上不可能 - 您需要自定义过程来执行此操作，这比从备份进行的简单和标准还原要慢得多且资源密集。
-
Large databases can be very difficult to backup / restore in a timely manner, possibly requiring additional spending on hardware to make it fast enough.
大型数据库很难及时备份/恢复，可能需要额外花费在硬件上以使其足够快。
-
Your application(s) that use the database will be harder to maintain and test.
您使用数据库的应用程序将难以维护和测试。
-
Any mistakes can be much more destructive as you can mess up all of your clients by a single mistake.
任何错误都可能更具破坏性，因为你可以通过一个错误搞砸所有客户。
-
You prevent the possible performance enhancement of low-latency by forcing your database to a single location. E.g., overseas customer will be using slow, high-latency networks all the time.
通过将数据库强制到单个位置，可以防止低延迟的性能增强。例如，海外客户将始终使用缓慢，高延迟的网络。
-
You will be known as the stupid DBA, or the unemployed DBA, or maybe both.
您将被称为愚蠢的DBA，或失业的DBA，或两者兼而有之。
There are some advantages to a shared database design though.
但是，共享数据库设计有一些优点。
-
Common table schemas, code tables, stored procs, etc. need only be maintained and stored in 1 location.
公共表模式，代码表，存储过程等只需要维护并存储在一个位置。
-
Licensing costs may be reduced in some cases.
在某些情况下，许可成本可能会降低。
-
Some maintenance is easier, although almost certainly worse overall using a combined approach.
一些维护更容易，但使用组合方法几乎肯定更糟糕。
-
If all/most of your clients are very small, you can have a low resource utilization by not combining servers (i.e., a relatively high cost). You can mitigate the high cost by combining clients with their permission and explicit understanding, but still use separate databases for larger clients. You definitely need to be explicit and up-front with your clients in this situation.
如果所有/大多数客户端都非常小，则可以通过不组合服务器来获得较低的资源利用率（即，相对较高的成本）。您可以通过将客户端与其权限和明确的理解相结合来降低高成本，但仍然为较大的客户端使用单独的数据库。在这种情况下，您绝对需要明确地与您的客户保持联系。
Except for the server cost sharing, this is a very bad idea still - but cost can be a very important aspect too. This is really the only justification for this approach - avoid this if at all reasonable though. Maybe you would be better off to change a little more for you product, or just not be able to support tiny customers for a cheap price.
除了服务器成本分摊之外，这仍是一个非常糟糕的想法 - 但成本也是一个非常重要的方面。这实际上是这种方法的唯一理由 - 尽管如此，尽管如此，要避免这种情况。也许你会更好地为你的产品改变一点，或者只是不能以便宜的价格支持小客户。
#2
4
I'm assuming that by multiple customers you're not just storing customer information, you're hosting databases for an application for the customers, like CRM systems.
我假设多个客户不仅存储客户信息，而且还为客户的应用程序（如CRM系统）托管数据库。
If so, then I would absolutely not store everything in the same database.
如果是这样，那么我绝对不会将所有内容存储在同一个数据库中。
Reasons:
原因：
- Backup, when one customer calls and says that he needs to restore a backup because an intern managed to clean out the production database and not the test database, you do not want to have to deal with all the other customers at the same time
- 备份，当一个客户打电话并说他需要恢复备份因为实习生设法清理生产数据库而不是测试数据库时，您不希望同时处理所有其他客户
- Security, even with a bug in the application it won't be able to reach data for other customers. Also, consider if one customer is a bit too relaxed in their own security considerations and leaks passwords or whatnot to the system, if hackers discovers a way into that customers database, consider the fallout if that also includes all other customers you're hosting for.
- 安全性，即使应用程序中存在错误，也无法为其他客户访问数据。另外，考虑一个客户是否在他们自己的安全考虑因素中过于放松并且泄露了密码或其他信息，如果黑客发现进入该客户数据库的方式，如果还包括您托管的所有其他客户，请考虑其后果。
- Politics, some customers will not allow mixing their data with other customers even if you can 100% guarantee that access to their data won't be (accidentally) given to other customers
- 政治方面，即使您可以100％保证不会（意外地）将数
So bottom line: separate databases.
所以底线：单独的数据库。
#3
1
One day your developer will screw up something and one customer will access info of another customer. You will lose your customers as result. This alone should tell you that multiple customers can't be in one data base. no one will want to be your customer if they know this.
有一天，您的开发人员会搞砸一些东西，一个客户将访问另一个客户的信息。结果会导致您失去客户。仅这一点就应该告诉您多个客户不能在一个数据库中。如果他们知道这一点，没有人会想成为你的客户。
Do I have to really go over all issues that will eventually happen if this is the case? The answer is simple here - NO. You don't want to have information of multiple customers in the same database.
如果是这种情况，我是否必须真正解决最终会发生的所有问题？答案很简单 - 没有。您不希望在同一数据库中拥有多个客户的信息。
Only time that this happens is if you have multiplexer database to keep track of customer logons, sessions, etc. But data used and stored by customers should be in the dedicated database.
只有当您拥有多路复用器数据库以跟踪客户登录，会话等时，才会发生这种情况。但客户使用和存储的数据应位于专用数据库中。
#4
0
Some of the advantages to each approach to be considered are:
每种方法的一些优点是：
Single Database
单个数据库
- Relating data from different services can be bound together by foreign key constraints
- 关联来自不同服务的数据可以通过外键约束绑定在一起
- Analytic extracts are simpler to write and faster to execute
- 分析提取更易于编写，执行速度更快
- In the event of a disaster, restoring the platform to a consistent state is easier
- 在发生灾难时，将平台恢复到一致状态会更容易
- For data that is referenced by multiple services, data cached by one service is likely to be used soon after by another service
- 对于由多个服务引用的数据，一个服务缓存的数据很可能很快被另一个服务使用
- Administration and monitoring is simpler and cheaper up front
- 预先管理和监控更简单，更便宜
Multiple Databases
多个数据库
- Maintenance work, hardware problems, security breaches and so forth do not necessarily impact the whole platform
- 维护工作，硬件问题，安全漏洞等不一定会影响整个平台
- Assuming each database is on separate hardware, scaling up multiple machines yields more performance benefits than scaling up one big one
- 假设每个数据库都位于不同的硬件上，那么扩展多台计算机会比扩展一台大型计算机产生更多的性能优势
资源