一个应用程序多个实例和不同的DB

I am building an case management application and one of the requirements is that each client gets their own Database with their own URL. However, it is starting to be a nightmare to maintain multiple instances of the application when upgrading. I am using IIS 7 ASP.NET MVC. I would like to have one application and have the application be aware of which database to get the data from depending on the User authentication. Is there a viable alternative?

我正在构建一个案例管理应用程序，其中一个要求是每个客户端都使用自己的URL获取自己的数据库。但是，升级时维护应用程序的多个实例已成为一场噩梦。我正在使用IIS 7 ASP.NET MVC。我想有一个应用程序，让应用程序知道根据用户身份验证从哪个数据库获取数据。有可行的替代方案吗？

7 个解决方案

#1

Yes its better to have one instance of the application if possible otherwise it gets complicated. If the features for different clients are different then use feature flags to turn-on/turn-off features for each client/user. See below article by Martin Fowler about it. Feature flags can be per user or client. Facebook and few other major sites use feature flags effectively.

是的，如果可能的话，最好有一个应用程序实例，否则会变得复杂。如果不同客户端的功能不同，则使用功能标志为每个客户端/用户打开/关闭功能。请参阅Martin Fowler的以下文章。功能标志可以是每个用户或客户端。 Facebook和其他几个主要网站都有效地使用功能标记。

http://martinfowler.com/bliki/FeatureToggle.html

Regarding the database, I think you can have a Common database which has all basic information including all clients and then have other databases specific to each client which has all other data for the client.

关于数据库，我认为您可以拥有一个Common数据库，其中包含所有基本信息，包括所有客户端，然后具有特定于每个客户端的其他数据库，其中包含客户端的所有其他数据。

When any user hits a client specific URL in the Session_Start method of Global.asax add logic to fetch the appropriate connection string and store it in session (Session["ClientDbConnectionString"]) so that you can use it whenever you need data from client DB.

当任何用户在Global.asax的Session_Start方法中命中客户端特定URL时，添加逻辑以获取相应的连接字符串并将其存储在会话中（Session [“ClientDbConnectionString”]），以便您可以在需要来自客户端数据库的数据时使用它。

I would suggest you to store all the connection strings in a table in Common database (with a key identifying each client) so that you can add new connection string row when you want to on-board a new client. Whenever you do a new release I would suggest to update all the client databases together instead of just updating one client DB otherwise it will become unmanageable after a while.

我建议您将所有连接字符串存储在Common数据库的表中（使用标识每个客户端的键），以便在您想要加载新客户端时添加新的连接字符串行。每当您执行新版本时，我建议一起更新所有客户端数据库，而不是仅更新一个客户端数据库，否则一段时间后它将变得无法管理。

#2

Your question is really just a tip-of-the-iceberg question. Multi-tenancy is a complex topic. Having a per-client connection to the tenant database is just one aspect. There are other aspects to consider:

你的问题实际上只是一个冰山一角的问题。多租户是一个复杂的主题。与租户数据库建立每个客户端连接只是一个方面。还有其他方面需要考虑：

balancing of tenants: how do you ensure that tenants that grow much faster than the average get adequate resources and do not overwhelm other tenants that are collocated on the same storage (eg. same instance). How do you move a tenant that had grown? How do you aggregate small tenants with low activity?
租户平衡：您如何确保比平均增长速度快得多的租户获得足够的资源，并且不会压倒在同一存储上并置的其他租户（例如同一个实例）。你如何移动已经成长的租户？你如何汇总低活动的小租户？
isolation of resources: a tenant can consume a large percent of resources (eg. it can run a query in the database that take sup all the CPU and starves all the other queries), how do you ensure fairness to other tenants that are collocated?
资源隔离：租户可以消耗大量的资源（例如，它可以在数据库中运行查询，占用所有CPU并使所有其他查询匮乏），您如何确保其他租户的公平性？
shared data. Replicating changes to shared data (data that is common among all tenants, eg. lookup tables) can be problematic.
共享数据。复制共享数据的更改（所有租户中常见的数据，例如查找表）可能会有问题。
schema changes. This is one of the trickiest topics. Database schema and code are usually changed in-sync (code expects a certain schema) and deploying a schema change to all tenants can be problematic.
架构更改。这是最棘手的话题之一。数据库模式和代码通常是同步更改的（代码需要某个模式），并且对所有租户部署模式更改可能会有问题。

I recommend you go over the Multi-Tenant Data Architecture white paper. This presents three basic approaches:

我建议您阅读多租户数据架构白皮书。这提出了三种基本方法：

Separate Databases
单独的数据库
Shared Database, Separate Schemas
共享数据库，单独的模式
Shared Database, Shared Schema
共享数据库，共享架构

In addition, I would add the SQL Azure Federations option (which was not available at the time the white paper was written). The paper discuss pros and cons of these approaches from database/storage point of view, considering things like:

另外，我会添加SQL Azure Federations选项（在编写白皮书时不可用）。本文从数据库/存储的角度讨论了这些方法的优缺点，考虑如下：

storage cost
存储成本
security
安全
availability
可用性
scalability
可扩展性
ease of schema change (app upgrade)
易于架构更改（应用程序升级）
extensibility
可扩展性

From the client side, I'm not aware of any MVC extension that helps for the multi-tenant case, something along the line of the act_as_tenant gem in Rails.

从客户端来看，我不知道任何有助于多租户案例的MVC扩展，这与Rails中的act_as_tenant gem一致。

is starting to be a nightmare to maintain multiple instances of the application when upgrading.

在升级时维护应用程序的多个实例开始成为一场噩梦。

This is actually one of the biggest problems in multi-tenant architectures. Even if you have eliminated the friction of actually doing the upgrade in a DB, it is still a difficult problem to solve. Unless you can afford downtime on all tenants and take the entire system offline, upgrade all tenants databases, deploy new code that understands new schema, then bring all tenants online, doing it online is challenging because the app code (the ASP/MVC code) must be able to understand both versions (old and new) at once. Is not impossible to solve, but is difficult and must be coded carefully.

这实际上是多租户架构中最大的问题之一。即使您已经消除了在DB中实际进行升级的摩擦，仍然是一个难以解决的问题。除非您能够承受所有租户的停机时间并使整个系统脱机，升级所有租户数据库，部署了解新架构的新代码，然后将所有租户联机，因为应用程序代码（ASP / MVC代码），在线进行是很有挑战性的必须能够同时理解这两个版本（旧的和新的）。不是不可能解决，但很难并且必须仔细编码。

That being said, an important part if 'eliminating the friction of actually upgrading'. I do no what procedure you employ to deploy an upgrade. It is critical that the upgrade is automated and scripted, without any manual intervention. Diff based tools are sometimes used, like SQL Compare from Red-Gate or even Visual Studio's vsdbcmd.exe. My favorite approach though is using upgrade scripts and metadata versioning in the application. For more details see Version Control and your Database. For reference, this approach is basically the Rails Migrations approach.

话虽如此，如果“消除实际升级的摩擦”，这是一个重要的部分。我没有采用您采用的程序来部署升级。至关重要的是，升级是自动化和脚本化的，无需任何手动干预。有时会使用基于Diff的工具，例如来自Red-Gate的SQL Compare甚至是Visual Studio的vsdbcmd.exe。我最喜欢的方法是在应用程序中使用升级脚本和元数据版本控制。有关详细信息，请参阅版本控制和数据库。作为参考，这种方法基本上是Rails迁移方法。

#3

You would need to store the connection string for each user somewhere (maybe in the user table?) and look up this value whenever you create a new db-connection. This means that you can't use a connection string from web.config, but I assume you know how to create a new db-connection with a custom connection string.

您需要在某处（可能在用户表中？）存储每个用户的连接字符串，并在创建新的db-connection时查找此值。这意味着您不能使用来自web.config的连接字符串，但我假设您知道如何使用自定义连接字符串创建新的数据库连接。

#4

If i understand you well, you need to provide authentication for each client. If client were successfully authorized, he should be redirected to his site, which means that connection string should be changed programmatically.

如果我理解你，你需要为每个客户提供身份验证。如果客户端已成功授权，则应将其重定向到其站点，这意味着应以编程方式更改连接字符串。

Alternativelly you can define the number of connection strings in web.config file. Then you can use it depends on clients authorization: How to: Read Connection Strings from the Web.config File

或者，您可以在web.config文件中定义连接字符串的数量。然后你可以使用它取决于客户端授权：如何：从Web.config文件中读取连接字符串

Here is an example web.config setting:

以下是web.config设置示例：

<configuration>
    <connectionStrings> 
        <add name="Client1"
            connectionString="..." 
            providerName="System.Data.SqlClient" />
        <add name="Client2"
            connectionString="..." 
            providerName="System.Data.SqlClient" />
    </connectionStrings>
</configuration>

Cheers!

干杯!

#5

A problem I see in this that you now separate the application and it's database.

我在这里看到的一个问题是你现在将应用程序和它的数据库分开了。

In a usual situation there are updated together. You now could end up with an updated application and one or more databases that aren't updated.

在通常情况下，一起更新。您现在可能最终得到一个更新的应用程序和一个或多个未更新的数据库。

I think Adarsh Shah's answer is very good, but it misses this point.

我认为Adarsh Shah的答案非常好，但它忽略了这一点。

My question would be: Why can't you merge those databases?

我的问题是：为什么你不能合并这些数据库？

There are solutions to achieve this. For example:

有解决方案来实现这一目标。例如：

Suppose you have a table names Cases. Create a view, for example Cases_V (V from view). At logon, set a session variable CompanyId (I think this is possible in SQL Server, at least it is in Oracle which I use).

假设你有一个表名为Cases。创建一个视图，例如Cases_V（视图中的V）。在登录时，设置会话变量CompanyId（我认为这在SQL Server中是可能的，至少在我使用的Oracle中）。

In the view, filter the table on this session Variable.

在视图中，过滤此会话变量上的表。

The SQL of Cases_V would look like this (in oracle syntax):

Cases_V的SQL看起来像这样（在oracle语法中）：

select *
from   Cases
where  CompanyId = sys_context('USERENV', 'CLIENT_INFO'), 'CompanyId')

In your application you would use this view like this:

在您的应用程序中，您将使用此视图：

select *
from   Cases_V

This prevents users to access information from other 'installations' and minimizes maintenance.

这可以防止用户从其他“安装”访问信息并最大限度地减少维护。

#6

AdarshShah statement is very Correct, you should maintain the connection strings of different database in a separate MetaDatabase to simplify the process of identifying the respective connection string of the Particular client, specifically in a Multi Tenant environment you may require to roll out/allocate dedicated Database or Schema for a client, it might become a nightmare to manage and maintain connection strings in the XML file such as SQL.Config or Web.Config.

AdarshShah语句非常正确，您应该在单独的MetaDatabase中维护不同数据库的连接字符串，以简化识别特定客户端的相应连接字符串的过程，特别是在您可能需要推出/分配专用数据库的多租户环境中或者客户端的Schema，在XML文件中管理和维护连接字符串可能会成为一场噩梦，例如SQL.Config或Web.Config。

I'm part of an organization where we specialize in building Multi Tenant Framework which could be used by ISVs, Developers to build SaaS Applications Faster without breaking their head in analyzing and understand some of the common Non Functional requirements like this. One of the Primary component of the framework is the Data Sharder, which exactly does the same.

我是一个组织的一部分，我们专注于构建多租户框架，可供ISV使用，开发人员可以更快地构建SaaS应用程序，而不会在分析和理解一些常见的非功能性需求时失去理智。该框架的主要组成部分之一是数据分片器，它完全相同。

Please have a look, http://www.techcello.com/products/saas-features

请查看http://www.techcello.com/products/saas-features

#7

I'm going to present how I would try to solve this issue.

我将介绍如何尝试解决此问题。

First of all it is mandatory to have multiple connection strings on your application config. I suggest you to use Entity Framework for DB Management if your app is in ASP.NET MVC. You can build wrappers over the DbContext and ObjectContext classes(I mean you wrapper to inherit the mentioned classes), and use Factory Method to choose the context based on logged in user.

首先，必须在应用程序配置上有多个连接字符串。如果您的应用程序位于ASP.NET MVC中，我建议您使用Entity Framework进行数据库管理。您可以在DbContext和ObjectContext类上构建包装器（我的意思是您使用包装器继承所提到的类），并使用Factory Method根据登录用户选择上下文。

I assume that you know the database where the users are stored. So from Global.asax you are going to active that dbContext at first, and then based on user profile choose the proper context.

我假设您知道存储用户的数据库。因此，从Global.asax开始，您将首先激活该dbContext，然后根据用户配置文件选择适当的上下文。

#1