SQL Database on SAN Disks
-
Hello Everyone,
We are planning to move our production database (45GB, 30%Writes/70%Reads daily and 70%Writes/30%Reads overnight) from local disks to SAN disks (Manufactured by Equallogic). However, a consultant just jumped in saying something very negitively about SAN solution. What he stated was while throughput of SAN type disk systems is often much better than local disks, latency is often higher (worse) depending on the infrastructure and technical setup. This is of particular concern to a high throughput database such as your database, because of the high volume of short writes to the disk. This may not be seen on a read only database test, as while database reads can be cached, writes are not.
I'm very interested in knowing any performance comparison based on latency of the SAN disks compared to local disks if anyone has them. Also, I greatly appreciate any comments and suggestions that will definitely help us to make a final decision.
Thank you very much.
-
There is some guidance on the Microsoft SQL Server Development Customer Advisory Team blog. I'm linking specifically to the Performance and Scalability category.
Performance and Scalability blog posts
There are several postings on the subject (3, in fact) of deploying SQL Server on SAN disks.
K. Brian Kelley
@kbriankelley -
Hi,
I am no expert by any means, but the problem with SANs and relational dbs is that you want to keep your log and data files on seperate disk arrays. In many SAN installs the network admins carve a LUN out for the db(s) and expect the data and log files on the same disk array.
SANs will increase in performance because their controller caches will increase write and read operations. Many SAN technicians will recommend RAID 5 for data disk arrays. I would recommend RAID 10. Disk is cheap and RAID 10 will give you better write performance.
-
I have a 14 disk SAN array and do both bulk inserts and reads. The SAN was partitioned into separate RAID partitions and I have my user databases on one set of disks, TEMPDB on another set, backups on yet another. My database got upto 250+ GB and never had any problems. Yes, the more data I got, the slower the system got...but it wasn't noticable until we went over 200 GB.
-SQLBill
-
BTW-the bulk inserts are done every 5 seconds.
-SQLBill
-
I have several clustered SQL Servers on a 114TB SAN, yes that is big for the moment. Performance is great, writes are cached, nothing goes to disc immediately, so not sure what your consultant may be thinking. There are serveral drives LUNed out to each cluster with just about everything on it's own drive. RAID 5 is slower for logs, tempdb, use RAID 0 or 1 or 10 if your SAN supports craving out different areas.
-
Going to a SAN environment is like the latest technology if you're comparing it to local disks, this consultant must not know too much about SAN.
Jules Bui
IT Operations DBA
Backup and Restore Administrator -
When you get right down to it, SANs are not much different than any other I/O system. Performance is a relatively simple matter of the speed of the disks and their configuration.
Strictly speaking latency is a function of the network and protocol. Fibre Channel is an extremely low latency protocol and fast even over long distances, iSCSI is not as fast and has a higher latency, but it costs less.
This article discusses SANs and latency for remote locations
http://www.infostor.com/Articles/Article_Display.cfm?Article_ID=129149&CFID=37909&CFTOKEN=68889103
This one points out that latency is much more affected by architecture than by the technology per se.
http://www.qlogic.com/news-events/details/show_file.asp?id=3657
If your question is about overall performance (not just limited to latency per se), most of the problems I've seen have been due to poor storage administration practices (how space is allocated within the SAN) rather than latency in the Fibre Channel network.
Yes, in some environments I've seen the Storage admins were very secretive (incompentent) about what they did so we couldn't tell if our data files were actually on different spindles than our log files etc... and performance sucked. It turns out that they were creating LUNs from individual spindles. When we finally got that resolved, even the problems we had in mirroring to our DR Site, which we had assumed was network latency, went away completely.
In contrast, our current admins are very good and offer complete transparency to us, which allows us to deliberately place our files. In several cases we have added additional raid groups to spread the load out better and have seen some significant performance gains with very little effort.
That is the real advantage of a SAN, the configuration options are nearly endless.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply