locked
Microsoft best practice for ssd RRS feed

  • Question

  • Hi,

    i can't find any new article from microsoft about using SQL server 2012/2014 on SSD drive.

    what is the best practice about putting DATA file,LOG file and TEMPDB files on SSD RAID's perspective and what types of SSD?

    THX

    Monday, May 30, 2016 5:53 PM

Answers

  • I don't recall ever seeing a Microsoft article discussing SSDs on SQL Server, and very few articles on hardware selection at all.  My impression is that SSDs have become virtually standard, at least for production systems, in about 50% of corporate environments, for one or more of log, tempdb, or main db storage.

    There are several types of SSDs: consumer, "pro", server-class, and then server-class on the PCI bus.  Starting in about a year you'll probably be able to get them configured to live right on the memory bus.  As long as you avoid the consumer class drives, I think the rest is a matter of cost and specs and choice.

    Due to the failure modes of SSD you want to use them only in some kind of mirrored or redundant RAID configurations, and should track their usage statistics and probably swap them out aggressively ahead of failures.  But the price/performance is now so good, it's well worth the bother.

    Josh

    Monday, May 30, 2016 6:54 PM
  • Hi!

    Do you want to direct attach the storage (DAS) or store your data in your network (SAN)? For DAS, everthing is already said above. For a SAN solution there is no general advice since every system works in another way and has other features. But in each case, a little more information would be fine.

    If you want to go on DAS, there are differences on the speed and prices. Look at the PCIe SSD with up to 1500 MB/s and the SATA on the other hand with "only" 400-550 MBs. As usual, it all depends on the price and how much you're willing to pay for it (also an important but missing imformation).

    Greetings


    MCTS (70-642), MCP Please click the "Mark as Answer" or "Vote As Helpful button" if a post solves your problem or is helpful! Bitte klicke auf "Als Antwort vorschlagen" oder "Als hilfreich bewerten", wenn mein Beitrag Dein Problem löst oder hilfreich ist.

    Tuesday, May 31, 2016 6:34 AM

All replies

  • I don't recall ever seeing a Microsoft article discussing SSDs on SQL Server, and very few articles on hardware selection at all.  My impression is that SSDs have become virtually standard, at least for production systems, in about 50% of corporate environments, for one or more of log, tempdb, or main db storage.

    There are several types of SSDs: consumer, "pro", server-class, and then server-class on the PCI bus.  Starting in about a year you'll probably be able to get them configured to live right on the memory bus.  As long as you avoid the consumer class drives, I think the rest is a matter of cost and specs and choice.

    Due to the failure modes of SSD you want to use them only in some kind of mirrored or redundant RAID configurations, and should track their usage statistics and probably swap them out aggressively ahead of failures.  But the price/performance is now so good, it's well worth the bother.

    Josh

    Monday, May 30, 2016 6:54 PM
  • Hi Medasystem,

    Josh has given very good hints concerning the usage of SSD. From my point of view SSD shouldn't be used different from "normal" disks.

    I would use the following layout (because of mix from performance and reliability)

    DATA: RAID 5 with hot spare

    LOG: RAID 10

    TEMPDB: RAID 10


    MCM - SQL Server 2008
    MCSE - SQL Server 2012
    db Berater GmbH
    SQL Server Blog (german only)

    Tuesday, May 31, 2016 5:24 AM
  • Hi!

    Do you want to direct attach the storage (DAS) or store your data in your network (SAN)? For DAS, everthing is already said above. For a SAN solution there is no general advice since every system works in another way and has other features. But in each case, a little more information would be fine.

    If you want to go on DAS, there are differences on the speed and prices. Look at the PCIe SSD with up to 1500 MB/s and the SATA on the other hand with "only" 400-550 MBs. As usual, it all depends on the price and how much you're willing to pay for it (also an important but missing imformation).

    Greetings


    MCTS (70-642), MCP Please click the "Mark as Answer" or "Vote As Helpful button" if a post solves your problem or is helpful! Bitte klicke auf "Als Antwort vorschlagen" oder "Als hilfreich bewerten", wenn mein Beitrag Dein Problem löst oder hilfreich ist.

    Tuesday, May 31, 2016 6:34 AM