如何处理连接多个SQL Server数据库的Web应用程序？ [英] How can I handle a web application that connects to many SQL Server databases?

问题描述

我建立，将使用SQL Server存储数据的ASP.NET Web应用程序。我继承现有的结构，我不能够很修改。谁使用这个应用程序的人是谁支付使用的应用程序个别公司。每个公司都有约5或10人谁将会使用的应用程序。大约有1000家公司。该系统是目前的结构方式，每家公司都有在SQL Server实例自己独特的数据库。每个数据库的结构是相同的。我不认为这是一个很好的数据库设计，但并没有什么我可以做些什么。有迹象表明，打这个数据库的应用程序，这将是一个相当承诺改写DB接口为所有这些应用程序。

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.

所以我的问题是如何设计的架构，新的Web应用程序。还有一个月的时间，其中的网站将获得大量的流量。我的感觉是因为我猜测，当我们有来自不同公司的同时，他们将每一个都有自己独特的数据库连接，访问该网站的500人，因为他们正在访问不同的SQL Server数据库与不同的网站将不会在这些时间表现良好连接字符串。 SQL Server将不使用任何连接池。我的IM pression是，这是不好的。

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.

如果他们要加倍客户数量会发生什么？有多少独特的数据库连接，可以在SQL Server处理？这是一个情况，我应该告诉客户，他们的必须的重新设计这个，如果他们想保持可扩展性？

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?

谢谢，
科瑞

推荐答案

您不必为每一个数据库创建单独的连接

You don't have to create separate connections for every DB

我有在同一服务器上使用多个数据块的应用程序。我preFIX每个查询了USE数据库名;

I have an app that uses multiple DBs on the same server. I prefix each query with a "USE dbName; "

我即使在同一呼叫运行查询在两个单独的DB的。

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

     Use myDB; spBlahBLah

相反，你必须明确地改变DB的连接对象。在.NET中，它看起来是这样的：

Instead you have to explicity change the DB in the connection object. In .Net it looks something like this:

     myConnection.ChangeDatabase("otherDBName");

然后调用存储过程。

then call your stored procedure.

这篇关于如何处理连接多个SQL Server数据库的Web应用程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！