Does it makes sense to install SQL Server 2017 on one single SSD drive on m5.xlarge with tempdbs, databases, program files, backups and logs for best performance? According to Amazon EBS specs the bigger SSD drive the better IOPS values. https://aws.amazon.com/ebs/features/

It seems if we have 10 300gb disks we get 900 IOPS for those disks, if we have one 3000gb disk we get 9000 IOPS in AWS.

Every article I read recommend to use separate disk partitions https://d1.awsstatic.com/whitepapers/best-practices-for-deploying-microsoft-sql-server-on-aws.pdf So what would be the best approach here?

  • You may have overlooked the obvious. If you want the best EBS performance and best practices then you need to use EBS optimized instances connected to Provisioned IOPS storage. It’s all covered in the best practice guide you already provided. – Appleoddity May 29 '19 at 19:47

The advantage that I see in splitting out different drives for different purposes is the ability to scale/size them according to your needs. Like, tempdb gets used for a lot of stuff, so if you can put its volume on instance storage (i.e. NVMe) or io1, you can do that w/o needing to put all of your data there (which would preclude the instance storage). Also, you can switch between gp2 and io1 relatively easily, so you're not stuck with what you choose today in that respect. And keep in mind that you may be able to get the performance characteristic for less cost by having a larger gp2 volume vs an io1 volume!

My recommendation would be to split into separate volumes and then monitor with Cloud Watch and whatever you're using for SQL Server-specific monitoring. If something starts getting hot because of disk performance, you have ways around it.

  • Regardless of the cost involved, if we use several gp2 volumes their IOPS will be much less than in one single large disk with same total capacity. It is counter intuitive to me but that what Amazon is saying and still they advise to split for whatever reason. – mikesv May 30 '19 at 12:39
  • From their perspective, their incentive is to get you to pay for io1 volunteers to get the iops. – Ben Thul May 30 '19 at 12:44
  • But! You may get the iops you need with gp2 just by increasing the size. – Ben Thul May 30 '19 at 12:46
  • This is for QA environment, we will try one single disk this time around and see how it goes. Thanks! – mikesv May 30 '19 at 12:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.