I've got a new server which will host MS SQL Server Virtual Machine along with Other VMs on HyperV. SQL Server will account for most(70%) of the Load.
The server has below Storage:
a. 2 x 240GB Read Optimised SAS SSD (Intel S4610) configured in RAID1 for Host OS
b. 2 x 480GB Mixed Use SAS SSD (KIOXIA PM5-V)configured in RAID1 - For DB Files
c. 4 x 1.2TB 12Gbps SAS 10KRPM HDDs configured in RAID5(no hotspare) - For DB Files + Other VMs
The SSDs are over-provisioned by leaving 10% space unallocated on both RAID volumes, yielding 200Gb and 400Gb usable space respectively.
Based on my research, below is the data I have:
- Data files (mdf) are generally read-intensive and can benefit from SSD.
- Log files (ldf) generate Sequential write IO. They don't benefit too much from SSD and can negatively affect SSD Endurance.
- TempDB is Read/Write intensive - It can cause SSD burnout. Can be slower on HDDs.
I was planning to put Data files on SSD and put Log Files & TempDB on SAS HDDs considering that tempdb can cause premature SSD failure. Does this still hold good in 2020? Am I being overcautious about this?
Any suggestions on how to configure storage?
Below are virtual file stats obtained from current(old) server by running query mentioned here:
- The old server has D:(data) & E:(log) drives running on separate RAID5 and RAID1 Arrays respectively.
- The server was rebooted at yesterday midnight and these stats represent usage at 3pm (15hrs of usage).
- SQL Server Version: Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
