Blog

Identifying and Correcting SQL Server Implicit Conversion

When you build a database in SQL Server, you need to pick a data type for each field in each table. Hopefully, a great database designer took the time to carefully understand business needs and what the field would be used for, and assigned the correct data type. When that doesn’t happen, you may run into problems down the road.

What is Implicit Conversion, and Why is it Bad For Me?

When SQL Server tries to join on or compare fields of different data types, if they are not the same data type, it will convert one to match the other. This is called implicit conversion.

Implicit conversion is not a desired action in SQL Server, and can lead to excessive CPU use. In some cases, converting from one type to another can cause a loss of precision.   A chart showing all the data types and their conversions can be found at http://msdn.microsoft.com/en-us/library/ms187928.aspx.

The most common places you will see implicit conversions happening is in the FROM and WHERE clauses.

Implicit Conversion in the FROM Clause

Joining two tables on a field that has a different data type in each table will cause SQL Server to convert one type to the other.

Let’s look at an example, using two tables, ProductCategory and Product.


CREATE TABLE ProductCategory
(
     CategoryID INT Primary Key,
     CategoryName VARCHAR(50)
);
CREATE TABLE Product
(
     ProductID INT Primary Key,
     ProductName VARCHAR(100),
     QtyInStock INT,
     CategoryID VARCHAR(10)
) ;

I’m going to insert data into the tables. Note that I am using the syntax to insert multiple rows into a table at one time, which is only available in SQL Server 2008 and higher.

INSERT INTO ProductCategory (CategoryID, CategoryName)
VALUES (1, 'Ale'),
(2, 'Lager'),
(3, 'N/A');

INSERT INTO Product (ProductID, ProductName, QtyInStock, CategoryID)
VALUES (1, 'Spotted Cow', 8966, '1'),
(2, 'Fat Squirrel', 7643, '1'),
(3, 'Moon Man', 339, '1'),
(4, 'Two Women', 1224, '2'),
(5, 'Home Town Blonde', 564, '2'),
(6, 'Ginger Ale', 899, '3');

This query will join the two tables on the CategoryID field.

DECLARE @Category INT
SELECT @Category = 2

SELECT PC.CategoryName,
     P.ProductID,
     P.ProductName
FROM ProductCategory PC
     INNER JOIN Product P ON P.CategoryID = PC.CategoryID
WHERE PC.CategoryID = @Category;

Reviewing the query execution plan shows CONVERT_IMPLICIT on Product.CategoryID.

Using a CAST in the FROM clause will remove this from the query plan.

SELECT PC.CategoryName,
     P.ProductID,
     P.ProductName
FROM ProductCategory PC
     INNER JOIN Product P ON P.CategoryID  = CAST(PC.CategoryID AS INT)
WHERE PC.CategoryID = @Category;

Implicit Conversion in the WHERE Clause

In this query, my variable is an INT and I am comparing it to a VARCHAR(10).

DECLARE @Category INT
SELECT @Category = 1

SELECT P.ProductName,
     P.QtyInStock
FROM Product P
WHERE P.CategoryID = @Category;

This also results in a CONVERT_IMPLICIT being performed.

Modifying the variable to be of the same data type as the field, a VARCHAR(10), resolves this problem.

DECLARE @Category VARCHAR(10)
SELECT @Category = '1'

SELECT P.ProductName,
     P.QtyInStock
FROM Product P
WHERE P.CategoryID = @Category;

Think Like the Optimizer

To get the best performance from your queries, you need to understand how the query optimizer works, and think like it. Preventing implicit conversion, and the resulting CPU usage, begins with proper database design. If you run into it after the database has been created, knowing how to spot it and work with it will result in a better-performing database.

We’re an Amazon Consulting Partner

I’m really excited to share the news that Brent Ozar Unlimited® is now an Amazon Consulting Partner. The Amazon Consulting Partner program is a way for Amazon Web Services to identify consulting firms specializing in helping customers design, build, and deploy solutions in Amazon Web Services. Cloud computing is some seriously cool stuff and we’re excited to be a part of it.

What does it mean now that we’re an Amazon Consulting Partner? It means that you have someone you trust when it comes to building solutions in AWS. We’re excited about SQL Server, we’re excited about AWS, and we’re really excited about combining the two to help you solve your problems.

The easiest way to tell you how we can help is to highlight some things we’ve already done. We’ve helped clients migrate seamlessly to AWS with zero downtime, tune customers’ SQL Servers in AWS, and designed highly available SQL Server architectures. Building fast, resilient SQL Servers in AWS is a lot of fun.

We’re excited about becoming an Amazon Standard Consulting Partner because this isn’t just a partnership agreement. To become a part of the Amazon Partner Network companies have to demonstrate clear wins in AWS – either by deploying products or successful customer implementations. We’re a part of the Amazon Partner Network because of the success of our friends and clients.

This doesn’t mean things are going to change; we’re still the same goofy bunch of SQL Server consultants. It does mean that when you have questions around SQL Server in AWS, or even questions around a new deployment in AWS, that there’s someone you know and trust ready to back you up.

Learn more about our SQL Server in Amazon cloud resources page.

Before You Fail Over a SQL Server AlwaysOn Availability Group

Think fast!  When it’s time to do a manual failover with SQL Server 2012 AlwaysOn Availability Groups, we need to make a few decisions and perform a few tasks as quickly as possible to get the business back up and running.  Here’s what you need to consider ahead of time so you can stay calm, cool, and collected – even if you have to trust someone else to do this work.

How much data will we lose?  If you’re failing over to an asynchronous replica, you’re probably going to lose data.  Build a set of queries that show exactly how old the replica’s data is – but here’s the catch: you can’t rely on being able to query the primary replica.  Instead, look at timestamp-based tables.  For example, if you’ve got a Sales table with a SalesDateTime field, run this query on the replica:

SELECT TOP 10 * FROM dbo.Sales ORDER BY SalesDateTime DESC

Time to get the backup car.

If the newest sales are 20 minutes old, you can tell management that they stand to lose up to 20 minutes of data if we fail over now.  You may still be able to recover the data from the primary – more on that later.  The amount of potential data loss helps you with the rest of the decisions you’re about to make.

How long will we troubleshoot?  If we stand to lose 20 minutes of data, then it helps to be able to tell an executive, “We can either go live with our DR replica right now and lose 20 minutes of data, or I can spend 30 minutes troubleshooting to find out how bad the situation is.  What would you like to do?”  Put that decision in management hands as fast as you can – preferably after you’ve done just five minutes of troubleshooting.  Within five minutes, you should know if it’s an easy problem or a challenging one, and you want to let management decide when the outage should end.

Who’s allowed to make the go/no-go decision?  If the DBA, dev manager, or IT manager aren’t around, who’s responsible for the call to fail over to the asynch replica and lose data?  Ideally, we’ve got as large of a list as possible here so that the company can react fast without waiting around for a long phone tree.  This list of people must be firmed up ahead of time.

Who’s allowed to perform the failover?  AlwaysOn Availability Group failover can be scripted out with PowerShell and T-SQL, and these scripts can be given to a 24×7 help desk rotation.  I know, as a DBA, it can be scary to hand out this kind of permissions, but I’d rather empower someone else – when armed with a manager who can make go/no-go decisions – so that the company can be back up and running faster.

Once failover starts, who’s got quorum votes?  If groups of servers, or heaven forbid, an entire datacenter is offline, failover starts to get more complex.  The team will need to force the quorum online without enough votes, and then reassign voting rights using PowerShell or cluster.exe.  We’ll probably need to change the quorum model as well, perhaps going from node majority to node-and-file-share majority.  These are complex topics that should be thought-through ahead of time rather than busting out Books Online.

Do jobs need to be enabled/disabled?  If we’ve turned a secondary replica into a primary, how does this affect our SQL Server Agent jobs that were running on the old replica?  Do we need to change any of our backup jobs?  In theory, the built-in backup preferences settings for AlwaysOn Availability Groups will cover this, but complex replica scenarios may have multiple backup jobs to maintain both onsite and offsite backups.  We might also have aggregation jobs or ISV jobs that must always run on the primary, and we need to ensure those get enabled.

Who will troubleshoot connectivity problems?  When there’s been a massive failure, I like having an open conference bridge with all hands on deck – but not necessarily all hands should focus on the call.  For example, in multi-DBA teams, I prefer to designate just one DBA team member as the primary point of contact on the call.  They handle all questions from developers and end users while the other DBAs focus on making sure the replica stays healthy.

What’s our backup plan? No, I mean how do we back up?

What do we do if we have multiple primary replicas?  In a split-brain cluster scenario, we can have two database servers that both think they’re the primary replica for the same database.  We need a written checklist that we can give to someone who will drive to the not-supposed-to-be-primary datacenter, shut the servers down, and stay there until the network comes back online to bring the server up gracefully.

Will we try to recover the lost data?  When the formerly-primary server comes back online, the databases will still be readable.  We can use data comparison tools to compare the two replicas and generate insert/update/delete scripts to bring the data back into sync – in theory.  In practice, identity fields can make this impractical or impossible.  I’d start by comparing timestamps in a few key tables just to see how much data is at stake, and then give management a rough time estimate on what it’d take to recover that data.  In order to give a good estimate, you’ll want to try this approach ahead of time (long before an outage) using a pair of development or QA database servers.  Get a feeling for how the tables are related and how much work would be involved to sync them.

There’s a lot of questions here.  When your team is armed with the answers ahead of time, it’ll make failovers much less painful, and you’ll look like the smartest, fastest-reacting ninjas in the company.

The Not-So-Secret SQL Server Agent – Free Video

Automating SQL Server tasks will make your job easier and make you more productive. SQL Server has a built-in automation tool: SQL Server Agent. Join us for this 30-minute webcast where I show you how to set up secret weapons like job steps, schedules, and notifications.

How to Pick a SQL Server Conference

As a consultant and speaker, I’m lucky enough to go to a crazy number of SQL Server conferences. Here’s what I think of each mainstream conference out there – who they’re for, and who they’re not for.

PASS Summit

  • Length: 3 days
  • Pre-Cons Available: Yes, many
  • Videos Available Online: Yes, but only for paid attendees
  • Tracks: SQL Server only
  • Typical Location: Seattle, WA

Just one of the PASS Summit after-hours options

For Attendees: The Summit is a mid-size (thousands of people) conference dedicated to all SQL Server, nothin’ but SQL Server. A dozen or more sessions run simultaneously at all times, so no matter what niche topic you’re interested in, somebody’s running a presentation about it.  The “somebody” is a key here – the presenters are from all walks of the SQL Server life, some professional presenters and some people presenting in front of a crowd for their very first time.  Quality is all over the map here, but the community is forgiving: this feels like a big family event.  Twitter is lively with attendee chat during the day, and the fun continues after hours with several big parties to choose from on any given night.

For Speakers: The conference doesn’t pay any expenses or speaking fees, but competition’s still tough for slots here just because so many community members are encouraged to submit abstracts.  Along with the lack of payment comes a lack of work, though – presenters aren’t required to spend time in various booths or attend a bunch of pre-conference meetings.  PASS seems beholden to Microsoft to keep this event near Seattle as often as they can, and like TechEd, this doesn’t make for good vacation time.  I’m sick and tired of rainy, gloomy, Novembers in Seattle, but I put up with it because it’s like a family reunion.

Microsoft TechEd North America And Europe

  • Length: 4 days
  • Pre-Cons Available: Yes, but only one for SQL Server
  • Videos Available Online: Yes, free
  • Tracks: SQL Server, Sharepoint, Windows, Development, Cloud, Exchange, basically anything Microsoft
  • Locations: various US and EU cities

TechEd Europe Keynote

For Attendees: This large (~10,000 person) conference has multiple tracks for almost all Microsoft technologies. If you wear multiple hats like developer AND database pro, sysadmin AND accidental DBA, or DBA and SharePoint admin, then this is the biggest conference for you. Biggest doesn’t necessarily mean best, though: I sat through a few painful vendor sessions and Microsoft marketing sessions. (The keynotes are simply awful.) Microsoft and the sponsors clearly run this event, and it shows – they’re pushing their own messages here. This is the conference to hit during release years because tons of Microsoft staff are readily available to answer detailed questions about new products. The vendor expo is huuuuuge, too, so if you need to compare multiple vendor products quickly, this is the place to go.  If you can’t go, though, you can watch any of the session videos for free.

For Speakers: Microsoft pays for speaker airfare, hotel, and a speaking fee, and there’s some prestige involved with speaking at Microsoft’s own event, so competition is extremely fierce for speaking slots. However, because the conference is run by Microsoft, the competition doesn’t necessarily mean the sessions are the best. Sessions have to conform to Microsoft’s speaking (marketing) standards, and presentations are vetted by Microsoft employees. You won’t see a lot of anti-Microsoft sentiment in the slide decks here. Many attendees even believe that all presenters are Microsoft staff, so I start my sessions by explaining that I’m an independent consultant.  Speaking at this conference involves a lot of meetings, red tape, and mandatory duties at various get-togethers and booths.  Worse, the conference isn’t always when and where you want to be – New Orleans in June is not my idea of a good time.

DevINTERSECTION and SQLINTERSECTION

Connections in Vegas. Because, y’know, Vegas.

  • Length: 3 days
  • Pre-cons Available: Yes, multiple dedicated to SQL Server
  • Videos Available Online: No
  • Tracks: SQL Server, Sharepoint, Windows, Development, Cloud, Exchange
  • Locations: Twice a year in Las Vegas

For Attendees: This small conference run by Paul Randal and Kimberly Tripp (SQLskills) feels very much like a professional training event. There’s just a few simultaneous sessions per track, but they’re absolutely top notch sessions. Even though there’s only a few sessions, they’re ALL sessions I want to attend – really good expert-level speakers that do a lot of presenting for a living. There’s no filler here.

For Speakers: The conference pays for speaker airfare, hotel, and a speaking fee, so competition’s pretty tough for the few speaking slots. As a result, the sessions tend to be more curated and fairly high quality. This is my favorite conference as a speaker because the organizers work hard to make it as easy as possible for you.  I get one email with my contracts and travel details, and that’s it.  I show up onsite, pick up my backpack with my logistics stuff, and I get to work.

DevConnections and SQLConnections

Connections in Vegas. Because, y’know, Vegas.

  • Length: 3 days, one of which is dedicated to Microsoft presenters
  • Pre-cons Available: Yes, usually one SQL Server pre-con per day
  • Videos Available Online: No
  • Tracks: SQL Server, Sharepoint, Windows, Development, Cloud, Exchange
  • Locations: Twice a year in Las Vegas

For Attendees: Like DevIntersection, this is a small conference focused on training. It used to be run by Paul Randal and Kimberly Tripp (who now run Intersection), so it’s easy to get confused as to which one is which. Connections is now mostly community-driven, though. Between Intersection and Connections, I’d pick the former. (And I do as a speaker.)

Microsoft MVP Summit

Jeremiah acting out SQL Server through interpretive dance at the MVP Summit

  • Length: 2-4 days depending on your interests
  • Pre-Cons Available: No
  • Videos Available Online: No
  • Tracks: All Microsoft technologies
  • Location: Microsoft campus in the Seattle area

For Attendees: This invite-only conference is the biggest benefit to being a Microsoft MVP.  Microsoft trots out developers and managers to talk about what they’re working on.  MVPs get to interact with the people building their favorite products, plus interact with other MVPs – and that’s a killer benefit by itself.

I include this conference in my list just because it’s an event away from home that I need to schedule time for, so it’s part of my decision-making process.

SQLBits

  • Length: 1 pre-con day, 1 deep dive day, 1 free day
  • Pre-Cons Available: Yes, several
  • Videos Available Online: Yes, but only for the free days
  • Tracks: SQL Server only
  • Location: Moves around the UK

For Attendees: Maybe it’s just because of their accents, but I think the presenters at SQLbits are some of the best ones out there.  I’ve been consistently impressed by the SQLbits sessions I’ve seen, and the sense of community at this event is just outstanding.  It’s held in fun places at fun times of the year.  Even the format is unique: if you just attend the free day, it’s much like a SQLSaturday, but if your budget allows then you can get much more in-depth training too.

For Speakers: SQLbits does pay presenters for doing pre-cons, but not for the free days.  It has an interesting mix between a professional-feeling conference and a community one, and I think it strikes a really good balance.  If you can get a pre-conference session approved, then this conference can pay for itself – they offer some of the best revenue sharing of any of the conferences.

SQLCruise

  • Length: One week
  • Pre-Cons Available: No
  • Videos Available Online: No
  • Tracks: SQL Server only
  • Location: Cruise ships, typically out of Florida

SQLCruise #1

For Attendees: This very intimate (15-attendee) conference is a chance to build close personal relationships.  Yes, there’s SQL Server training, but the really valuable part is the one-on-one time you get to spend with MCMs, MVPs, and experts.  There are much less presenters (and attendees) than any other conference, but you get tons of time with them.  At larger conferences like the PASS Summit, you’re not likely to get hours of time to have drinks and talk directly to the presenters – there’s just too much of a crowd at any given time.

For Speakers: Presenting on a cruise ship comes with some drawbacks.  Attendees expect plenty of time to build those relationships, which means you’re now required to attend every single session, group event, party, dinner, etc – and there’s a lot of ‘em.  The cruise room is paid for, but the airfare and pre-cruise hotel is not.  If you see this as an easy vacation, you’re going to be in for a shock – I found it to be more work than any other conference.  The benefit is that the attendees are sincerely thankful for the work you put into making the event a success.

How I Choose Which Conferences to Attend

If you’re an attendee, I’d recommend basing your decision on:

  • Is it more important to you to learn, or to build relationships? (And are you sure?  You can watch a lot of the conference videos for free online right now – so what’s holding you back?)
  • Is it important to you to have conversations with Microsoft employees?
  • Is it important to you to meet peers who have similar problems?
  • Do you want a conference located somewhere that you can do sightseeing before and after?

When I’m an attendee, the most valuable things are conversations and relationships.  I want to be able to talk to experts who have solved the same problems I’m facing, hear how they worked past it, and get their contact information so I can ask them questions later.  That’s why I prefer conferences with plenty of time for side talks – if everyone’s rushing from session to session, I don’t get as much value as an attendee.

I try to budget some money for lunch and dinner because food at these conferences is frankly horrendous.  The presenters don’t want to eat that stuff either, so when I needed help, I tempted presenters by saying, “Hey, can I take you out to lunch or dinner and pick your brain about something?”  You’d be surprised by how often that works.

Me at the PASS Summit Bloggers’ Table

I also care about the conference’s tracks.  I have to do more than just SQL Server, so sometimes I need to talk to experts from other fields.

This is going to sound crazy, but I don’t really care about the sessions or the keynotes.  If I want sessions, I can watch free videos from SQLbits or TechEd anytime I want.  I don’t have to wait for the conference to fix problems I’m having right now.  I also don’t care about product announcements because Microsoft doesn’t really do them at conferences anymore.  Take the recent Surface announcement: we had TechEd North America one week, then the Surface announcement the next week, then TechEd Europe the week after – but not a single bit of information to be had about Surface at either TechEd conference.  Given that the audience consisted of developers, and that developer support is absolutely critical for launching a new tablet, I don’t get why Microsoft would ignore these conferences – but they do.  If tech news is important to you, get it from blogs and news sites rather than conferences, because the Microsoft conference scene has stale news.  (That’s not to say these conferences don’t have good information about new features – you just won’t find it at the keynote sessions, which have turned into a boring rehash of old news and marketing demos.)

If you’re a presenter, here’s what I think about when choosing my conference calendar:

  • Where I can help the most people
  • How I can bring in business in the future
  • How I can minimize expenses (after all, as a consultant, I’m not getting paid when I do this stuff)

Each conference has its own pros and cons, and it sucks to decide, because I wish I could attend conferences all year!  I have to cut back to honor my family commitments, though.

The Presenter’s Bill of Rights

Bad news: you don’t have any.

You THINK you have the right to all of this stuff:

  • A working screen for a projector (as opposed to one that’s hung up in the ceiling and won’t come down)
  • A working projector
  • A functioning bulb in the projector
  • A stand for the projector within cord’s reach of the podium
  • A cord for the projector with inputs that match your laptop’s outputs
  • A working laptop (and by that I mean your own)
  • A laptop that doesn’t crash mid-session
  • A remote pointer with a laser
  • Charged batteries for the remote pointer
  • A current version of your presentation
  • An electric outlet near the podium
  • A podium
  • A whiteboard
  • Markers that aren’t permanent
  • An eraser
  • A comfortable room with working heating & air conditioning
  • A lack of noise from outside your room
  • In big rooms, a working microphone and speakers
  • An accurate introduction
  • Attendees who show up on time and don’t talk over you
  • Attendees

Attendees have the right to be distracted by my shirt.
Photo by Michael Kappel

But the reality is you’re not entitled to any of that stuff.  Oh, sure, if you’re lucky you might have some of it – but after speaking all over the world, I can guarantee that you’ll probably never have all of it.

If you blame the event organizers, you’re doing it wrong.  It’s your responsibility as a presenter to have backup plans.  You’d better not pack it up like a diva if the projector doesn’t work.  You’d better not throw a hissy fit if your opening act botches your name or your company.  Your heart rate better not rise if there’s no working electric outlet for your laptop.  You have to own your success as a presenter, and the very first part of that is to put the audience and the organizer at ease.

When – not if – you don’t have something you expected at the event, remember the real presenter’s bill of rights.

You have the right to get bad surprises,
and the obligation to deliver great surprises.

Architecting with AWS – Course Overview

The Friday before class started I received an email from people running the Architecting with AWS training class about how to get to the class, what time class got started, a schedule of topics, and a link to prerequisites that I should go through before arriving at class on Monday morning. Most training classes I’ve attended haven’t done anything like this, so I was anxious to get started with the prerequisites. The homeworks was simple: they walked attendees through setting up an AWS account, sign up for the necessary AWS services, and locate AWS account information. All in all, I was pretty impressed with how things started – a lot of the set up for AWS can be overwhelming the first time through and the prerequisite homework would have been very helpful when I first got started.

Day One: The Big Picture

On the first day of class, I grabbed a cup of coffee and headed over to the training class. I attended the class in Seattle. It turns out that Seattle is a great place to attend the Architecting with AWS training: the class is held in the same building that houses the AWS team. At several times during the class we had guest appearances from AWS staffers who gave short training sessions on different technologies. A few of the AWS staffers also hung out in the back of the classroom to help answer questions.

Class began with a round of introductions. The instructor, Evan Brown, lead off and discussed his experience with both AWS and the course material. What surprised me the most was the makeup of the attendees. There were people from large and small companies across many different industries. I had expected that the focus would be on smaller companies or consultants, but I was wrong. There were new hires at Amazon getting brought up to speed on the technical side, pure R&D teams, consultants, and people from some very traditional companies.

Training started immediately after the round of introductions. We started with a discussion of the course layout and then dove into our first sample architecture: a scalable web application. The sample application was presented as a set of business requirements and a loose set of SLAs. After going over the general application, Evan led a white board discussion of different features and functionality from AWS that could be used to build the application.

Architects have to deal with the big picture. Depending on your specialization, this can be a database, network, or application stack. In AWS, the architect’s domain is everything that lives in or touches AWS – from software to storage to security. Needless to say, there was a lot of material to cover. We spent an hour or so discussing the core features of AWS – Amazon Machine Images (AMIs), RDS (database as a service), load balancing, and the different ways of storing files in AWS (S3, EBS, and ephemeral storage). Even though I’ve been using AWS for a while, this was a very helpful part of the course. It’s important to understand the different services that AWS offers and how they fit together as part of a larger solution – there are many services in AWS and they accomplish a lot more than a casual observer might think.

One of the most interesting parts of the day was the discussion around security in AWS. I know that the cloud is secure, but I also know that I’m not a security expert. The security section of the course made it clear that it’s possible to create applications with very fine-grained security. Not only can users be granted specific security permissions but the security model extends to VMs, services, and even API calls. I’ll admit that security is well outside of my comfort zone, but I came out of this portion of the class knowing that I have a good starting point for communicating my security needs when I design solutions in AWS.

Days Two and Three: Application Services

The second day started off where we left off the day before. After discussing the IaaS platform of AWS we started talking about the different services that AWS provides on top of the infrastructure as a service platform. Combining the AWS services with different ways of running your application in the cloud gives architects and developers flexible ways to create very rich services. With a little bit of development and configuration it’s possible to create automatically scaling, self configuring application server tiers behind load balancers that respond to increases and decreases in customer demand. In short – you can make an army of evil server robots that will do your bidding.

Day two was entirely centered around application services. We worked on material to help build out our multi-tier web framework from day one and then switched gears to design a batch processing application. Although the requirements were very different – batch processing has doesn’t have nearly the interactive needs of a web application – we were able to build on the knowledge of AWS services that we gained early in the class. This brought up some great discussions over when an architect should choose one service (Simple Workflow Service) over another (Simple Queue Service) when the overarching idea (queue up work and then do it) might be exactly the same.

In case you didn’t notice the theme of the first day, a lot of the classroom time focused on discussion. While there was plenty of lecture time around the AWS services, there was also a ton of time devoted to sharing experiences and ideas with other attendees. This was, hands down, my favorite part of the class. The best part of classroom discussion is building on the knowledge of others to get a deeper understanding the problem domain as well as potential solutions. In such a big class it was only natural that there were a lot of technical problems spread across a wide range of potential problem domains.

The Bad Stuff

Every topic in class could have been a 5 day training class. There was simply too much material to cover in any depth, so I was left hungry for more. I have a laundry list of things to try from building simple CloudFormation templates to building rich self-deploying application stacks. There is a follow up 4 day class for operations staff that focuses entirely on the automation piece of AWS, but that still didn’t stop me from wanting more detail right away. There is a lot of AWS documentation available, but the downside is that there’s a lot of documentation available (the EC2 guide would be 700+ pages long if you printed it out).

I mentioned how much I enjoyed the in class discussions, and they were a critical part of the event, but they could also be a bit much. It only takes one person to dominate a conversation or a classroom and it did happen once or twice. The instructor did a good job of handling the discussion and pulling things back around to the class material, but if you’re really bothered by that sort of thing, be careful if you go to this class. The other downside of classroom discussions is that you can’t predict how long they will take. Just because the syllabus says there are 15 minutes for class discussion, that doesn’t mean anything – At the end of the first day we were over an hour behind schedule. We caught up by the end of the class, but discussions can drag things off in a different direction.

Homework? Only If You Want It

There wasn’t any homework. At the end of each day I went back to my hotel room and reviewed my notes and went over the presentation material. When I wasn’t clear on something, or just wanted to play around with spinning up a few EC2 instances, I was able to repeat the exercises in the course material and make sure that I fully understood them. Thankfully, Evan showed up to class early on days two and three so I was able to head in early and ask any questions that I had about the previous day’s lecture or exercises.

At the end of the three days, I wasn’t feeling exhausted but I did feel like I’d been working my brain. I knew more about the AWS ecosystem than I did going in and I felt reassured that other people are running into the same problems moving into AWS and are solving them in a bunch of different ways.

Video: Open Question and Answer Webcast

Parameter sniffing, clustered indexes, clusters in VMware – you asked it, we answered!  By popular demand, here’s our first open Q&A webcast where attendees could ask anything they wanted.  Here’s the results – 42 minutes of rapid fire answers:

If you enjoyed this webcast, sign up for more now.  We’re doing another open Q&A one on August 28th, too!

Architecting with AWS – Before You Go

Architecting with AWS is a three day training session held at the AWS offices in Seattle (classes are available in other locations). Both outsiders and AWS employees come together to learn about building scalable solutions on the AWS platform. This is a practical training program – there’s no certification behind it. The Architecting with AWS training is an introduction to the products and services in AWS and how they can be used to create and automate great software.

Although the $1500 price tag isn’t for the faint of heart, three days of high quality training with access to AWS employees is a great deal. Before deciding to go to the training, I looked around for other options. Architecting with AWS had a few things going for it – the class was in many locations, the cost was better than fair, and the timing was right. It doesn’t hurt, either, that the training was coming directly from Amazon employees in the Amazon Web Services offices.

Who Is This Class For?

Great training has an audience in mind: you can’t make anyone happy if you’re catering to everyone. Architecting with AWS is aimed squarely at the people designing solutions that will be hosted in AWS. While most IT staff will get something out of the course, it is specifically designed for software architects, developers, and operations staff. The tutorials and exercises focus on designing solutions to real world problems.

What Should I Know?

A lot of training classes have pre-requisites before you attend. The Microsoft Certified Master training notoriously listed several thousand pages of SQL Server books, white papers, and documentation that attendees should read. The Architecting with AWS class was slightly different. Instead of providing attendees with a list of reading material, the pre-requisites cover the key concepts that attendees should be familiar with. In order to be successful, having a basic understanding of the core services provided by AWS (EC2, S3, and RDS) is required.

Things change in AWS all the time – new features are released every week. There are a few blogs that I follow to help me keep up to date on what’s happening in AWS. The most important blog is the official Amazon Web Services blog. The AWS blog covers major goings on in the AWS space – new feature releases, tools, techniques, and it even covers developments from other companies who are using AWS to do big things. While you’re not going to learn everything you need to know from a few blog posts, you will be able to keep up to date on the newest features, find online training, and figure out which parts of the AWS world catch your interest.

Where Should I Go To Get Started?

If you’re already using AWS, you can skip this section. Seriously, just scroll down to the next H3 in your browser and you’ll be happy.

If you aren’t using AWS, or if you aren’t sure where to start, you’re not alone. A lot of people aren’t sure how to get started with AWS. My first suggestion is to sign up for an AWS account. Yes, you’ll need a credit card, but there is a free usage tier – as long as you don’t use too much. Attendees get a small credit to cover their utilization during class. You can start by creating EC2 instances, EBS volumes, S3 buckets, and storing data in RDS, or you can just play around with some of the other services that are available.

That’s about all there is to it. If you want a more guided approach than "go out and play", I suggest you check out Getting Started with AWS and What is the AWS Free Usage Tier? There’s a huge volume of documentation available from Amazon, so it can be daunting to get started. Don’t get distracted by all of the bright and shiny features floating around. Focus on the core services – EC2, S3, EBS, and RDS. These four services combined act a lot like your local servers, so once you get a better graps of how to set up your own servers in AWS, you can start working with the other services available.

What can I expect from the training?

You can expect your mind to be blown. While that’s a bit of an overstatement, you can expect to learn a lot in a short period of time.

Architecting with AWS is a three day tour of some of the functionality that’s available in AWS. The course was delivered in the context of a sample video encoding application. Rather than focus on delivering knowledge in the abstract, we examined practical scenarios. The class was very interactive – there were many opportunities for attendees to offer up their own opinions of how to solve progblems with AWS products and discuss the merits of each potential solution with the class and instructor. One of the advantages of this approach was interacting with people from a variety of industries. Attendees came from many industries – media, aerospace, pure R&D, consulting, and insurance companies were all represented. The discussions were one of the most rewarding parts of the class, we talked about a diverse set of problems and got to hear how different teams have solved the same problems. Working with my classmates was worth the cost of entry.

In addition to lectures and discussion, concepts were reinforced through hands on exercises. In many cases there were two exercises – one for Windows users and one for Linux/Unix users. This was one of the more interesting parts of the class and I tried to complete both sets of exercises so I could better understand the challenges that users face when they’re working with AWS. There is so much documentation around using Linux with AWS and it was refreshing to see some Windows specific exercises to help understand how automation could be accomplished on a different platform.

You can expect a very practical approach to solving infrastructure problems with AWS. Discussion and training materials focused on solving real world problems using the features and functionality of the AWS. Don’t expect to walk out of the class a master of everything that AWS has to offer, you’ll be disappointed. However, if you expect to learn a great deal about the features and functionality available, how to apply those features to your own problems, and how to keep your application performing well, even under heavy load, you’ll be very pleased with the results.

Your First Good Job Isn’t In the Newspaper

Right now is an incredible time to do what you love.

Bear with me for a second and watch this one-minute video from Red Bull:

It’s full of people doing what they love.  These people are at the top of their game, but more importantly, they’re sharing their game with others, inspiring and entertaining them.

The more you watch this video – and believe me, I’ve watched it dozens of times – you start to pick up on an incredible number of things Red Bull had to pay for.  Concert sponsorships.  Running contests and events.  A logo on a stadium roof.  Not to mention professional film crews taping the whole thing – all while people on stage get to do what they love.

But that only happens to rock stars and skateboarders, right?  Not to IT geeks.  Or does it?  Watch this one-minute video from Red Gate:

This was the introduction to the Red Gate DBA in Space contest.  Red Gate forked out big money for sets, a professional film crew, and an actress – all to hang out with SQL Server professional Brad McGehee to film what amounted to a huge commercial.

But that only happens to just a couple of people, right?  This isn’t a widespread phenomenon – or is it?

SQL Server Community Activists Turned Evangelists

Check out this timeline:

  • 2005: Quest hires Kevin Kline
  • 2006: Red Gate hires Steve Jones
  • 2007: Red Gate hires Brad McGehee
  • 2008: Quest hires Brent Ozar (yay!)
  • 2010: Confio hires Tom LaRock
  • 2010: SQL Sentry hires Aaron Bertrand
  • 2010: Quest hires Jeremiah Peschka
  • 2011: Red Gate hires Grant Fritchey
  • 2011: Idera starts ACE Program
  • 2012: Idera hires Robert Davis
  • 2012: SQL Sentry hires Kevin Kline

That’s a lot of activist activity over the last several years – and if you notice, it’s picking up speed.  Every time an evangelist position is filled, I used to say to myself, “Well, that’s it – now all the companies have evangelist-type positions.”  But no – scroll down the list of PASS Summit sponsors and exhibitors, and ask yourself how many of those folks have SQL Server evangelists.  There’s still a lot of potential open positions.

Why Companies are Hiring Evangelists

Ken Block is a professional rally driver who does amazing things with cars.  No, really, I mean amazing things – he’s the Evel Knievel of our generation, a master of the combination of entertainment and promotion.  Years ago, he brought in a professional film crew to capture how he likes to show off his car control, zipping around an abandoned airport.  The resulting four-minute video has over twelve million views, most of which involve jaws hanging wide open as Block does donuts around a moving Segway.

This month – one week ago today – Block published the latest installation in his series, Gymkhana Five, filmed in San Francisco.  If you don’t have the ten minutes to watch the whole thing, some of my favorite highlights are at 2:25 (doing donuts around moving streetcars), 3:40 (sailing over a hill), and 5:30 (drifting sideways through the air):

That video has received almost twenty million views in just a week.  That’s 33 views per second, around the clock.

That’s why companies are hiring evangelists.  Companies need to get their message out to the public, and conventional advertising isn’t working as well as it used to.  We’re bored.  We’re jaded.  We fast forward past the ads on our Tivo, and we use AdBlocker on Firefox.  We’re not paying attention.  Marketers know now that they have to try a new trick: they have to be part of the message.  Whether they’re logos on the side of Ken Block’s car on a YouTube video or getting DJ Audrey Napoleon to help encourage responsible drinking via short films, they’ve gotta merge their message with yours and find a way to get people to want to hear the message.

What Kinds of People Get These Great Gigs?

The best evangelists are excited experts: people who know how to do something well, absolutely love doing it, and love sharing their knowledge with others.  In Malcolm Gladwell’s book Outliers, he posited that in order to be an expert, you have to practice the task for 10,000 hours.  He uses examples from Bill Gates to the Beatles, showing where these people put in their hours and why it mattered when they hit the big time.

It’s really hard to do something for 10,000 hours.  Most of us spend most of our day in meetings and doing email, and I’ve got bad news: unless you’re aiming to become a professional meeting attendee expert, you’re not making progress at your real dream goals.  If you really wanna be an expert, you have to minimize that non-task time and try to put in as many hours as you can at your favorite task.

I bet somewhere along the line, a guidance counselor told you, “Do what you love and you’ll never work a day in your life.”  This matters for experts, too – it’s much easier to become an expert at a task if you find yourself putting in the hours even when you’re not getting paid.  When I wake up and walk the dog, for example, I’m on my iPhone digesting tech, storage, SQL, and virtualization news as fast as I can.  It’s not my job to keep up with this stuff – it’s my passion.  It doesn’t feel like work, so the expertise-accruing hours keep racking up faster and faster.

But if you’re not doing what you love, you have to take a dramatic step like Dan McLaughlin did.  Malcolm’s book affected Dan in a big way, and in April 2010, Dan quit his job.  He wanted to golf for a living, so he gave up photography and started putting in 30 hours per week golfing with an aim to make the PGA Tour in 2016.  He’s well on his way, and you can follow his progress at The Dan Plan.  (And you know what’s funny?  Even though he’s not an expert yet, he’s already making money doing what he loves simply by having this web site.  If he manages to make the PGA Tour – even if he sucks as a golfer – he’ll make a killing as an author and speaker just talking about how he changed his life.)

I’m not saying you have to quit your job today and go golfing.  I’m saying if you don’t absolutely love what you’re doing, and you don’t look for reasons to do it in your spare time, then you’re doomed to spend your entire life barely tolerating what you’re doing, and nobody’s going to hire you to become an evangelist for that.  However, you’re sitting here reading this blog because you at least vaguely enjoy technology – or else you wouldn’t have found me here.  Odds are, you kinda-sorta like a lot of your job, but there’s parts of it that you actively hate.

Your job is more than your job description – it also includes the environment where you’re working.  Are you passionate about sports?  Have you thought about applying to work in the IT team at your local sports franchise or fitness center?  These companies get genuinely excited when they find someone who not only knows the technology, but cares – really cares – about the company’s core business.  Old resume advice tells us to learn a little about the company we’re going to interview for, but I’m here to tell you that it doesn’t just matter to the company – it matters to your job satisfaction.  The more you really care about the work your company does, the more excited you’ll be to show up for work.  You have to seek out the companies where you’d be excited to work, and then you have to tell ‘em why you have the best combination of expertise and excitement.

Kevin Kline and Ken Block Didn’t Respond to Newspaper Ads

When you’re an excited expert, the right doors don’t always open automatically for you.  Yes, sometimes people come out of the door and try to frantically wave you inside, but those aren’t always the positions you want.  I see most recruiters the same way I see peddlers in tourist trap cities – standing outside the front door, waving people in, talking about the great deals they’re going to find inside.  Even when you’re an excited expert, you have to find the right doors for you, walk up to the door, knock, and if nobody answers, you have to try the knob anyway and introduce yourself to the startled occupants.  Nobody expects an excited expert to come knocking, looking for work – but once they get over the shock, they’re just as excited as you are, and they’ll make a position for you.

The bad news is that many of us are zombies, shuffling to work, expecting our managers to turn our workplace around, and when they don’t, we blindly follow these streetside peddlers into another crappy job in a crappy environment.

The good news – no, GREAT news – is that both your expertise and your excitement level are entirely up to you.

css.php