kovaleff - Fotolia

Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

SQL Server SSD technology becoming a solid storage choice

With recent improvements in solid-state drive technology, it's time to consider SSD devices for SQL Server storage uses. SSDs can address I/O performance problems, but wear is a worry.

Many in IT once thumbed their noses at solid-state drives because of exorbitant costs and long-term wear issues...

that often caused the devices to fail prematurely. Even the growing I/O performance demands of their SQL Server systems weren't enough to lure IT teams away from tried-and-true hard disk drives. But dropping prices and SSD technology improvements are leading some past naysayers to take a second look at solid-state options for storing SQL Server data.

That's not to say SQL Server SSD devices are challenge-free any more than their HDD counterparts are. But SSDs have come a long way in the last couple years and are quickly becoming a viable option for addressing the performance woes that plague a variety of data-driven applications built on top of SQL Server.

Consumer electronics products such as smartphones and tablets made the SSD a household name, but SSD technologies have also now worked their way into lots of data centers. Today, most enterprise-grade SSDs use NAND-based flash technology, a type of nonvolatile memory that can retain data even if the power goes off.

In addition to the storage medium itself, an SSD includes an embedded controller that provides a bridge between the flash storage and host environment, handling critical management tasks to help ensure the integrity and reliability of the data stored in the drive. A high-quality controller is essential to meet the demands of a system like SQL Server.

No moving parts in SSD hardware

The most noteworthy difference between solid-state and disk drives is that SSDs contain no mechanical parts, while HDDs include a moving head that scans a rotating magnetic disk. The nonmechanical nature of SSD technology gives it a big advantage in an important area -- performance.

With an HDD, the disk and head both have to move to the right place for read and write operations to be carried out. But, because an SSD has no moving parts, there's virtually no I/O latency during such operations, resulting in remarkably high IOPS rates compared to disk drives.

The IOPS performance gains you can get by using SSDs with SQL Server are difficult to pinpoint upfront. You'll hear all sorts of statistics and anecdotal evidence about what you can expect, and estimates that vary widely. That's in part because much of the information is coming from SSD vendors, whose estimated gains are based on controlled benchmarks and ideal circumstances. But the differences can also be explained by how SQL Server SSD technologies are being used -- and, in the end, that's what matters.

Because SQL Server data and log files can reside in different locations on different types of storage media, enterprises often implement SSD storage in stages, which means they have to choose the right files for the solid-state technology. For example, suppose an organization starts with the transaction log, which records all transactions and database modifications. It might seem the logical choice because the log file workload can represent a significant I/O bottleneck in certain applications. But what if the database supports primarily read-only operations? If only the transaction log resides on SSDs, the company likely won't see much of a performance improvement.

Under the right circumstances, however, SSDs can provide a big boost to SQL Server performance. The transaction log could be the best choice in some cases, but, in others, putting the tempdb files or user database files on SSDs might be a better option. Even certain index files could benefit.

Questions on reliability and longevity

With SSD prices dropping and capacity increasing, it seems inevitable that enterprises will continue moving their SQL Server files to SSDs, especially since the devices can help offset other costs as well. For example, by using SSD storage, a single SQL Server instance can support more users and reach greater scale, requiring less hardware, data center space and energy use.

Yet, even if SQL Server SSD pricing can achieve parity with HDD pricing -- or perhaps go below it -- the question still remains whether SSDs are reliable enough to replace disk drives outright. With an SSD, you don't simply overwrite data. You must first erase the original data and then write the new data. The technology's Achilles' heel has long been the fact that memory cells wear out, limiting the number of times you can erase and write data. The more data stored on the drive and the greater the number of write operations, the sooner performance will begin to degrade.

Of course, high data volumes with lots of writes are exactly what many transactional SQL Server systems are up against. But there's hope. An important part of the management functionality provided by the controllers built into SSDs is wear leveling, a process that controls how data is written and erased across the cells in order to prevent one set of them from wearing out before others. In this way, the controllers can help to significantly extend the life of the drives.

That said, SSDs are still susceptible to a number of vulnerabilities, and they tend to fail less gracefully than HDDs. For example, an SSD can fail as a result of a power outage. You can also have wayward electrons, bit errors, faulty transistors or a number of other issues. Even bugs in the controller's firmware can cause data loss -- and data recovery can be much trickier with an SSD, often resulting in the need for specialized expertise or software to retrieve the lost data.

Despite all this, enterprise-grade SSDs have made significant strides, and it would be difficult to say with certainty that they're less reliable than HDDs. The key with SSDs is to ensure that you follow the same best practices you have been with your disk drives. You should have disaster recovery processes in place, and you should ensure data redundancy and fault tolerance through techniques and technologies like backups, mirroring, replication, RAID arrays and battery-backed controllers.

Taking the SQL Server SSD plunge isn't an easy decision. But HDD technologies have little room to grow, and it will likely become increasingly difficult for them to compete in the market. With vendors turning their attention to SSD devices, enterprises likewise will continue to migrate toward the higher-performing drives. Just be sure you're implementing SSDs in the right places and for the right reasons.

Next Steps

Experts predict that solid state drives will be big in 2016

Companies are using SSD flash storage for their primary application workload

SSD vendors are working to up throughput and endurance

Dig Deeper on SQL Server High Availability, Scalability and Reliability