Do you have any good or bad experiences to share running SQL Server OLTP Systems on NetApp appliances? I have been working with a small, relatively low-volume cluster with a lower-end NetApp device, and I have found the environment to be generally unstable, at least compared to my experiences with other SANs, iSCSI arrays, and DAS setups. I struggle to believe that RAID DP and WAFL are more than fairy-dust technologies. A solution has been proposed to me that I just need a bigger, better NetApp, with PAM cards and other cool technology I've not heard of, and I feel like I would be better off spending a quarter of that on good direct-attached drives and a beefy server. At the same time, I feel that an Enterprise-class SAN should be something I can count on to be consistently a more stable, better performer than the less expensive solution I might propose. Are you a SQL Server DBA in an OLTP environment and love your NetApp? If you don't like them, why not?
-
Could you be more specific about "unstable"? – David Mackintosh Jun 18 '10 at 15:13
-
When I first got involved in this particular environment, there were a lot of performance issues, and some general web-sql connectivity issues, as well as a high frequency of cluster failovers. The event logs and SQL logs were pretty sparse, but I did see a instances of lost disks and delayed writes. I would also hit performance bottlenecks during SQL Backups. There have been a lot of improvements I've made across the environment that have definately improved things, but it isn't 100% yet. I'm not certain that better storage is the answer or solution, that's being proposed to me. – Edinor Jun 18 '10 at 17:53
2 Answers
SQL Server will not (or at least would not fairly recently) work off a SMB shared volume as SMB does not support the necessary file locking semantics. You will need LUNs exposed through iSCSI, FC over Ethernet or Fibre channel to do this. If you're using the NetApp as a Filer sharing SMB volumes this could be the source of your problem.
Buy a SAN if you want SAN features (snapshot backups etc.) otherwise buy the direct attach storage. If you really just want a box running SQL Server to support a portfolio of apps with moderate load a single big SQL Server box with direct-attach storage will probably work fine. It will be cheaper, faster and probably cost you less in SQL Server licencing.
For some values of big, a bog standard two-socket Nelahem box like a DL380 G6 will probably qualify. This will cost you two sockets worth of SQL Server licencing, comes with 8 cores and takes up to 192GB of RAM. It can take 16 disks internally and you can attach 100 or more externally with stock controller and external shelf (MSA70) options. Most other vendors offer a box of similar spec.
If you're using SQL Server EE, the machine and a DR box will probably cost less than the SQL Server licencing for two sockets. If you're starting small, populate just one socket and save yourself $20k or more on the licencing.
I think this type of box is underrated and getting more so these days - as VMs and blades are all the rage now. However, this is a seriously powerful machine capable of supporting a pretty agressive database workload for just two sockets worth of SQL Server licencing. If you're currently on a low-end SAN, this will cost less and run rings around it for performance.
- 8,820
- 2
- 32
- 52
-
8 Cores but 16 threads! and SQL loves them too - we replaced a 2yo DL385 G5 with a DL380 G6 and saw average load go from 85% to <5% - :) – Chopper3 Jun 18 '10 at 15:52
-
I'm definitely a fan of DAS for data warehouse workloads. At one point I used to work in Leadenhall St. above the EMC office there. The reps there reckoned that quite a few of their customers use DAS for data warehouse systems as it tends to be quite a lot faster. – ConcernedOfTunbridgeWells Jun 18 '10 at 15:55
-
we're always in the situation where we have to cluster so can never get the great price/performance benefits of DAS. – Chopper3 Jun 18 '10 at 15:59
-
Typically, a data warehouse shouldn't really need that sort of SLA. Arguably if you need that service level you're probably trying to do operational reports of your data warehouse system, which is a bad thing as it ties the DW's service level to that of the source system. Scale that up to a dozen or so source systems and you can see why that might not be such a good idea. – ConcernedOfTunbridgeWells Jun 18 '10 at 16:02
-
Generally I find that MIS applications don't need short DR times - Once I saw a three day outage at a bank you've probably heard of and no-one seemed to really care. The customer had to bring in MS's Tier-3 support people to troubleshoot the problem. Unless you're doing something like a trading floor system (VaR and P/L reports are operational anyway) you don't need real-time or hot failover for MIS applications. – ConcernedOfTunbridgeWells Jun 18 '10 at 16:07
-
Thanks for the input and comments everyone, I have had a lot of the same experiences. I feel like if you ask me for a 5K TPS SQL Server, I can design that in my sleep on DAS, but there a LOT more variables that need to be conisdered if designing a system around a SAN. It gets even more tricky as there are completely different skill sets (and opinions) that need to get involved to determine the best solution. – Edinor Jun 18 '10 at 18:10
-
its all about spindles, a das solution can do the work just fine if you can buy a big enough box of drives. But you are rather limited on what that storage will do for you. In most cases I'm fine with that. FC base sans are just to expensive to scale out and like you said its a different skill set to manage the environment thus I like iscsi if I need a san solution. – tony roth Jun 18 '10 at 19:22
If all you care about is running MSSQL in a cluster then I'd suggest you'd be far better off going for a dedicated 8Gbps FC array - certainly if performance under load is a main concern and bottom-line price is less of an issue. Obviously if you don't need clustering then CoTW's post above makes a strong argument for DAS (I love MSAs too ;))
We're mostly a HDS/HP-XP and HP-EVA house but we still have LOADS of NetApps boxes around purely because they're a really good compromise. Having a single box that's often better-than-good-enough at all those protocols can be a real boon. That said in my opinion they don't do FC/FCoE anywhere near as well as dedicated boxes and aren't much better than OpenFiler etc. for iSCSI/NFS - I also think that they're a swine to setup from 'bare metal' if you have a reasonably complex environment. But there's no denying that NetApps look great value to many people, I just have my doubts personally.
- 101,299
- 9
- 108
- 239
-
Generally my experience with SAN performance has been somewhat underwhelming, although the issues tend to arise on data warehouse workloads. In recent years the XW9300 dev boxes I use with just 6x 15k disks will run ETL jobs significantly faster than the production SAN environments I'm deploying the systems to. – ConcernedOfTunbridgeWells Jun 18 '10 at 15:47
-
SANs are all in the implementation, we have some that are configured to be easier to manage and would be easily beaten by DAS as you say - then again I have some boxes that will happily flood multiple 8Gbps FC links all day long (slightly harder to configure like that though) – Chopper3 Jun 18 '10 at 15:54
-
Oh good, I should introduce you to the SAN administrators at some of the places I've worked at ;-}. I suppose I should qualify SAN as 'SAN tuned for a general purpose workload'. Oracle have some case studies about SANs tuned for data warehouse configurations ib their web site. It is possible, but if you've got to buy a SAN specially for your data warehouse you might still want to weigh up DAS as an option. – ConcernedOfTunbridgeWells Jun 18 '10 at 16:00
-
It's interesting that a lot of the same considerations are made if configuring SQL in a virtual environment as well. In the end, are the conveniences and trade-offs worth it? A lot of the features that SANs AND Virtualization are great, but a great many of them are also available in Enterprise Edition of SQL. – Edinor Jun 18 '10 at 18:14
-
If you read Chopper3's web presence he works for a large Telco, so I guess most of their requirements tend to fairly agressive SLAs with the budget to back that sort of kit up. I work at the opposite end of the SLA spectrum - data warehouse and MIS applications which tend to be more cost sensitive and where flexibility and fast I/O is more useful than 5 9's reliability. – ConcernedOfTunbridgeWells Jun 19 '10 at 10:37
-
Also, I see a tendency to forget that one DBMS server can support multiple applications, and you can actually host more than application on a single machine. This is quite common practice on Unix and Mainframe hosts, and most modern windows apps are sufficiently well behaved to do it as well. However, vendors of windows server side COTS software tend to insist on having the machine to themselves. – ConcernedOfTunbridgeWells Jun 19 '10 at 10:41
-
You're absolutely right CoTW, although I'm on the TV side so the focus is even more on SLAs etc. – Chopper3 Jun 19 '10 at 16:16