How to evaluate the hardware requirements for a SQL Server 2005 database?

Question

We are asked to specify the production database hardware for an ASP.NET web application that is not yet built.

The spectra that we need to determine are as follows:

• Core processor
• Database I / O
• Database RAM

Here are the indicators I'm currently looking at:

• Estimated number of future web site accesses based on current IIS logs.
• The estimated worst peak loads for the site.
• Estimated number of database queries per page, on average.
• The number of servers in the web farm that will delete the database.
• Tracing a cache from a database (using SqlCacheDependency).
• Invalid data cache skips.
• Estimated number of daily database transactions.
• The maximum allowed page rendering time.

Any other metrics we should consider?

In addition, as soon as we get all these indicators, how do they switch to equipment requirements?

Answer 1

What I have been doing recently for server planning is the use of some free tools that HP provides, collectively called "server sizers." These are great tools because they determine the optimal type of RAID to use and the correct number of disk spindles to handle the load (very important when planning a good database server) and memory processor, etc. I have provided the link below. Hope this helps.

http://h71019.www7.hp.com/ActiveAnswers/cache/70729-0-0-225-121.html?jumpid=reg_R1002_USEN

Answer 2

What I am missing is a measure of the necessary / required / specific level of reliability.

While you could probably define a large tracking machine to handle the entire load, depending on your reliability requirements, you might want to invest in smaller, but fewer machines and more secure disk subsystems (RAID 5) .

Mark

Answer 3

In my opinion, the evaluation of equipment for an application that has not yet been built and designed is more likely to be related to politics than to a scientific problem. By the time the project is completed, the current hardware capabilities and their price, functional requirements, the expected number of simultaneous users, external systems, and all other things will change, and this change cannot be controlled. However, this question arises very often, since you need to put numbers in a proposal or provide a report to your manager. If this is an offer, then what you are trying to fulfill is to develop a specification that can support the proposed software system. The only trick is to offer a system that won’t increase your competitive costs without jeopardizing your system’s poor performance.

Answer 4

If you can characterize your current workload in terms of getting to pages, then you can: 1) calculate the typical type of query that will be executed for each page 2) using the above 2 pieces of information, evaluate the workload on the database server

You also need to determine your performance requirements - what is the maximum and average response time that your site requires?

Given the load and performance requirements, you can calculate the capacity. The best way to make this assessment is to use some existing equipment, run the downloaded database simulation in the database on that equipment, and then extrapolate your hardware requirements based on your data from the first steps.