Search code examples
asp.netsql-serverconnection-pooling

How can I handle a web application that connects to many SQL Server databases?

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

Solution

  • 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 

         Use myDB; spBlahBLah

    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.