I believe you don't have enough data to define the best architecture. Performance is never a one size fits all kinda thing. You have an existing system, so I'd...
- Check out the hardware to make sure it's not having any failures.
- Run the Performance Monitor to look at disk/memory/CPU usage, swap space size/usage, etc... over time and make sure the servers are beefy enough for the job.
- Assuming 1 & 2 are ok, start looking at which component (webserver/x-tier/database) is the bottleneck. Basically "divide and conquer".
- Performance Monitor should include pegs for IIS. Review them over time.
- You'll have to figure out how to measure any business/data tier in the middle
- Use SQL Server Management Studio and SQL Server Profiler to identify SQL Server bottlenecks. Management Studio shows any detected deadlocks, so check it out first. I"d then use Profiler like this... identify a report that's slow, then identify which stored procedure/query it uses. Use Profiler's filters to narrow down the trace as much as possible... you're looking for an actual statement WITH the parameters used. Take that and go back to Management Studio. Open a new window, paste in the query, add "Include Actual Execution Plan" (under the Query menu), and run. It'll take longer, but SQL Server will tell show you the bottleneck (as a %).
This should give you enough data to know how to fix this performance issue.
Most importantly:
define performance standards and
measure them over time. My personal standards are: webpages must take no longer than 5 seconds & user facing SQL statements/stored procedures must be sub-second.
Performance tuning is a set of trades. You give up x to achieve y. Example: Reporting Server tends to give up space and performance for easy, user definable reports. To make reporting faster, write & optimize the high traffic reports yourself (thus loosing the user definable feature).
A few tools to help fix performance issues: (in no order)
- Obviously fix and/or upgrade all hardware issues.
- Recode any bottlenecks found in webpages or middle tier(s).
- Database tuning is a bit trickier...
- Make sure SQL Server does not do any table scans. You want either an index seek or index scan. Add/rework indexes as needed. (Table scans on temp tables and table variables MIGHT be ok... choose carefully.)
- De-normalize data as needed if the joins get too intense.
- Some tables have simply too much data to query effectively.
- Look at table partitioning as a possible fix.
- Consider pre-aggregating data. Example: assuming your system reports on receipts in the past week, month, and/or year... then have a nightly process to aggregate all receipts into separate weekly, monthly, and yearly tables. Base all reports on these new tables.
- I actually would recommend a 3rd server. Have the database live on the largest box and have 2 identical, load balanced webservers. Usually we don't get the hardware we need/want, so if you're stuck with two servers the web/database server config is probably your best. Always have your database on the most powerful box possible. It's usually the bottleneck and doesn't always scale well.
Side note: MAKE SURE the database is being backed up and TEST the recovery process. Losing the database in this config means you're toast and (if me) out of a job.
...hope it helps...