ansaurus

Question

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

Answer 1

A:

Hopefully, you have a single database for common items. Here, I hope you have a Clients table with IsEnabled, Logo, PersonToCallWhenTheyDontPayBills, etc. Add a column for Database (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 the Server and Database columns 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.

Brad 2010-10-06 17:48:05

Ok. I understand what you are saying. But my question is more about scaling and performance. I can set it up like you suggest but that doesn't solve the performance issue. Can the database handle 500 or 1000 simultaneous connections to 500 or 1000 different databases? My gut feeling is that this is a bad setup.

Corey Burnett 2010-10-06 17:52:30

I think you mean, can the **server** handle 1000 different connections to 1000 different databases. The answer is yes, the server can handle it. However connection pooling (defined in the connection string -- 500 by default) is set at the **connection** level. If you have all the clients on the same connection, you're going to run into the upper limit rather quickly it seems.

Brad 2010-10-06 18:21:33

Answer 2

A:

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.

Preet Sangha 2010-10-06 17:49:38

Connection pooling is limited to the IDbConnection.

Brad 2010-10-06 18:15:59

Answer 3

+2 A:

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.

Neil N 2010-10-06 17:50:32

Hmmm...interesting. So what database does your app connect to? Master?

Corey Burnett 2010-10-06 17:53:37

With that situation can you use stored procedures effectively? It would seem that you wouldn't be able to because your SQL would have to be built on the fly every time.

Corey Burnett 2010-10-06 18:00:27

It connects to the "default" database, not the master. But to be safe, it still calles USE even if its querying the default.

Neil N 2010-10-06 18:11:06

see the edit for sprocs

Neil N 2010-10-06 18:13:24

I think this is a good idea. **However**, if you have separate connections for each database, you don't have to wait for a statement for ClientA to complete to execute a statement for ClientB. I, personally, wouldn't go with this method.

Brad 2010-10-06 18:18:35

@Brad, that is incorrect. You can indeed run multiple queries against the same database, it's all taken care of via connection pooling.

Neil N 2010-10-06 18:22:05

@Neil, not if you follow your sample. You changed the database for the entire connection meaning that if you switch to ClientA, you can't execute any ClientB code until you can switch it back. No?

Brad 2010-10-06 18:25:23

@Brad, why not? The db doesnt care if you switch it back after a call is made. It's not going to interupt a currently running query and retoractively change it's db.

Neil N 2010-10-06 18:28:35

@Neil, okay, I see that logic (so +1 for your answer) *However* with 5000 potential users you're going to run thread-safe issues. You will need to lock the connection from just before changing the database until you execute the statement.

Brad 2010-10-06 18:30:56

Brad, I think you misunderstand how connection pooling works. It's not a single instance for every connection an application needs. Its an instance reserved for every active user. When a connection goes idle, it gets thrown back into the pool, to be used by the next user.

Neil N 2010-10-06 18:32:21

And if the pool hits its limit, the next user waits (if even for only a split second) until another query is done, freeing up the next connection.

Neil N 2010-10-06 18:33:04

@Neil, okay, so how does SQL know the difference between me and you? Is each and every user authenticating? Or is he using a single username/password the connection. If so, then to SQL they're all the same "user" and don't get separate connections from the pool.

Brad 2010-10-06 18:36:15

Brad, I STILL think you need to reestablish what you think about how connection pooling actually works. Even on sites that have a single user/pass and never switch DB, still create multiple connections in the pool.

Neil N 2010-10-06 18:40:10

@Neil, I understand that when you create a connection pool (i.e. IDbConnection) you can run multiple concurrent statements within it. My point is that the Database is set at the **pool** level, and between the time that you call `myConnection.ChangeDatabase("overHere")` and `myConnection.Open()` someone on another thread (this is the web) is destined to call `myConnection.ChangeDatabase("overThere")` and your `myConnection.Open()` is not going to connect to what you think it will.

Brad 2010-10-06 18:47:20

@Neil, my entire point about SQL not knowing the difference between users was your use of the phrase "It[']s an instance reserved for every active user". Connections within the pool aren't 'reserved' for users in the system but more likely threads. But the idea is that if you `.Close()` your connection, it goes back into the pool (which in my experience is not always foolproof -- but maybe I'm the fool).

Brad 2010-10-06 18:51:44

by user I meant vistors to the site (so yes, threads) and thread safety would only come into play if you were actually sharing a specific instance of the connection object across threads (which is bad practice anyways) I've had a site using multiple DBs on the same connection pool, that switches the db at will, with hundreds of concurrent users, since 2007, and never once has a query executed on the wrong db. The connection pool is thread safe.

Neil N 2010-10-06 19:01:11

@Neil, in my practice, I use a singleton database connection at the website level so as to avoid recreating it on each call. What you're saying is you create a new connection for each request? If so, then of course you're thread safe.

Brad 2010-10-06 20:04:11

a singleton connection is actually bad for performance, and defeats the purpose of the thread pool. There have been dozens of tests to prove this.

Neil N 2010-10-06 20:07:20

@Neil, I appreciate your understanding on this topic. If you could point me to resources on this, I would appreciate it. I'm not really understanding how you could benefit from connection pooling if you create a new connection for each thread (i.e. each postback). You would only be making, say, 1-10 calls on that connection, right?

Brad 2010-10-07 12:27:04

@Brad, you have to abstract it a little bit, every time you do new SqlConnection(), you are creating a new local instance, but NOT a new physical connection (that part comes from, and is managed by, the pool) http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Neil N 2010-10-07 14:23:50

And when you do a close() or dispose(), you arent really getting rid of the connection, you are only killing the local reference to it, the pool will still keep the physical connection open.

Neil N 2010-10-07 14:25:12

ansaurus

tags:

views:

answers:

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

related questions