SQL FAQ - Does SSD storage introduce any additional problems for Microsoft SQL Server recovery?

Solid State Drives are making serious in-roads into the enterprise datacentre, with enhanced read/write operations offering significant performance improvements. This speed boost coupled with consistent price reductions per gigabyte now means that datacentre arrays build around SSDs are a practical option for business.

But does the use of SSD storage present any new challenges that DBAs and network engineers need to be aware of?

The question of wear

One of the most common arguments against the use of Solid State Drives is the question of wear. SSDs are known to have a finite lifespan based on the number of read/write operations they undergo. As each drive approaches this milestone, performance degrades, slowing read/write operations and significantly impairing Microsoft SQL Server response times.

Traditional spinning disks are also affected by outages and failures, although they are more likely to fail due to motor malfunctions or other hardware related faults. However many years of experience means that CTOs and systems architects are well used to these failures, having developed fault tolerant solutions to cope – like RAID arrays.

SSD wear – a red herring?

SSD wear is indeed a genuine factor for consideration when deploying flash storage for use with Microsoft SQL Server. But when used in RAID arrays and SANs, the overall impact of wear can be minimised.

The effects of wear can be further mitigated by using wear-levelling techniques to ensure that read/write activities are spread evenly across the available memory cells. These distribution techniques create additional background write operations, fractionally increasing the effects of wear overall, but at the same time extending the lifespan of the drive by preventing the same sectors being constantly overwritten, increasing fragmentation and the likelihood of wear on critical disk sectors.

Despite enterprise-focused advances in SSD technology, concrete lifespan figures remain elusive. Manufacturers regularly quote 1 Petabyte read/write as the upper wear limit, but what this equates to an enterprise environment is harder to quantify. Using the best available data, Microsoft estimate that all but the smallest SSD should be able to sustain enterprise-class data operations for five years or more. Other tests suggest even longer theoretical lifespans – up to 29 years in some circumstances.

When installed in an enterprise-class data array, there is no reason to think that Microsoft SQL Server cannot be used with SSD. Advances in the memory cell technologies used in SSD drives, like SLC, also offer improved wear resistance over traditional MLC drive technology (commonly found in consumer-level computer equipment).

One potential sticking point – logs

The intensive read/write nature of SQL Server logs presents one potential sticking point when using SSDs. With so many IO operations, the SSD drive(s) on which write-ahead logs are stored are prone to wear faster than any other unit involved in Microsoft SQL Server operations.

In mission-critical deployments where transactions need to be closely monitored and audited, the failure of a drive containing logs could jeopardise the entire data recovery process.

Before making the move to SSD-based SQL Server data stores CTOs need to decide:

• How great a risk SSD failure poses to SQL Server recovery operations.
• Which technology provides the best balance of performance and reliability (SSD vs HDD).
• How to monitor the SSD drives on which logs are stored to assess health.
• How to recover data from a failed SSD in the event of a disaster or drive failure.

SSD & SQL data recovery – the devil in the details

It is also important to remember that the strongest feature of SSDs – a lack of moving parts – is also one of the greatest weaknesses. SQL data recovery from a failed SSD drive is an expert job, usually requiring professional assistance. Where platters could be removed and cloned from a traditional spinning disk in a clean room environment, SSD memory chips are typically harder to recover.

Data recovery from SSD memory cells is possible, but it remains a complex procedure. And because any SQL Server deployment needs to include data recovery provisions as part of its plan, CTOs and systems architects need to consider and mitigate the impact SSD outages can have on availability.