I think you would have to qualify your statement "it's handling everything just fine.". Does that mean that SQL Server is not having large request queuing? If SQL is not a bottleneck, you would have to find out what is. In general, it is easier and more cost-effective to scale out application logic tiers of a multi-tier application, than the database server. this is the general rule I think is true, probably for all relational databases. Usually you only get one database server, and it is very very expensive to scale. SQL Server enterprise is $4k / cpu core. Long-running requests that perform business logic could easily cause request queuing and blocking problems for SQL Server. An application will typically just sit there waiting for a response from the db server. The longer it waits, the less performant it will be, or at least seem to be. If you keep the requests to the database shorter-running (more granular, e.g. simple select statements that use indexes and only bring back the data you actually need), then your application servers will spend less time waiting for reponses from SQL, SQL will spend less time performing logic and more time retrieving data which is what it excels at. The queueing will be reduced on the database server, and you can increase the # of application servers and experience a predictable increase in the application's capacity to handle more. another thought just occurred to me, it sounds like your 25 web/app servers are all hitting the database directly. perhaps there is a case for creating a data access tier on a separate (small) set of servers that does all the database communications. using that approach you could implement some caching there. I would do a POC and also see if it makes sense to put the business logic in that same tier. anyways, that's my 2cents, hope that helps

This is a great answer! thanks Shuqian Ying. old topic though. A database server is a very expensive resource, and typically there's only ever one. stored procedures have a use I am sure, but on large systems where you need high concurrency, they are a killer. it would be like using your relational database server as a database server and an application server--just because you can, doesnt mean you should. if your app logic runs in c# code on an application server, then to scale the app would often just mean throwing in another cheap app server or two. The best illustration i have seen on how to properly scale apps from the db through to the clients is from an albeit quite dated book by Joseph Moniz: "Enterprise Application Architecture with VB, ASP, MTS". the first 100 pages which is on architecture and scaling, are still relevant today, and worth the effort to obtain the book and read. There are other really good reasons not to use stored procs if possible, two that I can think of are: lower all-around development costs (dev, testing, promotion, maintenance, versioning), and database-vendor neutrality (T-SQL wont run on Oracle; but DML SQL is at least very similar across db vendors).

Whatever the popular thing to detest is on this site, the fact remains that SSRS is at least a decade behind Crystal reports in terms of its capabilities as a report designer. Deployment is not free with SSRS either. SSRS in my opinion (these are all my opinions of course) runs best as an Intranet application where you have active directory going, single sign-ons, and very very simple reports. I remember the report designer.. I think it was Access 1.1 or 2. SSRS's designer is not much better, if. caveat is, I am familiar with ssrs on sql 2005 and have only read about sql2008's ssrs report designer yet. I am ever hopeful that someone at SAP/BO/Crystal will decide to make it easier to deploy crystal on a shared hosting environment (but not holding my breath).