what is the best specifications server to work with SQLSEVER 2008

Dear Colleagues

In the company that I work we need to buy a server .So they asked me what are the best specifications for SAN(Storage area network):

  • Drive types, from fastest to slowest.
  • RAID levels? (RAID 10 or RAID 5)
  • Connection methods

and I will give you the idea that what we want to do
we want to make a clustering networking and install SQL server in 3 servers and there are 500 clients use application design by ASP and the same time they are connected to the server and input data ,we need to do this operation very fast(overload),this the idea in general.

AS the DBA they asked me the best practice for SAN? I need the good reference to put the specification for the server ,what is the best server? any Idea How to do that ?
The ICT told me maybe this server is good .

HP DL380G7 X5660 Six Core High Performance Rack Server (583970-421) –Processor(s): (2) Intel® Xeon® Processor X5660 (2.80 GHz,12MB L3 Cache, 95W, DDR3-1333, HT, Turbo 2/2/2/2/3/3)

many thanks
more ▼

asked Jul 05 '12 at 07:46 AM in Default

MuhSQL gravatar image

102 7 7 9

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

It depends.

  • What's your data set size?
  • What are your I/O requirements?
  • How many users will this server be servicing?
  • What else will you be doing on this box? eg SSRS, SSAS?
more ▼

answered Jul 05 '12 at 09:25 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.3k 14 20 44

Thank you for quick respond .

  • What else will you be doing on this box? SSRS.

  • How many users will this server be servicing? 20 clients make scan and send the images to the database server and the size per image is 6 mb. and maybe per user scan and send 500 denouements .

What's your data set size? as I I mentioned below for 20 computer scan and send to sql server , the other 500 computer I don't now

What are your I/O requirements? could you plz tell me what you mean.

Jul 05 '12 at 09:58 AM MuhSQL

By IO requirements, I mean:

  • how much data is to be transferred from disk to users per second (in Mb per second)
  • how long are the users prepared to wait for that data? (in milliseconds)
These two figures may provide more information about the storage requirement - for example, a system with a very high data throughput with low latency would need to be serviced either by a very large number of physical disks in a RAID configuration, or could be handled by just a couple of SSDs...
Jul 05 '12 at 11:26 AM ThomasRushton ♦

Thanks a lot Thomas for your great notes and Anuj "SQL Server Hardware Choices Made Easy" Really it's very good book thank you

Now Thomas asked me a question "What are your I/O requirements?"

Is this query good to answer the question

SELECT DB_NAME(DB_ID()) AS [Database Name] ,
[file_id] ,
num_of_reads ,
num_of_writes ,
num_of_bytes_read ,
num_of_bytes_written ,
CAST(100. * num_of_reads / ( num_of_reads + num_of_writes )
AS DECIMAL(10,1)) AS [# Reads Pct] ,
CAST(100. * num_of_writes / ( num_of_reads + num_of_writes )
AS DECIMAL(10,1)) AS [# Write Pct] ,
CAST(100. * num_of_bytes_read / ( num_of_bytes_read
+ num_of_bytes_written )
AS DECIMAL(10,1)) AS [Read Bytes Pct] ,
CAST(100. * num_of_bytes_written / ( num_of_bytes_read
+ num_of_bytes_written )
AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) ;

Now , let say we did it to simple database like AdventureWorks abd the result was

alt text

how will be useful for us ? I hope I answer one of your Question Thomas .
Jul 06 '12 at 10:03 AM MuhSQL
(comments are locked)
10|1200 characters needed characters left

There is a lot of information missing here, that you seem not to know yourself rather than not supplying it in your question.

I suggest finding out what data access patterns are going to be. You cannot size a system for 520 users and only know what 20 of those users are going to be doing.

Be that as it may, a general rule of thumb for SQL Server that I follow is to prioritize your hardware spending as follows:

  1. RAM : Get as much as possible and addressable by your SQL Server Edition (check out the limits before buying!)
  2. HDD/SAN: Get as many spindles (physical disks) as possible. In a SAN environment you have to watch out because you will get LUNs and not disks. A LUN is a logical abstraction inside a SAN which allows for the use of RAID and other technologies/techniques to get better performance for reduced cost. A lot of SANs are setup for fileserving rather than databases and this can be a major performance problem later on.
  3. CPU: Most CPUs are so powerful today it is almost irrelevant. Unless you know you are doing serious CPU crunching a mid-level current generation CPU will be fine. Watch out for core counts, cache levels and also SQL Server licensing with regard to CPUs when making your choice. The one you mentioned is plenty powerful and should be fine.

The SAN portion is the most difficult and if you have no experience, then get in touch with an expert (in your company maybe). Brent Ozar has a pretty good Best Practice for SAN Storage article that may help here.

I strongly suggest you get more details about the applications that are going to be working with the SQL Server and if possible get some sort of baseline for the application.

more ▼

answered Jul 05 '12 at 10:14 AM

WilliamD gravatar image

25.8k 17 19 41

@WilliamD +1.

@MuhSQL Now a days with virtualization so common, general trend is to get one big machine instead of 2/3 mediocre machines. But it all depend upon the usage patterns, workload etc. The point is to keep bigger picture in mind which can save you licensing costs, physical maintenance etc. Also, the most common hurdle in such situations could be the company's budget. You may want to buy a bigger machine but may end up with a mediocre one ;)
Jul 05 '12 at 11:07 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

Thought to share free ebook from Glenn Berry's new "SQL Server Hardware Choices Made Easy" has plenty of tips for choosing right hardware for your SQL Server.

more ▼

answered Jul 05 '12 at 11:15 AM

Cyborg gravatar image

10.6k 36 39 45

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 05 '12 at 07:46 AM

Seen: 1075 times

Last Updated: Jul 06 '12 at 10:57 AM