Clustered SQL Server 2005 environment on a SAN
-
Hi, I’m new to SQL central and thought I would try to get some valuable advice. Our Infrastructure team are in the middle of a product select for a SAN. After they had explained the benefits of a SAN to our operations director he is keen to run our production SQL Server 2005 cluster from the SAN. I have heard mixed reports about doing this and wondered if anyone has had first hand production experience on a SAN?
Any help / advice would be greatly appreciated.
Chris
-----
The key, the whole key and nothing but the key
-----
------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------ -
we have been an EMC customer for years and they have great support
it works only as good as how you configure your SAN. our old one it worked great years ago but management made us use every last bit of disk space and it caused problems since you had the same spindles perform work for different apps. one time we added some disk and set it up separately and tested it and found that performance was 2-3 times faster on alter index operations on a qa server with less ram than production but same other hardware.
EMC now has tiers of storage on their DMX line and it's supposed to be faster. will find out next month. good thing about DMX is support. when you upgrade they have a team migrate the data for you
we also looked at netapps and some others but decided to stay away. cost was one reason and from what i remember netapps likes to make a RAID5 on the entire SAN and then partition the volumes.
performance you can get similar speed on current local storage since it's very fast now. the SAN is if you have a lot of storage, need disaster recovery and want to manage it centrally. our main sql servers we have the production volumes on the san and BCV volumes that back up every 4 hours. if there is problem we can just overwrite the prod volumes with BCV data. also a nice feature when you are doing upgrades. we also have an EMC at a disaster recovery site where we ship the data to. you can do the same thing with db mirroring, but with EMC it's better and easier. if something happens to your logs you have to run a full backup, restore and then set up mirroring again. and you are restoring over the WAN and it can take over a day with a 250GB database.
-
Both SANS and direct storage can outperform each other given the right conditions. I've been on both and have had good luck with them. SANs can be good for really high transactions, while direct storage can be better for larger transactions. These are generalities, but at a high level it'll suffice.
You definitely need to make sure you have the high transaction count to warrant the cost fo a SAN before you make that leap. They're expensive to buy and to maintain, and if you don't need the specialty hardware don't bother.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Thanks guys we have a very high I/O throughput but the transactions are relatively small. The schema isn't the best but the current business demand means we are unable to perform the required maintenance.
We are having an environment redesign for the 2nd version of our business applications and I am pushing for a SQL 2005 cluster and considering putting the database files on our SAN.
My concern is that the having heard mixed reviews of storing database files on SAN's I don’t want the performance gains achieved by improving the schema to be nullified by storing the database files on a SAN.
------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------ -
which SAN vendors have you talked to?
-
We're doing exactly that - running a SQL 2005 cluster against a SAN. In our case, we contracted an MS consultant to help set it up. Per his recommendations, all the usual rules about setting up storage (block size, offset, RAID levels) apply. His only additional recommendation was to specify independent spindles within the SAN.
We're an EMC client, and our storage architect didn't bat an eye upon getting the spec, so I believe it to be fairly straightforward on EMC gear.
-
We have EMC and are happy with it. You can raid 10 transaction files and raid 5 the data. A lot of flexibility with a SAN, especially with a cluster. When your data gets large, in the terabyte range it really helps. You will need fiber, I wouldn't do it over copper. If you can virtualize, even better, VMware works great with a SAN and allows you to do a lot of things not possible with iron. Backups are the real improvement, snapshot or snapclone.
-
Thanks again much appreciated, I don't know which SAN vendors have been aproached that is being looked into by our infrastructure team. They have been asked to shortlist 3 companies, meet with them and go from there.
------------------------
I am the master of my fate:
I am the captain of my soul.
------------------------
Blog: http://sqlserver365.blogspot.co.uk/
Twitter: @ckwmcgowan
------------------------ -
We have all our systems on SAN. Premium services on DMX EMC, test on Clariion. We had a lot of problems with both.
On Clariion we had issue with 2nd path and we've lost disks on cluster very often. It was not serious problem usually some reboots solved this issue but still on prod environment it's not acceptable.
With DMX we had so far 4 issues where we've lost couple of drives.
On one cluster disks F with database files and next day disks G and H with data files and logs, last week we've lost K drive with log files.
We didn't find the reason for this. In first two cases we've lost very urgent production box and we've spend to nights (2 accidents) to recover from backup, 2 databases without data loss but other unfortunately with (we've lost drive with log files).
Last fresh accident was due driver update.
This is very bad thing because you are loosing whole drive (so bad that even mirrored SAN didn;t help) and the only solutio is to format drive again !!!
We are not happy with SAN but to be honest there is no alternative and as usually more sophisticated solution more possible failures.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005 -
Hey,
In terms of running your SQL Server system on direct-attached storage vs SAN storage is primarily concerned with following:
1. Capacity
2. Performance
3. Scalability
4. Availability
5. Reliability
6. Cost
DAS - Simple to deploy and initial lower cost compared to SAN technology. Also, DAS is ideal for localized file sharing in environments with a single server or a few servers. Limited in scalability, but much depends on your organizations data growth forecast and availability requirements.
SAN - More involved deployments and higher inital cost in comparision to DAS technology, but very scalable in comparison. More suited for high performance database applications with mission critical availability and performance requirements due to off-host processing. Built on Fiber Channel and iSCSI technology, thus higher throughput and block level transfers
Overall, I have worked with most of the SAN technology available for SQL Server including EMC, Hitachi and IBM solutions, but also with HP MSA 1000\1500 DAS solutions. The most common issue with DAS is scalability due to limiations on number of disk and enclosures you can have. If you have low I/O requirements, slow data growth and availability is not an issue, a DAS solution will do the job nicely, but if you have a enterprise I/O, growth and require a easily scalable solution, I would highly suggest you go for a SAN solution and absorb the initial investment.
Thanks,
Phillip Cox
-
There is a pretty good article on this subject on the front page of SQLServerCentral.com: http://www.sqlteam.com/article/which-is-faster-san-or-directly-attached-storage
The Redneck DBA
-
Phillip Cox (2/5/2008)
Hey,In terms of running your SQL Server system on direct-attached storage vs SAN storage is primarily concerned with following:
1. Capacity
2. Performance
3. Scalability
4. Availability
5. Reliability
6. Cost
DAS - Simple to deploy and initial lower cost compared to SAN technology. Also, DAS is ideal for localized file sharing in environments with a single server or a few servers. Limited in scalability, but much depends on your organizations data growth forecast and availability requirements.
SAN - More involved deployments and higher inital cost in comparision to DAS technology, but very scalable in comparison. More suited for high performance database applications with mission critical availability and performance requirements due to off-host processing. Built on Fiber Channel and iSCSI technology, thus higher throughput and block level transfers
Overall, I have worked with most of the SAN technology available for SQL Server including EMC, Hitachi and IBM solutions, but also with HP MSA 1000\1500 DAS solutions. The most common issue with DAS is scalability due to limiations on number of disk and enclosures you can have. If you have low I/O requirements, slow data growth and availability is not an issue, a DAS solution will do the job nicely, but if you have a enterprise I/O, growth and require a easily scalable solution, I would highly suggest you go for a SAN solution and absorb the initial investment.
Thanks,
Phillip Cox
DAS scalability used to be a problem on HP, but these days you can put something like 100TB of storage per server and the new SAS drives with the MSA 70 is lightning fast. took 90 minutes last night to dump a snapshot of a table with 300 million rows to it. even the SATA drives have NCQ now and test faster than SCSI in some cases
only problem with SAS enclosures is you can't cluster them. i saw that Dell had a cluster ready SAS enclosure and a few white box ones around, but nothing from HP
-
You can also find very valuable input in this thread
http://qa.sqlservercentral.com/Forums/FindPost453679.aspx
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply