02 April,2011 by Jack Vamvas
Implementing SSD on the SAN has advantages such as hardware redundancy – but also limitations – i.e the data must first navigate CPU to drives – with the various limitations.
An alternative is to plug the SSD directly into the PCIe bus. The benefit is that IO will bypass the drives and RAID controllers . This type of SSD has it’s own controller , with the bandwidth dedicated to the drive – avoiding SAN contention and freeing up bandwidth for SQL data and logs.
This should amount to a greater and faster number of IO operations per second .OCZ and Fusion-IO are two examples of manufacturers
This is not practical for all types of SQL Server files – for example , would you want the Primary filegroup on a drive that is not part of the hardware redundancy schema. ? There are risks involved in not choosing the right files to use on a SSD drive.
Firstly , the TEMPDB is rebuilt every time the SQL Server restarts – no permanent data is maintained . The type of activity on TEMPDB is of a temporary nature – such as : temp tables, index rebuild, table variables, xml variables. TEMPDB is also a spillover space for sorting data, group by, snapshot isolation and large joins
This type of activity works well with SSD on the PCIe bus . If the server crashed and was not recoverable – TEMPDB could be rebuilt – with minimal risk .TEMPDB does not have to be represented onto another server – as there is no permanent data .
If redundancy is required for this type of drive – then a software RAID solution would need to be considered.
This solution would not work for clustering – as the clustering requirement is that all drives are remote to the server. You could potentially develop a workaround – but based on the SQL Server 2005 \ 2008 documentation - maintaining a local drive is not supportable.
This does not apply to LAN based SSD – which are considered to be remote