Search results matching tags 'san' and 'Storage Design'

New Project Starting. Got Gas?

merrillaldrich — Mon, 10 Sep 2012 19:32:02 GMT

“Storage is just like gasoline,” said a fellow DBA at the office the other day.

This DBA, Mike is his name, is one of the smartest people I know, so I pressed him, in my subtle and erudite way, to elaborate.

“Um, whut?” I said.

“Yeah. Now that everything is shared – VMs or consolidated SQL Servers and shared storage – if you want to do a big project, like, say, drive to Vegas, you better fill the car with gas. Drive back and forth to work every day? Gas. Same for storage.”

This was a light-bulb-above-my-head moment.

Now that everything is consolidated onto shared infrastructure, all the way down to complete servers, the way we think about funding IT projects has to change too. It used to be that if you wanted to do a project, you would enumerate what the systems would cost, then price and go buy them. It was like this: this new project will need a bulldozer and an excavator, and maybe a Super-Zooper-Flooper-Do, let’s buy them for the project, and then they will arrive on a truck and we will install them, and the project will move forward. Many people are still thinking this way, but it’s now officially backward. We don’t buy discrete items for projects anymore, we buy a slice of shared infrastructure. And planning for that infrastructure has to change, or you will be, as many organizations are, forever, endlessly, exasperatingly short of it.

Gas Up Early

Imagine you and your friends are cruising down the road on a beautiful day, and someone decides you need to, simply MUST drive to Southern California. Do you at that point look around at each other and say “OK, who has gas money?” Perhaps. But hopefully not if you run a large business.

Worse, do you just start driving that direction, and when you get down to 1/8 of a tank, then ask everyone in the car? Again, maybe, but not too many people travel this way who are over 25. I think, anyway.

So the obvious question is, and I see this in many companies, why do we pile projects onto shared infrastructure like SAN storage and VM clusters without planning what infrastructure will be required to take us where we want to go? Answer: the organizations haven’t finished shifting their thinking. They think, hey presto, now we don’t need to buy those unique pieces of equipment any more, we just get “free” VMs and databases and storage from that magic bottomless pool. But that’s only the first stage. They haven’t realized yet, at an organizational level, who fills that pool of resources up, and how quickly, and how much it costs.

Watch the Gauge

Part of the difficulty is there’s no single “gas gauge” to tell an organization how the shared infrastructure is doing – you need some clever, forward thinking administrators to do that, and they, in turn need some tools. Further, it’s pretty hard today to estimate what “slice” of shared infrastructure a project will need, and how or whether to literally charge back for that resource. That means you have one arm making plans for all the places the organization will drive, with no idea how much gas is in the tank, and perhaps another arm with its eye on the fuel level, but which doesn’t know what the travel plans are. If you just start driving, at some point someone’s going to be standing by the side of the road with a thumb out and a gas can.

And here’s another gotcha – you can’t, from a practical point of view, keep on filling this tank one gallon at a time, while always near empty. It’s not safe or economical. Do you really want to buy disks or shared servers and try to install them monthly? Weekly?

So start thinking about your servers and storage as a commodity, and do it now. Try to get your organization to make this simple shift – we don’t buy pieces of equipment for projects anymore. We buy a platform, then estimate how much more of that platform we need for all upcoming work, and to sustain growth, then implement it.

Scandalous II: Shh! I am De-duplicating Compressed Backups

merrillaldrich — Sat, 23 Apr 2011 05:01:50 GMT

This is part II of two Scandalous posts. Watch, mouth agape, as I run with scissors, right up against prevailing wisdom! Unfollow me now, before it’s too late!

Here’s the thing. There are two really outstanding posts out there on the ‘tubez that explain in vivid detail the problems with sending compressed data into a de-duplicating appliance. And these guys are both absolutely right. Everything in their posts is correct, and I would ask that, if you haven’t, you please read them before mine:

First, Brent Ozar:

http://www.brentozar.com/archive/2009/11/why-dedupe-is-a-bad-idea-for-sql-server-backups/

(And, may I say, well done on the Numero Uno Google result for that post. Very nice!)

Next Denny Cherry:

http://itknowledgeexchange.techtarget.com/sql-server/sql-backup-compression-and-backup-dedup-are-mortal-enemies/

(A very respectable #3 on the Google-ometer.)

Now, I’m not kidding. These guys know their stuff, and they are right. Stop reading right now.

Still here? Ok, now come closer.

Closer.

Shh.

I studied this whole thing very carefully, and I do it anyway.

While it’s true that de-duplication works poorly with compressed data, and if you compare the de-dupe ratios for “usual” uncompressed files with the de-dupe ratios for compressed files, the compressed data looks very, very bad. But there’s even more to this story, so much more that we decided to, in a limited way, stuff the compressed files into our DDR anyway.

Here’s why:

Both SQL Server backups and file compression are a deterministic process. If you back up the same database twice, and it has the same data pages in it, and those pages are largely unchanged, then the backup files will be substantially the same. This is true if you compress both files with the same algorithm and settings, too – the data in the compressed files will be largely identical. It will not be like any OTHER files on your network, but the two files will be similar to one another.

If you change a small percentage of the data pages in the data file, that will still be true: a compressed backup of the database on, say, Monday will be mostly the same as a compressed backup of the same database, with modest changes, on Tuesday.

What that means is that if I have a 1 TB database, which I do, that produces a 250 GB compressed backup file, and that database receives mainly incremental changes from day to day or week to week, then each successive backup will be similar to the previous one. And if I copy them into a de-duplicating store (at least the one I have to work with) then, while the first file will be basically 100% net new data, the second will de-dupe against the first. It’s not as effective as other types of files, but it does help. Let’s say, for argument, that I get 75% de-duplication of only the two files, instead of the normal 85%+ across many instances of other files, I am still getting 75% de-duplication, and that can be very useful.

Useful how? Well, we have SAN replication married to our de-duplicating store for offsite backup and disaster recovery. That means that each night I have to transmit a LOT of SQL backup data across a WAN to another site. What’s a lot? For me, that just means the pipe is small and the data is much bigger. And that process would go a lot faster if, somehow, by magic, a whole lot of the data were already at the other end of the pipe before I start.

See where I’m going with this? With de-duplicated files, as days and weeks pass, each time we replicate new files from one site to the other, a whole lot of the data is already there at the other site. We only have to transmit the net new data. Even if that’s only 50% (a very poor performance number for de-duplicated storage in most people’s minds) that’s still cutting the data in half. Which is pretty good. Plus it’s compressed, which helps every other aspect of the backup story.

So we have what I think is a good compromise, born out by internal testing:

Keeping compressed SQL Backups in de-duplicated storage indefinitely, as a replacement for tapes, is impractical. It’s just too expensive. So we keep the SQL Backups in there only for the purpose of DR, and we have a pretty aggressive purge schedule to be rid of old files. The sweet spot seems to be to keep only a week or two.
We use tapes too, for archival purposes, and they have longer retention.
We back up to local (DAS or SAN) disks first at the SQL Server and then copy into the de-duplicating store, so that the backup process performs well and isn’t bottlenecked at the network or at the speed the appliance can receive the files. So backups go to disk, then get copied into the de-dupe store, cancel against whatever is in there, and then it replicates them off site.

This is not a cheap setup, but it works great. I love it. That 250 GB file I mentioned is available at my other site in a couple of hours, because it’s always mostly there already. Your mileage may vary depending on all the specifics of the technology you have, and, as I said, Brent and Denny are right.

* Professional driver on a closed course; don’t try this at home; no animals were de-duped in the production of this post.

T-SQL Tuesday #004: Real World SSD’s

merrillaldrich — Tue, 09 Mar 2010 04:22:00 GMT

A contribution for T-SQL Tuesday #004, hosted by the illustrious Mike Walsh!

In the past few weeks I had some correspondence with Kendal Van Dyke leading up to his SQL Saturday presentation on SSDs, and he got me fired up to share a little of my team’s experience with a real implementation. Over the past four months or so, our IT group at work has deployed a new disk array incorporating enterprise-class fibre channel SSDs for database functions, and I am happy to report it’s been a success. I do have a few nuggets that I learned along the way, which might be useful if you are contemplating a move like this. Some of these tips I’ve talked about before, but they are doubly true in the rarefied (read: expensive) air surrounding SAN SSD’s. You really don’t want to sink this kind of money and not get the maximum possible return!

Project

In the Fall our organization faced a large data migration to a second datacenter, to enhance our disaster recovery and business continuity position. This entailed the purchase of some new infrastructure, and for various reasons the group decided a storage array including SSD’s made strategic sense for the next several years. I had had one eye on SSD’s when they started becoming cost-effective enough to make it into the smaller scale TPC benchmarks – indicating the cost/performance ratio was starting to make sense. Our SAN admin had also been watching them too, for all kinds of reasons, and I think I might have seen her drooling a little when this notion came up.

Anyway, we were able to obtain an EMC Symmetrix-class array with three tiers of storage, from SSD’s at the high end to 15k Fibre Channel disks to SATA disks. The array was incorporated into our existing SAN, and attached to a combination of new and repurposed servers, to create production environments for both Oracle and SQL Server. We tested the complete environment as pre-production for several weeks, mainly to validate the IO performance of this new architecture relative to what we had been running, and then have progressively cut production systems over to the equipment.

I can say that it’s performed really well – the Oracle and SQL Server systems that have SSD storage on the new array definitely saw a net boost in performance. Most of the time, our Oracle DBA’s report that they see about a 2/3 increase in performance, with some exceptions. I am running a data warehouse function on the SSD’s, and I can say we shaved perhaps 25% off of our ETL/load window by moving to the new hardware. At the same time, I would like to caution that, though it might seem like these things are supernatural in terms of performance, they are not a make-all-your-problems-disappear silver bullet. They are dramatically faster for some particular tasks, but not for everything.

Method

With help from EMC, EMC professional services consultants and our internal staff, we went through this overall process to design and deploy:

We came up with a general size and performance profile of all the existing systems we planned to move and/or consolidate onto the new array. Importantly, we got the data from real performance stats collected off our existing servers and arrays, so we could describe to EMC what the real IO requirements looked like, as well as the capacity needed. IO usually drives the cost of storage more than capacity in the database world, and we had to take pains to make sure our execs and other parties didn’t get sidetracked into looking only at capacity (so many TB) instead of performance.
EMC came back with a general design showing how many of what type of disks would be in the array. That design set most of our budget parameters :-). The new array ended up being sort of mid-range, with 46 200 GB STEC Fibre Channel SSD’s, 124 450 GB 15k RPM FC Disks, and 36 1TB 7200 RPM SATA disks.
While the array was in the manufacturing and testing queue at EMC, we did a deep dive into the specifics of how to arrange the data on all the available devices. We took about a month’s worth of performance counters from all our existing storage and mashed that into aggregate IO targets for the new servers on the new array. EMC professional services then took that data and proposed a design showing exactly how the LUNs and files would physically lay out. We did a couple of back-and-forth iterations before settling on the final design.
The basic guiding principle around the SSDs was to use them for the files that would benefit most: the read-intensive ones. We literally found the read-intensive files or disks on our existing systems from the IO counters and placed those, only, on the SSD tier. The rest of the files are on conventional 15K FC disks. All the SSD’s are set up as RAID5 (reads being the priority) and all the 15K disks are mirrored pairs, for performance.
Then The Beast, as I like to call it, was delivered, put together and health-checked by EMC.
We moved our pre-production data to the new system, and ran automated performance tests for about a month, just so we knew how to expect this thing to act.
Finally, we moved production systems over one at a time.

Take-Aways

I learned a lot going through this process – and it was really fun, to boot. Here are some basic ideas to think about if you are looking at SSDs in SAN:

If you don’t have a system for gathering IO performance counters on a rolling basis I strongly recommend, as I have before, getting or building one. This capability is vital to right-size your infrastructure and spend money wisely. I think it’s most cost-effective just to buy a monitoring system (and put your valuable working hours into something business-specific that can’t be purchased), but if you can’t afford a monitor, it’s not very hard to build a basic counter-collection system. Worst case, it is possible to log this data with perfmon, but that takes extra effort and can push your schedule out, as you have to gather the data when needed rather than just having it already in a repository.
Resist the idea that SSD’s are just so miraculously fast that it’ll be like RAM, or make all your performance worries disappear. In fact they solve mainly one problem – an important problem, but one only. They avoid the penalty of random access to disks, which requires mechanical moving parts to get to some region of a disk, then pass the data on the disk past the disk head. If you suffer from that problem, they will help – a lot. If not, then you should dig deeper and see if SSD’s will really be an advantage for your workload. Writes might not be that much faster, nor sequential reads, depending on the details. You might be best off with a hybrid solution that uses two kinds of storage, and it’s worth considering a design like that. Be realistic about their true performance characteristics and set expectations appropriately.
You still have to RAID the devices together. SSD’s can fail just as disks can fail, and if you are running a production system then you will want the redundancy. That obviously has a cost impact. On the other hand, the arithmetic might go like this: if you need, say, 48 disks in RAID 1+0 to get good performance for an existing server (and those disks are probably mostly empty) but SSD’s could get you the same performance (imaginary numbers here) with only 6 or 8 devices in RAID5, then maybe the budget starts to look attractive.

Still, it was really fun last week when I spied our little data warehouse doing over 7,900 IOPS! Sweet!

SAN 101 for the DBA

merrillaldrich — Tue, 16 Feb 2010 22:45:00 GMT

As will become apparent from this post, I am no Storage Admin. My apologies for offending the sensibilities of those who know this topic better than I do!

I get asked occasionally about placing SQL Server data on SAN storage, and I've done it with a few systems, and a lot of smart people helping me, so here's a SAN 101 crib sheet for DBAs who may be new to this.

SAN Basics

SAN stands for Storage Area Network, like LAN or WAN. The first thing of note is that it's a network, and not a device. Often it's a fiber-optic network, for speed and bandwidth, but sometimes it can be a copper network. It's easy to lose sight of the fact that its a network, because it's not what we all think of as "the network," and because the storage admins are usually the only ones that play there. Hence the colloquial term "a SAN" referring to one storage device. One device on the network is really a Storage Array (SA?), not a Storage Array Network (SAN). Tomato, tom-AH-to, I suppose, but it's important in larger environments to talk about one array separately from the storage network itself.
Because it's a network, there's a network protocol for devices to communicate. Usually that is FCP for fiber or iSCSI for copper (with exceptions.) The protocols really send SCSI commands, the same kind many hard disk devices use within a single server. That means, for practical purposes, that devices a server "sees" on the SAN look and act like hard disks rather than network devices. There can be switches in a SAN, and so on, just like other networks.
Servers typically connect to a fiber SAN with one or two (maybe more) Host Bus Adapters (HBAs). An HBA just acts as an interpreter, making the devices visible on the storage network appear to the host bus, transparently, as if they were disks. So, if you are building such a setup, you'll have a server with a couple of HBAs, those are connected to a switch or two on the fiber network, which is then connected to one or more storage arrays. If a chunk of storage on one of the arrays is allocated for your server, then you see that storage space as if you'd stuck a hard disk in. Partition, format, write. Simple!

Rule 1: There is no Magic

Storage vendors do have a reputation (they are selling things, after all) to pitch their stuff in a way that makes it seem like all your performance worries will vanish in some kind of magic fiber-optic {poof}. Not so, I'm afraid. Enterprise arrays are good, but they are still subject to the laws of physics. The disks in an array are still disks, the cache is just a cache. This is good for the DBA, in a way, because if you know how to size direct attached storage by spindle count and IOPS, then - whooee! - it's not really that different. There are things that help performance some, like a huge write cache with write re-ordering and fault tolerance, but you don't have to throw out what you know and start over. Lots of random IO still means you need lots of random IO capacity, which still means disks.

Rule 2: Performance Costs More than Space

The fundamental mistake I have seen is to hand a SAN administrator (or your SAN vendor) a spec about how much space you need for your data. That is a recipe for disaster. What is important is what the performance profile is, and, just as with plain ol' disks, IOPS will determine what the hardware has to look like in an array. And you want to do that before you get the price quote, because the performance, not the space, will drive the cost. Ask for a terabyte? You might get one 1 TB SATA disk. That won't be happy at all.

Rule 3: Yes, Direct Attached Storage is Cheaper ... But

OK, so there's a whole economics problem to be aware of. SAN storage is expensive, sometimes very expensive. In some circumstances, it's still worth it, but the price tag is undeniable. Examples: if you have the same amount of storage (or in the SQL world, amount of storage performance) in direct attached disks, and in SAN storage, the SAN flavor is going to cost a lot more up front. It's pretty easy to spend $1,000 per disk for 15k fiber channel disk drives in an array.

However, with real IT in real organizations of any size, things are more complicated - there isn't one, unchanging and monolithic system. In fact, things, servers, storage requirements, circumstances, applications all change at a breakneck pace. What we do, most of the time, is try to accomodate change and keep things "up." That is life for many IT pros. So it is not cheaper to have piles and piles of unutilized or overspec'd direct attached storage sitting around, married to single servers. If you had to buy twice what you needed, where's the savings? When you buy into a SAN what you are buying is, or should be, flexibility. The flexibility to use it fully, all the time, and to change the storage allocation for all your servers without physically scrapping storage hardware and buying new. If you have a SAN, and it's not providing that value, then you probably wasted your money.

So, if you have one relatively static database server and you're looking at storage options, direct-attached might save you a pile of money. Don't build a SAN for performance around a couple of servers -- there's no compelling performance-per-dollar argument. On the other hand, if you find that you look around your enterprise and see tens or hundreds of servers where there's a huge waste factor for storage in some places, and full disks in others, there's your SAN opportunity. This server over here is low on disk space? OK, let's just add some. That one had too much, and we're wasting space? Take some away. Storage over here is too slow? Migrate it to a set if disks with more performance.

In other words:

Best case: one SQL Server or cluster with correct direct attached storage, fully utilized. Nice, but this is the real world!

Next best: a heterogeneous, changing environment that uses SAN storage to best advantage

Worse: a collection of over-spec'd DAS hardware that is under 50% utilized, but can't realistically be changed. It seemed cheap at the time, but there's so darn much of it!

Worst: a big, expensive, under-utilized SAN

Rule 4: You Need a Good Relationship with the SAN Admin

I've blogged about this before, but suffice it to say that bad communication with the SAN admin = FAIL. SQL Server often has unique and demanding IO requirements that don't go away just because you have a fancy array. You have to be able to work that out with the storage admins, if you have them, or the vendor, if you are in a smaller shop. Together you will have to talk through the need to separate logs, data and backups, and what the performance profile of each "virtual" disk system needs to be, backed by perf counter data, to prevent the SAN nightmare: "We spent our $5,000,000 and the VP wants to know why it's SLOW."