Blog

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.

• StackOverflow.com for T-SQL programming questions
• ServerFault.com for systems administration questions
• DBA.StackExchange.com for DBA-specific questions that might need cross-platform expertise
• SQLServerCentral.com for general SQL Server stuff

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.

The tech event community is growing again.  Years ago, GeekCruises (now named InSight Cruises) pioneered the traincation concept, and last year SQLCruise brought that theme to the SQL Server community.  The new Startup Workaway brings tech founders to Costa Rica for startup sprints – a mix of work and play.  Red Gate’s SQL in the City invites database geeks to Los Angeles and London for a day of sessions.  If you want to learn and play, there’s a lot of fun options.

Now Denny Cherry, a longtime SQL Server community member, MVP, and author, and his wife Kris have formed a new event: SQL Excursions.  The first event is in Napa, California in September.  I talked to Denny about launching the new event.

Brent: Congratulations on launching SQL Excursions!  What made you take the big step?

Denny: Thanks Brent we are thrilled to be getting SQL Excursions off the ground. Kris and I put SQL Excursions together for a couple of different  reasons.  I wanted to be able to do some more speaking and teaching which is something which I love to do, and we wanted to put something together which would give spouses / significant others a way to go on the trip and having something fun for everyone to do.  As you’ve probably noticed, Kris comes on a lot of my trips with me, and to  often she ends up going to the dinners and parties and doesn’t really have anyone to talk to that isn’t a SQL Server person, and apparently she doesn’t find talking about SQL Server all day exciting. We see these events as a way to get some great technology information to the technology folks, and get the significant others to see a small piece of what we do, while giving them some fun events to go to.

Brent: I can see why Kris would love this.  What kinds of events is she planning during the day while the SQL Server training happens, and what kind of training are you doing?

Denny: On Thursday and Friday during the day the guests of the attendees will be off on a full day wine tour, which we haven’t set the price for yet (we need to know the interest level before we can set the price).  On Saturday there will be another full day wine tour for all the attendees and their guests which will also be an optional day.  Tom LaRock and I haven’t set the training schedule yet.  We will be putting up a survey with some topic options that we’d like to talk about so we can have the community vote on which of those topics will be covered.  The training will all be 300-400 level sessions.

Brent: How’d you pick Napa as the first location?

Denny: We picked Napa as the first location as lots of people love wine, and I’ve never heard of anyone not having a good time in Napa.  For people that decide to come to Napa a couple of days early or stay a couple of days after (like Kris and I are doing) there is tons of stuff to do in downtown Napa. There are several extremely good restaurants, as well as several tasting rooms.  All this is just a short few minute walk from the hotel that we have selected for our first SQL Excursion.

Brent: I noticed you said walk, and that’s a really good thing.  I, for one, love wine tastings, and driving home afterwards isn’t an option!

Denny: Drinking and driving is never an option, no matter what event you are at.  The great thing about Napa is that the downtown area has lots of stuff to do right there.  For official events which are away from downtown and away from the hotel, we’ll be getting shuttle vans to get everyone to where we are going.  If someone wants to venture out on their own Napa is just that short walk or few dollar cab ride away.

Brent: Both you and Tom do a lot of travel – I see you at all the big conferences. With SQL Excursions, this is yet another event – are you still going to all the other events like the PASS Summit and TechEd?

Denny: You are correct we both do a lot of traveling to do presentations and I don’t see SQL Excursions taking away from any of these other conferences. Conferences like PASS, Tech Ed, EMC World, and Connections are a major part of my continuing professional education and I will always do my best to attend, and speak (when they’ll have me), at them.

Brent: Who’s the perfect person to go on SQL Excursions Napa?

Denny: I would say that someone who is a mid to senior level DBA who is looking for two days of solid 300-400 level material while having a  great time in Napa with other data professionals.  Now you definitely do not need to know anything about wine to come to Napa, god knows I don’t know much about wine. During lunch in addition to learning about SQL Server, we’ll also be having some instructors come in to teach about wine, and how to find a  wine that works for you.  If any of this sounds like a good time to you, this is definitely an event to look at.

Brent: What do you want your attendees to take away from the event that’s different than typical events?

Denny: Oh course we want our attendees to get some great SQL Server knowledge out of the week.  We also want them to have a great social experience that at the larger conferences people aren’t always able to do because they get left behind and lost in the mass of people that are there.  At our Napa SQL Excursion it’ll be a small group, so there won’t be any getting left behind with everyone having the option of getting together with the group for dinner after the sessions.  We’ve got some  great after events that we are still working on getting setup which will make for a great way for the attendees and their guests to mingle and have a great time.

…

Brent again here.  I think this event looks like a lot of fun, and I wish I could attend – but if I add any more travel to my fall schedule, Erika’s going to helpfully arrange my belongings out on the front door.  Thankfully Denny’s one of the attendees on this month’s SQLCruise Alaska, and I look forward to talking with him about the event there.  As I wrote in my post How to Get Paid to Take a Cruise, anybody can start their own community and training events.  What’s stopping you from attending – or hosting – one of these fun events?

You can learn more about SQL Excursions here.

The fastest query is one you never execute.

The premise is that one of the slowest parts of starting up an application isn’t starting the application itself, it’s loading the initial application state. This can become a problem when you’re loading many copies of your application on many servers, especially you’re in the cloud and paying for CPU cycles. In that article, a commenter proposes reading application start up state from a serialized blob; basically a chunk of memory written to disk. The trick is that the serialized blob is stored in cache rather than on disk or in a database. Sometimes you need to hit disk in order to refresh the cache, but the general idea is that all configuration info is stored in a single binary object that can be quickly read and used to start up an application to a known good state.

Caching for More Than Start Up Times

Once you start caching application start state, it’s natural to look for more places to introduce additional caching. Remember, the fastest query is the one that you never execute.

Most people already know that they can add caching to their application to improve performance and get around slower parts of the system. There are a number of well understood design patterns that focus around caching and its place in software architecture. A lot of people don’t take this one step further and use caching as a trick to avoid down time when they roll out updates.

You might be thinking “Wait a minute, doesn’t my database/SAN/operating system have some kind of cache?” You’re right, it does. Storage cache is your last line of defense before reading from disk. Why not cache things in your application and skip the network hit?

So what happens when you need to update the application? In the past you probably scheduled an outage in the middle of the night. Or maybe you performed rolling outages from server to server and then slowly brought features online across groups of servers. However you did it, it’s complicated, requires down time, and you need to have a rollback plan; rollbacks on large databases can take a lot of time.

What if instead of just caching configuration to avoid slow start up, you start caching all data (or as much as can fit into memory)? You’re doing that already, right? Why mention it again?

If you’re caching data already, it seems logical that your application is written with multiple tiers. Those tiers are probably divided out by application or by service. If so, there’s a lot of logical separation between different features and functionality. You might even be calling a read/write API as if it were a service provided by a third party. This is a perfect example of how you can cache your reads and avoid hitting lower layers of the application; the front end never needs to know that anything exists apart from the services that provide data.

If you can cache data at the service level, you can theoretically take your back end systems offline for maintenance and bring them back online with minimal disruption to your users. Ideally, there would be no disruption. You could queue up modifications during your maintenance window and then commit them to the database once the updated database, services, or features are back online.

The Beauty of Isolation

By isolating features and layers from each other, you can make your applications more responsive. Rather than relying on servers to respond quickly during application start times, you can make it possible to load binary configuration data from cache. Frequently run queries can be served even faster by caching results in memory. Down times can even be avoided by caching reads and writes during the maintenance window. Of course, caching writes can be difficult. You can start by caching reads and keep your application up most of your users; it’s better than shutting everyone out completely.

To learn more about caching on Windows, read up on AppFabric Cache. On the *nix side of things, there’s the tried and true memcache. More novel and exotic solutions exist, but AppFabric Cache and memcache are great places to get started.

At the Connections conference in Orlando, I had the opportunity to sit down with Richard Campbell, host of RunAs Radio, and talk shop.  I love conversations with Richard because he gets to travel and touch all kinds of cool systems, so as a result we end up jumping off-topic all over the place, talking about the neat stuff we’ve seen and the way it changes IT jobs.

In the podcast, we talked about the two extremes of IT: seems like half the people are excited to cut their costs by going to the cloud, and the other half are excited to raise their performance by switching to solid state drives.  No matter which way you want to go, databases (and database performance tuning) techniques are changing.

Head over to RunAsRadio.com and listen.  Enjoy!

Copy, paste, copy, paste. I just love plagiarizing helping the community.

Tom LaRock challenged us to write an eleven word blog post and I couldn’t resist resurrecting an old theme.  John Dunleavy (who plagiarized my posts last year) emailed me a while back to say he’d like to meet me at the DevConnections conference in Orlando and buy me a drink to apologize.  I really admired his guts.  It takes huge drawers to make that kind of request, and I respect that.  We went out to dinner and had a wonderful time.

A wise man once told me that carrying a grudge is like swallowing poison and hoping the other guy dies.  Another wise man told me that DevConnections feels like the PASS Summit, only without the backstage drama.  Life is short enough as it is.  We can’t succeed by forcing others around us to fail – and in fact, it’s often the opposite.  I feel most successful when those around me succeed.  I don’t want any event, blogger, presenter, or consultant to fail.  I want us all to find our niche, our moral compass, and our happiness.

Life is a never-ending journey of learning our own lessons and helping those around us learn theirs.  If people had given up on me when I made my first mistakes, I’d be homeless right now.  I’m only here because my bosses and my peers saw enough value in me to be patient with my problems and help me get better.  Every time that I can pay that forward is a success.

What can you do to mend a fence today and help someone become a success?

Before I start with this sordid tale of low scalability, I want to thank the guys at Phusion for openly discussing the challenges they’re having with Union Station.  They deserve applause and hugs for being transparent with their users.

Today, they wrote about their scaling issues.  That article deserves a good read, but I’m going to cherry-pick a few sentences out for closer examination.

“Traditional RDBMSes are very hard to write-scale across multiple servers and typically require sharding at the application level.”

They’re completely right here – scaling out writes is indeed hard, especially without a dedicated database administrator.  Most RDBMSes have replication methods that allow multiple masters to write simultaneously, but these approaches don’t fare well with schemaless databases like Phusion wanted.  SQL Server’s replication tools haven’t served me well when I’ve had to undergo frequent schema changes.  Some readers will argue that the right answer is to pick a schema and run with it, but let’s set that aside for now.  Phusion chose MongoDB for its ease of scale in these situations.

“In extreme scenarios our cluster would end up looking like this.”

Their final goal was to have three MongoDB shards.  Unfortunately, they didn’t understand that the Internet is an extreme scenario.  If your beta isn’t private, you can’t scale with good intentions.  Instead, they went live with:

“We started with a single dedicated server with an 8-core Intel i7 CPU, 8 GB of RAM, 2×750 GB harddisks in RAID-1 configuration and a 100 Mbit network connection. This server hosted all components in the above picture on the same machine. We explicitly chose not to use several smaller, virtualized machines in the beginning of the beta period for efficiency reasons: our experience with virtualization is that they impose significant overhead, especially in the area of disk I/O.”

The holy trinity of CPU, memory, and storage can be tough to balance.  For a new database server running a new application, which one needs the most?  In the immortal words of Wesley Snipes, always bet on black memory.  A lot of memory can make up for slow storage, but a lot of slow storage can’t make up for insufficient memory.  When running your database server – even in a private beta – 750GB SATA drives in a mirrored pair is not web scale.

“Update: we didn’t plan on running on a single server forever. The plan was to run on a single server for a week or two, see whether people are interested in Union Station, and if so add more servers for high availability etc. That’s why we launched it as a beta and not as a final.”

Unfortunately, if you go live with just one database server and all your eggs in two SATA drives, you’re going to have a really tough time catching up.  As Phusion discovered, it’s really hard to get a lot of data off SATA drives quickly, especially when there’s a database involved.  They ran into a few issues while trying to bring more servers into the mix.  They would have been better served by starting with three virtual servers, each running a shard, and then separating those virtual machines onto different hosts (and different storage) as they grew.  Instead, when faced with getting over 100GB of user data within 12 hours, they:

“During the afternoon we started ordering 2 additional servers with 24 GB RAM and 2×1500 GB hard disks each, which were provisioned within several hours.”

Now things really start to go wrong.  They’ve gotten 100GB of user data within 12 hours, and they’re moving to a series of boxes with more SATA drives.  They still only used two slow SATA drives, but don’t worry, they had a plan for better performance:

“We setup these new harddisks in RAID-0 instead of RAID-1 this time for better write performance…”

Whoa.  For the part-time DBAs in the crowd, RAID 0 has absolutely zero redundancy – if you lose any one drive, you lose all the data in that array.  Now we’re talking about some serious configuration mistakes based on some very short-term bets.  Phusion had been overwhelmed with the popularity of the new application, and decided to put this “beta” app on servers with no protection whatsoever – presumably to save money.  This makes their scaling challenge even worse, because sooner or later they’ll need to migrate all of this data again to yet another set of servers (this time with some redundancy like RAID 10).  Two SATA drives, even in RAID 0, simply can’t handle serious IO throughput.

“RAID-0 does mean that if one disk fails we lose pretty much all data. We take care of this by making separate backups.”

Pretty much?  No, all data is gone, period.  When they say they’ll make separate backups, I have a hard time taking this seriously when their IO subsystems can’t even handle the load of the existing end user writes, let alone a separate set of processes reading from those servers.

“And of course RAID-0 is not a silver bullet for increasing disk speed but it does help a little, and all tweaks and optimizations add up.”

Even better, try just getting enough hard drives in the first place.  If two SATA drives can’t keep up in one server, then more servers with two SATA drives aren’t going to cut it, especially since you have to get the data off the existing box.  I can’t imagine deploying a web-facing database server with less than 6 drives in a RAID 10 array.  You have to scale exponentially, not in small leaps.

“We’ve learned not to underestimate the amount of activity our users generate.”

I just don’t believe they’ve learned that lesson when they continue to grow in 2-drive increments, and even worse, in RAID 0.  I wish these guys the best of luck, but they need to take radical steps quickly.  Consider implementing local mirrored SSDs in each server like Fusion-IO or OCZ PCI Express drives.  That would help rapidly get the data off the existing dangerous RAID 0 arrays and give them the throughput they need to focus on users and code, not a pyramid of precarious SATA drives.

(Jumping on a plane for a few hours, but had to let this post out.  Will moderate comments when I get back around 5PM Eastern.)

Next week, Microsoft MVPs from around the world will be gathering in Redmond at the Microsoft campus. This will be my second MVP Summit, and I wanted to give you a peek into what happens at this private event.  Next week, you’ll probably be hearing people tweet about how excited (or angry) they are, and they can’t tell you exactly what they’re talking about.  It’s time for you to learn why.

What are Microsoft MVPs?

The Microsoft Most Valued Professional award recognizes…well, we’re not exactly sure what they recognize.  The public PR page says the award is given to the “best and brightest from technology communities around the world,” but the criteria for selection is shrouded in privacy.  The page on Becoming an MVP hints that there’s a panel of people who judge “the quality, quantity, and level of impact of the MVP nominee’s contributions” to the community.  (Notice that they’re judging the contributions, not the person – I’ve known a couple of total jerkwads in the MVP program.)  Every year, the same panel appears to re-judge you on the same criteria, and every year, some MVPs lose their standing due to inactivity in the community.

Your first official act as an MVP is to accept a non-disclosure agreement (NDA).  Microsoft is about to give you some insider access, but in exchange for that, you have to agree to keep your yapper shut.  Microsoft can’t have future product plans or weaknesses made public.  This is an interesting challenge – they’re taking the most publicly active people, and telling them to keep quiet about something.  I can see why every now and then, Microsoft has to revoke an MVP’s status due to NDA violations.

What Kinds of NDA Things Do MVPs Learn?

Honestly, not much.  I’ve heard from very long-standing MVPs that the program used to allow much more insider access to Microsoft employees and future product plans.  These days, that’s not really the case.  As David Woods wrote when he quit the MVP program, some of the NDA’d Microsoft material is basic marketing content for future product versions.  Microsoft wants to get us psyched up about upcoming features because we’re active in the community, and we might get the community excited too.

When there’s a major new version of something coming, Microsoft lets MVPs get varying levels of access to it.  All MVPs might get early access to a community preview build, and some specialist MVPs might get even earlier access to builds that aren’t ready for general MVP consumption.  It’s a blessing and a curse – we get access to software that isn’t ready for the public yet.  We can’t run it in public, and we can’t rely on it for production.  The more we use it, though, the more we can help Microsoft do their testing for free.  To non-geeks, that would sound like masochism, but if you truly love the product you’re working with, it’s great.  I’ll take a buggy future version of SQL Server any day.

Content authors (bloggers, writers, presenters) take these preview versions and build content ahead of time.  This is how book authors are able to get new versions of their books published relatively soon after the product goes live.  They’ve been playing with pre-release versions, writing, taking screenshots.  This is also how I was able to publish my Denali high-availability blog post and my Microsoft Atlanta analysis within seconds of Microsoft announcements of those tools at the PASS Summit, and I couldn’t provide that kind of SQL Server journalism without the MVP program.  I liken it to how Engadget posts in-depth phone reviews within minutes of a phone’s release.

So How Can You Write About the MVP Program?

Very carefully.

I’m trying not to tell you anything you can’t figure out on your own, and yet give you enough of a taste that you’ll see the benefits of being an MVP.  The NDA stuff is only a small piece – another benefit is the MVP email list.  I enjoy lurking to watch conversations between smart, passionate people.  (The MCM email list is even better.)  A valuable benefit is free software licensing for not just Microsoft tools, but all kinds of stuff from software vendors like VMware, TechSmith, and every SQL Server tool vendor.  Anything we want is basically free for our own personal use.  And then there’s the free MVP Summit…

What is the Microsoft MVP Summit?

Once a year, MVPs from around the world are invited to Redmond to hang out.  Microsoft picks up the hotel tab as long as you share a room with another MVP, but you’re responsible for the airfare.  MVPs who work for a typical company usually don’t have to take vacation time, because companies see the value in having their staff get inside information from Microsoft.  Us consultants aren’t so lucky – the time I spend in Redmond is unbilled, so I lose money for the week.

During the week, Microsoft puts on sessions.  It’s like a conference, but with all Microsoft speakers.  Most of the Microsoft speakers aren’t professional trainers – they’re software developers – so they don’t specialize in entertaining the audience with lolcats slides, nor do they know how to handle hecklers well.  They do their best to show us something pretty technical. When the audience doesn’t like the feature or senses too much marketing, the audience reacts – big time.

Remember who the audience is – they’re the most active community professionals.  These people are opinionated loudmouths, myself included.  When we don’t like a feature, we don’t just raise our hand – we throw things and yell, because we passionately love our favorite Microsoft product, and we don’t want to see it take a wrong turn.  In an audience full of SQL Server professionals, it’s very hard to build a consensus on anything.  Ask three DBAs a question, and you’ll get four different answers.  The MVP sessions are like that.  It’s good for entertainment, and the fun gets even better when the sessions stop and the drinks start.

My favorite part of being an MVP isn’t the sessions or the email list or the free software – it’s the chance to spend quality time with other SQL Server professionals and the cool people at Microsoft building things I like to use.

So Why Do People Quit the MVP Program?

Over the last year, I’ve read a couple of blog posts from people who quit the program, and I’ve had the same reaction every time: “This guy couldn’t let go and enjoy the program for what it is.”

When Microsoft recognizes you with the MVP award, they’re recognizing you for the things you’re already doing.  Don’t change yourself, your blog, your presentations, etc in order to keep the MVP award.  I’ve never heard someone from Microsoft say, “Listen, you were doing good before, but now that you’re an MVP, we need you to ____.”  Microsoft just pats you on the back, hands you a crappy glass trophy, and starts giving you cool stuff.  You’re an MVP – but you’re not Steve Ballmer’s boss.

If you want to really influence Microsoft product direction, go to work there – and even that isn’t enough.  You have to work your way into a position of influence.  Microsoft’s chock full of smart, opinionated people who all want to drive programs in their own favorite direction.

If you want to help the community, just do it.  Helping the community is rewarding in and of itself.  If the MVP program folded tomorrow, or if they just kicked me out, I wouldn’t change anything.  To me, that’s the mark of a true MVP – somebody who loves helping the community just because.