SQL Server hardware specs

Looking for recommendations for the best server build and SQL configuration.  
Raid 10?
Different raid controllers?  physical disks?  TempDB...
Installed SQL Server 2008R2 many times but never on what I think would be the best hardware configuration to maximize the Servers efficiency.

Thank you
JCONNELLY626VP ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
John TsioumprisSoftware & Systems EngineerCommented:
Well hardware is great always...but a carefully designed database with normalized tables and correctly placed indexes should probably outperform a "lower quality" one even if the 2nd runs on better hardware...
Usually SQL likes everything to the max...CPUs/RAM/HDDs-->SSDs...personally i would go for the SSDs...i would want the CPUs/RAM to be the best money can buy but if it was to decide between buying snappier storage to something else i would go to the storage...
0
 
ITSysTechSenior Systems AdministratorCommented:
It would depend on your environment. I would recommend using VMware for your SQL back end to make your life a lot easier in case of a corrupted database etc.,. One important thing you want to remember is to separate your disk drives to make recovery easier. So for your *.mdf files you can put them under the D drive and the *.ldf files you would want them under the L drive. Then for the master,model,MSDBData and templog (system databases) could go under the M drive. If you put everything under the same drive letter you are asking of trouble. As for the raid controllers you could spec out a good server like a Dell power edge that has a USB slot for VMware and spinning drives for your raid. We found SSD drives are very expensive so we go with spinning drives which work great for many years. Before you chose a MSSQL version check with your software vendors to make sure their product will work with your chosen version. In many cases your MSSQL version will not work with their databases. If you can I would recommend Windows 2012 R2 with MSSQL 2016 SP1. It has been very stable and runs will with most software versions. Windows 2016 still has some issues and might cause problems in the future unless you waited for a more stable version Windows 2016.

"RAID 10 has advantages of both RAID 0 and RAID 1. RAID 10 uses all the drives in the array to gain higher I/O rates so more drives in the array higher performance. RAID 5 has penalty for write performance because of the parity in check."
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Mal OsborneAlpha GeekCommented:
How much money do you have, and how fast to you want to go? A few general pointers:

1. SQL will use a LOT of memory if it is there, approximately up to the size of the databases. So, a 100Gb database, 100Gb of RAM can be utilized. It basically keeps anything ever read from disk in memory unless there is not much left. RAM is usually cheap, so no sense skimping.

2. Generally, RAID 10 is the better option, giving good performance and availability, however you need to purchase twice the required capacity.

3. SSDs at significantly faster, but expensive, if you need a lot of space.

4. CPUs can be tricky. Newer versions of SQL use "core based" licensing. It differs a bit between SQL2012 and SQL2016. Generally, a smaller number of faster cores will be cheaper than a larger number of slower cores. You will probably have to talk to a licensing expert, or spend a few days researching this, and figuring out the best way to go, during the process of selecting CPUs, rather than just purchasing a server and then figuring out licensing. There are quite a few options, and getting this right can save you  heap of money.
0
 
ITSysTechSenior Systems AdministratorCommented:
No activity from OP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.