Monthly Archives: June 2011

Your Job Isn’t To Make Your Boss Happy

You might be surprised to learn that my goal as a consultant isn’t to deliver recommendations to my customers. Instead, my goal is to deliver recommendations that help my customer’s teams be better customers to each other after I’m gone.

This is a fancy way of saying my recommendations need to be practical. I don’t recommend changing all the application code to the DBA team if that’s not something within their control– I determine where the critical needs are located, what range of options are available, and what’s viable in the short, medium, and long term given their internal customers and service providers. I provide data and tools to work effectively in the specific environment.

I don’t always speak the language of customers and services, because not everyone understands it. Good news: you do have customers at work, and understanding that will make you more successful.

You’ve Got A Lot of Customers Inside Your Own Company

Making internal customer and service provider relationships successful was one of the best skillsets I learned when I worked at Microsoft Corporation. I kept mission-critical SQL Servers highly available in a very large and complex environment, and I had a lot of customers. Some of my customers were people who paid Microsoft for services, but my other customers were application development teams, program managers, product managers (there’s a difference!), user support teams, and system engineers.

Some of these relationships were reciprocal, like with my application development teams. We made requests of each other, we delivered things to each other. We provided services for each other.

Thinking about customers is more than terminology. It’s a mindset, and it’s beneficial. These aren’t “just” coworkers. They aren’t simply people with problems. You’re part of a team who provides a service: the service has limits and its exceptions, and the service needs to be defined. Your customers use the service, and by doing so they can make or break your job.

Here’s one secret I learned: even in a highly competitive environment, 99% of the time your customers want to help you succeed. No, really. They may be grouchy at times, and they may come off as unapproachable. But it’s almost always the case that if you succeed in providing a great service for them, their job will be better and their life will be easier.

Your Boss Is Not Your Customer

Not everyone is your customer. This is important to understand. Your mission is to provide services to your customers, but your boss is not your customer.

This can be hard for many of us to fully realize. We think that success at work is measured by making our boss happy. But the real truth is, like in any human relationship, the more you focus on just making someone happy the more likely they are to become dissatisfied with you. Instead, focus on the being great at what you do: both the technical aspects and your customer service. Your manager is there to help you be successful.

This probably isn’t how your job works now. You don’t need to walk into your managers office and declare “I want to make a change in our relationship!” Instead, as you change your focus and behavior over time, talk to your management with that focus. Your team relationships will naturally shift as your focus changes.

Your Job Is To Be Great at Providing A Service. Even if the Service Ain’t Great.

Your boss has a lot to say about who your customers are and how you provide services. You have a lot to say about this too– you should make sure these are both defined well enough for you to be successful.

The road to success comes in having productive conversations with key customers.

Know this: saying yes all the time won’t make your customers happy. In fact, it’s likely to do the opposite because you won’t be able to deliver on what you agree to. Inside of a company, sometimes you just have a job where things will be consistently imperfect. Listening to your customers and responding intelligently usually will make them happy, even if the overall service they’re getting has problems.

SQL Server Database Team

It may be accurate, but don't lead with this.

Step 1: Be Known

You don’t have to be great at small talk, just be curious about how other teams work, and ask them about how they interact with your team. Be a good listener.

Reach out periodically to customers you work with, both the ones who complain or make requests, and the ones who are less interactive. Ask them what their experience is like.

Step 2: Be Responsive

Let me say first: this doesn’t mean answering all your customers’ emails right away. You don’t want to become everyone’s helpdesk (unless you actually work in the helpdesk). People understand you have a variety of tasks to do in your job, unless you give them the impression otherwise.

Make sure you answer people within a window that’s acceptable in your company– at most places it’s usually a business day or two for normal priority requests. Answer questions within this window all the time when you’re in the office. Be consistent about this, but make sure you establish the urgency early on. If something is critical you don’t want two weeks of email spread out on the issue because you always take a day to reply. If an email thread starts feeling like it’s taking forever, set up a meeting to polish things off in person.

Step 3: Practice Saying ‘No’ In Six Sneaky Ways

Don’t write a lot of long emails about why you can’t or won’t do things. If it’s a big deal, arrange to have a conversation in person, and then follow up with a brief email. If it’s something normal, don’t say no– try these:

  • Suggest Alternatives. Most times a workaround is all someone needs. “Have you tried using Blarghomatic for that? It’s not designed specifically for it, but it’ll get the job done.”
  • Ask questions. Find out what’s been done in the past, or if they know someone else who’s confronted that problem. Enlist the help of others who are in the situation. There’s nothing new under the sun, and frequently there’s not much new in the cubicle, either.
  • Find out the frequency of the problem. What’s a big pain for you to change may be a one-time issue on your customer’s end. Even if it’s critical, if it’s a one time thing you may act differently than if it’s going to come back to your inbox weekly.
  • Explain the cost. If the issue is just too expensive to the company to fix, explain the factors. This is one thing you want to do by phone or in person rather than in email– just send a summary follow up. (Avoid the forwarded message titled “Why it’s too expensive to ever make you happy”)
  • Share the load. If the issue is just time and resources to get something done, see if you can get help. If there are things the customer can do to make the job 10% easier, let them know. If you can borrow someone from another team to help move things to get the customer through a critical period, research that. Whenever you can, let the customer know what you’re looking into.
  • Brainstorm. Your customers have ideas, too! Oftentimes, they can solve their own problems, and just need you to help them see it.

You can make your customers into fans by doing these things other than saying no. This can make enough of a difference that they send the occasional email to your boss about that great thing you did yesterday in helping them find a way to make their problem less of a pain. Those emails do something important: they help your boss demonstrate that you and your team provide value. And they make your boss feel good.

Step 4: Be a Good Customer Within Your Company

This coin has two sides. I also learned to be a good customer at Microsoft. This means being clear about your needs to your service provider.

80% of this is simple thoughtfulness about reducing back-and-forth.

Here’s a simple example: does it help your SAN team if you create a single request and include the amount of space you need immediately, the number of LUNs and RAID level for each one, the estimated size in a year, and the information about the host servers and whether the HBAs are configured? Probably.

If you’re writing a new application, does it make it easier to deploy if you document what the components are and their purpose, what the estimated activity levels are for the first quarter, half, and year, what capacity is estimated, and what the margin of error is? Build time for figuring that out into your project plan, and make sure it gets delivered.

Find out how to make it easier for people to provide you services, and then do it. Do it a few times and people will respect you for it. Suddenly, you’re known as someone who really has their act together.

Martha Stewart Is Right: Thank Other People

The other 20% of being a good customer is giving people credit for doing good things. When someone has made a difference, write a quick email to them and copy their manager. Mention what they did and how it helped you and your team. Be specific, and make it a simple thank you. Don’t always pick the obvious person, and don’t try to be strategic about who you thank– you don’t want to be a suck-up, you just want to be honest.

You can also say this in person, of course, but if your organization is larger than 150 people, writing it in an email will make a real difference.

Making this a habit does a couple of things for you: it gives people positive feedback for helping you. In a medium large organization, it also makes you noticeable. It shows that you are an important customer and that you share your opinions. Just thanking people for helping makes you a more significant player.

Being A Great Customer

If this is your first attempt at a thank you note, maybe you shouldn't send them.

What’s In It For You

The truth is, doing all of these things isn’t really extra work. If you practice these things for three months, it will save you loads of time thereafter. This will be because people know you and will see you as an ally. They’ll understand more about the work you have to do, and they’ll appreciate when you can do something for them.

Improving your expertise at service delivery will make you a person and not a commodity. This is a huge benefit for DBAs, developers, network admins, and anyone in technology.

“Not having time” to handle the social parts of a technical job will only give you overblown projects with confused requirements, and emergency work because of customer needs that were discovered too late.

Best of all, if you become skilled at working with customers, you’ll have raving fans. You’ll develop a network of people who want to help you succeed.

At the end of the day, that not only makes your boss happy, that makes each day at work more pleasant. And eventually, it gets you everywhere you want to go.

Kendra Little

Kendra specializes in high availability and performance tuning. She is a Microsoft Certified Master in SQL Server-- the highest technical SQL Server Certification available. Kendra loves databases and software development more than long walks on the beach. Those cartoons in her blog posts? She draws 'em all. Read more and contact Kendra.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Iomega StorCenter PX4-300d NAS Review: iSCSI Monster

I wanted to love this monster from the moment I read the spec sheets:

Iomega StorCenter PX4-300d NAS

Iomega StorCenter PX4-300d NAS

  • Network attached storage device with 4 or 6 hot-swappable drive bays
  • Available empty (so you can bring your own drives, including SSDs)
  • iSCSI server with 2 network ports, jumbo frame support, VLANs
  • Official VMware ESX/ESXi, Hyper-V, Windows DFS compatibility
  • RAID 0/1/5/10
  • Cloud support – automatically copy files to Amazon S3, Mozy, or other Iomegas over the Internet
  • External USB3 drive support (for leftover USB hard drives)
  • Insane number of home media support options including BitTorrent downloading, Time Machine backups for Apples, Bluetooth, automatic uploads to Facebook/YouTube/Flickr, recording server for Axis/Panasonic/D-Link webcams
  • Quiet (31dBa max) and low power (based on an Intel Atom CPU and 2GB of RAM)

The full feature list is ridiculous – it’s absolutely everything I needed for my VMware lab and my backups.  At around $700 from Amazon for the diskless version, I couldn’t resist.  I wouldn’t recommend buying the versions that come populated with hard drives – if you ever need to replace the drives, you’re in for a rough time, and oddly, the bring-your-own-disk version doesn’t suffer from that issue.

Choosing Drives and RAIDs for the PX4 and PX6

As of this writing, the list of officially supported hard drives is pretty short:

Storage pools in the PX4/PX6 have to all be the same size and speed, and I didn’t really need performance, so I chose to go with four of the Hitachi 3TB drives.  With those drives, RAID 5 would give me 9TB of usable storage, and the possibly-faster RAID 10 would give me 6TB.  I wouldn’t recommend the 2TB drives for a reason that’ll be clear shortly.

PX6 users have 6 drives to choose from, so in that environment, it might make sense to go with four magnetic hard drives and two solid state drives.  This would allow two tiers of storage: a blazin’ fast SSD mirror, and a bigger/slower RAID 5 pool.  With VMware ESX/ESXi’s Storage vMotion, we can move virtual machines back and forth between the two RAID pools without taking the VM down for a reboot.  It’s a fairly inexpensive way to get tiered storage, but with just 128GB in the fast pool, I’m not sure how useful this would be in practice.

Pluggin’ in the N: Network Attached Storage

iomega PX4-300d Control Panel

iomega PX4-300d Control Panel

The PX4-300d comes with the basics: power brick, one Ethernet cable, and the management tools CD.  After adding in drives and plugging it in electricity and Ethernet, the control panel displays the IP address it fetched from DHCP.  Fire up a web browser, go to that address, and you’re going to be impressed with the user interface.  You can even test drive the Iomega StorCenter control panel online.  (No, that’s not my Iomega, and no, you can’t delete data.  I tried.)

For the first minute or two, Iomega impressed the heck out of me.  The device could fetch its own firmware upgrades over the Internet, and yes, it was up-to-date.  I went into Drive Management, and sure enough, it offered a really easy GUI to configure RAID levels.  I picked RAID 10 and clicked Apply, but Iomega warned me that I hadn’t chosen any drives yet.  I’d missed the subtle little visual checkboxes inside each hard drive in the GUI, so…

I need to stop here and tell you a little about myself: I like to break things.

I didn’t say I like to take things apart and put them back together – oh no.  I just like to find new ways to break ‘em.  I take an evil satisfaction out of knowing somebody, somewhere didn’t test their code.

It's web scale!

RAID 10 with 3 Drives?

So I did what I do – I checked three boxes, chose RAID 10, and clicked Apply.

As far as I know, there’s not a way to perform RAID 10 with 3 hard drives (it requires an even number of drives) but the PX4 happily started initializing the array.  I sat there absolutely dumbfounded – this wasn’t a small bug, but a giant, ugly, monstrous bug.  I had no idea what the PX4 might be doing to the disks behind the scenes or whether I’d have any redundancy whatsoever.

I wouldn’t be so freaked out about this if Iomega wasn’t owned by EMC, one of the biggest, most reliable companies in the storage industry.  I had instant flashbacks to the click of death, the nasty sound Zip drives made before they crashed, resulting in a class action lawsuit against Iomega.  Was Iomega back to its old tricks of cutting costs at our peril, or were they up to EMC’s quality standards?  I took a deep breath, pretended I never saw it, and deleted the array.  I set up a four-drive RAID 10 array, decided to let one bug slide, and pressed on.

While the array is initialized, the Iomega’s LCD display and banners across the top of the web UI both warn the user that performance is degraded – very nice touch.  As the array is initialized, you can continue to use the storage array, and even write data to it – again, nice touch.  However, the initialization process takes hours – I left mine running overnight – which means I wouldn’t recommend the 2TB drives.  A PX4 with 2TB drives is $1,165, and with the 3TB drives it’s $1,405 – just $240 more for 50% more storage.  Yes, you could upgrade the drives later, but it’s going to take a loooong time to swap out each drive one by one and let the array rebuild.

Some Server Changes Require Reboots

The only way to win is not to play.

Restart Required

As the array initialized, I started wandering through the web control panel setting things up.  I changed the name to LittleBlackBox, and I was taken aback when the PX4 asked to reboot itself.  Really?  Just for a name change?  Okay, well, alright, nobody was on the array yet anyway.

Next up – configure remote access.  The PX4 will connect to a dynamic domain name service so you can use a simple DNS name to access your data from anywhere.  Sounds good, so I set it up and – you guessed it – reboot required.

Next up – configure iSCSI and jumbo frames.  This one at least didn’t require a complete NAS reboot, but it did warn that “This may take a few minutes to complete.  Anyone currently accessing this device will be disconnected.  Do you want to continue?”

The Problem with Rebooting an iSCSI NAS

NAS reboots aren’t a problem for some home users.  If iTunes stops working for a few moments, or my pictures stop uploading to the cloud, life goes on.

In a business or virtualization lab environment, it’s a massive problem.  VMware will be storing the virtual hard drives for several running machines on it.  In order to reconfigure many settings on the PX4/PX6, I have to:

  1. Shut down all of my virtual servers
  2. Shut down VMware
  3. Restart the Iomega and wait for it to come up
  4. Start up VMware
  5. Start up my virtual servers

This is a total showstopper in a corporate lab environment where multiple people might be playing with the device.  I simply wouldn’t be able to count on junior sysadmins not changing settings on the Iomega, triggering a reboot, and losing all of my virtual servers.  I would have to tightly control security settings on the NAS, but the only security option is a checkbox for administrative privileges: either you’re in, or you’re not.

That NAS is a Monster, M-M-M-Monster

Or was that philanderer?

Famous Philosopher

Philosopher L. Gaga once said in a poem entitled “Monster”:

I’ve never seen one like that before
Don’t look at me like that
You amaze me

While she was referring to crazy yet well-endowed gentlemen, I’m sure she would agree that the PX4-300d is just such a monster.  Its feature list is the size of a baby’s arm, but sometimes it’s got the business logic of a weiner.

The good news is that it’s got killer qualities for a virtualization lab – or perhaps even a small business’ virtualization needs.  The bad news is that it doesn’t have the stability or testing to back it up.

I was heartbroken.  I’d really wanted to recommend this storage device to small businesses for their staff to learn virtualization, storage vMotion, and perhaps even as a backup target.  With the current version of firmware, though, I just couldn’t do it.  With that in mind, I gave up on testing the network card failovers (which do work at first glance), multipathing (which doesn’t – more on that later), the speed differences with jumbo frames, and anything reliability-related.  For now, it’s just a good device for geeks to use in their labs and homes, so I’ll focus the rest of the review on that.

Device Features: Shares, Apps, Time Machine, BitTorrent, More

Onboard storage is set up in a hierarchy:

  • Drives are grouped together in pools (like 2 drives in a RAID 1 pair or 4 drives in a RAID 5).  Pools are a fixed size.
  • Pools are carved up into multiple volumes.  Volumes are a fixed size.
  • Volumes are carved up into multiple shares.  Shares are thin provisioned.
  • Applications are configured at the share level.

So in my PX4-300d, I have:

  • Storage pool – 4 drives in a RAID 10 for 6TB of usable space
  • “Media” volume – a 1TB volume that lives in my storage pool
  • “Torrents” share – a thin provisioned share in the Backups folder.  It starts at zero size, and can grow to whatever size of files I dump in there – up to 1TB.  I can access the Torrents share as a folder in Windows or on the Mac.
  • The Torrents onboard application added a Download folder in the Torrents share.  I can copy any torrent file into this folder, and the Iomega begins downloading it.
I'm in ur tubez, downloadin ur questions.

Torrent Download Details

The application user interfaces are designed by geeks, for geeks: they’re just good enough to be point-and-click usable, but not good enough that I’d want to walk family members through configuring or troubleshooting them.  For example, in the Torrent configurations screen, there’s a Port box.  You can manually configure a port or use the automatically supplied one, but you can’t tell if it’s actually working.  There’s no “test the router” functionality built in, so I have no idea if uploads are working or not until I start seeding a torrent.  There’s edit boxes for Maximum Download Speed and Maximum Upload Speed, but there’s no scale – is it KB/sec or MB/sec?

The apps have just enough capabilities to say they work, but not much more than that.  Modern BitTorrent clients all have scheduling setups so that you can transfer more at night and throttle back the sharing during the day – not here.

The Amazon S3 sync app will upload your files to S3, but it won’t delete the ones that get deleted on the NAS, thereby making your storage bill steadily increase.  If you use it for, say, SQL Server offsite backups, you would want to reuse your backup file names in order to get a 7-day rotation in the cloud.  You’d run into a separate issue anyway – S3′s max file upload size is 5GB.  Any larger files just don’t get uploaded, and you don’t get an alert.  No upload/download throttling or time-of-day scheduling here either – your bandwidth will just slow to a crawl as new files are added to S3.

Size Matters

Time Machine File Sizes Not Updated

Time Machine works wonderfully as a backup target, but the user is presented with two cryptic fields: Apple Network Hostname and Ethernet ID.  The network hostname comes from your Mac’s System Preferences, Sharing, click Edit, and it’s the part you can edit.  The Ethernet ID comes from going into Terminal, type ifconfig, hit enter, and look for en0 – the next line says ether, and copy/paste the characters to the right of that.  The Iomega doesn’t tell you any of this.  The only thing it does tell you is the Time Machine backup folder’s size, a constant 173MB, and even that is wrong.  After several days of backups, the folder sizes still showed incorrectly on the Time Machine panel of the Iomega.

But it works.

At least, I think it works.

And that’s what makes this review so hard.  When the control panel tells me it’s configuring a RAID 10 array with 3 drives, I know it’s wrong.  A basic error like that shakes my confidence in the entire unit.  Same thing with the incorrect Time Machine folder sizes, and the wacko configuration screens.  If I can’t trust what it’s showing me, how can I trust what’s happening behind the scenes?  I’m not sure.  This doesn’t bother me too much for home media backups, but it would bother me a lot for running my virtual machines.

Is the Iomega PX4-300d the Best VMware Storage Server?

Like MJ, I keep it in the closet.

My home VMware lab: HP Microservers and the Iomega PX4-300d

Most business iSCSI network implementations involve a completely separate logical network for iSCSI – separate IP addresses on a separate subnet.  For example, my home network is on 192.168.37.x, so I might use 192.168.47.x for my iSCSI network.  The iSCSI traffic is kept away from the regular network.

Most business iSCSI setups also involve redundancy: the storage device is plugged into the network with at least two network patch cables plugged into two different network cards.  If either one fails, you’re still able to talk to the storage.

At first glance, the PX4 can handle either one of those requirements, but not both.  If you want two separate logical networks, then you probably want to isolate the iSCSI traffic completely.  You can configure one of the PX4′s network ports into your management network (like 192.168.37.x) and plug the other into an iSCSI network.  When you do that, you just lost redundancy.  If you choose instead to have both of the PX4-300d’s network jacks plugged into the same network, that network is going to need to see the outside world if you want to use any of the PX4′s home-media-savvy features like BitTorrent.

This is where the good monster comes in again.

This Good Monster Has VLAN Support

This NAS supports VLANs – multiple virtual network subnets attached to the same network card.  Imagine an incoming network packet that finds its way to your server, and it’s got a tiny part at the beginning saying, “I’m coming from Virtual LAN #1.”  Your operating system would need to understand that it’s attached to a certain subnet and route it to Virtual Network Card #1.  The next packet might come in saying, “I’m coming from Virtual LAN #2,” and your computer would understand that it’s part of your Virtual Network Card #2 – the one for iSCSI.

Granted, your one network cable is only so fast, and we can’t stuff 2Gb of traffic into a 1Gb bag patch cable.  However, this does let us segregate network traffic at the switch level so that your chatty iSCSI traffic doesn’t overwhelm your regular network, and your chatty BitTorrent client traffic doesn’t overwhelm your iSCSI storage network.  While it is indeed delicious to get your chocolate in my peanut butter, it’s not nearly as delicious to get your BitTorrent in my iSCSI.

VLANs require a savvy operating system like VMware ESX/ESXi and a VLAN-capable switch.  I chose the $125 Cisco SLM2008-T because it supports VLANs, jumbo frames, has 8 ports, and it’s cheap.  Cisco switches have a reputation for being difficult to administer, but this one comes with a pretty good web control panel.  The setup process of putting in VLANs, jumbo frames, and ESXi configurations is a good idea for a future blog post, but suffice it to say that between Iomega’s web UI, Cisco’s web UI, and the vSphere Client user interface, you can be up and running with a very real-world-ish lab setup in under an hour – for some values of “running.”

Here comes the bad monster again – users can’t exclude network interfaces from iSCSI use.  Every time VMware rescanned the iSCSI adapters, the Iomega happily reported back that iSCSI services were available from every single IP address it owned.  I wanted to just do iSCSI over one network subnet, not both – no can do.  After pulling my hair out for a few hours, I threw in the towel and switched to NFS.  It worked the very first time I tried it, and it came with a nice perk: the StorCenter’s LCD display panel actually reflects the right amount of used space used on NFS volumes.  iSCSI volumes show up as completely full even if there’s not a single file on ‘em.  (That’s not the device’s fault, but users won’t know that.)

That’s probably a good lesson for StorCenter users: the features that require zero parameters are easy, and they work like a champ.

How Fast is This Cheap NAS?

They WILL vary.

Your mileage may vary. (Just one of dozens of test passes.)

Geeks love to tweak parameters.  I want to set the stripe size, caching, NIC load balancing, and flux capacitor voltage for every storage device.  That’s not what the StorCenter PX4/PX6 is about: it’s just, uh, storage.  Just as the other LifeLine apps don’t have much in the way of parameters, the iSCSI app checks just enough boxes to work – but that’s where the fun ends.

Most of my Windows-based iSCSI tests were able to saturate a 1Gbps Ethernet link with reads or with big sequential writes, and small or random writes ran around 10-60 MB/sec.  I was surprised, though, that no matter what tricks I played with multipathing or multiple iSCSI volumes striped together, I just couldn’t get this thing to blow past a single NIC’s throughput.  The lack of network card performance metrics on the Iomega made it a little tricky to troubleshoot, but after I checked the Cisco switch metrics, I got proof: the iSCSI traffic was only sent back through one network card on the Iomega – specifically, NIC #1.

I can write iSCSI data to both of the PX4′s network cards simultaneously.  They just won’t send data back from both simultaneously – at least, not when they’re both on the same subnet.  Maybe by getting even fancier with the multipathing setup and putting the two network cards on two different VLANs, in two subnets, I might be able to break past the 100 MB/sec read speed limit.  (For more about multipathing, check out my SAN Multipathing series.)

But you know what?  100MB/sec is fine for me.  See, if I really needed speed, I would have filled this thing with SSDs to begin with.  Solid state drives excel at random access, and that’s what every Iomega StorCenter NAS is going to end up doing – lots of random access.  Multiple VMs accessing the same LUN, BitTorrents being transferred, MP3s being played, these little devices are a party in a box.  The drives are going to be worked hard, and I bet for most people, the real speed limit will be the random access speed of the drives anyway.

But while troubleshooting storage performance multipathing, I ran into the bad side of the monster again.  I unplugged one of the Iomega’s two network cables, and I didn’t get any warnings whatsoever.  The PX4 didn’t show any errors on the LCD display or the web control panel, and it didn’t even notify me via the built-in email alerts.  I had to dig into the network screen to even figure out that one of the cables was unplugged.  Upon reconnecting the cable, the Iomega autonegotiated to 10Mbps (not 100, not gig, but 10) half duplex.  No warnings were shown on the control panel for that, either, and to make matters worse, you can’t set the Ethernet speeds.  It’s autonegotiate or nothin’.

Bottom Line: It’s a Little Monster Alright.

So close, but yet, so far...

Iomega PX4-300d: 95% There

The Iomega PX4-300d NAS seduced me with its long, strong…feature list, and I’m willing to overlook the wild infestations of crabs bugs for my lab at home.  It’s the first NAS I haven’t returned back to the shop in less than 72 hours.  Like the philosopher said:

Look at him, look at me
That boy is bad, and honestly
He’s a wolf in disguise
But I can’t stop staring in those evil eyes

Who should buy the StorCenter PX4/PX6? Home users that want one quiet, capable box to sit in a closet handling backups, media, BitTorrents, uploading photos to the cloud, and other menial chores.  Yes, you could build your own more-capable solution for less money, but it won’t be as point-and-click easy as the Iomega.  IT, DevOps, and developer managers should also pick up one of these as a reward for a well-performing team.  Throw one of these under a cubicle and for less than the price of a good workstation, the entire team now has an iSCSI sandbox and shared MP3 storage off the domain and away from the corporate IT overlords.

Who shouldn’t buy it? I wouldn’t recommend the PX4/PX6 in a business environment for anything other than a lab – at least, not until versions of the firmware come out that fix the glaring UI bugs and stop users from rebooting the NAS.  I would be very uncomfortable to go into a small business as a consultant, sell them a shared storage setup using a StorCenter PX4/PX6, and walk away.

Can they fix these issues with firmware updates?  Yes, but only if they test the new firmware updates better than they’ve tested so far, and as of August 2011, it’s not looking good.  After applying a firmware update to fix the VMware multipathing issue, I received this disturbing email from Iomega:

Thank you for downloading the recent firmware update for your Iomega StorCenter px. After release, we identified an issue with growing or expanding storage pools with the v 3.1.10. 45882 update.  If you have not yet applied the update to your system, please wait. We will be releasing a new version of the firmware that resolves the issue soon.  If you applied the update, you should not experience any issues unless you expand or modify the size of a storage pool.   If you do experience any issues with a storage pool, please reply to Iomega Technical Support using this incident number….

M-m-m-monster…

If you decide to buy it, you can throw me a few coins by buying the Iomega PX4-300d via my Amazon link or the 6-drive version, the PX6.  And hey, it’s in stock for Prime members, so you could have it tomorrow – plenty of time to play with it over the holiday weekend. I’m not sayin’, I’m just sayin’.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Ozar’s Hierarchy of Database Needs

You need to poop. Maybe not right now, but in general, you need to breathe, eat, drink, sleep, and poop.  If you can’t do any of those things, then the rest of your needs will take a back seat until you can accomplish the basics.

Psychology professor Abraham Maslow designed Maslow’s Hierarchy of Needs to illustrate how us meatbags prioritize our daily tasks.  It’s usually visualized with a pyramid:

Maslow's Hierarchy of Needs

Maslow's Hierarchy of Needs

As a presenter, I find this pyramid really useful because I can’t do a good job of presenting if I’m thirsty or if I need to hit el baño.  I’ve learned that before I start teaching database stuff, I need to hit the bathroom, grab a Clif Bar, and down a bottle of water.  No, I don’t have sex before I present.  (Maybe that’s why Paul and Kim do such a good job presenting – hmmm.)

I hereby propose Ozar’s Hierarchy of Database Needs:

No, bacon is not a database need.

Ozar's Hierarchy of Database Needs

Backups: You can’t lose more data than the business is comfortable losing.

Note that I didn’t say you can’t lose data.  You can, and you will, because safeguarding data is expensive.  Not all data is created equal, and businesses are comfortable losing certain kinds of data.

For example, I’ve worked with businesses that chose not to back up their development servers at all because they believed source code control was their first line of defense for developers.  They regularly refreshed development servers once a week from production.  If they lost a development server halfway through the week, everyone’s work was checked into source code control anyway, and the server could be rebuilt from scratch fairly quickly.

At the other extreme, I’ve worked with DBAs who knew full well their mission-critical databases weren’t well-protected from disaster.

The real problem isn’t backups – it’s communication.

Being a truly exceptional DBA means that your manager, plus every level all the way up the chain, is comfortable with your (in)ability to restore every system in the shop.  Your managers have a list in writing of all the company’s systems, a high-level overview of how frequently they’re backed up, how frequently the restores are tested, and how long a restore will take.  If you haven’t written a list like that, stop and do it now.  It’ll only take a few minutes, and it’ll pay off dramatically.  This list will cover your ass, and this list will get you the hardware you need to do the right kinds of backups.

This list will also free you from trying to be a perfectionist.  The business doesn’t need transaction log backups every 60 seconds on every server.  Learn to match up your job duties with the company’s financial needs so you can focus on what really matters.

Security: Know who has access to the data.

This gets a little tricky with Active Directory groups because the Windows team can add & remove group members without the DBA knowing.

My favorite visual communication method for this is a grid of servers & databases down the left side, and a set of columns for permissions across the top:

Database Permissions Grid

Database Permissions Grid

The first time I present managers with this information, I dumb this grid down because it’s an overwhelming amount of information for readers to digest.  I want them to get the big picture: “We need to reduce the number of people who can get the DBA fired.”  After the first round of cleaning house, I add columns for the database backup and the physical database files, both of which are a serious security risk.  If someone can take a SAN snapshot of an unencrypted database, they can waltz right out the door with a copy of it.

Note that I didn’t say, “You have to control who has access to the data.”  Just as the business needs to decide the value of data, the business also needs to make security decisions.  Police don’t make the law – they just enforce it.  Likewise, the DBA needs to serve and protect the data.

Capacity: Know when you’re going to run out of space.

Every database in your datacenter is growing.  All of them.  They’re not all going to drain your storage dry tomorrow, but every single database has an expiration date when they’re going to run out of space.  In consolidated environments with dozens, hundreds, or thousands of databases on the same server, this gets a lot harder to predict.

To make matters worse, growth rates aren’t stable.  All it takes is one leftover BEGIN TRAN statement in someone’s SQL Server Management Studio and your transaction log files can grow out of control overnight.  The most out-of-the-box fix I’ve ever heard for this was Dave Stein‘s excellent suggestion to leave a 10GB backup file on your log file drive, and to leave explicit instructions with your sysadmins.  When the alerts come in that the log file drive is running out of space, delete that 10GB file and start taking emergency actions.  No, it’s not the most enterprise-oriented solution, and yes you should work towards something better, but this is a good start for small businesses.

Before you build your own scripts to alert you when the SQL Servers run low on drive space, step back for a moment and ask who else in the company needs this same solution.  Odds are your team needs it for the file servers, backup servers, mail servers, even the web and app servers.  Rather than reinventing the wheel, suggest that the entire team adopt a simple monitoring system.

Performance: Know your current bottlenecks and how to fix ‘em.

Once the basic infrastructure issues are addressed, you can finally start turning your focus toward real performance tuning.  Glenn Berry’s DMV queries are my favorite place to send new performance tuners who want to learn more about what’s holding their server back.  SQL Server is constantly tracking what it’s waiting on, and Glenn’s queries use the wait stats DMVs to pry that information out.

If you even recognize the term “wait stats,” you’re already a long way up the hierarchy.  Most database professionals are still using tools like Perfmon to check CPU percentages and looking at Average Disk Queue Length for drive issues.

I didn’t say you had to fix the bottlenecks – I just said you need to know how to fix them.  The actual process of fixing them is a business decision: the business has to be willing to spend the money and time to make their server go faster.  Your responsibility is just to have the answer when the business asks, “Why is the server slow, and how can we make it faster?”

Futureproofing: Preventing availability, security, and performance issues.

When the groundwork is taken care of, you can start looking forward and reacting to tomorrow’s problems instead of today’s.  It’s hard as hell to get here, but wow, the view is fantastic from the top of the pyramid.  When your phone isn’t constantly ringing, you’ve got the time to:

  • Plan upgrades and offer budget options to the business
  • Review T-SQL code before it goes live
  • Learn and grow your skills
  • Train your coworkers and give back to the community

These are the most fulfilling parts of the database professional’s career.  Not only do they make you feel better, they pay benefits forward.  For example, when you’ve got time to learn automation, you can write scripts to save yourself even more time – thereby giving you even more learning time!  When you can offer budget options for the business to make an old, unreliable server go away, you’re more likely to make it happen.  By writing training presentations for coworkers and communities, you force yourself to learn more details.

Peace of mind: knowing where every database is at on the pyramid.

No, that’s not a line on the pyramid because what the database needs is separate from what you need.  See, I used to blame myself if every server wasn’t at the very top of this pyramid.  I got frustrated if I knew how to fix it but I couldn’t get the time/money/permission to make it happen.  After a few years of using the Getting Things Done philosophy at work, I came to terms with the fact that I’ll always have more work than I can handle.  My job is to do the things the business values most, and I just won’t have the time and resources to address everything on every server.

Your job isn’t to bring every database to the top of the pyramid.  Your job is to do what you can with the time you have, in the order the business wants things done.  Make a spreadsheet list of your servers (not databases yet – let’s start easy) and add columns for each level of the pyramid – Backups, Security, Capacity, Performance, Future-Proofing.  Today, you probably have servers where you focus on performance, but you’re not even sure if the backups are good.  It’s time to fix that.

And if you actually made the list, then enter the Red Gate Exceptional DBA contest.  You deserve it, because you’re the kind of DBA who cares.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Nine Reasons Developers Should Learn SQL

Let’s face it, there are a lot of cool new things you could be learning right now. It seems like there’s a new technology coming out every 12.8 seconds. Why the hell would you want to spend your free time learning a crufty old language like SQL? My reasons, let me show you them.

It’s portable

Computer science students are taught hundreds of techniques and theoretical constructs while they earn their degree. A lot of that information isn’t directly applicable in day to day programming tasks, but it introduces students to fundamental constructs that they can move between platforms – they have a common vocabulary and tool kit that they can take with them wherever they go.

Although every database vendor implements their own extensions, with every new version the vendors are moving their databases to be in line with the ANSI/ISO SQL standard. Standards compliance, while tricky, makes it possible to take your knowledge from platform to platform. If you learn SQL, you’ll be prepared to move from one database to another.

It never changes

My good friend and business partner jokes that he became a DBA because SQL hasn’t changed in 35 years. This is, largely, true. Vendors have implemented their own extensions that eventually make it into the SQL standard, but the core of SQL doesn’t change. Once you understand the basics of SQL and relational theory (it’s not that hard), you’ll find that you can continue to build on that knowledge and add features and functionality that you were relying on other tools or developers to implement.

It’s an easy place for performance gains

There are only a few places to implement performance gains in an application – the presentation layer, the application layer, and the storage layer. Let’s face it, your code is already well written and well tuned; getting any performance gains there is going to be like getting blood from a stone. The database, on the other hand, is an easy place to make a few simple changes (add an index, change a query slightly) and see tremendous performance improvements. Having spent a considerable portion of my career as an application developer staring at a profiler, I can attest to this. It’s possible to pry performance improvements out of application code, but modern frameworks and toolkits are typically so well-written that the database is usually a better place (read as easier place) to find low hanging fruit for performance improvements.

It’ll make you a better developer

The Pragmatic Programmer challenges developers to learn a new language a year; not because the landscape is constantly changing but because learning new languages exposes developers to new paradigms. There are different ways of thinking about problems that can lend themselves well to different solutions (I’ve recently learned a lot from diving into functional programming). Learning SQL will teach you to think in sets rather than iteratively. In the long term, this will change the way you think about working with data and lead to improvements in your database code.

Improve communication across teams

Have you ever tried to talk to someone who spoke your language but spoke a wildly different dialect? Communicating across a language barrier can be difficult outside of work, but it can be outright maddening when the success of a project depends on it. Learning SQL will give you a leg up when you’re communicating your goals to the DBA team. They won’t have to decipher your meaning and you can tell them exactly what you need. In the end there will be less miscommunication, things will get done faster, and you’ll no longer be “that frustrating developer.”

Job security

I hate to say it, but learning SQL may mean that you get to keep your job when Ted in the next cube gets canned during layoff season. The more skills you have and the more job functions you can perform, the more valuable you become to your current employer (and to a future employer). If you can work with the database team and the development team, you’re now a valuable asset that both teams depend on for success.

It’s really not that hard

Contrary to popular belief, SQL is not a difficult language to learn. It’s a different way of thinking, that’s for sure, but it’s not difficult. There are only a handful of commands, operators, and data types documented in the ANSI/ISO standard. While vendors may add their own features, there’s a compact core of knowledge that you can learn and apply everywhere you go.

Know when it’s not appropriate to do something in the database

The database is a phenomenal tool for solving many problems, but it’s also a horrible tool for solving even more problems. To put it another way: you probably shouldn’t be digging a trench with a hammer. By learning and understanding SQL, you’ll be able to make better decisions and move poorly performing code out of the database. In fact, you’ll be able to spot these problems before they’re even problems.

Once you understand SQL, you’ll have a much better grasp on the limitations of an RDBMS. You’ll know which portions of an application can safely live in a database and which will need to be moved further up the stack to a different layer. Some data validations should live with the data, some shouldn’t. Understanding how SQL works will help you determine which rules should remain in the database.

Simplify troubleshooting

Live applications are notoriously difficult to troubleshoot. The more complexity and layers that are involved, the more difficult it is to troubleshoot an application. A good understanding of SQL makes it possible to rapidly isolate problems that do exist in the database. To put it a different way: understanding SQL makes it easy to locate the problem in one of many different layers of your application.

Want to Learn More?

Where do you go if you want to learn more about SQL?

Jeremiah Peschka

Jeremiah Peschka has worked as a database and emerging technology expert at Quest Software where he researched new trends and technologies in the world of data storage. Over the course of his career he’s worked with companies across many industries as a system administrator, developer, and DBA. He’s been involved with all aspects of application development and deployment. He likes cheesecake, coffee, and ice cream.

More Posts - Website

Follow Me:
TwitterFacebook

How to Get Budget Approval for Conferences

If you’ve never been to a conference before, the PASS Summit and Dev Connections events seem like things that happen to Other People.  This year, I wanna help make you one of us.  You’re a geek, right?  Let’s break this down into numbers.

Lightning Talks at the PASS Summit

Lightning Talks at the PASS Summit

What It Costs to Attend a Conference

People usually think conference attendees fall into two buckets: people who paid everything, and people whose company picked up the tab.  Truth is, there’s a lot of gray area, and when you’re getting ready to attend for the first time, you want to aim for that gray area.  You don’t want to pay the full costs plus take vacation time to attend.  We need to make your attendance as easy-to-digest for everybody involved, so let’s break out the three costs to attend:

1. Your salary for the week. I bring this up first because it’s your negotiation tool.  Companies love paying your salary because it’s already built into the budget.  They don’t have to do any extra approvals or paperwork to get you paid.  It’s already happening.  Your goal is just to keep it going during the conference week.

2. The registration fee. Registration for a 3-day conference is around $1,500 for early-bird discounts and around $2,000 for late registrations.  Focus on the $1,500 number because we want to get you registered as fast as possible.

3. The travel & hotels. Companies hate paying for travel, and managers hate asking companies to pay for travel.  There’s a lot of opportunities here to save money by staying at cheaper hotels and eating fast food value meals.  Add these two things together, and this is actually a huge benefit for your negotiations.  Watch how this works….

How to Pitch It To Your Manager

Think like a boss: cost #1 is already covered, and they want to avoid touching cost #3.  Let’s make negotiation easier by starting from a place they might just approve immediately:

“Good morning, your highness, how’s it going?  Wow, you look fabulous this morning.  That combover takes fifteen pounds off for sure.  Just fifty more to go!  Comb harder.  Hey, I was thinking about going to training next quarter.  It’s not available locally, and I know it’s hard to get travel approved, so how about if the company just pays for the $1,500 registration and I’ll pay the travel?  Nobody needs to know it’s out of town.  Here’s the list of topics they’re covering, and I’ll brief everybody on what I learned when I get back.”

See what I did there?:

  • You erased the money and political problems of travel
  • You didn’t even mention your salary – it’s an assumed win for you
  • You’ve reduced the entire discussion down to $1,500 for a training class, which isn’t unreasonable
  • You didn’t say it was a conference – I know, it’s a little slimy, but negotiations are usually slimy
  • You gave a list of topics the manager wants his staff to learn more about
Don't tell your boss about this part.

Conference Downtime

Now that I’ve got your attention, I have to admit that I’ve skipped a few things.  Before you talk to the boss, you need to be armed with a few things ready to go.  When you walk into that office, you need to have the following:

A printed list of the most high-value sessions you’re going to attend. Don’t just print out the entire session list and dump it on your boss’s desk – cull through it to find the sessions that give the most value to your manager.  There’s no need to tell him that you’ll be attending those professional development sessions or the after-hours vendor parties.  Just keep it to 10 session abstracts max, one page front & back, and for every session, have one sentence about what the business will gain from you attending that session.

The registration link for the session. There is a slim chance your manager will say, “Sure, let’s do it,” and you want to be able to strike while the iron is hot.

Your next half-hour free. He may ask you to fill out some budgetary approval paperwork, and you’re going to need to track down the right people to do it.  Offer to do everything for him – just get the contact name of the person in accounting who manages this kind of thing, and hound that person.  Introduce yourself as the guy who makes sure their servers run.  (I play dirty.)

The Three Most Common Ways to Say No

Here’s some of the most common management objections to sending you to a conference, and how to neutralize them:

“I can’t have you gone for a week.” I’ll have my cell phone with me the entire time.  If there’s an emergency, I’ll leave the conference and walk across the street to my hotel where I’ve got free WiFi, and I’ll spend as long as it takes to fix the problem.  Work always comes first.  (If they continue to object, remind them about the last vacation you took.)

“We’ve already used up our training budget for this year.” Well, I really want to learn this stuff to do my job better.  How about we split the costs – I’ll eat the training cost as long as I don’t have to take vacation for the week?

“Let’s talk about this in a couple of months.” Only if you agree that the company will pay registration.  If I have to pay registration out of my own pocket, I need to have an answer this week because registration cost goes up.

After The Boss Says Yes

Optionally, Buy a Kilt

Optionally, Buy a Kilt

Register as fast as possible.  Ideally, you’ll get someone with a company credit card to pay the registration fee, but if not, use your own card and submit an expense report right away.  You don’t have to wait for the event to send an expense report.  You worked hard to get to yes for this cost, and it’s the single biggest barrier standing between you and a week of happiness.  Knock ‘er down fast.

During the registration process, you’ll be asked if you want to attend any pre-conference or post-conference sessions.  These are day-long events taught by a single instructor, so they tend to go much deeper into a single topic.  At the PASS Summit 2011 in Seattle, for example, I’m giving my day-long session on Virtualization and SAN Basics for the DBA.  These pre-cons typically cost around $400 and I think they’re a huge bargain.  You don’t have to sign up for these right away, but if you can get the company to pay for one, I’d highly recommend it.  It’s usually an easy sell to bosses, too: “I can spend a day learning about virtualization and SAN storage for just $400.”  Bam, out comes the credit card.

Block that week out in your group calendar.  When people try to schedule team meetings, software releases, projects, whatever, you want to be able to say, “No, I’m going to training that week, and I’ve had it blocked out in my calendar since June.  It’s already paid for.”

Book your airfare.  My personal favorite travel booking tool is Bing Travel because it has nifty sliders to change your arrival and departure times.  I would recommend flying in at least one day early, preferably two if you can afford it.  You’ll either decide to attend a pre-conference session, or you’ll want to join in as other geeks roam around town doing things like photo walks and tweet-ups.  It’s a great way to meet your fellow Twitterers in a relaxed session, and it pays huge dividends in your career.

Reserve your hotel room, but don’t pay for it.  Hotels will let you reserve a room with a credit card, thereby locking in your room and your rate, without charging your card.  It’s called guaranteeing the room.  You usually have to cancel by 6pm on the day of the arrival, but that’s plenty of time.  By prepaying your room, you get a discount, but you lose flexibility.  If you get the chance to share a room with someone else and they’ve already prepaid their room, you’re out of luck.

Finally, when it works, post a note here in the comments.  Your fellow geeks are just as scared as you are to approach The Boss, and they need encouragement to know it can be done.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Resolving Conflicts in the Database

Anyone who has worked with replication has run into problems caused by conflicting data. I’ve lost enough sleep over replication to know that having an effective plan for conflict resolution is important to keeping replication running and healthy.

What Causes Conflicts?

There are a few ways that conflicts can show up in a database. In strict transactional replication, it’s possible for there to be a conflict when a row exists in one place and not another.

Let’s say we have a row in Sales.SalesOrderHeader that is present on both our publisher and subscriber. During some maintenance to delete incorrect orders, the row is incorrectly deleted from the subscriber. An update to the order is correctly written on the publisher. When the command to update the record is run on on the subscriber, there’s a conflict. The command contains instructions to modify a row that doesn’t exist. Replication will be brought to a screeching halt while operations staff figure out how to get the data back onto the subscriber.

In a peer-to-peer replication scenario, conflicts can happen in even more ways than with transactional replication. Many situations are caused by similar scenarios – data is inserted on both servers, data is deleted from both servers, or updates happen to a row that isn’t present. Peer-to-peer replication can also suffer from a situation where rows are different on all servers. SQL Server’s peer-to-peer replication can be configured to use optimistic concurrency; row versions are added to make sure that SQL Server is updating the version of a row it thinks it should be updating. If the versions don’t match, then there’s a conflict that needs to be taken care of.

To look at it a different way, let’s say we have two SQL Servers, one in Los Angeles and one in Charlotte. There’s a user in each city; Alfred is in LA and Barney is in Charlotte. Both Alfred and Barney start updating information about the same customer (customer ALFKI, version 1). Alfred finishes quickly, clicks “Save”, and goes out for coffee. In the background, Alfred’s write is sent to the SQL Server in LA where the row version for ALFKI is compared to Alfred’s row version. Since everything is correct, Alfred’s data is saved. Meanwhile, Barney is a bit slower, but he gets his data finished and he clicks “Save”. Alfred’s data hasn’t replicated to Charlotte yet, so Barney’s save goes through the same way.

At this time, the databases on opposite sides of the country both contain a row for customer ALFKI that has a version number of 2. The problem is that we don’t know which version is correct. When replication kicks off from one server to another (let’s say from LA to Charlotte), the originating server is effectively going to say “Here’s version 2 of row ALFKI.” The receiving server (Charlotte) is going to respond “Uh oh, we got a problem: I also have a version 2 of row ALFKI.” At this point, replication will come to a screeching halt until someone can come in and fix the problem.

Let’s Hug It Out

Unfortunately, conflict resolution isn’t as easy as talking about the problem – there’s no way for SQL Server to figure out the best way to solve the problem. There are a few strategies that can be employed to make the process easier.

  • Manual intervention
  • Logging conflicts
  • Master write server
  • Last write wins
  • Write partitioning

Manual Intervention

This is the default way that SQL Server handles replication conflicts. It’s up to operations staff to figure out how the conflict happened and how it needs to be resolved. If there are a number of conflicts, they all need to be resolved before replication can continue. This is a DBA’s worst nightmare and why many DBAs try to stay away from replication.

The more critical the business, the less likely manual intervention is a possibility you want to consider; 3:00AM is unfriendly at the best of times, much less when a critical application is down.

Log Your Conflicts

Rather than require immediate manual intervention, it’s possible to modify the replication stored procedures to log that a conflict has occurred and keep on merrily replicating data.

This approach has some advantages over manual intervention. As conflicts are logged, they can be written to a queue where conflict resolution software and kick into action and attempt to resolve the conflicts automatically before alerting operations staff. Depending on the type of data modifications being made, it’s possible that there could be few or no true conflicts in your application. Conflict detection and resolution logs can be periodically audited to ensure that there are no major problems or to adjust the algorithms for additional scenarios and to increase the number of scenarios where data can be automatically corrected.

Master Write Server

This approach makes your network topology look like an airline’s spoke and hub system: one lone master server is used to serve all writes. There’s no possibility of write conflicts because all writes happen in the same location. The downside of this approach (as mentioned previously) is that there’s only so much optimization that can be done before a server is overloaded by writes, locks, and replication latency.

Last Write Wins

The last write wins approach assumes that writes are always issued in the correct order and the last write being sent to the database is assumed to be correct. Instead of relying on any kind of conflict detection, conflicts are ignored. This approach works well when all data is written in an append only manner – there are few updates, only inserts of new data. This can also work well when O/R-M code is use to only update columns that have changed. The likelihood of conflicting writes frequently can be reduced in these cases.

Write Partitioning

To be quick about it, write partitioning is a mechanism to ensure that all writes for a single row will always happen on the same server. There are multiple ways write partitioning can be performed. In short, a partitioning key is created by applying a hashing function to a row or application entity. This partitioning key is used to find the server responsible for that chunk of data.

Fully Partitioning Data

The last way to manage conflict resolution is to fully partition data. Peer-to-peer replication scenarios assume that all data belongs on all servers. What if that isn’t a necessity? It’s always possible to split up the data across multiple servers. Rather than write to multiple master servers and wait for merges to occur, it’s possible to spread data out over multiple servers with very little shared data. This removes most places for conflicts to occur and allows the load to be spread out across several servers.

Which Way is Right for Me?

There is no easy answer for this: it depends on both the application and network architecture. Requirements are very different when writes can occur in multiple data centers as opposed to a single data center. Some applications (recording sensor data) will never run into potential write conflicts, while others (content management systems) have the potential for write conflicts.

Jeremiah Peschka

Jeremiah Peschka has worked as a database and emerging technology expert at Quest Software where he researched new trends and technologies in the world of data storage. Over the course of his career he’s worked with companies across many industries as a system administrator, developer, and DBA. He’s been involved with all aspects of application development and deployment. He likes cheesecake, coffee, and ice cream.

More Posts - Website

Follow Me:
TwitterFacebook

Should SQL Server Denali Run on Windows XP?

Microsoft’s Dan Jones wants your opinion.  Right now, here’s the status of the next version of SQL Server:

The current support matrix for OSes is as follows:

  • Windows Vista SP2 or later
  • Windows Server 2008 SP2 or later
  • Windows Server 2008 R2 SP1 or later
  • Windows 7 SP1 or later

The installer is going to block installation on unsupported OSes and it will block unsupported upgrade paths. If the current support matrix for OSes, the current support matrix for upgrade, or the installation blocks will delay your adoption of of SQL Server called “Denali”, please provide this feedback to us!

You can provide feedback on his blog post.  I know there’s a lot of companies out there who’ve decided to stay on Windows XP, and this can be an issue if you’re developing apps that run on local copies of SQL Server like Express.  Now’s your chance to have your voice heard.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

Scaling SQL Server: Growing Out

Scaling up is hard, scaling out is even harder. We all know that we can scale reads by adding some kind of replication or read-only copies of databases or using a massive caching layer. What happens when we have to scale writes?

Borrowing Brent’s grocery store metaphor, a database that is effectively scaled for reads will have ample parking, the aisles will be wide and well lit, and there will be plenty of high capacity shopping carts available for everyone to use. There are even 20 staffed checkout lines. The problem is that goods coming into the store only enter the store through a single loading dock that has space for one truck. Shoppers can get in or out quickly, but there’s no way for new groceries to get to the shelves.

If we were to redesign the grocery store to be able to handle more shipments, we need to ask ourselves:

  • How often do we need new shipments? Do we need more shipments or more shipments at the same time?
  • Where do those shipments need to go in the store?
  • Do we need to put similar items together on the truck?

Scaling Out For More Produce

To get more data into SQL Server, we need more places to write. There comes a time when the hardware in a server can’t handle any more writes, no matter how much hardware is thrown at the problem. Writes also have the distinct problem of issuing locks and hanging on to them until the lock passes out of scope (be that an individual statement or an entire transaction).

We can approach the problem a few ways:

  • Writing to one server and reading from others
  • Writing to many servers and reading from many servers
  • Finding a new job

Since this is an article about solving the problem, we can rule out finding a new job right away. That leaves us with two possible solutions.

Write Master

This is a tried and true way to solve the problem of scaling writes. Since a mix of reading and writing to a single server can cause a lot of locking and blocking, much of the locking and blocking can be eliminated by moving reads to a separate server.

Moving reads to a new server solves the problem of writing quick enough to our master server, but it just spreads the problem out to all of the read slaves. Instead of having to troubleshoot locking and blocking problems on a single server, someone will have to troubleshoot locking and blocking problems combined with whatever mechanism we’re using to move data to the read servers. Instead of having one problem, we now have a lot of problems. And, to put a big red cherry on top of this sundae of problems, eventually there will be problems scaling writes on a single write server.

Sharing the Load

Instead of writing to a single server, it’s possible to write to many servers. SQL Server supports peer-to-peer replication, MySQL can be sharded through a driver, PostgreSQL has the Postgres-XC project, and other databases have their own solutions. Clearly this is a problem that people have approached before.

A major difficulty with something like peer-to-peer replication is determining where to write data. Sometimes this is done via a discriminator column. Some attribute of the data is used to determine which server will receive the data. This could be first name, user name, email address, or an arbitrary value assigned at object creation. Schemes like this work well with a finite number of servers and an even distribution of data. Natural keys can have data skew based on regional and linguistic preferences. Splitting the alphabet in even chunks won’t lend an even distribution of data.

The other option is to randomly partition the data. We could assign a random number when data is initially written, but random numbers aren’t entirely random.

If you can’t trust randomness what can you trust? Math.

Hashing algorithms make it easy to take an input of any length and produce an identifiable output of a fixed length. The best part is that some hashing algorithms have the interesting side effect of evenly distributing data (roughly) across that fixed length output value. This is usually called consistent hashing. However the consistent hashing is generated, it’s easy to divide the total range of hashed values into multiple buckets and spread writes out across many databases. This has been discussed at length throughout computer science and in multiple papers, including Consistent hashing and random trees: distributed caching protocols for relieving hot spots on the World Wide Web and Dynamo: Amazon’s Highly Available Key-value Store.

Where Does the Load Split?

Implementing a combination of consistent hashing and peer-to-peer replication isn’t easy. The decision to route a write has to be made somewhere.

  • In the application A layer of code is written to determine how a write will be routed.
  • On the wire Instead of routing writes in our application, writes are sent to the appropriate server by router that performs packet inspection looking for a hash value.

Both approaches have their merits. Routing in the application can make this process as easy as deploying a new DLL or configuration file. A determined developer could write code to look for new databases and partition writes accordingly. On the flip side, routing writes on the wire makes this routing transparent to any calling applications – no code will need to be changed to add additional databases. Router configuration changes will be required, but the changes can be performed at any time rather than waiting for a regular application deployment.

Specialty Stores

Just as there are specialty stores in the real world, it may become necessary to create special purpose data stores and separate the data. This is frequently called sharding and is a different approach to spreading out load. Rather than assume that all databases need the same data, sharding assumes that the data can be separated across servers by some sharding key. In applications where there is no reporting that performs aggregations across all of the data, sharding can be an easy way to scale out read and write performance. Additional servers are added and new data can be written to them. Problems arise when one shard becomes heavily loaded and data needs to be moved to a new shard. This is a tricky operation at the best of times and requires careful planning.

Where Do I Split the Load?

Designing a scale out strategy isn’t easy. There are many factors to consider, and knowing how to scale out writes is just one thing to think about before designing your overall architecture.

Jeremiah Peschka

Jeremiah Peschka has worked as a database and emerging technology expert at Quest Software where he researched new trends and technologies in the world of data storage. Over the course of his career he’s worked with companies across many industries as a system administrator, developer, and DBA. He’s been involved with all aspects of application development and deployment. He likes cheesecake, coffee, and ice cream.

More Posts - Website

Follow Me:
TwitterFacebook

Fixing SQL Server Management Studio’s Tab Text

I hate the way SSMS tabs look by default.  Check this out:

SSMS Tab Defaults

SSMS Tab Defaults

That’s nearly useless.  The tabs are wide, but they still don’t show useful information.  Even worse, I’ve got more tabs than I can fit in the window, so the rest hang out in a meaningless dropdown.

To fix it, click Tools, Options,and go into Text Editor, Editor Tab and Status Bar.  Check out the Tab Text options:

Tools, Options

Tools, Options

You can uncheck the database name, login name, and server name because those are shown in the status bar anyway.  Then, if you wanna get fancy, change the status bar location to Top – it’s right above the Tab Text options.  Voila:

SSMS Tabs Fixed

SSMS Tabs Fixed

When I’m working, I save my queries in c:\temp with a short descriptive name.  If I’m performance tuning, I’ll save them as Before.sql and After.sql, or maybe Index1.sql and Index2.sql.  Presto, I can easily switch tabs without playing the guessing game.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube

How to Design Multi-Client Databases

When you’re building an application for lots of clients, there’s two common ways to design the database(s):

  • Option A: Put all clients in the same database
  • Option 2: Build one database per client

I’ve got clients on both sides of this religious war, and last week I had the misfortune of taking two of them on opposite sides to lunch.  After hearing them passionately debate their choices, I had to put together a blog post talking about the pros and cons of each solution:

Lots of Clients, One Database

The Traditional Solution

Putting All the Clients in the Same Database

It’s simple: just add a Client table at the top of the schema, add a ClientUsers table to make sure people only see their own data, and away we go.

Easier schema management. When developers deploy a new version of the application, they only have to make schema changes in one database.  There’s no worries about different customers being out of sync or on the wrong version.

Easier performance tuning. We can check index usage and statistics in just one place, implement improvements easily, and see the effects immediately across all our clients.  With hundreds or thousands of databases, even the smallest change can be difficult to coordinate.  We can check our procedure cache contents and know for certain which queries or stored procedures are the most intensive across our entire application, whereas if we’re using separate databases per client, we may have a tougher time aggregating query use across different execution plans.

Easier to build an external API. If we need to grant access to our entire database for outsiders to build products, we can do that easier if all of the data is in a single database.  If the API has to deal with grouping data from multiple databases on multiple servers, it adds development and testing time.  (On the other hand, that “multiple servers” thing starts to hint at a restriction for the one-database-to-rule-them-all scenario: one database usually means all our load impacts just one database server.)

Easier high availability & disaster recovery. It’s really, really simple to manage database mirroring, log shipping, replication, and clustering if all we have to worry about is just one database.  We can build a heck of an infrastructure quickly.

Each Client Gets A Database

The Trendy Solution

Putting Each Client in its Own Database

Many applications start their lives as internal apps for a single company’s needs.  They realize the application is really valuable, so they start selling access to outsiders.  It’s simple: when your second client signs on, just add another copy of the database and away we go.

Easier single-client restores. Clients are unreliable meatbags.  (Except mine – they’re reliable meatbags.)  They have all kinds of “oops” moments where they want to retrieve all of their data back to a point in time, and that’s a huge pain in the rear if their data is intermingled with other client data in the same tables.  Restores in a single-client-database scenario are brain-dead easy: just restore the client’s database.  No one else is affected.

Easier data exports. Clients love getting their hands on their data.  They want the security of knowing they can get their data out anytime they want, avoiding the dreaded vendor lock-in scenario, and they want to do their own reporting.  With each client’s data isolated into their own database, we can simply give them a copy of their own database backup.  We don’t have to build data export APIs.

Easier multi-server scalability. When our application needs more power than we can get from a single server, we can divide up the databases between multiple servers.  We can also spread out the load geographically, putting servers in Asia or Europe to be closer to clients.

Easier per-client performance tuning. If some clients use different features or reports, we can build a specialized set of indexes or indexed views just for those clients without growing everyone’s data size.  Granted, there’s some risk here – by allowing schema differences between clients, we’ve just made our code deployments a little riskier and our performance management more difficult.

Easier security management. As long as we’ve properly locked down security with one user per database, we don’t have to worry about Client X accessing Client Y’s data.  However, if we just use a single login for everyone, then we haven’t really addressed this concern.

Easier maintenance windows.  In a global environment where customers are scattered around the globe, it’s easier to take customers offline for maintenance if we can do it in groups or zones.

What’s My Verdict?

(No, I wouldn't actually eat this.)

And you thought cake couldn't get any better.

There’s no one right choice: you have to know your own company’s strengths and weaknesses.  Let’s take my two clients as examples.

Company A excels at hardware performance tuning.  They’re really, really good at wringing the very last bit of performance out of hardware, and they don’t mind replacing their SQL Server hardware on a 12-18 month cycle.  (They refresh web servers every 4-6 months!)  Their Achilles’ heel is extreme compliance and security requirements.  They have incredible auditing needs, and it’s just easier for them to implement bulletproof controls on a single server, single database than it is to manage those requirements across thousands of databases on dozens of servers.  They chose one database, one server, many clients.

Company 2 excels at development practices.  Managing schema changes and code deployments across thousands of databases just isn’t an issue for them.  They have clients around the world, and they’re processing credit card transactions for those clients around the clock.  They need the ability to spread load geographically, and they don’t want to replace servers around the world every 12-18 months.  They chose one database for each client, and it’s paying off as they start to put SQL Servers in Asia and Europe for their offshore clients.

Brent Ozar

Brent specializes in performance tuning for SQL Server, VMware, and storage. He's one of the very few Microsoft Certified Masters of SQL Server, a published author, and a Microsoft MVP. He likes travel, Jeeps, Apple gear, jokes, and writing about himself in the third person. Read more and contact Brent.

More Posts - Website

Follow Me:
TwitterFacebookLinkedInGoogle PlusYouTube