Link to home
Start Free TrialLog in
Avatar of jkirman
jkirmanFlag for United States of America

asked on

How to configure RAID drive arrays for SQL Server apps on Windows 2016 server?

Greetings,

I am needing to propose a server configuration to a client for the following environment and applications:

- will be running 2 SQL Server applications under separate instances of SQL - one running on SQL Server 2012 and one on SQL Server 2014.
- the SQL Server 2012 application will be servicing c. 15 users, and the SQL Server 2014 app will be servicing c. 80 users.
- am recommending a Dell rack-mount server R440 with chassis for up to 10 X 2.5" drives, 64 GB RAM running Windows 2016 Server Standard
- CPU's are 2 X Intel Xeon Silver 4110 2.1 GHz 8C/16T 11M cache
- dual Broadcom DP 1 GB NIC's for fault tolerance
- controller is Perc H730P with 2 GB NVCache

For the drive configurations, my base / minimum configuration for budget purposes will be:

- for O/S paging file - 2 X SAS 300 GB 15K drives in RAID-1 array
- for 1st SQL app / SQL Server 2012 - 2 X SAS 300 GB 15K drives in RAID-1 array
- for 2nd SQL app / SQL Server 2014 - 2 X SAS 300 GB 15K drives in RAID-1 array

Giving each SQL app its own RAID array will ensure there is no (minimal?) drive / disk access contention between the 2 instances of SQL Server.  

Yes, pretty vanilla in most respects.  I've had amazing success with the plain-Jane 300 GB SAS 15K drives for the last decade+, even though there is definitely newer technology out there, so I tend to stay with what has worked very well.  Haven't had the same level of success with NL SAS and certainly not SATA, so I stay with the basic SAS 15K drives.  They just work, no unreadable errors from the RAID controller, etc.

So above is the base disk setup.  I'm now pondering what to offer as next-level options.  Which if these would you recommend from a combined reliability / performance perspective?

Option 1) RAID-5 upgrades for SQL apps containers

a) Upgrade the 2 SQL arrays to RAID-5 by adding an additional 300 GB SAS 15K drive to each SQL app container, so there will be 2 RAID-5 arrays of 3 X 300 GB drives for the SQL apps.  I'd leave the O/S partition as RAID-1
b) Add 2 more 300 GB SAS 15K drives as global hot spares that can be used to protect either of the SQL containers as well as the O/S RAID-1 partition

Option 2) RAID-6 upgrades for SQL apps containers

a) Upgrade the 2 SQL RAID-1 arrays to RAID-6 by adding 2 X additional 300 GB SAS 15K drives to each SQL app container, so there will be 2 RAID-5 arrays of 4 X 300 GB drives for the SQL apps.  I'd leave the O/S partition as RAID-1.

I have not used RAID-6 in any system to date, but have heard many good things about it from a reliability perspective .  I have read a number of articles regarding how RAID-6 affects SQL performance, but have not been able to get a clear sense of whether this would or would not be recommended, as I'm seeing experiences and opinions going both ways.  One source e.g. said that RAID-6 with SQL is OK as long as you have a ton of cache on the controller, for which I assume the 2GB NVCache is a good configuration.  Another source said forget the RAID-6 and go with global spares, because RAID-6 adds is an additional drive to stripe per container and that introduces a consistent performance hit vs. RAID-5 and global spares.

Appreciate any thoughts on the above, and/or any other suggestions.

Many thanks.

jkirman
Avatar of kevinhsieh
kevinhsieh
Flag of United States of America image

I would remove all 15K SAS drives and go to a pair of SSD in RAID 1 instead. About 20X the performance for similar cost. Manufacturing of 15K drives is winding down. New investment in 15K drives has halted. SSD is cheaper, faster, and more reliable. Pick all three.

You don't need two of those CPUs. Those two CPUs could run 30-100 virtual machines, depending on workload. SQL Server is not CPU intensive. You may also be licensing SQL per core, in which case 16 cores is going to be really expensive.

I would virtualize on your favorite hypervisor (either vSphere of Hyper-V). Your Windows Standard license will give you 2 VMs, so you can have an extra free VM, or you can put each SQL Server installation into it's own VM, all for no additional licensing cost.
Avatar of Robert Lem
Robert Lem

You don't say what will be the size of your database and the expected amount of transactions per seconds. I agree with KevinHsieh on the SSD drives but at the condition that you have a lot of transactions going, otherwise 10K drives will be fine. Depending on the size of your database, 64GB might not be sufficient at all.
Avatar of jkirman

ASKER

First off, thank you both for your responses.

kevinhsieh - I looked up current data on SSD vs. SAS.   Although SSD clearly holds the speed advantage and is becoming adopted in many capacities and industry tiers, I still have some concerns with SSD, particularly from not having deployed it in any enterprise installation to date and not having a sense for its reliability.  Cost-wise, when configuring the Dell server, an 800 GB SSD SAS Write Intensive drive is about twice the cost of a 900 GB SAS 15K drive, with a similar size Read Intensive drive being c. 20% less than the Write Intensive version.  It is interesting to see that some drives are specifically designed as Read Intensive and some as Write Intensive.  I do see that SSD SATA is comparable in cost and in some cases cheaper than SAS.  However when it comes to enterprise storage do you recommend SSD DATA vs. SSD SAS?  I don't know how those 2 compare with regards to reliability.

On the pure reliability front, I see from several articles that the uncorrectable error count in SSD's becomes much higher than for HDD's after 3-4 years.  One such assessment is from this article:

http://www.enterprisestorageforum.com/storage-hardware/ssd-vs.-hdd.html

I also see that SSD's develop a higher number of bad blocks over time.  One statement caught my attention in particular:  

"When comparing traditional hard disk drives and flash drives, flash drives have a significantly lower replacement rate in the field, however, they have a higher rate of uncorrectable errors.”

Another article I  came across is:

https://therevisionist.org/reviews/ssd-vs-hdd-one-reliable/

This is from Dec. 2016, so I'll concede its findings may be somewhat dated.  One statement I came across was:

"Other sources indicate that SSDs tend to have a higher bit error rate than HDDs. In addition, those error rates increase with age, with usage having almost nothing to do with it. So, data integrity is an issue."  

The Unrecoverable Bit Rate Errors are my concern with SSD as they tend to be much higher than with HDD's.  Practically speaking, how do you protect against this taking place and corrupting your data?  I know in the typical HDD environment that e.g. Dell's Open Manage will warn you of predicted disk failure, which is critical to head off issues.  Of course, sometimes the drive just fails with no warning, and then you need to replace it ASAP.  Is the same early warning system available when it comes to UBER errors with SSD's?  It is a nightmare scenario to imagine getting a call from a client and hearing about massive data corruption from this.

Robert Lem - the SQL database size for the 1st SQL app is c. 500 MB, although the application it is running creates a ton of small scan/image files, and when I run a SQL backup, the resulting backup file size is c. 25 GB.  For the 2nd SQL app, the db size is c. 7 GB with a 1.5 GB log file.  Due to how I have come into working with the firm, I am not actively supporting the 2nd SQL app, only the 1st one.  Ultimately, though, both SQL apps will need to be migrated to the new server, as they are both currently running on temporary servers due to a main SQL server crash back in December, before I started working with the client.

I could find out some more from the client regarding transactions per day.  I assume this corresponds to data entries, changes, deletes etc. made to the database  For the 2nd SQL app, I would guess it is several hundred to a couple of thousand transactions per day, based on a user population of 80-90 users.    The 1st SQL all does not see much volume, as it is used by only c. 15 users, and they might each do 30 to 50 scans per day.  Again, I'll check to confirm if these numbers are reasonably close to actual usage.
ASKER CERTIFIED SOLUTION
Avatar of kevinhsieh
kevinhsieh
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jkirman

ASKER

Greetings,

My apologies for not responding sooner, as I've been out on vac.  Got back in this past weekend, did some additional research while out.  Putting some responses together, will post on Monday night.  Have a good Labor Day if you're in the states.

Thanks.

jkirman
Avatar of jkirman

ASKER

kevinhsieh and Robert Lem:

Thanks again for the previous comments and suggestions.  I have a few general SSD questions I've put together from my reviews over the last couple of weeks and would appreciate any feedback you can provide.   If you prefer that I close out this question, award the points, and open a new one with the following q's, I'll gladly to that, so please let me know in your comments.  Thank you.

 In no particular order:

1) I'm seeing SLC / MLC / TLC drives out there.  I assume SLC is the enterprise drive to go with, as it has a P/E program/erase cycle of 100,000 vs. e.g. 30,000 for enterprise MLC, and am also assuming that when Dell lists SSD drives on their server configuration pages, these are SLC only.

2) I've read that defragmentation is not recommended for SSD drives, as it causes unnecessary use and wearing out of the memory cells in order to constantly re-pack and consolidate the files' data on disk - is this true?

3) I see that 512n SSD drives are the most commonly offered, with 512e and 4K also available.  However I've read that VMware and to some degree HyperV have issues reading from the 512e and 4K drives.  Is it best to stay with 512n?

4) Dell lists mostly SSD SATA and SSD SAS drives as options.  with SSD SATA rated at 6 GBPS and SSD SAS at 12 GBPS.  SSD SAS is c. 40-50% more expensive than SSD SATA for given lifetime capacity.  Is there any reason or environment to justify SSD SAS vs. SSD SATA?  I've read that SAS drives' communication with the RAID controller is more pro-active than SATA, as SAS will actively notify the controller of issues, pending failure etc. whereas SATA drives need to be polled.  For traditional HDD's, SAS MTBF is much higher rated than that of SATA drives, but that most probably does not apply to SSD's.

5) Kevinshieh - you recommended going virtual and creating either 1 or 2 VM's for the SQL apps to run on.  For this I would plan to use Hyper V under the main O/S, create 2 VM's, each running Windows 2016 server and the appropriate version of SQL for whichever app is running on that VM.  I'm figuring that I'd want to arrange the disks in the following way:

a) For Windows O/S & boot partition - 2 X 200GB SSD SATA Mix Use 6Gbps 512n with capacity of 3 DWPD,1095 TBW in RAID-1
b) For the first VM, configure with Windows 2016 Std and SQL 2014 running the 1st SQL app - 2 X 400GB SSD SATA Mix Use 6Gbps 512n with capacity of 3 DWPD,2190 TBW in RAID-1
c) For the 2nd VM, configure with Windows 2016 Std and SQL 2014 running the 1st SQL app - 2 X 400GB SSD SATA Mix Use 6Gbps 512n with capacity of 3 DWPD,2190 TBW in RAID-1

My thought above is to use separate RAID-1 containers for every critical component for isolation in the event of failure of 1 section.  Is this a sensible approach or is my paranoia working overtime?  

My sell to the client on going with a virtual vs. conventional server environment is that virtual can be replicated to offsite for disaster recovery, and to a local onsite Business Continuity server using e.g. Veeam Backup and Replication to protect against main server failure.

6) Robert Lem - thanks for suggestions on using different tier storage for active SQL db vs. storage of full backups, as well as RAM for tempdb.


{End of questions)

Many thanks for all your combined assistance.

jkirman
Avatar of jkirman

ASKER

Appreciating both your responses, extensive and informative from your own experiences.  I was able to answer the last round of q's with some more research.  Virtual server definitely the way to go, recommended that.  

Many thanks again.

cheers.

jkirman
Thank you. I am glad we could help