Blog

VMware vCloud Air SQL Summarized

After reading through yesterday’s announcements about VMware vCloud Air SQL, here’s a quick boiled-down version for busy people with jobs:

  • It’s Microsoft SQL Server 2008R2 and 2012 Enterprise Edition hosted in VMware’s data centers (not on-premises.)
  • Only 3 instance sizes are available: small (2vCPU, 8GB RAM), medium (4vCPU, 16GB RAM) and large (8 vCPU).
  • Drive space is limited to 150GB today (including data, log, and tempdb) but will be 2TB at official launch.
  • Licensing is not included yet – you have to bring your own EEs. (Unfortunately, due to the way MS License Mobility works, that means you have to move your licenses to vCloud Air SQL for a minimum of 90 days. Not many of us have spare licenses for this type of thing.)
  • The instance will need a public IP address if you want to access it from outside of VMware’s data centers. (See the “secure access” section on page 3 of the Solutions Brief PDF.)
  • The instances appear to be single virtual machines – the only high availability is the VMware host protection. (I can’t find mention of a SLA for data loss.)
  • You get a dedicated VM (not a dedicated host), but you can’t access the VM. You can only access SQL Server through your chosen port (typically 1433).
  • To get your data into vCloud Air, you’ll be pushing it through ETL products or insert statements. Restores are not supported.

In summary, if it was announced three years ago, it still wouldn’t have even competed with Amazon RDS for SQL Server back then – here’s our initial 2012 post about the RDS release. Amazon has more options for instance sizing, drives, licensing, connectivity, high availability – heck, even database platforms, because AWS supports MySQL, Oracle, SQL Server, and even their own flavor, Aurora.

I don’t get why VMware would enter this market, at this time, with this product.

Consulting Lines: “Keep going. What else?”

Some of my consulting lines are all about context. Let’s just jump right into a project kickoff meeting with an architect and a few developers:

The Conversation

Me: “So, what pains is this SQL Server having?”

Phil I. Buster: “When I first started out here, we had SQL Server 2005. The app only had a couple of users back then, so as we added functionality, we decided to keep the business logic in the database. It wasn’t too bad at first – ”

Me: “Ballpark, when was this?”

(The rest of the employees in the room give subtle winks at each other and elbow each other, knowing that Phil is getting ready to tell the application’s life story, and you can tell they’ve heard it a million times.)

Phil: “2005. And about six months into the project, we lost the key architect who’d initially designed the stored procedures. Here’s how the database looked in 2006 before we decided to rewrite it.” (Phil walks over to the wall and points at a Visio diagram taped together from tens of yellowing pieces of paper.)

Developer: “I’m not sure this is really the best use of Brent’s time…”

Me: “No no, Phil, keep going.”

What That Line Does

Things to keep in mind when using this consulting line

Things to keep in mind when using this consulting line

Put your pen down, because otherwise this line will make you want to jam it into your ears.

It’s really tempting to remind Phil of your hourly rate, or try to aggressively steer him back toward your original question, or playfully ask if everyone else wants to take a bio break while Phil brings you up to speed.

But here’s the thing – there are some people who really need to share their scarred emotional history on a project before they can face the current problems. Sometimes they want to make sure it’s clear that they’re not to blame for the issues, or that they were doing the best job they could, or that they’ve tried everything in the book over the last few years.

If you try to cut Phil off right away during the beginning of the relationship, it sets the tone that you don’t care about Phil’s work, input, or feelings. You have to let him get his story out and trust that eventually, his story will arrive at the present day.

What Happens Next

You listen attentively and patiently, and you never roll your eyes at the other attendees. (You don’t have to – they’re going to understand that you’re a patient saint, even if you don’t give them the slightest verbal clue that you’re just as frustrated as they are.) Whenever Phil mentions something that might be relevant to today’s pains, you write that down, but otherwise, encourage him to keep right on going.

Eventually, Phil’s going to arrive at the present day.

And your psychiatry work probably won’t be over, either. During your first couple of meetings, as you open up various parts of the system’s problems, Phil’s going to see something that sparks his memory and makes him want to tell you another story. At that point, you can take a slightly different tactic and ask that we put this story in the parking lot.

Another common variation of Phil is The Guy Who Wants to Demo Every Piece of the Software Before He’ll Show You The Problem. He has to show you his application because you’ve surely never seen anything like it, and he’s doing something that no other data professional could ever have conceived. Be patient with this guy too – and sometimes, he’s actually doing something cool.

If you enjoyed this technique, check out more of my favorite consulting lines.

Fake moustaches, barbecue, and SQL Server.

Must be Dell DBA Days:

Join us live as we performance tune – and break – SQL Servers:

  • Thursday Morning: Finding Your Slow SQL Server’s Bottlenecks in Record Time with Wait Stats
  • Thursday Afternoon: How to Prove Hardware is the Problem
  • Friday Morning: SQL Server High Availability, Disaster Recovery, and Licensing Explained
  • Friday Afternoon: Watch SQL Server Break and Explode

Register now and join in.

New and Improved: Relaunching Our Online Training – With a Daily Deals Sale

About two years ago, we launched our online training classes, and you loved ’em. We sold thousands and it became a pretty big part of our business.

The New Hotness

The New Hotness

We’ve launched our all-new online training video store with features like:

  • Comments – so you can ask questions on each video module
  • More powerful e-commerce – VAT, group discounts, Paypal, PDF receipts
  • HTML-based modules – so we can include code samples, images, and posts
  • Better analytics – to understand which modules you like, and which ones to tweak
  • Course reviews – we’ve had thousands of buyers, and it’s time to let them speak
  • Better coupons – including getting a discount for liking us or tweeting (and boy, Black Friday is gonna be fun this year)

To celebrate the launch, we’re running a set of daily deals:

  • Today (Monday 8/24) – The Everything Bundle: $599 – a bundle of all of our online video courses available for $1,553 just $599. Only 50 available. Gone!
  • Tues (8/25) – Chicago SQL Server Performance Troubleshooting class seats for $3,195 just $995. Only 3 available. Sold out!
  • Weds (8/26) – $99 SQLSaturday Pittsburgh Pre-Con seats – normally $199, this one-day Developer’s Guide to SQL Server Performance is half off. 25 available. Gone!
  • Thurs (8/27) – Mystery Pocket Bundles – we’ll post a handful of $29-$99 deals with secret unknown contents. On sale now. Sold out!
  • Fri (8/28) – all $299 courses 50% offfor the first 50 buyers of each course. Sold out!

Team Photos from the Company Retreat

For this year’s company retreat at the Oregon coast, we asked awesome photographer Justin Bailie to shoot updated team pictures for the site and this year’s Christmas cards. Enjoy:

Brent_Ozar_Unlimited_Team

Erik_Darling_Jessica_Connors Erik_Darling_Jessica_Connors_Doug_Lane

Brent_Ozar_Kendra_Little Kendra_Little_and_Mister_Little

Jeremiah_Peschka_Kendra_Little Jeremiah_Peschka_Brent_Ozar

Doug_Lane_Kendra_Little Erik_Darling

Brent_Ozar_Unlimited_Team_Goofy

Back up every database on the production server. No exceptions.

Before our consulting engagements start, we send clients a couple of queries to run. One of them outputs a list of databases, and the last backup date for each.

You wouldn’t believe how often this query produces surprises like:

  • “Wait – your script must be wrong. I swear we’re backing things up. We’ll fix that.”
  • “Whoa – it turns out we only run full backups monthly, and differentials weekly, but if we add a new database in the middle of the month, it doesn’t get backed up for weeks. We’ll fix that.”
  • “It’s okay – we don’t need to back up that database because it doesn’t have anything useful.”

The last one is actually the scariest one because often we discover real production data in databases that aren’t supposed to be used.

For example, at one client, there were two databases: AppV3 and AppV2. AppV2 hadn’t been backed up in months, and the architect assured me it was a really old version of the app that was just kept around for checking data every now and then.

However, as we started the engagement, we discovered that the login queries were still hitting AppV2 and storing data in there about user profiles. If they’d have lost that database, their users would have been destroyed, and their app would be down.

If the database is important enough to live on the production database server, then it needs to be considered a production-quality database, and it needs to get backed up. Full stop. Don’t gamble that no one is putting data in it.

How Do You Manage DBAs? Part 2: Training Budgets

Your DBA wants to get better at what they do, but … they don’t know exactly what it is they do, or how to get the business to pay for it. Here’s how.

My I Need to Learn List - man, where do I even begin - by Luis Llerena

My I Need to Learn List – man, where do I even begin – by Luis Llerena

Encourage them to start an “I Need to Learn” List – maybe as a text file, in a notebook, or in a shared spreadsheet.

When they’re working on help desk tickets, server outages, or architectures, and they want to learn something, they open up the list and add:

  • The problem they’re trying to solve (and be specific here)
  • Who needs them to solve it (departments, VPs, staff names)
  • How much time or money they spent working on the solution

That last one is important because your team is probably always going to find a solution even if they don’t understand the problem well. They’re going to reboot the box to make the problem go away, or apply some script from an Internet stranger, and they’re not going to feel terribly confident about what they just did. As far as the business is concerned, Mission Accomplished for now – and that’s fine. The “I Need to Learn” List is about knowing where these gaps were and what they cost at the time.

The DBA is never allowed to add a line “because I’m curious” or “because it sounds awesome” or “because all the cool kids are doing it.” This is about real business pains, because that’s where money comes from.

How to Turn the “I Need to Learn” List Into Money

Once a quarter, meet with your DBA and go through the list.

Look for a common theme. There’s probably one area of trouble that pops up over and over, like clusters or query tuning.

This forms your business case – you can say things like:

“This quarter, we’ve spent 35 hours of outage troubleshooting on the the sales team’s SharePoint cluster and the BI team’s data warehouse. We need to cut that troubleshooting time down, so I need $5k to send Ann to DBA training for that topic.”

“This quarter, Andy has spent 80 hours tuning slow queries for the developers. He’s run out of easy buttons, and he doesn’t feel confident with the advanced problems they’re hitting now. We need to invest $5k in training him to make that tuning time more effective going forward.

This suddenly makes it easier for management to open wallets, and you can even go to these other departments to get them to fork out training money as well. (Hey, look, I gotta be optimistic here.)

Microsoft SQL Server DBA Training Options

We’re lucky in the SQL Server community because we’ve got so many options. Here are the most popular ones – note that the price estimates include travel, but not human hourly time:

Blogs – free, but unorganized – you can learn a lot by regularly reading blogs, but they don’t help you start from zero and go to hero in a short amount of time. The articles aren’t focused on a clear progression. This option is best for DBAs who have a long time to learn and no urgent requirements.

Books – $50, but slow and not interactive – for some reason, people seem to think that buying a book will make them an expert. I’ve tried keeping them on my bookshelf for months, but that just doesn’t do the trick. You have to allocate a lot of time to actually read the book, and most human beings can’t read a technical book start to finish in one week while actually absorbing the material. You read some, get up, do other things, come back, read some more, absorb some of it, and so forth. Technical books often take weeks to fully absorb.

Training videos – $200-$300, ready when you are, but require focus – these cover a technical topic in a logical order, start to finish, broken up in easy-to-digest modules. You can spend 30-60 minutes a day absorbing a topic, tackle a few demos, and take a quiz to see how they’re progressing. The material is ready whenever the DBA is ready to start learning the topic, and you can learn it as fast as you can dedicate the time.

SQLSaturdays – $250-$500, but not often, and topics aren’t organized – these awesome one-day events take place all over the world, once a year in major cities. They’re run by volunteers, and often you can see great sessions – all free. Don’t just wait for them to occur in your city though – at this price, it can make sense to drive to a regional event. Thing is, the sessions are relatively short, and you can’t learn a topic start-to-finish at these events. Again, best for DBAs who have a long time to learn and no urgent requirements.

Pre-Conference Sessions – $500-$1,000 – not often, but fantastic deal – on the days before & after major conferences, speakers put on one-day classes at a bargain price (typically $200-$500). This is a good way to learn a topic in an organized way, although you can only go so deep in one day. You don’t have to attend the entire conference to buy a pre-con seat, which can sometimes make this a heck of a bargain. Conferences with pre-con and post-con sessions include the PASS Summit, SQL Intersection, SQLbits in the UK, and SQL Server Live 360.

Full Conferences – $2,000-$4,000, but topics aren’t organized – these are 2-4 days of material, broken up into 60-75 minute sessions, but the sessions are all over the map. You shouldn’t expect to learn a single subject start to finish here, but rather learn a variety of things about the current and next version of SQL Server. That list of conferences above is a good starting point.

Training classes – $3,000-$5,000, in-depth coverage of a topic – if you need to make a serious skills investment, set aside a week of your calendar, put someone else on the on-call duty, and go to a training class to focus for a week. This gives you the most interactive learning time because you can ask plenty of questions – to both the instructors and your fellow attendees – and maximizes your chance of fixing a serious gap on your “I Need to Learn” list.

Make the DBA Prove the Money was Well-Spent

No matter which training method you and the DBA choose together, give them a homework assignment.

They need to write a Book Report – summarize what they learned, in their own words, and how it relates to the pain points they wrote down in their “I Need to Learn” List. Have them talk about how they would have handled each of those past situations differently, knowing what they know now.

Telling them about this homework ahead of time is critical.

It keeps them focused on why you’re sending them to training – not to buddy around or get drunk with strangers, but to learn things to relieve real business pains.

And that’s how you get the training budget renewed.

Consulting Lines: “I have a hard stop at…”

This has grown to become one of my favorite consulting lines. I don’t use it often, but used judiciously, it’s a lifesaver. I use it a few different ways:

Using This Line at the Start of the Meeting

If you know in advance that the organizer (or some of the attendees) tend to ramble on or miss the agenda entirely, use this line right at the beginning, but do it quietly to the meeting organizer.

“Just to give you a heads up – I’ve got a hard stop today at 9:45. I need to leave early to prep for my next meeting. I’ll leave quietly so I don’t disrupt the meeting.”

The secret is the word “prep” – it tells the organizer you have work that you have to do to prepare, and that’s not negotiable. If you just say you need to make it to the next meeting, they’re gonna hold you as long as they possibly can.

It also doesn’t mean that your next meeting starts at 10:00 – you’re saying that you have prep work to do. Maybe the meeting actually starts at 10:30, or 11:00, or 11:30 – but you have work to do to prepare for it, and that’s why you have to bail.

However, this also means that you’re prepped for this meeting you’re attending now – and you’ve gotta be when you use this line. You need to have all your homework done and give prompt, accurate answers to any questions that come up. You want to show that you respected everyone else’s time too.

This line turns the organizer into a guardian looking out for your schedule. If the organizer is the problem, then it gives them fair warning that you’re going to bail. (But if the organizer is the problem, AND he’s your manager, then you’re kinda screwed.)

Using This Line During the Meeting

If the meeting’s going way off topic and it’s not relevant to you, this line gets a little harder to use:

“I hate to interrupt, but it’s 9:25, and I’ve got a hard stop today at 9:45. Are there things on the agenda that you need me for, before I leave at 9:45?”

Be warned: this is kind of a jerk move.

Scratch that – it’s totally a jerk move.

It’s like going nuclear. You only want to use this when things are way, way off topic, the meeting isn’t making progress, and there are other people in the room whose time is being wasted. (If you’re the only one whose time is being wasted, don’t even bother saying the line because you’ll get a bad reputation amongst your peers – just suck it up, wait until 9:55, and then quietly duck out. If anyone asks where you’re going, you can mumble the hard stop line, but that’s it.)

The Best Part: Your Family is a Hard Stop Too

I have a hard stop at 4:30PM for squeaky time.

I have a hard stop at 4:30PM for squeaky time.

If your kid’s got a baseball game after work, and you need to leave promptly at 4PM to make it, then your hard stop is 3:45PM. You just need to say, “I have a hard stop at HH:MM.”

The first time I used that line for personal reasons, I felt so dirty. I felt like I was lying, abusing the line, but it’s honestly true – I do have a hard stop at that time for commitments. I thought somebody would ask what was so urgent that I had to leave for.

Nobody does.

And it turns out, most of the time people are actually appreciative of you giving that heads-up warning when you need to leave, and they respect your time more.

Check out more of my consulting lines posts.

When Did My Azure SQL Database Server Restart?

In Azure SQL Database, no one can hear you scream query common system objects that tell you when a restart happened. You don’t get the access you need to sys.dm_os_sys_info, sys.dm_exec_sessions, sys.traces, or sys.databases.

The closest I’ve been able to get is to query sys.dm_os_wait_stats for several common wait types that seem to correspond with clock time – meaning, for every minute on the clock, we get about one minute of these waits.

If we subtract those waits from today’s date, we can get a rough idea of when the server restarted:

SELECT DATEADD(ms, AVG(-wait_time_ms), GETDATE()) AS approximate_restart_date
FROM sys.dm_os_wait_stats w
WHERE wait_type IN ('DIRTY_PAGE_POLL','HADR_FILESTREAM_IOMGR_IOCOMPLETION','LAZYWRITER_SLEEP','LOGMGR_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH','XE_DISPATCHER_WAIT','XE_TIMER_EVENT')

I’m using an average here because each of those waits actually reports slightly different numbers as shown here:

How to get the Azure SQL Database restart date/time

How to get the Azure SQL Database restart date/time

Best I can tell, this SQL Server restarted on July 14th at around 3:09-3:16AM, and the average reports 2015-07-14 03:11:51.570.

Who cares when the server restarted? sp_AskBrent® cares – because folks have started asking me why their Azure SQL Database is slow, so I made it Azure-friendly. Now when you use the @Seconds = 0 parameter, you can see overall wait stats since server restart – just like your on-premise SQL Servers. (ASD also has sys.dm_db_wait_stats, but I like seeing what the server’s overall bottleneck is too.)

Consulting Lines: “Write this down, because I’m not going to write it.”

Before I became a consultant, I was on the other side of the conference room table – we would bring in consultants, and I’d be amazed at all the cool lines they whipped out. Now that I’m the one giving advice, I like sharing some of my favorite consulting lines that you can use in your own day-to-day work.

The Conversation

Let’s set the stage: I’m walking a client through the sp_Blitz® results on their production server. They called me in because they were having problems recovering from outages. We’ve found a few nasty surprises.

Me: “Alright, so everyone understands why Dr. Honeydew’s backup script was skipping all the user databases on Tuesdays and Fridays, and what we need to do to fix it, right?”

Beaker: “Yes, I’ll get Ola Hallengren’s maintenance scripts installed tonight.”

Me: “OK, great. Now, while we’re looking at the Agent jobs, what’s this Hourly FREEPROCCACHE one here?”

Beaker: “Oh no. Oh no. Ohhhh, no. I thought I’d gotten rid of that.”

Me: “So it sounds like you already understand why that’s a bad thing?”

Beaker: “Yes! I am so sorry – that was another one of Dr. Honeydew’s experiments, and I thought I’d removed it from every server.”

Me: “Alright, you write that task down now, because I’m not going to include it in your written findings.”

What That Line Does

This line establishes that you’re on their side – you’re not trying to document every tiny little detail of what someone is doing wrong. Nobody needs all of their dirty laundry aired out in public, or in a written document that’s often escalated up the chain or added into someone’s permanent personnel file.

Not everything needs to be on the itemized receipt.

Not everything needs to be on the itemized receipt.

When you’re delivering written findings, you need to focus on just the top relevant issues for their problem. You’re going to find other stuff – but stay focused on what they’re paying you for.

This technique works with code reviews, infrastructure audits, or anything where you’re doing a peer review of someone else’s work.

What Happens Next

Sometimes, they pick up a pen and start writing immediately because they’re ashamed of what you’ve found.

Other times – especially when there’s a group in the room – they need a little encouragement. Everyone just nods and expects you to go on. You have to stop, wait, and look around the room for who’s going to write. Sometimes you even have to ask: “OK, who’s going to write that down and take action on it?”

If you enjoyed that, I’ve got more of my favorite consulting lines.

css.php