如何处理连接到多个 SQL Server 数据库的 Web 应用程序？
我正在构建一个 ASP.NET Web 应用程序，它将使用 SQL Server 进行数据存储。我正在继承现有的结构，并且无法对其进行太多修改。使用该应用程序的人是付费使用该应用程序的个体公司。每家公司大约有 5 到 10 人会使用该应用程序。大约有1000家公司。按照当前系统的结构方式，每个公司在 SQL Server 实例中都有自己独特的数据库。每个数据库的结构都是相同的。我不认为这是一个好的数据库设计，但我对此无能为力。还有其他应用程序访问此数据库，为所有这些应用程序重写数据库接口将是一项艰巨的任务。
所以我的问题是如何设计新的网络应用程序的架构。每月的某些时候，该网站会获得大量流量。我的感觉是，该网站在这些时候表现不佳，因为我猜测，当我们有 500 名来自不同公司的人员同时访问该网站时，他们每个人都会有自己独特的数据库连接，因为他们正在使用不同的方式访问不同的 SQL Server 数据库。连接字符串。 SQL Server 将不会使用任何连接池。我的印象是这很糟糕。
如果他们的客户数量增加一倍会发生什么？ SQL Server 可以处理多少个唯一的数据库连接？在这种情况下，我是否应该告诉客户，如果他们想保持可扩展性，他们必须重新设计？
谢谢， 科里
I am building an ASP.NET web application that will use SQL Server for data storage. I am inheriting an existing structure and I am not able to modify it very much. The people who use this application are individual companies who have paid to use the application. Each company has about 5 or 10 people who will use the application. There are about 1000 companies. The way that the system is currently structured, every company has their own unique database in the SQL Server instance. The structure of each database is the same. I don't think that this is a good database design but there is nothing I can do about it. There are other applications that hit this database and it would be quite an undertaking to rewrite the DB interfaces for all of those apps.
So my question is how to design the architecture for the new web app. There are times of the month where the site will get a lot of traffic. My feeling is that the site will not perform well at these times because I am guessing that when we have 500 people from different companies accessing the site simultaneously that they will each have their own unique database connection because they are accessing different SQL Server databases with different connection strings. SQL Server will not use any connection pooling. My impression is that this is bad.
What happens if they were to double their number of customers? How many unique database connections can SQL Server handle? Is this a situation where I should tell the client that they must redesign this if they want to remain scalable?
Thanks,
Corey
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您不必为每个数据库创建单独的连接，
我有一个应用程序在同一服务器上使用多个数据库。我在每个查询前加上“USE dbName;”前缀，
我什至在同一个调用中对两个单独的数据库运行查询。
至于调用存储过程，这是一个略有不同的过程。因为您不能这样做
，所以您必须显式更改连接对象中的数据库。在 .Net 中，它看起来像这样：
然后调用您的存储过程。
You don't have to create separate connections for every DB
I have an app that uses multiple DBs on the same server. I prefix each query with a "USE dbName; "
I've even run queries on two separate DB's in the same call.
As for calling stored procs, it's a slightly different process. Since you can't do
Instead you have to explicity change the DB in the connection object. In .Net it looks something like this:
then call your stored procedure.
希望您有一个通用项目的数据库。在这里，我希望您有一个包含
IsEnabled、Logo、PersonToCallWhen TheyDontPayBills等的Clients表。添加一列数据库（即目录），当您使用它时，服务器。您的 Web 应用程序将在启动时指向通用数据库，并为每个客户端构建数据库连接列表。使用表中的Server和Database列以编程方式构建数据库连接字符串。更新：
在与@Neil讨论之后，我想指出我的方法假设单例数据库连接。如果你不这样做，那么听从我的建议就是愚蠢的。
Hopefully, you have a single database for common items. Here, I hope you have a
Clientstable withIsEnabled,Logo,PersonToCallWhenTheyDontPayBills, etc. Add a column forDatabase(i.e. catalog) and while you're at it,Server. You web application will point to the common database when starting up and build the list of database connetions per client. Programmatically build your database connection strings with theServerandDatabasecolumns in the table.UPDATE:
After my discussion with @Neil, I want to point out that my method assumes a singleton database connection. If you don't do this then it would be silly to follow my advice.
扩展是一个复杂的问题。但是为什么你不扩展网络方面呢？然后连接池仅限于Web应用程序。
编辑：
我这里说的是一般情况。我知道池化发生在很多级别，而不仅仅是 IDbConnection (http://stackoverflow.com/questions/3526617/are-ado-net-2-0-connection-pools-pre-application-domain-or-per-过程）。我想知道提问者是否考虑过在我们的应用程序级别进行扩展。
Scaling is a complex issue. However why are you not scaling the web aspect as well? Then the connection pooling is limited to the web application.
edit:
I'm talking about the general case here. I know tha pooling occurs at many levels, not just the IDbConnection (http://stackoverflow.com/questions/3526617/are-ado-net-2-0-connection-pools-pre-application-domain-or-per-process). I was wondering whether the questioner had considered scaling at the we application level.