I'm planning to start a EC2 high memory instance with SQL server on it. I'm working through how to setup the drives as the current dedicated hardware is set to a raid-5 but only 4gb of ram so a lot of IO right now. With the high memory I am basically quadrupling the amount of memory and half the database would be in memory, so I am not worried about I/O limits.
What I am worried about is cloud drive performance, current plan is as such,
Raid 5 three Local ephemeral drives for tempdb,transaction logs
Raid 5 three EBS volumes for data
Nightly full backups of the database and transaction log will occur. My only question is, is this the best way to setup an EC2 SQL instance, and since everything is software raid, only Raid-5 is supported, is there a way to get Raid-10? Also what happens with an EBS or ephemeral drives dies, does the software raid(dynamic disk) kick in a spare or do I have to do that?
Best Answer
Ephemeral drives should be considered extremely volatile and absolutely nothing that you need to retain should be kept on them. It's a good place for unimportant logs and things like tempdb. If one of the ephemeral volumes fails, however, there will be no way to restore it.
Ephemeral volumes are hardware volumes on the instance's physical host. If the drive dies your best hope is that someone at Amazon is able to replace the physical disk without the host going down.
With regards to the EBS volumes; they can be added to the instance easily but you would need to do this yourself or automate the process but there is nothing in the system already to handle it automatically.
You could set up a cron job on the instance that checks the RAID health and in the event of a failure you could have it talk to the EC2 API to add another volume.