I am creating a website that I want to offer as a service. Each customer will have their own database, and each site requires two databases. If I have 100 active customers and they are all working in their sites, I could have 200 distinct connection strings.
How do I find out how many is too many? I don't want to wait until I encounter a problem - I want to plan for it way in advance.
Are you using ASP.NET? .NET reuses the SQL connections with connection pooling. The real question, how many connections are open directly:
select COUNT(*)
from master.dbo.sysprocesses p
join master.dbo.sysdatabases d on p.dbID = d.dbID
where d.name = '<database>'
You can call this statement from your DAL, but I think it's not neccessery. Why? I have experiences with MSSQL 2000. It's stable with houndreds of open connections.
If your webservices are stateless (and that's a common and good pattern I think), you can avoid that connection-problem.
With statefull (I mean there is an permanent open connection) services it's hard to plan and I think you should rethink your design.
Load test.
Write a little multi-threaded console application that opens many connections that you would like to establish and check it out for yourself. Try to determine how much query execution each connection will be performing and make sure that you include that in your test. When you're running your test, open up the performance monitor on the db server and watch the CPU cycles. Figure out what your benchmark is for CPU cycles and when you have gone over that then you have your answer. Make sure the db server that your testing is set-up exactly like the server that your going to be running in production.
Don't wait until you have a problem. Your customers will not be happy with that.
The number of connections isn't a particularly useful resource to place limits on. The load on your server is a lot more sensitive to what is being done on those connections. What would you do with the knowledge? Refuse connections once a limit is reached? How will you know that exceeding that limit will start to degrade the user experience?