asked
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
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?
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.
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...
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][1] [1]: /storage/temp/295-awres.jpg how will be useful for us ? I hope I answer one of your Question Thomas .
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][1] 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. [1]:
http://www.brentozar.com/sql/sql-server-san-best-practices/
@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 ;)
Thought to share free ebook from Glenn Berry's new "[SQL Server Hardware Choices Made Easy][1]" has plenty of tips for choosing right hardware for your SQL Server. [1]:
http://www.red-gate.com/products/dba/sql-virtual-restore/entrypage/sql-server-hardware-ebook
11 People are following this question.
