Blog

How To Hire Top Talent

You want to hire a fantastic DBA or database developer.

You want someone who can hit the ground running, who you can trust to maintain your current system and tune it like a pro. Plus you want someone who can see key improvements, communicate effectively, and drive change in your system.

You want it all.

It’s incredibly hard to find this candidate. First of all, you’re not the only one looking.

But guess what? You’re probably looking for the wrong person. And you’re almost certainly asking the wrong questions.

Newsflash: You’re Not Looking To Hire an IT Pro Who’s Done the Same Job Before

Hiring top talent just got a bit more fashion flair
It’s hard to hire people who are superstars and don’t have something unique about them. Like a mask.

I’ve talked to many hiring managers about what they’re looking for and how they look for it– I’ve asked these questions both as a candidate and as someone who’s helping them find the right person. I typically hear this:

I’m looking for an IT pro who has five or more years of experience with our technology, is an expert in several of those areas, and who can communicate effectively.

This limits your field too much right from the start.

Managing and developing data environments is interesting because of the variety of work available. There are common patterns and practices, but environments are highly unique and technology varies widely.

Say you want someone who’s worked with highly available internet applications. Is the person who’s already worked with a website just like yours and happens to be looking for a job now necessarily the best person?

Instead, consider this:

I’m looking for an IT pro who adapts quickly and has five years or more of experience managing or developing data environments. Strong production change experience is absolutely necessary.

There are small changes here, but it makes a huge difference. You’re open to many more people, but you’ve specified if there are any particular types of experience that are absolutely required.

Maybe you’ll find someone who hasn’t worked on Windows in several years, but has an interest in your company. Maybe they’ve worked on related technology, but different components than the ones you use. Don’t shut them out. Instead, open your mind.

Break Out of the Recruiter Room: Ask the Right People “Who Is A Great Candidate?”

Your recruiting staff is overworked. They use common tools for hiring for different departments– they probably have one or two websites they post to regularly and some established channels for finding candidates.

You want more. You need to use smart alternatives to find great candidates. Here’s how you do that:

  • Write a good job description. The job description says a lot about your business and you as the hiring manager. Write it like you care. Think about the person you’re looking for, not just the position you’re trying to fill. Be clear if the job entails on-call duties. Mention if you have a flexible working option, and if relocation is available. Please– if you list requirements for “X years with Y product”, make sure you get the product name right and that someone can realistically have been working with it for that number of years. (I don’t have 10 years of experience with SQL Server 2003, and I never will.)
  • Throw your net wisely. Find out where your recruiters are listing, and supplement with other sites if you can. Not all recruiters post on StackOverflow.
  • Use your network. Let your network on LinkedIn, FaceBook, and Twitter know that you’re hiring. Send emails to former colleagues who may have leads.
  • Reach out to community leaders. Drop a line to community leaders in the area you’re searching. Include the job description. Ask if they know anyone great who’s looking, and ask them for feedback on the job description. You can find these leaders on Twitter and in blogs. (We’re happy to help.)

You Won’t Hire a Great DBA or Developer By Asking Boring Interview Questions

If I had a dollar for every time I’ve been asked about the difference between a clustered and non clustered index, I’d be blogging from a private island. Questions like this imply you’ve phoned it in and you don’t really care about your candidate.

Don’t try to find experts with nit-picky questions, either. If you have Senior SQL Server DBAs ons staff, they probably think your next Senior SQL Server DBA should be able to wax poetic on how to change collation or write a dissertation on the nuances between data types. Once you work with a product for a while and you do certain things you get this mindset– everyone should know that. But this knowledge is typically based on how your own applications are developed.

These questions also turn off great people. Your best candidate may or may not be able to answer these immediately in extreme detail– but if they couldn’t, they could find the answer and test it out in a matter of minutes. So why not find that out?

Screen candidates by asking about their experience, not about your own experience. Become good at asking probing questions, and drive through the experience questions to find out what knowledge they have about the technology they were working on.

If you don’t have a big support team and you need someone to come up to speed immediately, don’t panic. Ask questions about times when the candidate needed to learn new skills quickly. Spend time on it– find out how much they remember and what they found difficult. You want someone who can learn, and who can describe hurdles they’ve overcome in learning in the past. Your great candidate is someone who knows that not everything comes easily, and they’ll tell you about that.

Use Timed Labs in an IT Job Interview

If it’s critical that your new hire already have certain skills, don’t try to determine that with Q&A.

Set up a VM with an environment job candidates to work in. Build questions, or have someone build them for you. If you really need those skills immediately, they should be able to fix broken things in that environment, and do it under a time limit with someone sitting in the room with them.

If they’ll have the internet available to them while they work, make it available in the lab environment, along with any standard tools.

Call in an Interviewing Professional

What if you’re in a small company and you need to hire an expert in an area where you don’t already have an expert? How do you know to ask the right questions or set up the right lab?

In this case, build the parts of your interview that cover team fit and interaction with related teams on your own. For the specialty area, rent an expert.

Be Consistent Across Job Interviews

This is a trick. Because guess what? In order to be consistent, you have to be prepared.

It’s good to be consistent for HR reasons, but it’s even better to be prepared with a set of good questions. And make sure that all your interviewers across different sessions know what they’re asking about, and have a set of prepared questions to ask as well.

You won’t end up having the same conversation over and over again because good questions start conversations where you use good follow up questions, or probing questions.

Never forget: top candidates are interviewing you, too. They have choices and they’re judging on multiple criteria, including team dynamics, manager effectiveness, and whether you appear to have your SharePoint together.

Prepare your questions and be a little serious, but also be human and have some fun. Share interesting things about your business and your team. Show that you listen. Listening makes you interesting. Top talent often will pick the team that’s fun and interesting over the boring team that pays a little better.

Make sure that you do your research and read the candidate’s blog. Make a place in your interview structure where you ask the candidate about specific blog posts they’ve written. Ask why they did something a certain way, or what they might do differently now.

Are Your Change Management Styles Compatible?

There are cowgirls and there are bean counters, and there’s everything in between. Allow a little time before you start asking about the candidate’s change management process and style– because this candidate should be asking you about that.

Make sure that you cover this in the interview process at some point. You want to find out how the candidate handles change and if they can fit well with your process. If you have a really heavy process, it may drive them nuts, and cause you endless trouble. The opposite can also be true.

This is also a good place to find out how your candidate has handled errors in the past. If you’re looking with someone with experience handling critical downtimes smoothly and calmly, devote a lot of time to asking them about past situations and how they handled it. You may also include several hypothetical questions in a time limit, in either a written or verbal format.

Ask Candidates for Feedback

Let every candidate know at the end of their interview loop that you welcome feedback about the interview experience at any time. Make sure they have your card with your email address. If they send you feedback at any time, read it, keep it, and review it later. You may see patterns you want to change.

You Can Hire a Top DBA or a Superstar Database Developer

You can hire great talent. When you do, you’ll find they aren’t what you picture ahead of time.

They have a varied work history, possibly. They may be significantly older or younger than you would expect. They may not have the degree you’d assume. But they have a passion for data, an enjoyment for learning, a love of technology, and a level head on their shoulders when everything goes down— and that’s all that matters.


The $1,000/Hour Consultants

31 Comments

I’m not the most expensive consultant I know.  In fact, I’ve recently hired not one, but two separate consultants that charged $1,000 per hour.

But remarkably similar
Not actually my plumber.

One evening, Erika reported that the kitchen sink disposal wasn’t working.  I walked in and did what any geek would do – I rebooted it.  I flipped the electric switch on and off, and when that failed to resuscitate it, I guessed there was a circuit breaker on the bottom of it.  Indeed there was, so I hit that button, but still nothing happened.

Now I’m not Bob Vila, but I understand the basics of mechanical repair.  I knew the disposal operated by spinning sharp blades at high speed, and that if something got jammed in the blades, they’d stop working.  I unplugged the electric cables, stuck a screwdriver in there, and said encouraging words.  Still no workie.  I even put my hand into the drain (I know, I know) and it didn’t feel anything like what I expected.

I gave up.  I hit Yelp, looked for the highest-rated local plumber, and called him.

Me: “My kitchen disposal stopped working.  I hit the switch, I reset the circuit breaker, and it’s not moving.”

Plumber: “Do you have the key?”

Me: “The what?”

Plumber: “Never mind.  After this I’ve got another job near you, and I’ll call you when I’m done with that.”

A couple of hours later, he called from the front door of my high-rise.  I went downstairs to let him in, and I noticed something odd.  His partner sat out in the van, not bothering to come in, and the plumber himself wasn’t carrying anything at all.

He went straight to the disposal, pulled out some kind of multitool from his pocket, and plugged it into the bottom of the disposal.  He put way, way, way more force into it than I would have done, and in a matter of seconds, the tool spun freely.  He reset the circuit breaker, plugged it back in, and voila – all done.  He ran a few loads of ice through the drain to make sure it was working.

Plumber: “Are you happy with the service and the result?”

Me: “Hell yeah.”

Plumber: “Alright, that’ll be $100.”

It took me longer to write the check out than it did for him to fix the disposal.  As I walked him out, I checked the clock and realized the whole service call took four minutes.

$1,000/Hour Consultant #2: My Exterminator

My new exterminator did me a similar favor last week.  He spent less than ten minutes waltzing through my condo, spraying the baseboards and the window frames.  As he went along, he pointed out areas where my caulk had dried up and retreated, leaving big openings for bugs.  “Go to Home Depot, get some clear caulk, and go right over the top of all your old stuff.  You’ll never need to call me again.  I don’t even need to spray if you don’t want me to.  Everybody in this building has that same problem.”

I thought for a moment.  “You knew this when I called you and gave you my address, didn’t you?” I said with a grin.

He smiled and nodded, and I wrote him a check for $150.  “I’ll tell you a secret – it’s not just your building, but every one of ’em built by this developer.  You’re all my clients sooner or later.”

Why They’re Worth the Money

Especially not for you.Some people might get angry that a professional breezed in, fixed something in seconds, and then charged a lot of money.  Not me – I was absolutely ecstatic because the plumber and the exterminator made it seem so effortless.  They knew exactly what the problem was, came armed with the solution, and didn’t waste anyone’s time.

Just because someone makes a task look easy doesn’t mean the task is easy for you and me.  I’ve struggled with even the most basic home repairs, and I just shake my head when a pro comes in with specialized knowledge.  Yes, I could have Googled for how to free a stuck disposal, but I didn’t have my disposal’s special wrench key.  Even if I’d have bought the key, I wouldn’t know how much force to use, and I would have given up long before it worked.  I would have gone on to other troubleshooting steps, and probably ended up replacing the whole disposal, thereby making a huge mess in the kitchen.  I’d have lost hours and probably some blood.

When I waltz into a SQL Server, SAN, or virtualization implementation that is just flat out busted, I know how the DBAs and sysadmins feel.  I feel that same way when my plumber comes in and fixes everything quickly.  I feel guilty, because as A Guy, I feel like I should know how to do this stuff.  I think that if I was being paid to be the Man of the House, I’d be fired.  I’m good enough to do the general stuff, but when something specialized breaks, I’m way out of my comfort zone.  I can struggle for days to fix it, or I can just call in a pro.  Sometimes money is the best way to solve these problems.


“What Were We Thinking?” Enter the Brent Ozar PLF Photo Contest

Humor
48 Comments

Before we set sail on SQLCruise Alaska our good friend Crys Manson ( b | t ) captured a few moments of Brent Ozar PLF together in action in Seattle.

It was a normal enough afternoon for the team– there was a lot of jumping, some surfing on traffic cones, and we almost crashed a wedding by accident. But looking at the pictures, we found one moment that’s hard to explain.

Tell Us What We Were Thinking

To enter the contest, leave a comment on this blog post before 12 pm Eastern (that’s high noon) on Monday, July 11.

Your comment must list what Jeremiah, Brent, Tim, and Kendra were each thinking in the photo below. Pro tip: the captions all need to fit in the picture, so shorter captions may be more likely to win!

The fine print: Contest limited to US and Canada residents only because international shipping is such a pain in the PLF.  If you’re abroad, you can still enter, but you won’t get the prize – we’ll designate an honorary US/Canada winner on your behalf.

Fabulous Prizes!

We’ll immortalize the winning entry’s genius in a version of the picture which will appear in the post announcing the winner, and we may feature your thoughts in our presentation slide decks. Great fame could be yours.

But there’s more than fame: there’s fortune! We’re giving away a Brent Ozar PLF book pack– it’s a mix of books we love to read and books we loved to write:

Brent Ozar PLF Caption Contest
Left to right you see Jeremiah, Brent, Tim and Kendra. We have something on our minds... but what is it?

Here’s an example from Thomas Duclos of Pragmatic Works for inspiration:

Captions by Thomas Duclos
Captions by Thomas Duclos

A Drumroll Please… We Have a Winner!

Our winner is James Serra, who truly knew what we were thinking. Take a look:

James Serra -- how DID he know what we were thinking?

A trove of treasures is on its way to James. When reporters asked James for comment on his big win, he responded:

I would like to thank the developers of SQL Server Denali, who kept me up until 3am using the new version, which inspired a moment of creativity of what to write in the captions while I was dosing off at the keyboard. Also, Brent was my inspiration for starting my blog at JamesSerra.com.  And he was my inspiration for the way I dress, but I won’t go there.

Thanks, James, and thanks also for not going there. Really.

For those of you who know James, be careful: he probably knows what you’re thinking, too.

Thanks everyone for your entries!


Your Job Isn’t To Make Your Boss Happy

18 Comments

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.


Iomega StorCenter PX4-300d NAS Review: iSCSI Monster

Storage
73 Comments

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’.


Ozar’s Hierarchy of Database Needs: a 6-Month DBA Training Plan

Processes and Practices

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 always have sex before I present.  Not to say it hasn’t happened.

I hereby propose Ozar’s Hierarchy of Database Needs:

Ozar's Hierarchy of Database Needs

1. 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.

Month 1: Learning Backups

Here’s what you need to focus on in the first four weeks:

It seems like a lot of work, but if you spend a few hours per week on this, you can knock it out and move on to the next level of the pyramid. I can’t emphasize enough how important it is to get this level done right. Even though it seems like it can’t impact performance or capacity, it really does.

2. 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.

3. 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.

Month 3: Learning and Implementing Capacity Monitoring

Make a list of things that bit you from behind recently:

  • Running out of space in the data or log files
  • A transaction that gets started, but not committed
  • Long-running Agent job
  • Blocking/deadlocking queries

Set up a development server where you can reproduce those problems at will. Then get demo versions of the monitoring programs you’re interested in, and see how they react to the problems. Are you able to troubleshoot the underlying problems much faster? If so, which one is more pleasant to use?

4. 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. Most database professionals still use tools like Perfmon to check CPU percentages and looking at Average Disk Queue Length for drive issues, but there’s a much better place to get started: our free First Responder Kit. We give you a checklist and free scripts to attack performance problems as they’re happening, live.

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?”

Month 4: Learn Performance Troubleshooting

Go through the First Responder Kit and learn how to use these tools:

  • Wait stats
  • sp_WhoIsActive
  • The First Responder Kit Checklist

This month, you’ll be building experience that will carry you through the rest of your career troubleshooting the toughest issues. Mastering these basic techniques is the key to getting started solving really fun problems.

5. 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.

Month 5: Learning How to Future-Proof the Database Server

Start by making a list of business problems. I’m not talking about “we’re experiencing locking on the Sales table” or “the CPU use is too high” – think about the problems that the business users face. Go walk into the offices where someone is interacting with both the customers AND the database server, and ask them what they’re worried about.

Don’t just ask them what bothers them about the database – ask them what they wish they could do better, or what keeps them up at night. You’ll hear problems like this:

  • “We’re getting ready to add dealers in 3 new cities, and our reports are going to get bigger.”
  • “I’m worried about what happens if this office gets hit by a hurricane this season.”
  • “The vendor isn’t being good about giving us support on this app, and we’re thinking about changing to another vendor.”

Then figure out what SQL Server features, versions, and editions can help them solve their problems – or maybe the answers involve hardware or software changes, too. When you start examining what the business will be doing six months or a year from now, you can be ahead of the curve and fix problems before they get ugly.

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.

Month 6: Give Yourself a Report Card

After you’ve put in all this work, stop and gauge your success. Make a list of before & after scenarios for each level of the pyramid. When you started, what did the database environment look like? What does it look like now? What work and learning did it take to get here?

Show the business the progress you made over the last several months, and explain that you were able to do it all for free (thanks to the help of friendly bloggers, ha ha ho ho.) This is great evidence for you to get a raise or get a training budget.

Subscribe to Our Free 6-Month Training Plan

We want to train you on this stuff for free. Subscribe to our emails, and every Wednesday for 6 months, you’ll get a lesson with some of the best free resources from around the web. We’ll start with backups, then work our way up to the top of the DBA pyramid. After six months, you’ll feel confident using the word DBA in your job title.

This 6-month series includes links to the best scripts, free training videos, blog posts from around the web to train you fast and give you homework. You even get coupons for our training videos.

Get started now.


Nine Reasons Developers Should Learn SQL

32 Comments

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?


How to Get Budget Approval for Conferences

#SQLPass
22 Comments

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.


Resolving Conflicts in the Database

Replication
11 Comments

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.


Should SQL Server Denali Run on Windows XP?

6 Comments

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.


Scaling SQL Server: Growing Out

SQL Server
2 Comments

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 Primary

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 primary server, but it just spreads the problem out to all of the read replicas. 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.


Fixing SQL Server Management Studio’s Tab Text

88 Comments

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.


How to Design Multi-Client Databases

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

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

(There are also hybrids, and I actually prefer one of the hybrid approaches, but I’m keeping this simple for the sake of this post.)

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 reports and 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.


SQL Server Locking and You!

Did you know that SQL Server’s locking has a name? It’s called two-phase locking. If we’re really getting specific about it, SQL Server uses what’s called strong strict two-phase locking or SS2PL. We’ll get there in a few minutes, right now we’re going to take a look at what makes up two-phase locking.

But First, Some History

The earliest references to two-phase locking (2PL) that I can find is in Bernstein and Goodman’s 1981 paper Concurrency Control in Distributed Database Systems. The authors examine multiple 2PL techniques for synchronizing transactions, alternatives using timestamp ordering, integrated concurrency control methods combining 2PL and timestamp ordering, before mentioning some other also-rans in an appendix. (This may also be the first use of the now tired example involving bank balances and transactions.)

Even though his paper focuses on distributed databases, it’s still valuable because it sets up a common vocabulary for things to come. There’s a lot of theory in here. It’s interesting, but it’s still mathematical theory and most people glaze over when they see that sort of thing.

The Basics of Two-Phase Locking

2PL works by being explicit about who is doing what to whom. Or, in clearer terms:

(1) Different transactions cannot simultaneously own conflicting locks; and (2) once a transaction surrenders ownership of a lock, it may never obtain additional locks

Locks conflict if they’re not compatible with each other. That is to say that if both locks are on the same thing and at least one lock is a write lock, that’s a conflict. (Astute readers will notice that readers won’t block readers.) Anyone who has used SQL Server for a while will be familiar with what happens when we try to acquire a conflicting lock: we wait. Sometimes, we’ll wait for a good long while and a lock will eventually be released. Sometimes, we would end up waiting forever (a deadlock) if the lock manager didn’t step in and kill off one of the processes.

Why Is It Called Two-Phase Locking?

This process is called two-phase locking because there are two distinct phases. The two rules above hint at them, but in effect a transaction can either be issuing locks or releasing locks, it cannot be in stasis.

In reality, this works more like the following:

  1. A statement is issued by an application.
  2. SQL Server compiles the statement and determines the types of locks that are needed to most efficiently satisfy the query.
  3. Once all locks are acquired, the transaction is in a ready state.
  4. SQL Server will begin operations and release locks as appropriate.

If you’re really playing along at home, at some point you’ll figure out that lock acquisition and release varies by isolation level and results in the various phenomena that you see in each of the isolation levels. If you noticed that on your own, give yourself a gold star. If you didn’t, you’re normal.

Deadlocks

Deadlocks are, sadly, a byproduct of a 2PL mechanism. Most of the literature talks about things like transaction graphs (or waits-for graphs) and edges. The Great Triumvirate illustrates this perfectly.

Original source via http://www.flickr.com/photos/charmainezoe/5307975564/
Deadlocks, circa 1836

Daniel Webster is grooming his eyebrows in a fashion that almost became known as ‘the Webster’. He’s impatiently waiting for Henry Clay to finish with the funny pages, but he won’t release his comb until he has the funny pages. Henry Clay is reading what passes for the funny pages in 1836 (hint: it’s the New Yorker). Henry is well pleased with himself, but he’s waiting on John C Calhoun to relinquish the volumizer before he will give up the funny pages. John C Calhoun is volumizing his hair to lofty heights but he really wants Daniel Webster’s eyebrow comb. Everyone is waiting on something from everyone else, but nobody will give up first. This is a deadlock. Okay, maybe that’s not really a deadlock, but it’s better than a waits-for graph. A combination of techniques can be used to determine which transactions will be killed off. SQL Server will typically use the least expensive transaction (in terms of optimizer cost). SQL Server avoids some problems, too, by not attempting to retry transactions.

A Waits-For (or Deadlock) Graph

Strict Two-Phase Locking

So far, we’ve only talked about 2PL, but I said SQL Server is SS2PL. Between the two is Strict Two-Phase Locking or S2PL. S2PL is like 2PL but there are some more rules.

To be considered S2PL, a transaction has to follow the rules of 2PL (sound like normalization rules?). In addition, a transaction also has to release write locks after the transaction has ended and either been rolled back or committed. Interestingly, nothing is directly said about read locks in S2PL. However, read locks can be released as they are no longer needed during the transaction.

Strong Strict Two-Phase Locking

SS2PL (called S2PL in Concurrency Control and Recovery in Database Systems) requires that the locks are only released after the transaction is finished and has been committed or rolled back. SS2PL provides serializability – database transactions appear as if they are atomic and occurring in complete isolation from one another. Serializable transactions are interesting because for a database to truly be serializable, it should be possible to process transactions in any order, s long as the effective is the same as that of some serial order (not any, just some).

Why the Devil Should I Care?

Locks are the primary way that SQL Server manages concurrency. This is a limitation of the database. New CPUs, a SAN, more disks, solid state hardware, and more RAM will not remove locking, blocking, and deadlocks from the database. New CPUs, a SAN, more disks, solid state hardware, and more RAM will make locking, blocking, and deadlocks happen faster. They may happen so fast, that you don’t really notice the problem until it’s growing out of control.

Combine a healthy knowledge of how locking operates with a working knowledge of isolation levels and some allegedly insurmountable application problems can be resolved through simple changes in the data layer.


Top 10 Keys to Deploying SQL Server on VMware

Virtualization
79 Comments

The most successful deployments of virtual SQL Servers have a few things in common.  When a company is doing all of these things, odds are they’re going to be very happy with their virtual SQL Servers:

10. Use vSphere 4.1, not earlier versions. vSphere 4.1 introduced major improvements in multi-CPU scheduling that vastly improve performance.  SQL Server will break queries out across multiple virtual CPUs, and it expects all of those vCPUs to perform identically.  vSphere 4.1 helps make that happen.  You can read more about the vSphere CPU scheduler here.

Lacie IAmAKey Drive9. When using blades, avoid overcommitting switch uplinks. Virtualization works great with blades – but only when the SAN and network teams monitor utilization rates between the blade chassis switches and the rest of the networks.  All too frequently, the SQL Server is thrown into a blade chassis with dozens of other servers all competing for the same small amount of storage and network bandwidth leaving the blade chassis.  Everyone looks at their monitoring systems and can’t understand why the backups, big queries, and maintenance jobs run so slow, and the culprit is the hidden small uplink.

8. Avoid 1Gb iSCSI for storage. While this cheap and easy-to-use storage works great for most servers, it doesn’t work as well for SQL Server.  If you’re happy with the speed of SQL Server running on local storage, you can be in for a rude surprise with 1Gb iSCSI.

7. Test storage performance before deployment. I’m partial to SQLIO, which might be the worst-named tool in history.  It has absolutely nothing to do with SQL Server – it doesn’t require SQL Server, and it doesn’t mimic SQL Server.  It just flat out hammers IO using whatever parameters you pass in.  Using a test file of the same size as the expected database, the storage should consistently exceed 100MB/sec whether it’s random or sequential, reads or writes.  Higher numbers than that may be required due to the application design or end user expectations, but that’s a good foot-in-the-door number.

6. Compensate for slow storage with memory. If you can’t get the storage performance you need, you can help by caching as much of the database as possible in memory.  If the projected database size is 50GB but the virtual server only has 16GB of memory, then it’s not going to be able to cache the entire database.  Perhaps the users won’t be querying old data, in which case you might be able to get by with less.

5. Ensure CPU power saving is turned off. While Intel’s latest Xeon processors provide impressive power savings, they won’t ramp up to full processor speed unless the CPU is under heavy load.  SQL Server will rarely push the processors that hard, which means they stay slow – sounds good in theory, but in reality, every query takes 70-100% longer than it did pre-virtualization.  If you care about query performance, turn this setting off in the hardware BIOS.  You can read more about the power-saving CPU issue here.

4. Coordinate VMware reservations and shares with SQL Server settings. VMware vSphere has a great set of options to ensure that a guest OS gets the right amount of resources for its needs.  These settings need to be set in concert with SQL Server’s min and max memory settings.  There’s plenty of bad advice out on the web saying things like, “Just disable the balloon driver and give SQL Server all the memory” – that’s not right either.  There’s a happy medium in letting SQL Server and VMware cooperate to balance resources across multiple guests, but it only works when these settings are aware of each other.

3. Use Resource Pools to track SQL Server licensing. Microsoft’s virtualization licensing for SQL Server is notoriously complex.  Starting with SQL Server 2008R2, only Datacenter Edition ($60k/cpu socket) provides unlimited virtualization rights.  Enterprise Edition ($30k/cpu socket) provides just four VMs.  Tracking these closely with VMware Resource Pools can result in huge cost savings.  If SQL Servers are allowed to move to any host in the VMware cluster, then a licensing audit can produce staggering costs.

2. Use VMware HA for high availability. If your users can tolerate a SQL Server outage of 2-3 minutes, VMware HA is much easier to manage than a SQL Server cluster.  If your users require less than 30 seconds of downtime, consider implementing a physical SQL Server cluster instead.  SQL Server clusters are tricky enough to manage on their own, and doing them inside VMware adds an impractical level of management on servers that can’t be down for more than 30 seconds.

1. Virtualize small SQL Servers first. Start by gaining experience with 1-2 vCPU servers with under 16GB of memory.  As the company’s sysadmins grow accustomed to how SQL Server uses CPU and memory in a virtual environment, they’ll be more confident virtualizing larger servers.  If the DBAs and sysadmins don’t get along when trying to pin down performance problems on smaller servers, they’re going to be very adversarial when dealing with larger servers.

Wow – That’s a Demanding List!

I know what you’re thinking: “This outside consultant is greedy and expects everybody to dump tons of money into their SQL Servers.  He’s asking for the moon.  Nobody does this in real life.”

Let’s rewind back to the beginning of the recommendations where I said, “When a company is doing all of these things, odds are they’re going to be very happy with their virtual SQL Servers.” You can certainly skimp on some of these items and still stand a pretty good chance of being happy with your SQL Server performance.

The more items you skimp on, the worse your chances become.  If you implement a virtual SQL Server with 32GB of memory trying to cache 250GB of databases on RAID 5 storage, hooked up via 1GB iSCSI, with no prior experience virtualizing SQL Servers of that size, odds are you’re going to be miserable.  Users will scream and complain, and you’ll bring in an outsider who will track the problems back to these types of recommendations.  Keep my contact info handy.

Our Training Videos: VMware, SANs, and Hardware for SQL Server DBAs – our 5-hour training video series explaining how to buy the right hardware, configure it, and set up SQL Server for the best performance and reliability. Here’s a preview:

https://www.youtube.com/watch?v=S058-S9IeyM

Buy it now.


I Think You’re Exceptional. No, Really.

Professional Development
22 Comments

A funny thing happened on my way to becoming a judge in the 2011 Exceptional DBA Awards.  But to get there, I gotta tell you a story about my dark past.

I used to be a developer.  No, wait, it gets worse: I coded web pages in Classic ASP, aka VBscript.  My idea of reusable code was stuff that I could copy/paste into multiple web pages.  I didn’t have a QA team – our idea of testing was to run it a few times on our own machines, and then keep an eye on it after we deployed it to production.  To me, the word exception will always remind me of exception handling, one of the many things I didn’t do well as a developer.

The bad news was that my code sucked, but the good news was that I knew it.  I wasn’t destined to be a developer, and I started transitioning into database administration instead.  I loved SQL Server, hardware, and performance tuning, so I began interviewing for jobs that would let me focus on that.

Along the way, I had a job interview that didn’t go well.  It was a company with offices in multiple states, and part of the interview involved a phone discussion with the head DBA in another state.  The DBA asked me, “If you wanted to monitor for exceptions and problems on your SQL Servers, how would you do it?”

I answered immediately without so much as a thought: “I’d buy an alerting package from a vendor.”

The DBA drilled down deeper, asking me how I’d build an alerting system with things like DMVs and SSIS, but I refused to budge.  I explained that my code simply sucked, and that it would take me way longer to build a collection utility – and even when it was done, it’d be garbage.  The DBA asked if I would use any of the freely available scripts at places like SQLServerCentral.com, and I could hear the DBA’s anger and dissatisfaction through the phone at my replies.  I just didn’t want to hassle with rolling my own software.

Needless to say, I didn’t get the job.  However, that experience pushed me harder to hone my skills. In my epic post Rock Stars, Normal People, and You, I documented my grueling struggle to take control of my databases, my career, and my life.  It was a long, hard road, and it all started with one simple thing:

Guess which one of us is not in IT.
Working in IT for a wine company is hard. Really.

I spent time in the SQL Server community.

I started reading blogs, watching webcasts, and attending presentations.  As I got my confidence up, I realized I could give back too, so I started presenting my own stuff.  The more I gave back, the more addictive it became.

I know you’re exceptional too – because you’re here.

When I go out and talk to people at conferences, SQLSaturdays, and user groups, I talk to people about what blogs they read.  Most of ’em don’t have time to read blogs.  You’re already in the minority just by being here – you’re taking time out of your day to advance your knowledge.  You’re not getting paid to read my blog.  You’re doing this because you love what you do, and you love to learn.  That’s exceptional.  It’s outside the norm.  Sure, I know, you think you’re just one of thousands here in the online community, but it’s easy to forget that simply by being here, you’re already ahead of the curve.  You’re ahead of the tens of thousands of database professionals who never take the time to read blogs, watch webcasts, and attend presentations.

Red Gate just launched their annual Exceptional DBA Awards contest to honor people like you.  Yes, you.  And I want you to go enter.  Stop comparing yourself to the people up on the podium, and start comparing yourself to the people who never even bother to show up to free conferences in their own town.  You’re exceptional.  Stop thinking of it as blowing your own horn, and start thinking of it as being proud of what you do.  I know you love what you do, because you’re here reading this blog.

Trust me – I’m judging the contest.

I’m honored to say that Red Gate invited me to join Brad McGehee, Rodney Landrum, and Steve Jones in judging the 2011 Exceptional DBA Awards. We’re looking for people who don’t just do their jobs, but they go above and beyond – spending time reading blog posts, answering questions online, and giving back to others.

I’m really honored to be a judge because it’s a champagne moment for me.  See, several years ago, when I was struggling through that job interview with The DBA?  That guy was Rodney Landrum.

He was right to turn me down, because at the time, I wasn’t exceptional.  I didn’t read blogs.  I didn’t know DMVs well.  I didn’t give back to the community.  But you, dear reader, are already far ahead of where I was.  You’re exceptional, and it’s time you threw your hat in the ring.

Go visit ExceptionalDBA.com today and learn more about this year’s contest.


What Makes a Good Conference Session or Keynote?

Writing and Presenting
8 Comments

One of our FreeCon Chicago brainstorming exercises was to talk about what makes a good training session, conference session, or keynote speech.  I started it by asking a few questions.

And yes, these are the cool kids.
Sign of a bad session: all the cool kids would rather stay out in the hallway.

Does a successful session require a packed room? I was so happy to hear the attendees answer, “No.”  A packed room has absolutely nothing to do with the success of a session: a packed room has to do with the success of the conference schedulers picking the right room size for a given topic, abstract selection committee picking the right abstract for the audience, and speaker’s marketing ability in getting the word out.  You can’t even judge success by the population in the room at the end of the session, either, because many attendees won’t leave mid-session out of sheer politeness.

Does a successful session require demos? The attendees universally answered, “NO!”  The best explanation I’ve seen comes from a post Jeremiah shared in our community newsletter: Why how is boring and how why is awesome by Benjamin Pollack.  No, most audiences don’t really want to watch you click and type and fix typos, but even if they did, conference rooms are horrible, awful places to watch demos.  You can’t see the screen well, you can’t take notes fast enough, and you need a step-by-step reference that you can follow along later anyway.  That’s not to say presentations with demos aren’t successful – indeed, they can be.  It’s just that demos aren’t required to be successful.

Does a successful session require slides? Again, the answer was simple: “NO!”  We talked about some all-demo sessions that were spectacular, Buck Woody’s sessions where all he used was a whiteboard, and panel discussions.  We even liked the Actor’s Studio style where a session is nothing more than a very well-conducted interview.

I have presentations that are on both extremes: my Virtualization & SAN Basics presentation is 100% slides, and my Blitz: SQL Server Takeovers presentation is 100% demos.  Every now and then, a fellow presenter will come up to me afterwards and say (with more than a little disdain), “I noticed that you didn’t use any (slides/demos).  Do attendees ever leave bad feedback about that?”  I totally understand their point of view because presenters are used to certain delivery mechanisms, but instead of the tools, we need to focus on the storytelling.  It’s a tough concept for us technology people to get because our very business is tools.  Instead, we have to take a step back and ask the audience what they’re really here for.  At FreeCon, the answer from the attendees was loud and clear.

A successful session requires one thing: engagement. Attendees have to feel that they’re interacting in some small way.  They want eye contact from the presenter, but much more than that, they want to feel a sense of belonging and bonding with both the presenter and their fellow audience members.  They want a session that engages their brain, shows them something interesting and new, and gives them something to talk about.

Because they serve beer.
My Kind of Keynote

Think about how you engage at the ball game. Whether it’s our kids playing soccer or a visit to a baseball/basketball/football/drinking game, we engage.  We talk back to the announcer on the loudspeaker, we yell at the players, and we share our feelings with the people sitting next to us.  If we’re lucky, we interact directly with the players by catching balls or catching their eye as we sit courtside.  We build up rituals like the seventh inning stretch.

Presentations are spectator sports. We pay for tickets (sometimes), root for the home speaker, share our thoughts on Twitter, and hope to catch a thrown t-shirt.  Engagement gets harder as audiences get bigger, but it’s still possible.  As I walked into my “Tuning T-SQL Step by Step” presentation at Connections Orlando this spring, I realized my session had been moved to the developer track, not the typical SQL Server track.  When the big room filled up, I took a show-of-hands poll to see the mix of developers versus database administrators.  Since it turned out to be a diverse audience, I engaged the audience throughout the presentation by pitting them against each other.  I’d say things like, “Well, you developers know how those DBAs are – they’re control freaks, aren’t they?”  I tried to pick on (and promote) both sides evenly so that everyone in the room would feel like I’d taken their side at least once.

If it’s a blowout or a bad session, we vote with our feet. When I first started going to conferences, I heard the experienced veterans say the same thing over and over: “I’m skipping the morning keynotes – they suck.”  I understood the motivation – many of us partied late into the night – but in my wide-eyed naïveté, I showed up each morning hoping to see the home team knock it out of the park.  Unfortunately, many of the keynotes I’ve attended have just plain sucked.

Which brings me to a question for you: what should we tell new speakers?  What makes a good session or keynote?


PASS Summit 2013 Location: Charlotte, NC!

#SQLPass
9 Comments

This morning, the Professional Association for SQL Server announced that the annual Summit will be held in Charlotte, North Carolina.  For the last 5 years, it’s been held in Seattle, and PASS members have protested.  We wanted the Summit to move around from place to place to make it easier for other people to attend and to let us see some different scenery.  (I mean really, how many years can we try to talk our spouses into going to Seattle in the fall when it’s cold and rainy?)  This year, it’s moving!  I’m so happy to hear this.

You can read the announcement at SQLPass.org.


An Inside Look at Our PASS Summit Submissions

#SQLPass
1 Comment

The Professional Association for SQL Server (PASS) holds a summit every year where thousands of database professionals gather to learn about the latest developments from Microsoft, meet their fellow community members, and drink Jägermeister. This year, there’s a new way to help shape the PASS Summit – you can vote on the sessions you’d like to see.

ROOOOOXANNE...you don't have to put on your red light....
ROOOOOXANNE…you don’t have to put on your red light….

Here’s the sessions we’re submitting this year along with some personal notes about why we’d like to present ’em.

“And Then It Got Even Worse”… Great Mistakes to Learn From (Panel Discussion- Vote)

Brent says: Nobody calls me when things are going well. When my phone rings, the poop has already hit the fan. My favorite disaster was when we found out that the redundant air conditioners…weren’t. This wouldn’t be so bad except that it was in the middle of summer in South Florida. It was a Sunday, so it took us a while to drive into the office. We tried opening the datacenter doors to help cool things off, but then we realized that since it was Sunday, the office air conditioners were turned off too. Good times. In that story, I’ll talk about why RAID 5 still isn’t enough for serious data protection.

Tim says: I was one of those accidental DBAs everyone talks about 12 years ago. Then things got busy, really busy. Now I find myself going back and fixing all the poor decisions made by the DBA who was learning on the job: Me.

Jeremiah says: I’ve written my share of bad code, recovered downed servers on 2 hours of sleep, and had every SQL Server upgrade go horribly wrong. It’s good to learn from your own mistakes, but it’s even better to learn from someone else’s.

Kendra says: It’s funny, when everything goes terribly wrong, you’ve got to stay very calm but also think fast. More than once I’ve had a situation go south where I thought of a story someone told me– and it saved the day.

BLITZ! The SQL – More One Hour SQL Server Takeovers by Brent (Vote)

Brent says: “You’re the database guy, right? We’ve got this SQL Server that’s been under Johnny’s desk. Everybody’s using some app on here. It’s yours now. Kthxbai.” DAMMIT, that sucks. I hate those moments as a DBA. Suddenly I had a completely new – or rather, completely old and busted – server that I had to manage. No backups, no documentation, no security, all problems. I built my Blitz script to take over servers faster and easier, and last year at the 24 Hours of PASS, I shared it with you in an all-demo session. Today, I’m a consultant, and I have to rapidly assess SQL Servers several times a week. I’ve built a new version of the script that does it harder, better, faster, stronger, and if this session is picked, I’ll give it to you at the Summit.

Tim says: I’ve been using variations on this script for a couple years now. It continues to save my bacon and make me look like a superstar in the office.

Jeremiah says: I had a collection of scripts for years that did something almost like what the Blitz script can do. I can’t say enough great things about this session.

Kendra says: ZOMG, he’s giving this away?

I don't blame him
How It Looks for Us (yes, Robert Davis is hiding his face for shame because he’s in Brent’s session)

Consolidation is NOT a 4-Letter Word by Tim (Vote)

Tim says: I’ve been given a VM farm to fill with SQL Servers. So how do I decide what instances and databases I’m virtualizing and consolidating? I USE THE MATHS! Dynamic Management Objects, system objects, and the like are all tools I’m using in the process. I’m not going to talk about how to consolidate. This is about how to come up with the numbers to work your load balancing Mojo.

Brent says: I got a four-figure bonus from my boss’s boss after I showed how to consolidate a bunch of crappy old servers onto one brand new server. I saved over half a million dollars in licensing, made my own job easier, and paid off my Jeep. How cool is that?

Jeremiah says: When I was a production DBA, I frequently said “The less I have to manage the better.” Consolidation made my job much easier because I had to worry about less licenses, fewer servers, and less things that can break.

Kendra says: Consolidation is terrifying. What if it all goes horribly wrong in six months, which is when you can’t get more hardware? What you need is a method to figure things out.

How StackOverflow Scales with SQL Server by Brent and Kyle Brandt (Vote)

Brent says: I remember back in 2007 when I went to my first PASS: I loved the session on MySpace’s SQL Server infrastructure. I’ve been helping out with StackOverflow’s databases for a couple of years now, and one day I was just thinking to myself, “Wow, this stuff’s getting pretty big. Maybe we should talk about it at PASS?” I asked Kyle Brandt (BlogServerFault@KyleMBrandt) if he’d like to co-present a session with me about the magic behind the scenes.

Kendra says: This session is so full of win. I’ve worked with Brent and Kyle together and they’ve mastered a complex and dynamic environment. They’ve faced common challenges that happen to many companies, and they’ve managed to conquer the problems without breaking the bank.

No More Bad Dates: Best Practices for Working with Dates and Times by Kendra (Vote)

Brent says: I remember the first time my company grew into multiple time zones. Suddenly GETDATE() on one server didn’t match GETDATE() on another server, and all our reporting tables had to be reworked. Whoops. <sigh> Now what? Do I store it in UTC? Do I change all my server clocks to UTC, or do I use offsets? Why is this stuff so hard?

Jeremiah says: Temporal data has always been tricky to deal with. The new data types make life much easier for DBAs, developers, and end users alike.

Kendra says: After working with TSQL for many years I realized there are a lot of intricacies to dates and times that you don’t see until you’ve mucked things up a bit. It all seems easy from afar,  but once you get your hands dirty it’s a different story. Who knew language settings were a big deal for dates? Who knew computed columns with dates were picky? What do you mean, TIMESTAMP isn’t a stamp with a time? I pulled together all the wackiness, gotchas, and oh-look-at-that’s into this talk to save *you* some time.

Performance Through Isolation: How Experienced DBAs Manage Concurrency by Kendra (Vote)

Concurrent something-or-others

Brent says: WITH (NOLOCK) only gets you so far, buddy. Time to cowboy up.

Tim says: Is ANTI-SERIALIZABLE an isolation level?

Jeremiah says: I like it when I get the rows in wrong the order, or twice, or twice, because of isolation I like it when I get level tricks.

Kendra says: Each time I give this presentation I’m amazed at how important it is to manage transaction isolation properly in an active environment, and how easy it is to misunderstand how things work– I’ll give you steps to manage concurrency that will save you loads of trouble.

Rewrite Your T-SQL for Great Good by Jeremiah (Vote)

Jeremiah says: Rewriting T-SQL is not the most glamorous way to make your database faster, it’s not always the easiest way to make your database faster, but it’s frequently the most rewarding way to make your database faster. Over the course of my career I’ve written a lot of bad T-SQL. I’ve re-written even more bad T-SQL to make applications run faster. Sometimes I even had to rewrite the code in ways that didn’t make sense to me as a developer but it made perfect sense for the database. I learned a lot but I always felt like there should be more information about this topic.

This session focuses on real problems I’ve faced, real patterns I’ve uncovered, and real solutions I’ve used to make things faster.

Brent says: Just because your T-SQL runs doesn’t mean it runs fast. Most of the time, this is completely okay, but every now and then you need to rewrite a working query to make it fly. That’s where Jeremiah comes in: we do this constantly. Learn from people who make a living doing this.

Tim says: So recently when I ran across a trigger that included a WAITFOR DELAY in order to resolve a timing issue with a GUID Primary Key in a child table that may have been bad code? (And unfortunately I’m not making that up for the sake of humor. Sadly they had removed the GUID Primary Key over two years ago but left the unnecessary WAITFOR DELAY. Easily fixed with a couple hyphens. I’m sure Jeremiah will lift heavier in this session.

Kendra says: This isn’t about putting lipstick on a pig– it’s about making your pig glow with health. Jeremiah will teach you more than tricks or how to use flashy features. Instead, he’ll teach you how to systemically improve your application performance.

Rules, Rules, and Rules by Jeremiah (Vote)

Jeremiah says: I like digging into something and figuring out how it works. When I first started working with databases, I thought that this was a pretty easy way to store and retrieve data. The more I learned about databases, the more I became fascinated by the way they worked. As I started digging into databases, I discovered that the most fascinating part of a database wasn’t really the database itself, it was the underlying software. To keep learning and understanding more about how databases work, I dug into the computer science that makes them tick.

There’s a lot of theory out there, some of it’s only applicable when you’re writing a database, but a lot of it can be applied and can help you make your life as a database professional easier. If you’re half as interested in this as I am, you’ll get a kick out of this session.

Brent says: if you like Dr. DeWitt’s keynotes diving into the technology side of databases, but you’re not quite sure how to relate that back to your own job, this is the session for you. Jeremiah likes reading database source code in his spare time, and he can show you how things like drive rotational speeds influence your schema designs.

Kendra says: Jeremiah’s unusual because he’s been a full time developer, a full time DBA, and a full time consultant. This has given him a big-picture view of systems from the spindles to the compiler, but with a practical twist. This is a great talk, and it’ll set you up to engineer better applications.

SELECT Skeletons FROM DBA_Closet by Tim (Vote)

Brent says: Experience is a fancy word for someone who’s made a lot of mistakes. Tim’s experienced, and he’ll talk about his educational opportunities so you can be experienced too – but without all the burn marks.

Tim says: I was the King of Cursors and Prince of Linked Servers when I was learning by successful failing earlier in my career. This public trousering hopefully provides some redemption. If not then it wouldn’t be the first time I’ve made a fool out of myself in public.

Jeremiah says: I can’t wait to learn about all of the skeletons that Tim is going to bring into the open, mainly so I can laugh along with him at the mistakes we’ve both made.

Good coffee, great conversations, amazing friends.

Seven Strategic Discussions About Scale by Kendra (vote)

Brent says: Want to amp up your career? Like reading stories on HighScalability.com? Kendra’s got experience with huge systems and a talent for communicating. If you’re frustrated with a crappy app design, she can help you convince your fellow developers, DBAs, and the people holding the checkbook. It’s really, really hard to find good training on big, high-performance systems, and you can get it for free in this session.

Jeremiah says: The best way to learn about scaling is to do it wrong and then do it right. The next best thing is to learn from someone who’s worked on massive databases and who is willing and able to share that information with you.

Kendra says: After years of experimentation, I’ve figured out how to sell a good idea: honestly, but with great data and timing. For any given issue, there are concrete things you can do to be more persuasive. Here’s how to find the right change for your systems, and the data which will get your message through.

Storage Triage: Why Your Drives Are Slow by Brent (Half-Day Session – Vote)

Brent says: This year, PASS let us submit 4-hour sessions for the first time. I’m really excited about this because there’s some sessions I just can’t cover in an hour. Very often when I’m called into clients, the SAN administrator is getting thrown under the bus. I need to quickly figure out who’s really at fault – is storage slow, or is storage slow because we’ve got memory or query problems? In this session, I’ll explain my diagnosis decisions to help relieve storage pains, and I’ll give attendees a poster with my decision tree.

Jeremiah says: I wish I’d had a session like this when I started learning about storage. Heck, I still wish I could go to a session like this. Storage is a critical component of SQL Server performance – getting it right is key.

Kendra says: Trust me: you want to be friends with your SAN admin. This talk will set you up so you only bring your SAN administrators problems that they can solve, with data that makes sense. If you can do that regularly then you’ll have an ally on your side when things go wrong, and that can really save the day.

The Database is Dead, Long Live the Database by Jeremiah (Vote)

Jeremiah says: We’ve all supported application features that we knew shouldn’t be in a relational database. Sometimes, you just have to suck it up and do what you can to keep that SQL Server running, right? It turns out that there are a lot of other ways to get things done and some of them work a lot better than storing data in SQL Server. This is something that I learned the hard way while performance tuning different applications – you need to pick the right tool for the job. I’ve struggled with trying to make a solution fit into a relational database and perform well. Sometimes, it just doesn’t work out.

Peanut Gallery
Brent and Kendra at the keynote bloggers’ table (or as we call it, the peanut gallery)

You’re going to love learning about the mistakes I’ve made and how I’ve solved them, sometimes in novel ways.

Brent says: You’ve got that one app you keep swearing at because it performs horribly in SQL Server. It uses too much space, it tries shredding XML in every query, or it never gets queried period. Turns out it shouldn’t be in SQL Server to begin with, and Jeremiah can help you see the alternatives. They’re a lot easier (and cheaper) than you think.

The Other Side of the Fence: Lessons Learned from Leaving Home by Jeremiah (Half-Day Session – Vote)

Jeremiah says: I worked at Quest for a while as an Emerging Technology Expert. My job was to take a look at different databases and investigate how they could benefit businesses. I made some mistakes and had to re-learn a lot of things before I could start making sense of things. There’s more to it than learning new features and ways of doing things – by getting outside of my comfort zone, I had to make sure that I really understood how SQL Server operated in order to compare and contrast it to other databases.

Unlike The Database is Dead, Long Live the Database, this talk is all about different features in SQL Server, how they work, and why they got that way.

Brent says: Jeremiah likes to experiment. He’s a SQL Server guy who’s been playing doctor with several other database platforms. I love talking to him about how SQL Server does stuff because he can relate it in terms of how PostgreSQL or Hadoop do it. I don’t have the time to become an expert on those other platforms, but the things he teaches me about them help open my eyes about how I can do things differently in SQL Server.

Kendra says: Jeremiah and I talk about different database platforms often. Working with different platforms is like travel: you understand your home in a deeper way after you’ve been elsewhere.

The Periodic Table of Dynamic Management Objects by Tim (Vote)

Brent says: Tim’s poured a lot of work into making DMVs easier to understand. His introduction for this session says it all – “It’s time to have fun… WITH SCIENCE!” I love this session idea.

Tim says: I’m having a lot of fun with what started as a way for me to exercise my creative demons. We’ll go over the namesake poster and learn some interesting tricks using Dynamic Management Objects. We’ll also play some games to reinforce what you’ve learned. Like the abstract says: “It’s time to have fun… WITH SCIENCE!”

Jeremiah says: I was so excited when Tim swore me to secrecy and showed me an early draft of his poster. There a lot of DMVs and Tim figured out a great way to communicate meaningful information about them all.

Kendra says: You know how you thought Chemistry class was going to be all math, but then you got to light things on fire and blow some things up in the parking lot? You wanna be here for this one.

Top 10 Crimes Against Fault Tolerance by Kendra (Vote)

This raccoon was the secret to winning Quiz Bowl

Brent says: When I first talked to Kendra before SQLCruise 2010, I loved hearing about the scale and flexibility of the servers she was working with. Multi-terabyte databases behind load balancers for constant uptime? Your ideas are intriguing to me and I wish to subscribe to your newsletter. If she’s that serious about data warehouse uptime, I’d love to see her tricks for everyday databases!

Kendra says: This talk is all about the important things that are much easier to do at the beginning. When  you’re first designing an application or a service and you’re creating new databases, there are ways you can vastly reduce your recovery time down the road. As time passes, it’s much harder to make changes to add these things in. This is a list you can live by.

Virtualization and SAN Basics for DBAs by Brent (All-Day Pre-Con – Vote)

Brent says: I’ve given this all-day session at SQLbits in the UK and Connections in Orlando to rave reviews, and my clients frequently bring me in to give this same session to their DBAs, VMware admins, and SAN admins. I help get everyone on the same page so they don’t try to throw each other under the bus.

Tim says: Brent has presented on this topic on SQL Cruise and each time I walk away with more things to try back in the “Real World”.

Jeremiah says: Every time I talk to Brent about SAN or virtualization, I come away feeling smarter that I was before.

Kendra says: This session gives you the foundation you need to make it through every day comfortably– it’s like permanent clean underwear. We all need that, don’t we?

Sound Interesting? Vote For Us!

You can vote for the sessions you’d like to see here. Vote before midnight Pacific on May 20th.


Twitter #SQLHelp Hash Tag Dos and Don’ts

6 Comments

If you’d like to get quick SQL Server help, the #SQLHelp hash tag is a fun way to get it.  My original “How to Use the #SQLHelp Hash Tag” post hit a couple of years ago, and it’s time for a followup.  Read that post first, and then come back here for some basic guidelines.

Don’t use #SQLHelp to promote your blog. Congratulations on writing an informative post, and we’re sure it’s got some useful information in it, but the #SQLHelp hash tag is for people who are asking questions.  Unless your blog post was written to answer a question currently live on #SQLHelp, please refrain from tweeting about your blog.

Do answer a #SQLHelp question with a product if that’s the solution. Vendors build products to solve pain points, and sometimes those pain points surface as #SQLHelp questions.  If the answer is a product – whether it’s a free one or a paid one – then feel free to mention it and provide a link.  If you’ve got personal experience with the product, that’s even better.  If you’re a vendor, you might wanna disclose that in your tweet.

Don’t demo #SQLHelp at conferences by saying, “Say hello, #SQLHelp!” Immediately, dozens of users around the world will reply to you, and the #SQLHelp hash tag will become unusable for half an hour or more.  Rather than saying Hello World, ask the audience to give you a question, and then post that question on #SQLHelp.

Do suggest that long discussions move to a Q&A web site. Sometimes questions need a lot more detail than we can get in 140 characters.  If you notice a discussion turning into a long back-and-forth conversation, helpfully suggest that the questioner read my tips on writing a good question and then create a post on whatever site you prefer.

Don’t post jobs to #SQLHelp. Use the #SQLJobs hash tag instead.

Do thank people who give you #SQLHelp. This is a group of volunteers who love to lend a helping hand.  It’s like getting consulting help for free around the clock.  High five ’em if they helped you get through your day easier.