Disk Organization on a SAN
Today I wanted to talk about deploying your databases on a SAN. The SAN brings a different set of opportunities often outside the control of the DBA.
In a perfect world we try to break up disk contention between competing disk resources. For this reason you would attempt to separate on different physical disks, tempDB, tempDB transaction logs, database data files, database transaction logs, etc. Using a SAN that is not designed specifically for your database(s) often means that this separation is not possible.
In some cases it isn’t even reasonable. Consider some of the larger SAN systems. They may have hundreds of physical disk drives, and huge amounts of cache allowing performance to be quite impressive, even if the disks are all on a single physical raid volume.
In this case, the placement of your data and transaction files makes little difference; most everything is handled in cache. Small databases and large databases alike perform rather well. This isn’t to say that a huge SAN could not be tuned for better optimization. Database load differs radically from that of file access methods, and other optimizations are possible. Generally the performance is quite acceptable so optimization isn’t worth the time or cost. Possibly a SAN of this sort is shared by multiple companies or groups within a company, so the SAN is optimized for the needs of the group, not the individual system.
In contrast, a smaller SAN with less than 20 physical drives and limited cache may require disk optimization to get the necessary performance. In this case, the placement of data and transaction files can make a large difference. Your options are greatly restricted, because the resources are so few. Unlike the large SAN, the way you configure the smaller SAN can have a huge impact in the performance of your database.
Because of the reduced cache availability on the smaller SAN, fragmentation of your database files can have a larger impact, much like direct attached disks. In this case, it is better to pre-allocate disk space for your database to grow, than to grow it incrementally. This results in the files being contiguous on the host drives. It is improved even more as you defragment the database data contained within the files themselves.
Share your experience working with a SAN by leaving a comment below or sending an email to btaylor@sswug.org. Perhaps you have information about configuring your HBA connections, or working with SAN administrators.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
SQL Server Database Backups and Recovery Models – (Part 2)
In this second part, you’ll learn about various SQL Server backup types, and how to add and use permanent backup devices and temporary backup devices to perform SQL Server database backups. You also learned about differences between various backup options, and how database recovery modes relate to available backup options, and when and how to run a tail-log backup.
Featured White Paper(s)
Extending the Value of SCOM with Spotlight on SQL Server
read more)