flash with sql

[corey10112]

Is flash to be used for SQL instances best as all flash or should it be paired with sata for databases that are queried a lot but not a lot of writes?

[jmmorrell]

It depends on the usage

[Locknload]

I have customers running their SQL databases all on Flash either in EF540/50 and on All Flash FAS SSD aggregates.  Their businesses are very happy with this and keeping it simple is easier to manage rather than second guessing which data should sit where.  Flash is getting cheaper and cheaper so is affordable to have all SQL on SSD.

[welch]

It all comes down to latency.  Flash storage (All Flash FAS, or EF) will provide you with the lowest latency, SAS drives with FlashCache or FlashPool (Hybrid) are next best, and SAS drives after that.  It is not best practice to run SQL on SATA the drives struggle to meet even the slowest latency requirements (think anything above 10 ms). Putting cache in front of SATA can help the situation, however, every cache miss will have a greater impact on the performance of your database.  While the SATA/Cache solution tends to work with Exchange, SQL Server solutios can struggle due to the random nature of reads.

So, to answer your question, we would need to know your latency requirements.  If you require low latencies (sub-millisecond), use a flash aggregate or EF series,  If you need a mix of lower latencies (below say 7ms) and capacity use FlashCache or FlashPool combined with SAS, if you dont care about performance as much (dev environments for example) feel free to exclude flash all together.