SQL Server hardware specs

JCONNELLY626 used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

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...
Senior Systems Administrator
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."
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.
ITSysTechSenior Systems Administrator

No activity from OP

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial