Episode 124: Beyond the runbook; DR Organization

Perhaps you have heard about a runbook–the documented instructions you should follow in the event of a disaster or some situation where an outage occurs.  Instructions are great for IT folks as they give a reference to follow–and they don’t freak out even if we might. They don’t, however, include instructions for everyone–what about the folks that aren’t tapping away on the keyboard?  In this episode, we chat with Greg Moore about his experience in emergency situations outside of IT and how we might apply these principles in our environment.

Whether you are directly in the line of fire or support those that do, we think you will find this conversation interesting.

Episode Quotes

“No battle plan survives first contact with the enemy.”

“When something bad really happens, the first thing you should do is sit down and make yourself a cup of tea. [It will] force you to slow down and stop and think about what’s going on.”

“One of the challenges that we as IT people have is that we tend to think that our way is the best way.”

“I’m a huge fan of people learning the how and the why behind things, not just what to do in X, but why are we doing X, why are we doing Y?”

Listen to Learn

01:34     Compañero Shout-Outs
03:27     SQL Server in the News
04:38     Intro to the guest and topic
06:49     The beginning of the caving example as an object lesson in IT disasters
10:37     Make sure we don’t make things worse
13:27     Recognize your limitations – know what help to offer and when to step back
16:03     What happens when you don’t trust your team – don’t be a micro-manager
18:48     Training helps in most situations but not always
25:03     Always ask “why”
26:40     Don’t be afraid to practice possible disaster scenarios – Jeff Bezos & Delta examples
32:39     How can we manage our managers
33:54     Ask questions to assess the situation before you charge in
36:35     SQL Family Questions
39:47     Closing Thoughts

About Greg Moore

Greg Moore is a graduate of RPI. There, he majored in CompSci, but probably spent as much time hiking, canoeing, caving and rock-climbing as he did studying. He started working with SQL Server 4.21a in 1995 and has survived numerous upgrades. He’s been a Director and later VP of IT at several startups including PowerOne Media, TownNews and Traffiq and now consults. These days, when he’s not busy with playing with SQL Server or spending time with his family, he can often be found underground caving or teaching cave rescue with the NCRC. While his focus is on the operations side of DBA, his interests include DR, performance and general IT problem solving. He is the author of: IT Disaster Response: Lessons Learned in the Field. You can find it here: https://smile.amazon.com/dp/1484221834/

 

FEMA’s ICS course link: https://training.fema.gov/is/courseoverview.aspx?code=IS-100.b

Transcription: Beyond the runbook; DR Organization

Carlos:             Compañeros, welcome back to the SQL Trail! This is Episode 124. Today we’re talking with Greg Moore. He’s the owner of Green Mountain Software, and the author of the book IT Disaster Response: Lessons Learned from the Field. And ultimately, that is the topic of our conversation today, is this idea of getting beyond the runbook. So in a disaster, how we can better coordinate with one another, not so much in simply just the execution of the response. So, we’re excited to have him. Greg is up from the northeast portion of the United States and works with cave rescue teams and so we’re interested in talking with him about some of his experiences.

We do have a few Compañero Shout-Outs we want to get to. The first, John Workman reached out on LinkedIn. He just took a job with Microsoft, helping folks on-board with the various services that are there and wanted to reach out. I think we’re going to try to get John on the program here to talk about some of the things that he’s doing. Also, I’m not sure if we have any Podable listeners here, but if you’re getting your podcast through Podable, welcome. Welcome to the SQL Trail, we’re glad to have you. Want to give a quick shout-out to Dave Mason from the panhandle of Florida, reaching out, saying hello. Dave and I were talking about a few ideas for the podcast as well and I think I’m going to snag him here later on for an episode as well. Now Cristian Satnic, he suggested a topic, although I couldn’t convince him to come on, but he had suggested a topic, and wanted to talk about continuous delivery to the database. And so Christian, I’m happy to say that that episode has been recorded and will be out next month, so we’re looking forward to that. I also want to give a shout-out to Steve Stedman. Unfortunately, through work and busy lives that we lead, Steve is no longer going to be able to be on the podcast with us. That’s a big blow, I know, to all you listeners. I’ve enjoyed having Steve on the program for, gosh, these 50 plus episodes and appreciate all of his contributions to the program. That’s not to say that he won’t be back, but unfortunately, he won’t be a regular guest. Now he is going to join us for today’s conversation and there’s one other episode that he’ll be with us. Then moving forward, you will probably see a variety of other folks that are panels, if you will, that we’ll be doing on the program to try to get a few more people there in as part of the conversation. But I wanted to thank Steve, again, for all of his time and effort, because it does take quite a bit of time to do that many episodes, and so I’m appreciative to him.

So now I think it’s time for a little SQL Server in the News! I want to give a shout-out to some of my friends at Channel Advisor. I think there are a couple of others, but those folks, Tracy, Mark and Brian, have put together a website called We Speak Linux at wespeaklinux.com, surprisingly. This website is directed to Windows administrators and developers who want to learn about Linux. The reality is, I think, most of them are coming from the DBA background and they want to help others who are going to be in dual environments, which Channel Advisor is, and running SQL Server in multiple operating systems and some of the challenges that they’re going to face. I’m not sure if they’ve started yet, but I know they are going to be starting in March. The first week in every month, they’re going to have a webcast and invite folks to come on, ask questions, so there’ll be more content. They are just kind of getting the site up, but there’ll continue to be blog posts and whatnot around that. So if you are interested in finding out a little bit more about running SQL Server in Linux, then that site might be for you.

Our URL for today’s episode is going to be sqldatapartners.com/caving or sqldatapartners.com/124. Let’s go ahead and jump into the conversation with Greg.

 Carlos:             So, Greg, welcome to the program!

Greg:               Thank you guys. Happy to be here.

Steve:              Yeah hey Greg, it’s great to have you.

Carlos:             Yeah, it’s great to have you on the program today. We don’t get too many authors on the program and so it’s nice to see that you’ve put out a book and we’re going to, obviously, be focusing on that a bit today. Our topic is how IT folks can learn, maybe, from some of the other systems or other organizations and how they respond to disasters. And I think this idea of beyond the runbook. You know, as IT people, we know, hey, we have to have a runbook and that’s going to tell me what I need to do, or for those of us who have actually had a disaster, we know that it doesn’t always work quite as smoothly. So you actually give a couple of examples or scenarios and we thought we might chat about those and how do we apply them to IT.

Greg:               Sounds good to me, where do you want to start?

Carlos:             So, we have a caving example and then we have a plane crash example. Which one do you want to start with?

Greg:               Let’s start with caving and as folks can probably tell, I have this fascination with when things go wrong, so I kind of pick the highlights and the lowlights there, I suppose.

Carlos:             Yes, that’s right. What I find really interesting about this, at least from your book, I’m not sure if this is still the case, but at one time you were actually the database administrator for one of the caving teams, like this group of people who would actually do rescues.

Greg:               I still am the database administrator. It’s the National Cave Rescue Commission. We’re not a rescue team, we actually train folks who form the rescue teams. I’m also an instructor, so I like to tell people where to go underground and what to do when they get there.

Carlos:             Yes, so I thought that was an interesting mix of professional and personal interests colliding there.

Greg:               Yep.

Carlos:             So, in the book you tell this story, in the middle of the night you get called in, there’s a caver in a cave who’s been free climbing and they’ve been injured, and your goal is to go in and help them get out.

Greg:               Yes, one of the mantras that we try to teach is get people out as quickly as safely as we can and hopefully in better shape than we found them. He unfortunately was not in great shape after falling about 40 feet. Yeah, it was not a pretty scene, but by the time I got there, actually a lot of the rescue had taken place and there were quite a few other folks there so I was just one cog in the overall machine.

Carlos:             Now one of the challenges in this specific instance was that, so if you’ve never done caving before, if you’ve been on a tour, it’s not the same thing as some of this cave exploring. The holes are very small, so you go on a tour, everything’s very big. I won’t say caving is unsafe, you have to crawl around a bit. I’ve been caving here in Virginia and there’s actually instances where you have to, you’re on your belly almost, trying to get through some of these places. And so that kind of lends itself to this story in the sense that he was not in a big open space and you could just get the stretcher down there and roll him on out.

Greg:               You’re very right. I always love in movies where they walk through caves, there’s plenty of light and the floor’s nice and smooth and everything like that. Especially in New York, our caves don’t tend to have any of those features. Crawling is a big part of it. This particular cave in Vermont is much like that. The entrance is kind of a weird trapezoid shape that you kind of crawl in, crawl over some rocks, and then you get to the top of basically about a 50 foot drop and straight down from there, there is actually a large room. So, where the unfortunate patient had landed was actually quite a large room and there was room to work there. But to tie this back to IT, the cavers in the area had recognized that something could go wrong here, and they had done some measurements and they knew at the top of that drop, to get a patient through in a litter was not going to work. It was basically chest-size, normally, you put them in a litter, it’s not going to work. So they had actually had a pre-plan, kind of think of it as our runbook, that basically said, “hey, if we ever have to evacuate a patient from beyond this point, we need to get in here and remove some rock.” So they actually had spent about 2 to 4 hours removing that rock there and setting up some other stuff, which is why by the time I got there, it was about a 2 hour drive, I kind of got there into the middle of the rescue because the local rescuers had already done a lot of the pre-plan. But you know, as they say in the military, what’s the quote, something along the lines of no battle plan survives first contact with the enemy. That was the situation here. We had a great plan that we got him to the top of that drop, but them getting him out through that trapezoid shape, we realized very quickly that, hm, the plan isn’t going to work. It’s 40 feet long, so we can’t remove 40 feet of rock, we’ve got to come up with Plan B. Or, I like to say in the back of my head I’m thinking Plan B, C, D and E and trying to figure out what’s going to work.

Steve:              So, with that, then, with a 40 foot fall, someone could have pretty significant injuries.

Greg:               Correct.

Steve:              And to just try and drag them out without being on a stretcher or a basket of some kind, could do much more damage than the fall itself. So therein lies your challenge, is how are you trying to get them, you’re trying to get them through this narrow passageway and it just won’t fit because of the size of the stretcher and the patient themselves. So, what did you do at that point, then?

Greg:               Steve, you bring up a great point, and since this is more of a database podcast than a cave rescue podcast, let me tie some of this back. One of my fellow rescuers who got to the patient recognized the injuries, had some basic first aid training, a little beyond that and was able to stabilize the patient including putting a little traction on one of his legs, which immediately calmed the patient down. As you can imagine, having broken legs was a likely result of this and not a comfortable position. But as I mentioned, we had to clear rock out. And one of the first rules we have is, make sure we don’t make things worse. And it’s kind of the same thing in, say a database failed or something like that. Sometimes the temptation is “oh, let’s reboot the server, oh, let’s do this.” I’ve had a boss come in and say “oh, let’s do this” and make things worse because they didn’t stop to think, “hey, what’s the situation, how can we stabilize things, make them better?” In this case it was “let’s move the patient out of the way of the falling rock, let’s get the patient into a litter.” For those who are EMS geeks, we tend to use the big orange Ferno litters. Let’s make the patient comfortable, they’re going to be there for a while, let’s give them some food and water and wrap them up, so we spend a lot of effort stabilizing the patient. So, we have one team kind of doing that while we have the other team putting in the ropes, opening up the rock, things like that. And it’s one of those things that I’ve seen in what I call kind of the large IT disasters a failure sometimes where there isn’t a whole lot of thinking beyond the immediate problem. There’s the “oh, this is crashed, we need to focus on this” but someone’s got to be thinking about, “well, who’s going to handle the flux of emails we’re going to get from our thousands of irate customers that might overwhelm our email server. So maybe I should have a team focusing on that while we have a team focusing on the dead database”, or in this case, a team focusing on the patient and a team focusing on the rockfall.

Steve:              As you say that, I’m thinking back to an example I worked on last week with a corrupt database and with the whole concept of don’t make things worse. And I think that when people run into database corruption, one of the first things they do is they try and reboot the server to see if that’ll fix it and that never fixes it, and in fact, sometimes it makes it worse. But the one that I was working on, it was actually if they had rebooted it in the condition the database was in, it would not have come back up. So that was one of them we got lucky that they followed that same logic, like you were talking about there, don’t make it worse, and they didn’t do the reboot. They just left it running until somebody could look at it. And I think that so many of these things you’re talking about here, really translate over from rescue world to dealing with database issues or IT crises.

Greg:               Exactly, and then the other problem that we run into and I started to mention this, too, is so we get the patient up and we try to get them out through the entrance and realize, huh, our initial plan is not going to work. And this is where, because I knew several members of the rescue team, I had worked with them previously, or I had trained with them, I had actually given instruction to a couple of them. And I also had with me an expert rigger inside the cave and one outside the cave. And I basically came up with a plan, I yelled at them, said “hey, come close so I can tell you, this is what I need.” And then I stepped back and let them make it happen. And it’s something I’ve seen again in IT, my best managers during a disaster have been the ones who know what help to offer and what help to step back. I joke, but it’s a true story, back, I’d say it was probably about 2000 when the internet was still young and we were all not as jaded and grey haired as we are now. I don’t remember what was on, our website was suffering performance issues or something and the CEO of the company who was also a good friend of mine, he came in, looked at us, realized that we knew what we were doing and he said, “I probably can’t help you, but can I order you some pizza?” And it sounds like a small thing, but it really was a big thing because we were going to be there for another hour or two at least, it was close to dinnertime and we were all focused on the problem but in the back of our mind, we’re all thinking, “hey, I’m kind of hungry, here.” So as a manager, he recognized his limitation, he wasn’t going to sit down and start typing out SQL commands or anything like that, but he could remove the small issues and he could keep all the sales people from bugging us every 5 minutes, “when’s the site up, when’s the site up?” So, he worked very well in that role as, let the people who can solve the problem, solve the problem. Back to the cave rescue here, that was my attitude was these two, they know what I want, I trust their rigging, if they can’t make it happen they’ll let me know and when they do make it happen, we’ll go ahead and try it. And it worked very well, and that’s why I say I don’t want to take credit for it. It might have been my idea, let’s try this rope this way, but these guys did all the hard work. And in a disaster, knowing that you can trust your team, you can trust the people below you or alongside you is a big part of that. And I’m going to guess Steve, as an EMT, you probably have that where you work with certain partners where you just know that you get to the patient and you’re zoned in and you just know what to do without having to shoot a hundred questions back and forth.

Steve:              Oh yeah, absolutely. And I think that, the thing I see oftentimes, from flipping that around on the IT side, is you get that IT manager who doesn’t have the knowledge or confidence in the team and their solution of dealing with issues is to come in and sit next to or stand next to the people who are doing the most critical work and ask them questions and basically interrogate every single step that they’re doing, instead of ordering pizza like in your example. Where that actually can make things much worse or slow things down, either in the rescue situation or in the IT side of things.

Greg:               Exactly, and I think we’ve all had that. I know I’ve had that. I’ve actually had, it was a brand-new VP to the company basically come in my office and “well, what were you doing?” and giving me a hard time. I basically said, “while you were asking a thousand questions, I was solving the problem.” It was almost a career limiting event at that point, but it’s kind of what he needed to set him in his place and I would not recommend doing that to most managers. But again, the most successful managers I’ve had have been the ones who will back off and say, they’ll ask the pertinent questions, “hey, do you have an ETA so I can get out an email. Okay, that’s 30 minutes, great. I’ll check back with you in 31 minutes.” But yeah, the ones who hover over you and stand over your shoulder while you’re trying to get stuff done, I don’t think they realize that half the time they’re going to make things take three times as long.

Steve:              Right, and they’ll even add the risk of more mistakes being made, because I know I seem to make more mistakes when somebody’s looking directly over my shoulder. I’m more concerned about what’s their next question going to be, than what it is you’re doing on the system, perhaps.

Greg:               Oh sure, and of course, there’s a rule that the more people standing over your shoulder, the harder it is to type, regardless. And if the high-ranking, your fingers aren’t even going to find the keyboard.

Carlos:             Yeah, and I think some of this ultimately kind of comes back to culture as well. And if we’re not working with our teams, and we tend to be very siloed, when we do get into this situation where we have to, it’s all-hands-on deck kind of situation, if that’s not a culture that we’ve developed, then I think it does make it very challenging. And I think maybe in the manager’s defense, perhaps, is that if the teams are not reaching out to them, so I’m not singling anyone out here, necessarily, but if we’re not reaching out and trying to have those conversations and work together to solve those problems, then when it becomes super important, the pressure of that doesn’t make for a great combination.

Greg:               Agreed.

Steve:              So, one thing I’m really curious about there in your caving example is that, and I see this on the EMS side quite a bit as well as in IT is that it really comes back a lot to training. And there’s things that are sort of the common things that could happen, and those are the things that you train for quite a bit. And then there’s also high-risk things that can happen, but maybe they’re less frequent. And those things you train for because of the risk involved in them. And I think that when you come across a scenario where it’s something you’ve trained for and you’ve seen it lots of times or you’ve trained for it lots of times, it makes it much easier to deal with. But then when you get in a situation where it is way different than anything you’ve trained for or there’s some twist that really changes it up, that’s where I think mistakes end up being made. And I guess I’m curious if you’ve seen that same thing in your background.

Greg:               Yeah, I definitely would agree and this is one area where I think what I’m going to call street EMS vs backcountry EMS or rescue differs a bit. Street EMS, like you say, a lot of it is pretty standard calls, broken limb, broken that and everything like that. And then like you say, you’ve got the very out of the ordinary ones. With cave rescue, there’s a, I don’t want to say a magazine, something that comes out about every two years called the American Cave Accident Report. It’s put out by the NSS that basically covers all the reported cave accidents, and one of the goals is to say, here’s the commonality, here’s the things. But one of the things that strikes me is generally, while there’s often a lot of similar injuries, and a lot of them are, honestly self-rescue, you know, a person twists an ankle and their friends help them out or something like that. When actual calls come out, every cave is unique. It’s really kind of hard to develop standardized plans, so what I really try to teach, and I’d say the NCRC, the Cave Rescue Commissioner I work with tries to teach is, here’s a set of skills, here’s a set of tools to put in your tool box and keep them in mind because you may never know which one you’re going to need. And that’s a big part of it. This one rescue up in Vermont, it was a lot of putting little pieces together of, hey, this will work here, this will work there, but there was obviously nothing we could train for when you have this particular cave, do that. Unless you’re training on a particular cave, which some teams might do because they know that cave is popular. But in terms of making mistakes, you’re right and again it goes back to something I was taught decades ago, literally, was kind of a joke but not entirely was, in the back country when something bad really happens, first thing you should do is sit down and make yourself a cup of tea. And it sounds like “oh my gosh they’re going to”, well, unless they’re going to bleed out in the next five minutes, in which case, take care of that. If it’s 6, 12 hours to a rescue, making that cup of tea is going to force you to slow down and stop and think about what’s going on. And one thing that we all get caught up in is the term Go Fever. We’ve got to go, go, go. Well, sometimes we need to slow, slow, slow. And it’s a hard thing to really do and I think especially with a more critical and often what appeared to be the more larger disasters is the ones where we most often have to slow down and stop. To use an analogy, Steve, if you’ve ever done mass casualty incidents, they always say “the person who’s screaming in pain is probably not the problem.” You know, hey, they’ve got an airway, everything like that. It’s that person sitting quietly that you’ve got to think about. What’s going on with them, why are they not making any noise? And again, we have to slow down and do that slow, slow, and stop and think about it and take that extra 5, 10 seconds to evaluate before we jump in.

Steve:              Yeah, you know what’s interesting about that is that there’s a term that we use oftentimes in EMS, which is called the distracting injury or a distracting patient. Where it’s something that jumps out as sort of obvious and big but it hides the real issue. For instance, it might be a broken leg when someone’s really having a cardiac issue. And people will tend to focus on the obvious thing and sometimes overlook the more serious things and I think that happens quite a bit in the database world as well, because you’ll see one thing and you’ll immediately start dealing with that one distracting issue and not realize that you’ve got a file system failure or something more significant on your database that is really causing all of it and not just causing the distraction.

Greg:               You bring up a great example. Just dealing with a client of mine where “oh my gosh, our backups filled up our disc space, what went wrong. It’s like, okay, we can clean up a few of the backups, no I’m not going to reduce your retention rate because you really need to have this many days’ worth just in case, but maybe we should look into why for three days in a row your backup that normally takes one hour took five hours. Your worried about the disc space, well that’s a symptom of the fact that something went wrong with your file system that your backups now are taking five times longer.

Carlos:             Yeah, I also think that going back to the differences in caves and whatnot, so standards, if we’re going to apply that to an IT environment, so standards might play a role here, and everybody does things a little bit differently. And I think one of the challenges that we as IT people have is that we tend to think that our way is the best way.

Greg:               Well, of course my way’s the best, but that’s beside the point in this discussion.

Carlos:             Yeah, and coming back and say okay, well, do we have any processes, maybe going back to that runbook, how is it that we’re supposed to be tackling some of these things.

Greg:               I’m a huge fan of people learning the how and the why behind things, not just what to do in X, but why are we doing X, why are we doing Y? bringing back again with Steve’s EMS, a lot of it is why is the patient’s heartrate this way. You know, we can treat it, but if we understand why, we can do a better job of it. Same thing with databases, why is this happening, why do I back up the tail of the log? Well, because it’s useful, not just “oh, let me hit a button because the commercial software says to do that.

Steve:              And you know, that’s interesting because I often say that why is perhaps the most important question you can ask and somebody, I’ll get this working with clients where somebody will ask for something and I’ll come back and ask “why do you want to do that” or “why are you asking for this?” Because they just said, “do this” without any detail around it and sometimes when you really understand that why, it changes how you approach it or how you’re going to deal with things.

Carlos:             A question I want to bring up, we’ve talked about preparation, how different might things be training. Now again, I think for a lot of people, getting the runbook would be a huge win, right?

Greg:               Yep.

Carlos:             Like hey, we’re done, right, I’m finished. But what are your thoughts around taking that to the next step and how can we be better, is prepared the right word for working together?

Greg:               I think one of the things that companies are afraid to do sometimes is practice. One of my former employers had a very good setup, you know, very robust network architecture and everything like that. And my network engineer came to me one day and said, “hey, I’d really like to do a failover of our switches, just to make sure it works and everything.” And I said, “yeah, that’s a great idea, what’s the risk” everything like that and he wrote it all up. And it was something like maybe worst-case 30 seconds to rehome the BGP tables and all of that and we would do this at like four in the morning when nobody’s using our site. I said, “great.” So, I went to my managers and one of the other managers and they’re like “oh no, no, we can’t do that, it’s too risky.” And what I couldn’t get through to them and my network engineer felt the same way was, it can’t be too risky. We either trust the setup and prove it or we don’t, in which case we can’t rely on it in an emergency. And I think in my book, and I mentioned in one of my talks, supposedly Jeff Bezos would walk through his data centers and just randomly pull out a network cable or hard drive. And you know, I say that to people and they get this look on their face, “oh my gosh, I could never do that.” And I ask them, “how do you know your DR snare is robust enough to handle that? If you have a saying and it’s supposed to handle a drive down, how do you know it’s going to do that until you actually pull a drive?” Now, that said, I’m not quite that extreme of just walking and pull out hard drives because you might want to check first that you don’t already have a failed hard drive. But you know, practice really is an important part of it.

Carlos:             Yeah, so from that perspective, cause one of the things that, the problems you have, and there have been plenty of examples I think, so what was it, Delta that had the big downtime? They were trying to switch their power supply and they were like, “ok, well, this is a test. Let’s switch from one to the other.” And then it failed and then, so the practice caused an outage, and so there’s that fear, which I think is real. Part of that is because you already have all this infrastructure, we talked about testing, is already in a production state. Users are depending on it, somebody is depending on it. It can get complicated. I’m not saying you could necessarily recreate your entire production environment. Obviously, that would be nice, but you know, routers and switches and things, that gets expensive. But what about maybe trying that in another environment. Okay, yeah, maybe I don’t have all the users on there, but can I at least attempt on these two VMs, and go from this one to that one? Okay, well, at least I know it can do that.

Greg:               I think it’s a brilliant question and a brilliant point. Again, what’s it worth to you. If you’re doing mission-critical systems, say Wall Street where they’re literally trading hundreds of millions of dollars a minute, then investing in basically a duplicate infrastructure is probably worth it. If you’re doing $1000 an hour of business, well, yeah, you probably can’t duplicate your entire data center, but like you say, these days you can pretty much virtualize anything in your environment and taking the time to do that. But I’m going to go back to your Delta comment for a minute and say consider this. In a proper test and everything, you have people in the key places that, oh yeah, normally software would do this, but we’ll have someone there that can manually type in the command if need-be or something like that. So, imagine that the Delta scenario was not a test but had happened for real, that for whatever reason the power had failed and they didn’t have any of their preparation in place? I’m assuming that they had a plan in place of, okay, if something goes wrong, we’ll fall back. Again, think about if it happens in real and I don’t have anything in place, it’s probably going to be worse than if I do it in practice and something does go sideways.

Steve:              Oh yeah, and I think that whatever outage you cause when you’re doing a practice like that is going to be less significant than when it happens in reality if you had not practiced for it. And yeah, if you cause an hour of downtime because of a practice, that might prevent 12 hours of downtime in reality.

Greg:               Exactly.

Steve:              And that’s one of those that I think it’s oftentimes hard to get management to approve.

Greg:               Oh, very hard.

Carlos:             Oh, I agree, I agree.

Steve:              Because oftentimes, it’s running right now and if you touch anything it’s your fault when it breaks.

Greg:               Exactly. You know, I’m reminded, I just finished teaching my son to drive, he’s got his license now, and you know, growing up it the northeast, we get snow storms. So, one of the first things that we did last winter, actually was took him over to the local parking lot with big snow banks, said okay, let’s do some skids, let’s do some slides. And he asked that question, “well, dad, what if I slide into a snowbank?” or there’s like two light poles that are in the middle of the parking lot, “what if I hit that?” and I had to point out to him is “I’d much rather have you do that at 5, 10 miles an hour in the parking lot than on a highway for real where there’s other cars and people around. I mean, yeah, I’ll be upset if my car gets a dent in it, but that’s a far cry from if you’ve never practiced it for real and go into a real skid on a real curve with other people in the car and stuff like that.

Steve:              Very good points.

Carlos:             So, what do you think, I guess maybe the last question here and then we’ll move into SQL Family. So, for the non-managers, for managers, I’m not saying it’s easier, but ultimately you have a little more decision-making authority. So how could we, as the humble IT worker that we are, how could we begin to influence some of those things?

Greg:               How can we manage our managers? I would do two things. I would honestly try to read up a little more on the incident command system, ICS, and then on cockpit resource management or crew resource management, which deals with how management both in terms of structure and then in terms of communications can work during a disaster scenario. ICS was developed to handle everything from a car accident up to wildfires in California. And I honestly would love every IT manager and maybe one step above that to take at least a 100 level ICS course, which by the way, you can go to the FEMA website and click through the whole thing in about half an hour. Maybe us IT folks can convince our manager to take that class once and let me do a little more research on this so I learn more on how to be part of a team and then how to be able to communicate up and down and across my team.

Steve:              Oh yeah, I would completely agree with that. If IT management could learn ICS, it would make disaster response much more organized, I think.

Carlos:             Yeah, interesting. So I guess I’ll leave, maybe another word. I remember one time I was at church and an older gentleman was in the pew and he appears to be, so there’s something wrong with him. He slumps over, I was several pews back but he appears to stop breathing, and so obviously there’s a situation going on here. And so, people around were like “oh gosh, what’s going on?” and they didn’t immediately move him. Obviously, they called the paramedics and the paramedics come over, they get him out. And what was interesting, I did not see this, I was seeing this second hand or hearing this second hand, was that again, we were all concerned, “oh my gosh, do something.” And what the EMT did, he came over, again, he hears this call, guy stopped breathing, this older gentleman, you know, 80 years old, might be having heart attack. And at that time he was alert, and the EMT walks over to him and just starts talking to him, “hey, what’s your name? I’m” whatever. And they’re like “wait a second, aren’t you supposed to be doing something?” But again, I think through that training, he wanted to establish “where am I in the process?”

Greg:               He’s assessing the situation and everything like that. And that’s a big part of it. One last thing with cave rescue, we tell folks and we drill this into our students, when you come across a scene, the first thing you do is stop and look around and see what happened. Why did that person fall down? You don’t want to, if it’s bad air in the cave, which is actually very rare, but you don’t want to go charging in after somebody and find out that, oh, it’s bad air.

Carlos:             Right, and then you’re down there as well.

Greg:               So yeah, really is often just sometimes the simple things of, “hey, let me just ask a few questions” and patients, 9 times out of 10, they’ll tell you everything you need to know. You just have to be listening. With databases, you can’t quite ask them questions in terms of English, but if you’re willing to take the time and look at the logs and spend an extra couple minutes looking at stuff, it’ll answer most of your questions.

Steve:              Yeah, it’s interesting because so often when there’s a problem whether it’s in EMS or in a database, people get tunnel-vision and they just think about a specific issue that’s being reported but they don’t ask the questions like that either from the patient or from a database to understand what the real issues are. I think, yeah, I would completely agree there.

Carlos:             Awesome, shall we do SQL Family?

Greg:               Sounds good to me.

Steve:              So Greg, how did you get started with SQL Server?

Greg:               You know, I joke because when I was in college, I took a class on databases and it discussed the SQL language and how this is starting to be more popular and maybe someday it’ll be a force in the industry. And probably within about 10 years I had a client that did Information Management System for Laboratories. And they started moving from using Informix, for anybody who remembers that, now I’m dating myself, over to SQL Server. And what they needed was their team could install their software, but they wanted someone to go in a day or two beforehand and install the SQL Server setup and everything like that. So, I kind of became their go-to person for that and after that, moved into an internet start-up in 1998 where I became the DBA and stuff like that and just really decided I really enjoy it. I love the fact that there’s so much data at your fingertips and if you just know how to ask the questions, you can get some amazing stuff out of databases.

Steve:              All right, very cool.

Carlos:             If there’s one thing you could change about SQL Server, what would it be?

Greg:               You know it’s funny, I just answered this question on Quora.com. one of my beefs, and it’s a small one, but I’m sure every DBA’s gotten bit by it once is the fact that when you do a Restore Database, it defaults to With Recovery. And if you forget to type With No Recovery and you’ve just restored that database that’s taken 5 hours to restore and you try to restore the logs and discover, ooo, I can’t, you’re going to say “ah, I wish I’d typed no recovery.” So really wish No Recovery was the default because worst case is, if you’ve got nothing else to do, you just then say With Recovery and you’re done.

Steve:              What is the best piece of career advice that you’ve received?

Greg:               I don’t know what’s the best piece I received. The best I can give is do what you enjoy, not what you love, because if you do what you love for a job, it becomes a job and you stop loving it. But if you do what you enjoy, you’ll probably still enjoy it while working.

Carlos:             There you go. Our last question for you today, Greg, if you could have one superhero power, what would it be and why do you want it?

Greg:               I go back and forth between teleportation and flying. I think being able to fly like a bird and swoop and soar and all that would be pretty darn cool. And I think cool is a good enough reason for wanting a super power.

Carlos:             Well, there you go. Yes, I was going to say you must still have hair that the wind can flow through.

Greg:               I do, I just got it trimmed last week, so it’d be pretty short, but I’ll grow it out if I ever get the chance to fly.

Carlos:             Awesome, Greg, thanks so much for being on the program. We do appreciate it.

Greg:               I enjoyed it. Thanks for having me and if you ever want to talk about plane crashes in the future, we can do another one on that.

Steve:              All right, sounds good, thanks Greg.

Carlos:             So, I went ahead and took Greg’s challenge here, and went to the FEMA website, and I found this ICS course that he talked about. While I admit I didn’t get through all of it, I did begin the course. It won’t take too long, I just need to spend a little bit more time with it. But I did find it interesting that this was created and kind of looking back to the origin of FEMA and some of these things is the California wildfires in the 70’s. They just got bigger and bigger and with more homes and people dying as a result of these fires, the government decided to put together kind of a plan of action, or at least some ideas or a framework on how first responders and emergency personnel could get together and coordinate ideas. I do think it harkens a lot to some of the thoughts that Greg had in today’s episode and so of course we’ll have the link to that course up on the website if you’re interested in checking it out. I think we in technology get a little bit spoiled with some of the interactivity that we have. Now there are some videos and whatnot that are in the course, but for the most part it’s going to be PowerPoint types or slides that you’re going to go through and do a bit of reading on. Again, very interesting, I think one of these ideas from a culture perspective, so this is not necessarily technology-driven, but how can we change the culture around disasters? I was actually at an event last night talking with folks, and these are general IT people, and security is always a big issue. But there’s always that question in the back of my mind is, “if I make this change, am I going to break something? Is it going to cause an issue, and then what are the repercussions there?” So, I think part of this idea is that we can more forward if we know how we’re going to interact when emergencies do come. And so, thanks to Greg for that conversation.

As always, compañeros, we’re interested in your thoughts on what we should be talking about on the podcast, or if you’d like to join us, of course we’re very interested in that. Our music for SQL Server in the News is by Mansardian used under Creative Commons. Compañeros, you can always reach out to us on social media. I am interested in connecting with you on LinkedIn. I am @carloslchacon. And compañeros, I’ll see you on the SQL Trail.

Episode 123: Top 5 things to know when getting admin access

Listener Eduardo Cervantes wanted to get our take on what developers should do when they get admin access to a database.  We take on this challenge and I give 5 points you might consider if you are a developer with admin access to the SQL Server.  As Uncle Ben in Spiderman quotes, “With great power comes great responsibility.”  We hope you use yours wisely.

Episode Quotes

“The question you should be asking yourself is, why was this not already enabled? What is the downside to implementing this course of action?”

“Who owns the code? We all do. The same could apply to the database. Documentation then comes into play.”

“Rolling back is very hard when you don’t know the original state. ”

“You may think you understand the consequence, but then if there’s unintended consequences, give yourself a way to get back.”

“Patching, security, backups, boring. Perhaps, but they need to be taken care of and they do become important.”

“Just because you have admin access doesn’t mean that everybody else should have admin access.”

Listen to Learn

02:09        Compañero Shout-Outs
03:39        Tips & Tricks
06:42        SQL Server in the News
09:48        Intro to the topic
10:12        We are going to assume there is no DBA present.
10:32        This could apply to both production and development
10:50        Do you know the consequence of your action? You have some control of the behavior of the system—just make sure you understand the consequences.
12:28        Who is the owner of the system? Does that person now what you are doing? Shared ownership-new concepts in programming.
14:19        Rollback is hard when you don’t know the original state. Always give yourself a way to get back. Containers. 😊
16:26        Don’t forget the small stuff. (patches, security, backups, etc)  Just because YOU have admin access doesn’t mean everyone should.
18:28        Install Database Health Monitor
19:33        Close-out

Transcription: Top 5 things to know when getting admin access

*Untranscribed Introductory Portion*

Carlos:             Compañeros! Welcome to another edition of the SQL Data Partners Podcast. I am Carlos L Chacon, your host, and this is Episode 123. We would like to excuse Steve Stedman. He’s not with us this episode, so that’s unfortunate. But the good news is, he’ll be back next episode. We’re looking forward to having him. We’re doing something a little different this episode. Of course, if you’re a long-time podcast listener, you won’t notice, however, we are trying to incorporate some video into this episode. So, if you’re joining us via YouTube, welcome. And of course, for you long-time compañeros, welcome back to the program. It’s good to be back with you.

Today’s topic is five things developers should know when they get admin access to a SQL server. For a lot of you developers out there, you don’t have DBAs, we know that there are more and more of you listening to the program. So, actually in 2018, we are trying to gear more of our content to you so we can help bridge that gap and help you be better prepared as you’re trying to tackle some of these administrative tasks while you’re developing. This topic was suggested by a listener, Eduardo Cervantes, so we thank Eduardo for suggesting this topic and sending it our way. So, before we get into that, we do have a couple of compañero shout-outs. We want to give a shout-out to Aaron Hayes. Aaron Hayes is ready for the SQL Trail event . Hooray! Aaron joined us last year, in 2017 and looking forward to another great event. I know we are still trying to work some of these details out. We haven’t announced the 2018 event just yet, but hopefully that will be coming fairly soon. We’ve been working with some sponsors and looking to do some things with some labs, and so that will be exciting once that finally gets announced. Vivek Patel reaching out on LinkedIn. Hey, Vivek! Nathan Hills chimed in on Episode 114 on Getting Started with Consulting. He thought that was kind of interesting, so thanks for that feedback, Nathan. And Chriss Voss, shouting out, sharing some enthusiasm for having developers on the show. I know we made fun of Bert a little bit in Episode 120 and Chriss said “hey, glad to have developers on the program, ‘cause there’s hope for the rest of us!” So Chriss, thanks for that, and we are trying to do, like I mentioned, a bit more content for you developers. So welcome, and it’s great to have you as compañeros. And then Davy, all the way from Langholm, Scotland, chimed in, “really enjoyed the podcast, really well structured, surprised how much I know and horrified there is so much that I don’t know.” So, Davy, yes, join the rest of us in not knowing a whole lot and we look forward to having you around for future episodes.

So, we did promise you compañeros that in 2018 we would include a Tips and Tricks segment. This obviously was user-suggested, and we’ve been soliciting some ideas, and I have to admit, I’m a little concerned. Those ideas aren’t coming in quite as rapidly as we would like. So, we’re going to go ahead and start sharing some of the ones that we put together. And I think that maybe you’ll be surprised at potentially how simple they are, and this idea of, again, just sharing the way that we work. And so, for this Tips and Trick, and again, I’m going to be using the power of video here. So, let’s say that we wanted to pull some data from a database, or we wanted to copy some data from the internet, and it’s not formed well. In this example that I’m using, I’m actually just doing SELECT * from sysdatabases and I have this, and let’s say I want to put it in a report or something, I want to format this for whatever reason. In this case, I’ve actually put the output to text so that I can get that format, so that it lines up correctly, cause if I do it from grid and copy there, you know, the headers get all messed up from the body. So, I run that SELECT query, I copy the results, and then I want to do something with that, so I’m going to paste that. In this case, I’m pasting it into SQL Server Management Studio, but this will actually also work in Word and the other Office products. And so, what I see here is I have a lot of white space in between some of the columns. Or let’s just say there’s a column that I didn’t want to include in my report. So, for example, I know that by policy, is auto-shrink on, is not something that I’m ever going to have to take care of, but I don’t want to have to go through each line and delete that data from the line. So, one of the things I can do is I click where I want to start, and I’m going to hold down the ALT key. If I hold down the ALT key, then I can click and drag, and what that will allow me to do is to get blocks of data, or blocks of columns, if you will, and I can select that and remove it and all of the rows will line up nicely. And so this is particularly useful, again, more for white space, where there’s white space I just want to take out, but it’s in multiple rows. So, I would click there, hold down the ALT key and I can do that and again the size doesn’t really matter. I find that to be really helpful when I want to some formatting and so I hope that you’ll find that useful if you’re not already using it. And so that is the podcast Tip and Trick for this week.

And now, time for a little SQL Server in the news. So perhaps not news. It was announced, I know at PASS, I think they introduced it at PASS Summit last year in October, and this is the SQL Operations Studio. What is news, is that they have started, very similar to the SQL Server Management Studio, is that they have started to introduce monthly updates to this program. For those of you who are not familiar with the SQL Operations Studio, the SQL Operations Studio is the new tool and it is ultimately a visual interface to be able to connect to SQL Server in a visual way but that runs on LINUX and Windows. For those of us who have been using SQL Server Management Studio for some time, you’re going to find some of the features lacking, so don’t get too concerned here. I think there are some very interesting developments that are happening, that are coming, and talking with the product team, again, when I was out in Seattle, there are some things coming, but it’s just like everything, it will be slow coming. So, kicking the tires a little bit on the SQL Operations Studio, again, very similar to SQL Server Management Studio, I can connect to a server, I can see some of my databases, I can write queries, select the database that I want to run the query against. There we go, I can change the database and run it against that database. So again, all very simple, if you will. If you’ve been using Visual Studio to do some SQL Server queries, that’s probably a little bit more familiar in the sense of, you’re specific to writing those queries. A lot of the administrative-type tasks, yeah, not baked in, and I’m not sure that they’ll come. SQL Server Management Studios probably is still going to be the place for you to do that, but there are some different things that they’re trying to do here with the Operations Studio. One of which I know, and again, talking with the product team, that they’re going to allow us to do is to create these reports, if you will, and then tag them onto the dashboard. So here they have a couple, backup status and search databases, and you can actually go in here and run the query and that’s what this is, and it will show you the data behind the query. What at least this knuckle-dragging Neanderthal hasn’t been able to figure out just yet is how to take a query that I’ve written and then put it into the dashboard. I believe that’s going to be coming. Maybe it’s already there and again, I just don’t know how to do that. I found it difficult. I couldn’t immediately find the documentation to do that. But I know that it is coming. So again, it’ll be exciting to see what happens with the Operations Studio. Not super feature-rich, but particularly if you’re in an environment where you’ve started using LINUX but want that visual way to be able to connect to the database and start doing something with it, then again, you have that ability with SQL Operations Studio.

So, with that, we’ll go ahead and get into the episode. The URL for the show notes for today’s episode will be sqldatapartners.com/access. Yes, like the database, because we’re talking about developers getting access to the SQL Server environment, or you can go to sqldatapartners.com/123. For this conversation, the idea is what developers should know as they get admin access. So, we’re assuming that there is no DBA present, and there’s kind of a shared responsibility model. There’s not maybe a group responsible, and so you or the other developers are taking on that responsibility. And this, obviously, would apply to both production and development environments. I’m not necessarily going to get into specifics as to is one different than the other. In my mind, these initial steps are going to apply to both scenarios, and then we’ll go from there. So, the top 5 steps are one, do you know the consequence of your action. We like to make changes, you might run into a problem and you read on the internet, oh, you should do X thing. You should change this parameter or you should use this function or you should enable this trace flag. While the suggestion is that that will help you solve your problem, the question you should be asking yourself is “why was this not already enabled? what is the downside to implementing this course of action?” You know, there’s a reason they don’t turn certain things on by default. Now, more and more, some of that is just because they don’t want to break the old stuff, so if you’re developing new things, there are lots of, you know, those best practices evolve over time. I get that, but at the same time, when you want to start implementing something, you need to be able to understand what the bad stuff is, what the negative is. So, do you understand what that consequence and what the trade-off is? What maybe you might be subjecting yourself to, now that you have taken this action, that you wouldn’t have otherwise? And you could potentially, you know, could you solve that a different way, based on that knowledge? So that’s the first thing to take in mind because when developers get that access, we tend to “oh, it’s exciting”, it’s like “hey, I can finally do what I need to do.” But we want to take a moment, pause, think about what we’re doing.

The next thing is to identify the owner of a system. I’ve been the DBA for many organizations. They’re going to come to me as the owner. So, a lot of DBAs think of themselves as the gatekeeper, but you won’t have that without a DBA and if you’re developers taking care of this, then who then is the owner? So, from a developing concept, there’s pair programming and there’s a shared ownership idea. These concepts have been around in programming and so it’s almost similar to say well, who owns the code? Well, we all do, so the same could apply to the database. Now having said that, documentation then comes into play. Source Control does a great job for the store procedures, the views, even the table structure. All of that you can get into Source Control and then you can okay, well, here’s what’s changed and whatnot, who changed it, things like that. What’s very difficult, or what Source Control doesn’t give you are system setting type processes. So, who changed the trace flag, or who allowed this action in the database. So, from that perspective, you’re going to have to find a way to document what those changes are, who’s making them, and then, are we all okay with making this change? You know, with code it’s easy to “okay, let me work on it, let me commit it and then people can take a peek at it and give feedback.” Again, with those settings it’s a little bit harder and so then coming up with a “here’s how I’m going to manage this” is an important thought process to go through. Cause rolling back is very, very hard when you don’t know the original state. How do we get back to the way we were?

Which brings me then to step number three: always give yourself a way to get back. Obviously, backups are a good way, if you’re changing parameters or system settings, writing that down, what was it, what did I change it to, those kinds of things? That’s no-brainer-type stuff. But we know that database changes, sometimes, they can be problematic, particularly as your database starts getting large. Like oh, do I really want to take a backup, can I just save off a table? Yeah, maybe. Maybe you can. Me personally, I think this is one of those areas where containers are going to start playing a larger role. And obviously if you’re using containers in your development environment, the implementation of containers into the database is going to be a no-brainer. It’s going to give you that ability to be able to spin it up, okay, let me try to make this change implement, right, I’m implementing something here, you could think of that as a code push as well. Is it behaving the way that I think it’s behaving? Okay, yes it is, now let me apply the same change to the database that I want to change. It’s very easy to make that leap because I have a way to test that very, very easily. So I’m interested to hear how containers play a larger role in the database environment, and I think that specific scenario, so in the dev scenario, and being able to get a container of your production environment very, very quickly, without having to do all that restore, allocate all that space. It makes a lot of sense and I think that’s a great way to go, if that’s something that you can pull off. Obviously, the folks over at WinDocs will be very happy to help you out. We are looking to partner with them and work with them and help more organizations take advantage of those containers. And so that all goes back to giving yourself a way to get back, just in case. Cause you never know, you may think that you understand the consequence, but if there’s unintended consequences, give yourself a way to get back.

The fourth thing is don’t forget the small stuff. So, patching, security, backups, boring. Perhaps, but they need to be taken care of and they do become important. Just recently, just this last week, we’re getting word about the CPU bug, where under certain circumstances, someone gets access and they can actually get access to the memory layer for the CPUs and see in clear text all of the things that we’re trying to encrypt because it’s what’s the CPU sees. And so again, these are very real problems. You’re patching your software, for example, the things that you develop, you know they’re going to have bugs. Well, okay, the database is no exception and you have to think of a way, “okay, how am I going to keep up with this stuff?” Or again, who is going to take ownership of that or how have I looked at solving this problem? There are lots of third party applications out there from a backups perspective. So again, just understanding what it is that you’re getting from that point in time, how long am I going to keep those backups, those kinds of things are things to think about. And then of course, just because you have admin access doesn’t mean that everybody else should have admin access. Security still plays a role there, particularly from the application perspective. You have a web app, you don’t want to be giving that user admin rights because you’re just inviting the ability to be compromised, frankly, because you have bad security in place.

So, the four have been: do you know the consequences of the action, identifying the owner of the system, and if you have a shared ownership model, detailing who’s going to be making those changes and how they’re going to be documented, always give yourself a way to get back and then don’t forget the small stuff. And the last suggestion I have is to install Database Health Monitor. In my mind it’s a no-brainer. It’s a visual interface, it’s going to give you that ability to be able to get better insights into the database without having to look at all of the logs or know all of the queries. There are lots of reports that are baked in that are going to give you some feedback. The biggest benefit to the Database Health Monitor is the wait stats history. Now you’re going to have to install a small database to keep some of that history, but now when you go back and you’re like “wow, at 10am this morning I was having problems”, you’ll actually be able to have the history to be able to go back in there and start digging around. What was going on? Again, lots of different ways to home-grow that yourself, and if that’s your option, go for it, but for an easy out-of-the box way that’s free, Database Health Monitor, I think, is the way to go there.

So, compañeros, what do you think? Do you agree with my list? I’m very interested in hearing from you. I’d be interested to get your thoughts and feedback. And you’ll note that I didn’t give specific, “hey, you should do this”. There are some best practices out there, but I thought that identifying some of these first steps would really be more important. Again, even using the Database Health Monitor as an example, that would help you understand what the best practices are and you can start implementing that as you see fit. So, you can leave comments in the show notes or reach out to us on social media. We are always interested in hearing from you. That is going to do it for today’s episode. We have quite a bit of music. Our music for SQL Server in the news is Mansardian, used under Creative Commons, as is all of the music that we used today. We hope you’ll reach out to us on social media. A lot of people are connecting with us on LinkedIn and we invite you to reach out to us on LinkedIn. I am @CarlosLChacon and compañeros, I’ll see you on the SQL Trail.

Episode 122: Isolation Levels

Does not play well with others. Is this description appropriate for some of the queries in your database?  In this episode, we explore the role behind isolation levels–why they are important, what they help prevent, and why so many people try to get around the rules they try to enforce.

Isolation Levels

We want our databases to be responsive and provide us with the data as quickly as possible; however, the database has to juggle everyone trying to make changes (updates, inserts, and deletes) with all the reporting options (selects).  To do this, SQL Server has a mechanism to retain order when there are multiple requests at the same time.  In some cases this requires locking records to ensure only one request can modify the record at a time.  These locks can lead to blocking and this is when people start trying to do crazy stuff.

Dirty Reads

When you are reading data that is involved with another transaction.  You ignore any potential affect it may have on your results.

Phantom Records

Read

Episode Quotes

“Isolation levels are about the degree of isolation you want an individual transaction to have.”

“If everybody is only reading and there is no write to the database then isolation levels don’t matter.”

“An optimistic model does work well if you don’t have to read conflicts and the pessimistic model works well if you have conflicts.”

“It push load on TempDB and if TempDB is already a bottleneck then, yes, you could potentially have issues there.”

“Memory optimized tables supports snapshot isolation, repeatable reads and serializable.”

Listen to Learn

00:05 Introduction of the guest speaker (Jos de Bruijn)
00:24 Episode Topic: Isolation Levels
00:52 What are isolation levels and why it is important?
04:18 Dirty reads and Phantom reads
09:30 Isolation levels when building a database application
11:51 Optimistic Model and Pessimistic Model implementation
18:15 TempDB bottlenecks, locking and blockin user databases
21:09 Difference between the utilization of Read Committed Snapshots and General Snapshot Isolation
26:41 Implementation of repeatable read and serializable isolation on memory optimized tables
34:43 Locking hints and when to use it
36:31 SQL Family questions

About Jos de Bruijn

Jos de Bruijn is a Senior Program Manager in the Database Systems team. He works on the SQL Server Database Engine and the Azure SQL Database service, focusing on query processing and data storage. Jos has led the query and transaction processing for In-Memory OLTP in SQL Server 2014, and delivered In-Memory OLTP enhancements in SQL Server 2016/2017 and Azure SQL Database. His current focus is performance and scalability in Azure SQL Database. In a previous life, he obtained a Ph.D. in knowledge representation and semantic web technology, and worked in academia for several years as an assistant professor.

Transcription: Isolation Levels

*Untranscribed introductory portion*

Carlos: Well Jos, welcome back to the program.

Jos: Well, thanks Carlos! I’m happy to be back.

Carlos: Yeah, it’s great for you. It’s always great to talk with a Microsoft folks and we enjoy you taking some time out to talk with us. It’s been almost a hundred episodes, and so it’s nice to have you back on.

So today, we’re going to be talking about isolation levels which might seem like a slightly daunting task. But I think it’s one of those very important features, and I guess let’s go ahead maybe set the stage for why isolation levels are important, and what are they help us to do. Right, so I guess how do we want to frame that in this conversation?

Jos: Yeah. So isolation levels, so we are talking about transaction isolation levels and isolation levels are, as the name suggests are about the degree of isolation you want an individual transaction to have. Because in a database system, database systems, SQL Servers, support a higher degree of concurrency that you can have. Lots and lots of different transactions, user operation happening at the same time. So the question is if you have a transaction that is operating on some data, reading some data, making modifications to what degree it isolates from another transaction that is touching the same data or reading the data and modifying the data at the same time. So that is why isolation levels are important because what you don’t want is that you are making an update on some data. Let’s say you’ve banking software, is a very classical example, you are doing an account transfer between one account at the other. You want to make sure that the overall balance is between the two is zero, right? Subtract 10 from one account then add 10 to another account. At the end of the day, you want to make sure that you have to balance, that’s only $10 a shift, is you don’t want another transaction to come in halfway and look at the data as if there was higher total amount. You want the other transaction to only see the result of your transfer and not…

Carlos: In between state. Ultimately, if you’re writing queries to the database, it’s how those queries behave with each other. Right, that’s kind of the idea of our conversation today.

Steve: And maybe just to phrase that a little bit different from the developer’s perspective, when the developers has an application that’s talking to the database. It is how those queries that are coming from that application are going to behave. And the different transaction isolation levels may change behavior of those queries slightly. Is that fair way of saying it?

Jos: Yeah. It is actually about the interaction between the queries and the write operations. If everybody is only reading and there is no write to the database then isolation levels don’t matter, and there is no difference whatsoever in the behavior between different isolation levels. It’s only in the interaction between the reads and the writes. So if you have a query and some other transaction is doing writes to the database, your isolation level essentially governs what you can see, what’s states of the database you are allowed to see.

Carlos: And what we are trying to solve here is I think help that basic like basic transactional database logic from the very beginning, right from the very beginning, almost the reasons that these transactional systems were put together. These are kind of the rules that it agree that it would follow in correlation from those updates and reads.

Jos: Yeah, that’s right.

Steve: So two terms that seem to always come up when we start talking about isolation levels are the term dirty reads, and the term phantom reads. Can you touch on what those exactly mean from the SQL perspective?

Jos: Yes. So dirty reads means that is reading data that has not actually been committed to the database. So usually when you have a transaction that does writes, you do the write operations. You make the changes in the tables and then at the end of your transaction you’re doing a commit. And then when you receive an acknowledgement from the server that that transaction has been committed, that means only at that point in time is the data really part of the database. At any point before that it, it is actually dirty data and if anybody reads that data it will be a dirty read because before the transaction is committed, it can roll back at anytime. And if the server crashes or failsover before a transaction is committed it has always roll back. So a dirty read means you are reading data that has not yet been committed to the database and you don’t know if it will ever be committed to the database. So that is a dirty read. That is kind of you’re getting in very flimsy territory and usually dirty reads is what you really want to avoid. Phantom reads, that is something very different. Phantom reads, that is data that has been committed to the database, but it has been committed essentially while your transaction has been running. It’s a subtle kind of interaction between transactions.

Steve: When you’re selecting from a table and you’re reading through page by page, as a query does, and something happens that causes pages to shift around in that table. For instance, an index is being reorganized, or somebody has inserted into a page that’s full that causes a page split. What can happen when that occurs, you get records that are either duplicated or missing. So, phantom records are basically when rows are either being counted twice, or they’re not there at all. And what that can do is if you’re doing reporting, and it has a table with this going on, is you can get very skewed numbers on your results. So, if you’re doing, like here’s 10 rows and then at the bottom there’s a summary that says here’s our total line, if your total line is being calculated and a row moves, some rows can be counted twice and some rows can be missed. If you’ve got a clustered index on a GUID or a last updated date or something that can change, and that changes, that causes the placement of that row in the table to move. And if you’re doing something like select count where from that table, with locking, make sure that you get the right number or rows. But if you use select count with no lock, you can then end up with those phantom records, where you have either rows missing or rows duplicated because using no lock says “I don’t care about other people’s locking, I’m just gonna scan through the pages and grab whatever’s there that’s not fully committed.”

Carlos: Exactly, so although that work is happening, and it’s still gonna give you the results, regardless of what other people are doing.

Steve: Yep, and what that means is you can grab the same row twice, or you can miss a row completely.

Carlos: This sometimes can be difficult to pin point when you talk about coming from a developer’s perspective. You know, when you think about testing as to writing your queries against your own database. You know, they happen very, very fast even when you try to “load test” and then maybe you have some different windows open or connections and you’re trying to replicate some of the stuff. But really it gets important when that concurrency, when the number of transactions increases at the same time on the database. And that’s when you start to see all these rules to start to come in force. I know sometimes when I was first starting with this it seemed a little abstract but again that idea of who gets to go first and what’s going to happen plays an important role.

Jos: Yeah, absolutely. But it is important when you are working with a database application especially if you have sensitive data that you are working on especially with things like financial data. It is important to really think through like when you are reading the data. Like how important it is to isolate the reads from concurrent applications. How important it is that when you do for example a range query, when you query all these account data for a single use, how important is it that nobody adds any additional account data to it, right? So that is what you need to think about when you are building a database application.

Carlos: Yeah, and again I think this could be a little bit blurry in the sense particularly when you have all of this, I guess you use the streaming data, right? For all of these, the no SQL Solutions that are coming on the scene. Again it’s that balancing act of how do you want these interactions to play into. And these are again core fundamental issues. Now, while we want our transaction to play nicely together like you mentioned with the account, my bank information, I don’t want somebody to see that I’ve taken 10 out and have it deposited into the other account while that’s going on. But in order for the database to be able to force that one of the things that I’m going to have to deal with then is blocking. That’s saying like, “No, I’m sorry you can’t you until I’m finished.” And that’s kind of the mechanism that the database is going to use to help enforce that traffic.

Jos: Yeah, that’s right. I mean, there are essentially two ways that this can be enforced. So first off is you need to make the choice what isolation level is right for your transactions and for your read operations. Usually you pick an isolation level either for you whole application or for individual transactions. But it is even possible to pick for individual read operations. So that is first part, choice. And then in terms of how these isolation levels are implemented. There are generally two ways to implement. One is optimistic way, the other is pessimistic way. So pessimistic is the default or has been the default for the long time. Optimistic way, that has been introduced first with snapshot isolation for kind of general tables. And then later with memory optimized tables also if you pick the repeatable read or serializable you’ll also get optimistic. But essentially the pessimistic model means that you are going to assume that there are going to be conflicts between read operations or different read operations that will get into each other’s ways. We are always going to assume some other transactions go to write something to the database that’s going to violate your transaction isolation which means that you need to take a lock on a row or range of rows. So basically if you pick for example, repeatable read. A repeatable read scenario if you read a row you will always want to be able to read that same row in the same state or later point of your transaction. Or serializable meaning that you essentially have serialization path between different transactions, right, so you don’t see any interference between transactions, meaning in practice if you read a range of rows or you read an entire table, no rows will have been inserted into or deleted from that range. So in the pessimistic model you actually take a look to make sure that nobody can change those rows or these range of rows. Whereas in the optimistic model, in the optimistic model, what you say is, “Well. I’m going to anticipate that there are not going to be any conflicts between these different operations”, and if there is a conflict so for example two concurrent transactions want to update the same row at the same time, you get a failure right away. If there is some concurrent transaction that commits but violates that serializable isolation then your commit fails. An optimistic model does work well if you don’t have to read conflicts and the pessimistic model works well if you have conflicts. But then of course you have these locks, and especially if you use serializable you can have lots of low ranges of data and then you can have transaction that start to wait for each other.

Carlos: Right, and this is again some interesting ideas that have been floating out there. Steve and I were chatting about this before we get started here and so you mentioned SQL Server is using a pessimistic by default, right? So that isolation level read committed and we know that it wants to protect the reads that are happening. So we are going to start putting locks there then we start getting blocking. Well, we have this, I don’t know, if it’s secret sauce. But there is this idea out there that people can use a function called no lock and that’s somehow is going to get by. It’s going to ease their burden and they are not going to worry about all these things.

Jos: I think when we start talking about this level then you are already kind of assuming that you are already using a pretty low level of transaction isolation. So earlier while we were talking about transaction isolation we were talking about higher isolation levels like serializable or repeatable read. Those are pretty high isolation levels. When you get actually to the lower ends, when you get to read committed which is the standard isolation level in SQL Server. It’s default. That is the isolation level where you still have some integrity in the database where you avoid dirty reads. So every wrote that you read has actually been committed to the database that is why we recommit it so you have a minimal level of consistency. You can still have locking and blocking because when you’re updating a row, you need to take a look on that row so that nobody reads it. But at the same time I will throw it here as well that we do have read committed snapshot isolation which is not used by enough customers of ours. So locking and blocking, read committed snapshots. Unfortunately, there are some horror cases where the application can’t observe differences of behavior between read committed snapshot and traditional read committed. But in most cases you’ll be fine so that is what I would point at first.

Carlos: So I guess what we are suggesting here is that if your organization as it’s rule is using no lock everywhere by default, you probably want to take a look at your isolation levels before you keep slapping that on all of your transactions.

Jos: Yeah, so first I would say, look at the read committed snapshots. What happens is, so in SQL Server, read committed snapshots came to SQL Server in 2005. Unfortunately, because we have legacy it is still not the default. But if you create a new database in SQL Server, read committed snapshots is still of the database. Now, what does it mean to have a read committed snapshot on? Read committed snapshot means that every individual query that you run within a transaction, each individual query operates all the snapshot of the database, the snapshot as of the start of the execution of that query. This means that if there is some rows that are being updated by a concurrent transaction you are actually reading older version of that row. Which means that you can just continue reading your blocks by updates that are happening at the same time.

Steve: Now, that row versioning of keeping track of those older versions of the rows that’s done in TempDB, is that correct?

Jos: That’s correct, yes.

Steve: Ok. I mean, one of the thing that I’ve ran into as I’ve come across database is where I think this will be great to run in read committed snapshot isolation level because people are already doing with no lock everywhere and it would be great to make it so that they didn’t have to do that. But then on those the same systems because they are already overloaded a little bit to begin with and that’s why they started using the no lock, they find out then that I think look at it in the TempDB is their biggest performance bottleneck. And I guess how big of an impact will turning RCSI on. I assume that going to put quite a bit more load on TempDB and if TempDB is already a bottleneck that would not be a good idea to turn on.

Jos: Well, indeed. It push load on TempDB and if TempDB is already a bottleneck then, yes, you could potentially have issues there. However, usually locking and blocking in a user database is often a different performance or a different performance issue from TempDB bottlenecks. So indeed, if you have lots of TempDB bottlenecks and on top of that you have these blocking bottlenecks in the user database. Then yes, maybe it will not help you. Maybe TempDB will become an even bigger performance bottleneck.

Steve: But if you have locking and blocking in your user databases and TempDB is not overloaded and that’s a good scenario to turn on the read committed snapshot isolation level.

Jos: Exactly.

Steve: Ok, I just want to make sure I got that right.

Carlos: That’s where we’re looking at things. I think, Steve, to your point the idea is like you’re going to trade one problem for another, right? So I’m going to trade my locking and blocking for potential TempDB issue contentions. As long as I knew that’s that taken care of. Good current feeding there. Maybe you’ve move that over to your SSDs or whatever is going to flip your boat there. There is no magic bullet. I guess the idea is that you want to make sure that you understand the trade-offs and that you’re prepared then to deal with them.

Jos: I do want to put another point to this and there is a difference here over the utilization between read committed snapshots and general snapshot isolation. Because read committed snapshots, you use that snapshot only for the running of single query not for your entire transaction. If you have traditional snapshot isolation you use for the entire transaction. So if you have traditional snapshot isolation, you need to keep the row versions in your TempDB for the entire duration of the transaction, and if you have long range transactions in the system which lot of strain on TempDB. But if you use read committed snapshots you only need to keep the version for the duration of the query which means that there is a lot less pressure on TempDB compared with whole snapshot isolation.

Steve: That’s a great point.

Carlos: I feel like the read committed snapshots however is an introduction to the in-memory isolation levels.

Jos: To some extent, so if you look at isolation levels which are supported by memory optimized tables. So memory optimized tables supports snapshot isolation, repeatable reads and serializable. Read committed is actually not supported, so snapshot isolation. But with memory optimized tables the row versioning is not done in TempDB instead it is done in the memory space of the user database. It is build into the table data structures so the row versioning is a lot more efficient than for traditional disk-based tables.

Steve: So then with the read committed snapshot or the snapshot isolation levels, those could be turn on in existing databases without changing the structure of the tables. But for the memory optimized tables that’s involved in changing over to the Hekaton or the memory optimized style table.

Jos: That’s right, yeah. And when talking about the way this isolation levels work for memory optimized tables, they are optimistic model. So first is you have the basic snapshot isolation on memory optimized tables that works functionally very similar to disk-based tables. So you see a snapshot of the data as of the start of the transaction. If there are concurrent rise let’s say row, you have conflicts that are being detected, throughout your transaction you always read from the same snapshot level of the database. Now, where it gets interesting with memory optimized tables is for repeatable read that are serializable isolation levels, how they are implemented, because they are implemented using the optimistic model which means that you have the same guarantees. So repeatable reads nobody has change, nobody has change this row between the time you read it and you commit the transaction. For serializable you essentially don’t see any other transaction committing data in any of the ranges that you read from. Those are the basic guarantees.

Carlos: Now, you’ve actually been bringing up the serializable and repeatable read quite a bit, but you also mentioned the read committed snapshot something that probably more customers should take a look at. Now admittedly, I think may have had one database that I have to put in repeatable read but I’ve never work on a database that has been in serializable. How common is that. I mean, I feel like I only know it because it will only be used for exams and stuff.

Jos: Yeah, that is true. So if it’s serializable is the only true transaction isolation if you want to look at from a purist kind of point of view. That is where the research comes from. But then when you hit reality then you have performance, right, and concurrency. With serializable, with the pessimistic approach where you take the locks, then you have this range of rows that you’re reading and if your query does a full table scan you take a look on the entire table. And between that time and when you commit a transaction nobody else can make any change in that table. So that will be very restrictive so there is a lot of locking and blocking. And if you don’t absolutely need it for this particular read operation then you don’t do it. That is why the lower isolation levels are more popular, that’s why read committed is the default isolation level in SQL Server. And actually one fact that not everybody realizes is that you can actually pick and choose isolation levels on a query level and even on the table level within. So within your query you can add a hint to the table and say, “The read on this table need to be under serializable for example but the other tables can be read under lower isolation level.” So you could say for example, my accounts table I always want to read that under serializable but other tables I can read them on the lower levels.

Steve: Well, so then with that, I mean independent of what your overall database isolation level is if you that control on a table by table basis then you can, as long as you understand what all those mean. You can get the behavior you need between different tables where you have one table that no lock is ok. You might have another table where you need serializable and make it sound how often things are changing, right? Is that correct?

Jos: No lock is ok. I think there are very few scenarios where no lock is ok. There are some cases that, I’ve heard of some cases where you have an application that for example has two connections of the database and one connection needs to see operations that the other connection did in the database without it actually having commit the transaction. And in that case, no lock or rather I would prefer people use read uncommitted in that case because then you’ll actually say what’s your intention is even though implementation-wise it’s the same. But you will actually say what’s your intention. Then that kind of thing might be ok but otherwise it is usually use as workaround around locking and blocking. So semantically people don’t like to read dirty data usually, right? It is more of a performance workaround. I would say rather read uncommitted, consider read uncommitted only if read committed snapshots puts too much pressure on your TempDB or your application somehow cannot deal with the snapshots semantics of the individual queries.

Steve: Ok, thank you. And perhaps I was baiting you a little bit there with no lock comment because I am a big opponent of no lock. It causes so many issues from what I see in day to day organizations. There is so much misunderstanding from people out there around you just turn it on as to go faster switch. And that is really not the right behavior of it.

Jos: Yeah, read committed snapshot does the go faster switch.
Steve: Absolutely, alright. Love it.

Jos: It’s always good to please your host.

Steve: Well, no. I guess what I said I love it, I love it because now I can refer back to this section of the podcast and help solve debates that I’ll have in the future.

Jos: Let’s talk about, besides no lock which I guess you should never use. There are a number of locking hints that people would be familiar with that are useful in some cases where you can say, “I want an update lock on this row” or “an x lock on this row”. I would suggest before going dead route because these locking hints are typically used as to pick and choose on the query or statement level or table level what you actually want. So I would suggest before going dead route think about whether you can solve the same problem with isolation levels because isolation levels actually, you actually declare your intent what you want to do rather than you specify what is the mechanism that the server should use. That’s often easier to understand by the user.

Carlos: Sure, from a troubleshooting perspective when you somebody come in and take a look at it. They know kind of from a system setting like you mentioned here is my intention rather than having to then find a query, oh in this scenario it’s different.

Jos: Yeah, exactly. I do want to acknowledge that there are always scenarios, there are always cases where people do have to resort to a locking hints but I would say do that as a last resort.

Carlos: Sure. I mean you have that in your bag of options but don’t make that the first one probably.

Jos: Yeah, exactly.

Carlos: Ok, so shall we go ahead and do the SQL Family?

Steve: Yup. How did you first get started with SQL Server?

Jos: I got started with SQL Server, when I started to work for the SQL Server team. That was in October 2010. I moved to Seattle and started working in Redmond on SQL Server query processing.
Steve: And you had not worked with SQL Server before that?

Jos: Nope.

Steve: Wow! Ok.

Carlos: So what made you make that leap, I guess you know when you’re applying for this job and it would be like, “Hey, you want to come work on our database?” “Sure.”

Jos: Yeah. I was working in Academia before. I was working at a university doing research. Doing some teaching, but I’ve kind of decided I wanted to move to the industry, product development to kind of have a more immediate impact on the world and also work in a more closely and collaborative teams. And I want to work in interesting technology, so impact, interesting technology, as SQL Server needs to build there. I knew someone from university who has moved to the SQL team earlier. He told me about an opening. I applied and that’s what happened.

Carlos: There you go. Now that’s interesting. Ok, now I understand why where all the serializable comments came from. Obviously, you are working on a team, right? Maybe we won’t ask what you would change about the SQL Server but maybe we’ll ask you, what’s the next thing you’re working on?

Jos: I actually find the first question, I find it interesting. I do want to give an answer for what I would change in SQL Server. Something I would change if there were no legacy. I guess I know it’s making changes while there are legacy applications very hard but I really wish we could change the default on a lot of things. Like for example the ANSI session settings, like ANSI_NULLS, ANSI_PADDING, all that stuff. I would like to put read, the snapshot that we talked about as the default and a number of other kind of best practices that people have developed over the years. I would like to be able to make those the default to SQL Server. But it’s very difficult because we have a legacy and we cannot break legacy applications of course because it would ruin our reputation.

Carlos: Right, sure.

Steve: Very interesting. Ok. So was there something you want to talk about what you’re working on next or do you want to jump into the.

Jos: No, let’s jump to the next one.
Steve: So what’s the best piece of career advice that you have ever received?

Jos: The best is, work on something you’re interested in. That is the best career advice I’ve received and it’s also the career advice I would give, always do something you find interesting.

Carlos: Our last question for you today, Jos. If you could have one superhero power what would it be and why do you want it?

Jos: What would it be? I think that’s pretty for me. I would like to be able to slow down time or stop time for everybody besides myself so that I could either do some extra work or more likely take a nap while nobody notices.

Carlos: True superhero power, right? You want to take a nap without anybody noticing.

Steve: I would like to take advantage of that one myself.

Carlos: Well, Jos, thanks so much. We do appreciate the conversation and thanks for giving us some of your time.

Jos: Yeah, thanks for having me on, always a pleasure.

Episode 121: What makes a good manager?

I have heard it said “People don’t quit jobs–they quit managers”.  At a recent event, I decided to ask people what they thought the qualities of a good manager are.  Perhaps you aren’t thinking of getting into management; however, at some point in your career you will have the opportunity to lead.  We share your thoughts and comment on the challenges of having the qualities of a good manager.

Episode Quotes

“The best trait in a manager is someone who can give you enough strength to do your job.” – Doug Parnell

“The best attributes for good manager is trusting their good people to do the things that they hired them to do.” – Matt Gordon

“A good manager sets clear expectations. Understand how to hold things accountable and doesn’t micro manage.” – Royce Cavitt

“A good manager is someone that can lead and direct the team to success… a critical management skill is communication.” – Jim McCullen

“You also have to build that trust as the manager or between the manager and the employee.”

“There is an old expression it’s called ‘complain up’, if you got something to complain about, you should bring that to your manager.”

Listen to Learn

00:26 Brief introduction about the episode topic – What makes a good manager?
00:58 Companero shout outs
01:59 Reminder on “Tips and Tricks”
03:56 Upcoming Database Health Monitor webcast
04:50 Show notes links
05:24 Interview recordings about attributes of a good manager
15:10 Priorities perspective of a manager and the manager’s boss
23:02 Empathy and Accountability
26:25 Importance of communication
29:14 Relationship and Trust

Transcription: What makes a good manager?

*Untranscribed introductory portion*

Carlos: Companeros, welcome to another episode. This is Episode 121 and it’s good to have you on the SQL Trail again.

Steve: Today’s topic is on “What makes a good manager?” That’s an interesting one.

Carlos: That’s right. So this was when I go to SQL Saturdays and I like to try and get people’s thoughts about different things and I was in Charlotte. I guess, it’s been a month or two now. I thought, hey let’s talk to people about what makes a good manager. And then this topic evolved a little bit for me and I will get into that as we begin our discussion.

Steve: Alright, but before we do that do we have any companero shout outs this week?

Carlos: Yes, so we’re going to mention a few people obviously the folks that we’ve talked to and we are appreciative to those who have lend their voices to this episode. It’s interesting however, I have been thinking about it but it was vocalize actually by another blogger. And they were specifically talking about Twitter, but it seems like social media in general, the idea of the “like” button and how engagements tends to be a little bit lower but likes continue to go a way up. How do you kind of judge that when you put consent at things? Obviously companeros we are very interested in hearing from you and your thoughts are not stupid and so obviously we’re appreciative to those who are willing to engage with us. You know, drop us a line, whatever that might be.

Steve: Oh yup, definitely. We always like to hear from people.

Carlos: That’s right. So a reminder, we’re still collecting tips and tricks. We want to start this new segment in January and we invite you to leave those comments either via social media or on the website of the podcast page. You could leave that and we’ll put them on the mix and we’ll start that up in January of next year.
Steve: Yup, so what we’re looking for there is really, what are your ideas on things that you know that other people don’t always know when you’re getting around in SQL Server or Management Studio?

Carlos: Just let us know. You will always be surprised what people don’t know.

Steve: I see this like if you’re sitting next to someone at a keyboard and you’re helping them work through something, and they are doing something the hard way and you try and explain how to do it in an easier way. Sometimes it’s not that easy to explain and we’re going to try and take some of those on.

Carlos: That’s right, exactly. I even think, and so it was interesting, we were actually talking about a function today, right? So functions might sometimes play into this as well. If you have a favorite function that people ask you about. Like, “Hey, why are you using that?” We would love to know that too.

Steve: Yeah. I definitely agree on that. There is this stuff function in SQL Server and I was explaining it to somebody years ago and she said, “Well, stuff, what’s the real function you’re using?” She thought I was just using the word stuff as a placeholder for a real function name. I came back to point out that well there is indeed a SQL server function called stuff.

Carlos: There you go, perfect example.

Steve: Yup. So we also an upcoming Database Health Monitor webcast on the 14th of December.

Carlos: That’s right, so for those of you who are catching this early there is still time to register. Obviously if you are listening to this after the 14th you can watch the video recording when it comes out.

Steve: Yup, and that will be at 10AM Pacific or 1PM Eastern on the 14th. And we’ll be going through and talking about the Quick Scan Report in Database Health Monitor and things you can do there. And you can find the link at sqldatapartners.com/webcast. You can go there and sign up now.

Carlos: Yeah, so we are looking forward to putting on more of those and hopefully doing those this second Thursday of every month. Let’s see, so for our show notes today, you can reach us at sqldatapartners.com/manager.

Steve: Or sqldatapartners.com/121 for the episode number.

Carlos: Yeah, so as I mentioned, we started by getting some people, we’re kind of man on the street, took my recording equipment and went to Charlotte for the SQL Saturday down there in October. And so collecting some thoughts, let’s go and listen to those and then we can go from there.

[START OF INTERVIEW RECORDINGS]

Gita: My name is Gita and I work for a financial institution. And as the question which you are asking the best attribute. I think the manager can understand your strength and he gives you the opportunity which can explore you and make you shine where your strengths are. Also, he develops like a very good trust relationship where it’s not like a micro management. He gives you more responsibility to do it effectively by yourself without managing if the things are happening. And if you’re working without your manager managing you, like micro manage you, and still you’re working then that’s a best manager.

Doug: Hello! My name is Doug Parnell. I work for Elon University. And the best trait in a manager is someone who can give you enough strength to do your job, do some research, but then pull you back in when there is a high priority item or a task need to get completed. I might have that now and it’s great. Give you some freedom but also, I know when there is an email sent, or that passing hallway conversation. Like, “Oh ok that deserves my attention.” Rally back, get it done and then continue on.

Sandra: Hello! My name is Sandra Peel. I am a software developer at CMI Solutions. I think the best qualities in a boss are integrity and being ethical with at everything they do with employees, customers and any business dealings. Thank you.

Rick: This is Rick Hieges. I think one of the best attributes they can have is for them to listen to you. Although they may not be able to do anything about it at least they can let you know that you’re being heard and showing that you’re important to them.

Matt: Hi! I’m Matt Gordon. I am a data platform solution architect for DMI. So the best attributes for good manager is trusting their good people to do the things that they hired them to do.

Carlos: So what does that trust look like to you?

Matt: Lack of micro management. If you assign a task, you hire them to do it. Let them do it within parameters but let them do it. That’s what you’re there for, that’s what they are there for.

Carlos: I’m going to extend this question a little bit because we have this idea of micro management come up a bit. What then maybe is the responsibility of the employee to help the manager not micro manage? Does that make sense?

Matt: Yeah. Well, you have to earn that trust. I mean, you know, if you’re new to a company generally they’ll let the leash out in sections. And you need to accomplish the task as you go. Even if you’re walking off the street with all the certifications, credentials, experience, you’re still getting use to a new firm, new manager, new structure so you have to earn that trust as you move forward. And if you don’t, then they are not going to let the leash out in that section.

Guest: The best qualities of a manager I would say is somebody who listens and somebody who is attentive to his direct reports. A good manager I would say is somebody who you can sit and have a conversation with. Somebody who is also a risk taker as you and somebody who helps his direct reports, you know, people who reports to him. And somebody who is understanding because sometimes you go to stuffs, and there’s a lot of events happen in life. You know, family, or whatever the case maybe; somebody who is understanding towards that. Work is work, definitely you are here to work, and you have a manager who is really understanding the outside push besides work.

Jeff: Jeff Garbus, CEO, Soaring Eagle Database Consulting. I’ll go for two. The first is that a good manager empowers his employees or her employees to be able to do the task and perform the task he needs them to perform. That means not only do you give a task, but you give the person the ability to complete the task without having to jump to additional routes without curtailing the persons creativity, without putting limits on his or her ability to perform.

Royce: I’ll add. A good manager sets clear expectations. Understand how to hold things accountable and doesn’t micro manage. Allows them to do their job and encourages them to make the right decisions.

Joseph: Joseph Miller, Soaring Eagle Account Executive. A good manage is transparent. A good manager is somebody that cares not only about the task but making their, the person that’s under them, successful. As well as a good manager is candid. Somebody that’s candid with the person that they are supervising or working with.

Jason: My name is Jason. I work for Idera. When I think about managers, I’ve had good managers and I’ve had bad managers. That qualities that I like the most about good managers is far that they empower you. They listen to what you have to say about the things that are your challenges to do your job directly, and they empower you. They listen and they empower.

Vera: I think there are a lot of qualities but I think the main one would have to be understanding because you need to understand what your people look right through. You have to be able to relate to them. So understand and be relatable, so understand what they are dealing with. Trustworthy. A leader, got to be able to lead your people there and relate to them.

[END OF INTERVIEW RECORDINGS]

Carlos: So I also opened this up to Twitter, not too long ago. I mean we had a few people chimed in. Mike Armentrout, a former guest. He was with us on the Quilk episode. So Mike says, the fortitude to run interference, so prioritize work and be focus on without constant firefighting.

Steve: And you use #enforceprriotities on that. So that’s a really good point there. I mean, I’ve seen managers who they claim they have a priority list for you to work on. But you number one priority changes six times a day. Sure, there are some days where that’s just the way it’s going to be. But if that’s your everyday occurrence then somebody is doing something wrong probably. I think that to be able to run interference and keep people focus so they can actually get something done rather than just the amount of trash that you spent shifting gears between projects.

Carlos: Right, and I think again, we’re going to come at this from a manager’s perspective here a little bit but I can see how this can sometimes be tough because we talked a little bit about this because some people just liked to be firefighters. And so this also plays in and can make it difficult from a priorities perspective.

Steve: Right, but I think that in dealing with that you will always need to consider the amount of trash that’s involve where you are switching from one project to another. You want to make sure that whatever you’re working on if you have a new #1 priority, you don’t lose what you’re doing. You save it off so you can come back to it later.

Carlos: And the ability to communicate that, right? You get an email, you see the email modification pop up, and then they just show up in your cube, “Hey did you get my email?” “Ahh, yes, modification just popped up. I haven’t read it, consumed it and have a response for you.”

Steve: You know that’s interesting. It sort of comes down to as well how often do you read email? I know one of the things that I do when I’m heads down actively working on something that I need to get done, well everything needs to get done obviously, but to get done quickly sometimes I’ll ignore my email for an hour or two. People, text and calls, say, “Well, did you get my email yet.” “Well, no, I’m working; in a minute.” I think that the expectation that email is an immediate response mechanism is not always a valid assumption.

Carlos: Exactly. Yeah, you’re right, so culture is kind of plays a role in there, right? So I think are you helping culture when it comes to helping with priorities. So John and Sean McAllen from Minionware, they chimed in as well. They talked about, the ability to listen and to hear. It seem like that was a fairly common response when I was talking with people. So common answers, they don’t want to be micro managed, they want their manager to listen and to help them succeed. And so I thought it was interesting that we tend to think of managers, those in authority as people that are put there in a position to help us. I’ll say a bit of narcissistic view, almost innocence. Maybe not quite but that idea is what I think you should be doing is helping me, and it’s easy to think that way because obviously you report to them. You have responsibility to them and things like that. They have a responsibility over you. There are things that they can do for you that you may not be able to do for yourself.

Steve: But then also, if you look at the perspective of your manager’s boss. Your manager’s boss as view of your manager is that the manager is there to help them.

Carlos: Yeah, exactly. We actually then, sort of another level. So I started asking some of the other managers. I said, “Ok, managers what do you think your role is?” And so we have one voice and then we get into some of the other emails that I got.

Karla: This is Karla Shields, the Executive Director of Computer Technology Institute of Central Piedmont. What makes a good manager is involving your team, engaging them, and being their biggest advocate so that they can succeed in this world of technology.

Carlos: So then I went ahead reached out to a couple of my previous managers actually and just asked them. I said, “What do you think it is to be a good manager?” And one of them said that management is about watching the right things and supporting at the right time which I thought was kind of interesting. That idea made me think a little bit about almost like an alert or like a dashboard. You kind of get a pulse, or a beat on what’s going on and then respond accordingly. Just kind of take that scenario even further is, are you a good alert? Or are you the kind of alert that’s eventually is going to get because you’re popping up all the time.

Steve: Right. Are you giving your manager the right information so that they can support you at the right time?

Carlos: Exactly. And I know a lot of times, and that’s very difficult. There is a balance here, right, because I know for a lot of folks. We’ve talked about this kind of subject before. A lot of folks are only kind of feel like they’re having that discussion at the annual review. And that’s just not enough, right? That’s like, I am not watching the dashboard. But then if you’re constantly popping in or sending emails everyday and say, “Hey, what’s my status? What’s my status?” You know, that’s probably not helpful either. I guess, how much information do you give so that they can feel like the know what’s going on without also then giving them and say, “Hey, by the way I like micro manage me.” I mean more hands on than maybe you’re doing.

Steve: That’s right and I think that’s something that where if a manager is having a regular meeting with each team member individually, like more often than their annual review; and whether that’s once every other week, or once a month, or once every six week, who knows. I mean, I’ve seen it and any combination of those. It’s a good way to be able to talk about the manager to be able to talk and say, “Well, what do you need?” or “What can I do to help you?” But it’s also from the employees perspective, it’s a great time to be able to push the manager and say, “What can I do to better support you?”

Carlos: Exactly. There is that two way street.

Steve: I used to be a big, when I was doing a lot of management, I was a big believer that when you do an annual review there should never be any surprises. Everything on there should be things that have been talked about throughout the year. That’s not always the case. There is a lot of surprises when it comes to annual reviews.

Carlos: Yeah, unfortunately. Ok, so another unto my managers. Ok, so a few things that he has learned, empathy, compassion go a long way. I thought was kind of interesting. And then the second one, accountability, holding people accountable helps keep everyone honest including yourself. And those two things almost are a little bit combatant in a sense, empathy and accountability. I’m not saying they are opposites but they can seem like opposites when you’re one spectrum or the other.

Steve: Right. Oh yeah, and I think that there is the right place for both of those. I mean, with the empathy and compassion there are certain things that are appropriate as a manger and certain things that are probably very inappropriate as well. I can think that, I don’t know, it’s the difference between like something tragic has happened in your life and you’ve come in late today because of that or you’re in a car accident, that’s something tragic your late versus someone who is continuously late for meetings or habitually I guess. Looking at that, I mean, that’s one of those that I’ve seen and I’ve probably done it myself the job and a bad job probably on both sides of those.

Carlos: Yes, that’s hard, right, and they are learning too. I guess that is one of the interesting things. I mean, just like we, not all of us, myself included. I’m definitely in this camp, I didn’t become a DBA because I had deserved it. It kind of fell to me a little bit in a sense, and managers are sometimes the same way, right? There is a need and they get caught up in it.

Steve: And you know on that I think there is a lot of people who end up as managers because that’s sort of the career path that’s set. And that you’re a stellar technical person.

Carlos: And you’ve been around for a while.

Steve: And you’ve been around for a while and a manager leaves, and the team grows they need another manager and they say, “Oh, grab him or her.” They’ve been here … And sometimes you’re taking your absolute best technical person and putting them into a role that they may not want to do but then oftentimes they associate pay raise with that, so suddenly they want to do it even though it may not be something that they really want to do.

Carlos: Or really great at because that’s different. I mean the things that we’ve been talking about soft skills, building culture, all these types of things have nothing to do with keeping databases up or maintaining things like that.

Steve: Yup, absolutely.

Carlos: So Jim McCullen who is a former guest when we did our CIO Panel – Episode 54 came and he said, “A good manager is someone that can lead and direct the team to success.” And then he gave a couple of follow up or ideas around how that could happen. So in order to do that they need to clearly understand the goal to be achieved and they need to understand how to motivate and communicate with their team and with the stakeholders. Like, ok, communication, big deal. He goes on to say, “I think a critical management skill is communication. When a project fails or a team gets demotivated it is usually due to lack of communication.” Now this was interesting because nobody really brought this up. They all talk about listening and listening is part of communicating but I think kind of the outbound stuff can also be important.

Steve: Right, and I think with that too a manager perhaps with too many direct reports that many meetings combined with that may not always be able to put out the communication that’s needed there which leads to that demotivation that you’re saying or that was mentioned.

Carlos: Right. I think it can also be tough for me, because like that’s just saying for example, let’s say you have ten reports which is probably pushing it. But some teams are that large and they have conversations with two or three people because they get along with them or whatever. They’ve communicated some idea to them and then the team kinds of disseminates that is not the same as me the manager communicate with all ten however that might be whether that’s email, team meeting, or whatever it might be.

Steve: An interesting quote that I heard many years ago when I was actually, I think it was in a management training class. The quote said, “People don’t quit jobs, they quit managers.”

Carlos: I’ve heard that.

Steve: I think that, I mean, it’s so true because oftentimes you might love your job or hate your manager and you don’t want to be there. Or you might hate your job but you love your manager so you’re willing to put up with the job you hate because of that.

Carlos: Right, because you know they’ll go to bat for you or whatever.

Steve: Yeah, and I think that could make all the difference in the world. And I think that, it’s just one of those that I think about is people quit don’t jobs, they quit managers, and just from the employee perspective or manager perspective that keep that in mind.

Carlos: Yeah, that’s right. I think an interesting idea here is what makes a good manager is that relationship and then of course it takes two to tango on that. So you’re going have to put in some work into the relationship. It can’t be just one sided, and the same obviously as for the manager. Don’t be afraid to give that input. Talking about culture, things that have worked for you in the past if that’s open and they are willing to receive that. But at the same time we have to recognize that they have a lot on their plate as well.

Steve: But you also have to build that trust as the manager or between the manager and the employee to a point that you can be open. You can say, “Yeah boss. I got a problem with this.” And no, it is not going to turn around and twist it on you for not getting your job done or whatever it may be. I’ve seen some managers that when you bring up something like that they do everything they can to try and help you whatever it is you’re having an issue with, whether it’s technical or soft skills or personal or whatever. But I’ve seen others that you say, “Oh, I’m having problems with this”, so they write it down as goals – must fix this problem. And then you’re expected, ok, if you don’t fix that problem you’re in trouble. And that’s just sort of burns the bridge of trust there I think.

Carlos: Yeah, so that idea takes me back to Episode 110 talking with Richard Campbell – Building Trust with My Team. Yeah, that does take time and you have to make some investments there.

Steve: Yup. I can remember a manager, just a short story on that, who really burns the trust bridge really quickly, and this was probably 15 years ago. We had a web conference or go to meeting type call going on. And we get into the call and we get talking about ok who’s here. I was in the role of a team manager at that point and then the VP type manager was above me at that point. And I have four or five people on the call. And I was on the call, the team was on the call, the VP was supposed to be on the call or my boss but he wasn’t there. So we did the whole call because we figured, ok he’s not there we’re going to proceed without him. And we get to the end of the call and as soon as the call ends, he phones me directly and just rims me out for how I behaved on the call, whatever, for how I’ve worked with the team on the call.

Carlos: So he was listening in but did not state that he was there.

Steve: Yes. There were several times that during the call it came up with things that he should have chimed in with had he been honest about being there but instead he was just there to sort of entrap people, to get people. So rather than just jumping in and saying, “Well, what about this, what about that?”, he just kept notes and afterwards he came back and just sort of made a list of here’s all the things that he didn’t like that I did on the call.

Carlos: Obviously, we would probably not include that in the list of good manager.

Steve: And that was interesting because I quit that manager. And in fact, the company I was working for resigned and when I resigned I said, “I’m happy to work here but I will not work for this person anymore.” And they reassigned me to a different place a. And I ended working there for couple more years and I enjoyed it.

Carlos: Interesting, yeah.

Steve: You got to be able to sort of be understanding but also to realize when the person you’re working with cannot be trusted.

Carlos: Yeah, you cannot be there. And that’s the topic that we, I guess again, we kind of get into later is that your manager is not showing the option. Obviously you need to built trust but going to an HR if your organization is large enough to have one, and most at least have someone responsible for payroll, you know, nothing else, a shoulder to cry on. But having some of those avenues to talk about I think one thing that can be damaging to culture into that trust is to start talking about your managers kind of behind their back, that water cooler talk because you’ll never know if that’s going to get back to them and it can be really damaging. You may not like it, voice it to the manager but don’t voice it to everybody else and tell them how crappy they are, won’t do you any favor.

Steve: Yeah, there is an old expression it’s called “complain up”.

Carlos: Oh, there you go.

Steve: Where if you got something to complain or gripe about, you should bring that to your manager. Not take it to your peers or co-workers.

Carlos: Right. And I remember, now this was for a startup and they were going through some growing pains. I think they were, I guess I can’t remember exactly how many people. But I want to say there are about 40 people at that point. But they were growing, they were moving to a new office space, things like that. And I remember one of the things they always keep asking for was for patience. You give us a suggestion, you give us a feedback, just because we don’t turn around on it in 10 days doesn’t mean that we forgot about it. And that could be one of those things too, very similar to that, again back to that dashboard alert. You want to give that feedback again if you let some time pass, if enough time is passed and you feel like you haven’t got a response. Feel free to try that again and say, “Oh yeah, I’m sorry.” Give us status update, right? Get that pulse and let your manager know that you’re still thinking about it.

Steve: Yup.

Carlos: Well, interesting. Thanks everybody for chiming in, kind of giving in some of your thoughts. We do appreciate it. We like to have this kind of collaborative episodes and just another good example.

Steve: Yeah, definitely. How do we call it? The non-technical, sort of the more soft skill side of things topics have been interesting lately. I think we’ve done a few of those over the last several months. It’s just a different take on things. I like it a lot.

Carlos: As always you can give us your thoughts and feedback on social media. You can hit me up via email, carlosl[email protected] You can reach out to us on LinkedIn. I am at Carlos L Chacon.

Steve: And I’m on LinkedIn at Steve Stedman. And we’ll see you on the SQL Trail.

Episode 120: SQL Injection

Shouldn’t they have fixed that instead of putting out all these new features?  That might be what you all thought when you saw the title for today’s episode.  SQL Injection is still a big deal in today’s databases and we are pleased to have Bert Wagner on the program to talk with us about how it can affect you and the applications you protect.

One of the most difficult aspects to deal with SQL Injection is to decide who is responsible for dealing with it?  Bert does a great job giving us some insights on what he has seen work.  We invite you to give us your comments about how you have gone about trying to evade a SQL Injection attack.

Little Bobby Tables

Episode Quotes

“SQL Injection is essentially when you have a dynamic string that you create in SQL that’s getting executed and it ends up doing something that you didn’t intend to do.”

“When it comes to security it never solely depends on one person.”

“It doesn’t even matter if your database is kind of public knowledge or not, someone is going to be able to guess it.”

“The best thing you can do to protect yourself against dynamic SQL Injection attacks is just get rid of dynamic SQL.”

“Once again injection attacks only can happen with dynamic string execution.”

Listen to Learn

00:04 Introduction of the guest speaker (Bert )

00:38 The famous SQL Injection meme

01:19 What is SQL Injection and possible SQL Injection attacks

02:45 How to know if there is SQL Injection attack in your system?

07:43 Thoughts about dynamic strings, sp_executesql, dynamic SQL

10:38 Dynamic SQL and parameter sniffing issue

16:37 Misconceptions about SQL Injection

23:20 SQLMap

23:58 Tips on how to prevent SQL Injection

34:21 SQL Family Questions

SQLMap

About Bert Wagner

Bert WagnerEver since watching hackers first try to break into his website in the late 90s, Bert has been fascinated by the world of security. When not building secure web applications and working with SQL Server by day in Cleveland, OH, he enjoys blogging and vlogging about SQL Server at bertwagner.com. Away from a computer screen, Bert is an avid outdoorsman and all around do-it-yourself-er.

Transcription: SQL Injection

*Untranscribed introductory portion*

Carlos: Bert, welcome to the program.

Bert: Hey guys, thanks for having me.

Steve: Yeah, it’s great to have you on the show. I know we chatted at PASS Summit at our SQL Trail event. I think that was a lot of fun, good to meet you.

Bert: Yeah, it was a lot of fun.

Carlos: Yes, and this is one of the very cool things about having a podcast like this is that we get to have a little bit of a pleasure, right, it’s the Trail Mix and then do a little business as well and talk about our favorite subject which is SQL Server, so we are glad to have you on. Now ultimately, our topic today is SQL Injection and I’m reminded of the meme out there, and it’s a stick figures, but you get a parent or something on a phone and then you see the caption, it’s like, “Why did you name your table or drop table students;”, right?

Steve: Yeah, why did you name your son that?

Bert: It was a famous little Bobby Tables, right?

Carlos: There we go, little Bobby Tables, that’s right. And so that might set the stage a little bit for that idea of SQL injection. Talk to us about SQL injection. What it is and some of the problems and then we’re going to start from there.

Bert: Sure, so SQL Injection is essentially when you have a dynamic string that you create in SQL that’s getting executed and it ends up doing something that you didn’t intend to do, right? A user is passing in some parameter value that is then changing the content of that dynamic string that you built and is causing the query to perform an action that you weren’t originally intending. In essence, that’s what is a SQL Injection attack is, to give an idea from the minor things that can be done obviously. It could be something not very malicious at all. You could inject just random code that won’t really do anything just to maybe test out if the server is vulnerable to SQL Injection or not. Then on the opposite end of the spectrum, you can go all the way to querying system tables to learn more about the data, or querying receiving the full content of other tables. You could modify and manipulate data so it’s not just read-only. It’s really any command that you can think of you can potentially execute through a SQL Injection vulnerability.

Steve: Interesting. Now, with that, I mean if somebody, seems like there are two categories there, one category that could do damage and there is the other category that they are just browsing, and they are borrowing, they are stealing, they are taking some inner data. And I think with that, is there necessarily any way to even know if someone has done that to your system? If there was a vulnerability there to know if anybody ever hit it?

Bert: Right. I mean, so the only way you’d be able to tell is through logging. If you are having users input, free form data into a website or your application which is then maybe kicking off a store procedure, some adhoc query. If you’re logging that information you’d be able to tell. But if you’re not doing that kind of logging then you might not know. It really depends. It’s not just knowing whether you have an injection attack or not becomes a big issue because if you don’t know then you don’t really know the validity of the data that is in your server, right? A lot of times people think SQL Injection, why does it matter and the first big thing that comes to mind is someone is going to steal all our database data. They are going to steal our usernames, our passwords, out highly sensitive data about our customers, and obviously that’s a really big problem, but that’s not the only problem that you get. Like what you’re alluding to, data validity might be a problem if you don’t know that someone is maybe manipulating data on your server. You’re running a shopping cart and they want to give themselves a really good discount so they are updating the prices of your products table

Carlos: Our airline ticket is a little too expensive, right?

Bert: Yeah. I mean, that’s a major problem there if you don’t know what’s happening. There is no guaranteed way of finding out. And then to just round it off, another major issue with SQL injection is just that availability of your server or your application. That’s another thing most people don’t think of is if you’re able to write any kind of SQL code you want and inject it, you could potentially write code that will tie up your server or potentially disrupt access for other users if you just lock everything in the database and no one else can access your app. That’s downtime for your application and that causes another big problem too.

Steve: Yeah, that’s interesting because when people start chasing a performance issue or blocking issue. I mean, very rarely you ever think, “Oh, could it be a SQL Injection attack”, that somebody is messing with you.

Carlos: Right, and to that point along with that so, who ultimately then is the owner of this. Now from our listeners, most of our companeros are data folks in general. We do have some developers out there that listen but I can see this very quickly pointing to into a, “Well, that’s not my problem.” That type of issue. It’s almost like a security issue. No, it’s whoever is writing the start prox issue. And so I guess, maybe we should start digging into, from a DBA perspective, how can I know or which I’ll be looking for to see if SQL Injection is even a problem for me. Is there a good test there?

Bert: Yeah, I guess to answer your first question with finger pointing. I feel like when it comes to security it never solely depends on one person.

Carlos: Sure. It takes a village.

Bert: It takes a village with security. The more layers you have, better off you are typically. And so whether you’re a DBA and you have injectable in a store procedure that’s on one of your boxes, obviously your are responsible for that even though it could be a developer who wrote that could. But it should also be there responsibility to not write this type of code. And then there you could have people if you’re in a large company who has whole groups devoted to security, it should be on their radars too if they are running different pieces of software that profile maybe, types of data that’s going into your servers. It’s on them too. It’s really I don’t think any one person is responsible. I think we are all responsible. And in terms of a few, maybe if you are a DBA and you’re getting a new server. You don’t know what’s on that server. You want to see if you’re vulnerable, right? Because last thing you want is getting an email saying, “Hey, why did you cause our data to get lost?” Yeah, I mean, there are a few different things you could do. None of them are 100% foolproof. You know, I’ve written some scripts on my blog that basically look at various system use that search for queries inside your procedures and functions that may have dynamic string execution occurring. You could pretty easily search the definition of prox and use everything else to see are dynamic strings being executed and then that will help you narrow down where you could start looking to see if you have injectable code.

Carlos: Ok, so I’m looking for “executesql” in my store procedures and then I could start testing there. Is that basically the …?

Bert: Yeah. The reason that’s not foolproof is even though you can say, find me where my definition text is like “execute” or like “sp_executesql”. One, if you find things it’s not necessarily mean that they are injectable. But two, it doesn’t count for all the adhoc queries that might be coming to your server. I mean, that’s only searching your procedures but if a developer has hard coded a SQL query into their app, you’re not going to catch that in the system definitions there.

Carlos: Which I think is not trivial because I think about all the ORMs, right? That’s 90% of what they are doing is creating that code for the developer into the database.

Steve: So just show me back there to the whole sp_executesql place for instance. I mean, one of the things that I ran into a problem a couple of years ago. I was dealing with parameter sniffing issues. And I’ve been to PASS Summit and I thing that was in Charlotte that year and I’ve seen Kimberly Tripp talk about dealing dynamic SQL as a way to work around some of the store procedure parameter sniffing issues you had. I came back and learn from that and adapted some store procedures to it to work that way using dynamic SQL safely with parameters of course. But then immediately everyone jumped on it saying, you can’t use sp_executesql ever because you are going to allow parameter sniffing. Sorry I said that wrong, you are not allowed SQL Injection. And I think that’s one of the sort of misconceptions is that simply using dynamic SQL that’s being executed doesn’t necessarily equate to, yes you are allowing SQL Injection in. Would you agree with that or do you have any thoughts on that.

Bert: Right, so I mean, dynamic SQL exist for a reason. I know, like you’re eluding too, there is a lot of negative I guess association with it because of the injection problem. But there are really good things that you can do with the dynamic SQL like you’re saying, right, parameter sniffing. That’s one way to potentially solve the parameter sniffing problem. There are other things where if you have an application and you need like the ultimate performance to be extracted from it. Sometimes the only way you can get that performance is by writing a dynamic SQL query. Or maybe you need to vary the output of your results set, right? And dynamic SQL is the only way to do it. There is a lot very valid scenarios to use dynamic SQL. It’s just that SQL Injection could be a side effect that you need to be careful of.

Steve: Right, and I think that’s the key there is making sure that where you are using a dynamic SQL that it is safe from the SQL injection perspective.

Carlos: Right, some of the red flags there that you are looking for. Once you’ve indentified a store procedure as being a dynamic SQL like what’s the next step?

Bert: Yeah, so it’s an interesting problem. I personally think that the best way to understand how to protect against it is to fire up your own test database. I mean, don’t do this at work or anything where you might flagged by a security duties at home. Unless you are an info security and that’s your job to test this kind of stuff out. Try it out and that’s the best way you’re going to be able to learn really how it works and how to protect against it. But things you can look for is if you are concatenating parameters into your strings. That’s probably the biggest thing to watch out for because if that’s happening basically you are allowing input data coming from a user, malicious or otherwise, and they are able to append to the SQL string that you’re building dynamically.

Carlos: Ok, so knuckle-dragging Neanderthal that I am. I feel like I have to ask this question just from our previous conversation. The whole reason I’m using dynamic SQL, and very simple example, SELECT * FROM table WHERE parameter = mystore procedure parameter. Right? It will allow people to best set it, so if that’s just one, if it’s equals, it’s ok. But if I’m adding like parameter1 + parameter2, is that where I get into trouble?

Bert: Yeah, so if you think of that exact example you gave where you’re building a string SELECT * FROM table WHERE parameter =, and then that’s all a string and then you’re concatenating in a parameter. That is potentially vulnerable to SQL Injection. And I would argue in that specific example. You shouldn’t even be concatenating a parameter to a simple string like that to begin with. That is a query where you’re parameterizing the value of a WHERE predicate for example. That’s something you can parameterize and use for example sp_executesql t osafely execute. You shouldn’t be using necessarily dynamic SQL to execute that kind of statement to begin with. And that’s a problem that I’ve seen with just people are coming from maybe developer backgrounds not just single amount full disclosure I’m a developer.

Carlos: Oh men! We’re going to have to vet our guest a little bit better, Steve, not just.

Bert: I mean, depending on your background building a dynamic string where you’re concatenating user input values to a query might be acceptable in whatever language you’re coming from. Actually, if your knowledge in that subject is maybe a little older, so it’s not necessarily that people are doing this knowingly or they want to write injectable code. It could just be that their background is that’s the correct way to do it or that’s an okay way to do it, that’s how I’ve always done it. But it’s not necessarily safe secure code.

Steve: Yup, and I think from the perspective of application code that’s making a call into SQL Server. Usually when somebody get started and learns a new programming language or new interface to talk to the database. Usually the examples are there without parameterization, they just show you concatenating something. So when somebody jumps in and just learning it and they haven’t learned the value behind parameters and how to use them. It’s just somebody just doesn’t know any better.

Bert: That’s so true, Steve. I mean, so SQL Injection just as a quick background has been around forever, right? This is not something that’s new. This is not something that’s even in the past decade. This has been around since the 90’s. It’s been around with SQL Server from SQL 6 and 7. It’s been a problem for that long and it continues to be a problem for that long. And I think you’re exactly right. A lot of those beginner tutorials that you follow. They are just trying to teach concepts of here is how to do something or here is how to write a query. And they’re kind of foregoing the whole security aspect of it and that’s unfortunate.

Steve: And whenever I see one of those I always try and go to that next step to understand how to use parameters. Not just from the SQL Injection perspective but also from the performance and reusability perspective.

Carlos: Are there any kind of misconceptions out there that you see around SQL Injection that people are commonly confused with or get wrong?

Bert: Yeah. I definitely interact with people where maybe they are aware of SQL Injection and kind of what it is but they think, “Ok, this doesn’t apply to me for a bunch of reasons.” One of the things I hear is that, “It’s ok. I don’t need to really protect against SQL Injection because the structure of my database isn’t public so my attacker isn’t going to be able to know what to query.” That’s a huge misconception for multiple reasons. One, is that a lot of our databases that we probably use. They have really easy to guess table names and column names, right? A lot of databases probably have a products table or a users table. It doesn’t even matter if your database is kind of public knowledge or not, someone is going to be able to guess it. But then take them step further there is great ways in SQL to find out the structure of your database. Like sys.objects and things like that will just actually tell you that all the table columns in your database. Malicious users know about that and so even if they don’t know the structure of your database they can very easily find it out. Another common misconception I hear them, you know, if I follow up on that is, “Ok, well, I escape my table names”, which I hope you don’t do that. But once again, using something like sys.objects, sys.columns is going to reveal that information. So it doesn’t matter if you columns are called A1, A2, A3. To a hacker or someone trying to get your data it’s not going to stop them all.

Carlos: Now having said that, if, that’s a big if, you’re using an application user that just has read and write to that database. Don’t those objects then no longer available?

Bert: Yes, that’s a great point, Carlos. That’s one of when I’m trying to write secure code and trying to protect an application from SQL Injection. You know, that’s one of the number one things that you want to do for all your code that you’re writing, that’s accepting user input parameters is lockdown that user that’s executing the code to kind of minimize damage. It still might not fully protect you from SQL Injection but it’s going to limit what that malicious user is able to find out or do in your database.

Carlos: Right, so they are going to work a little bit harder which may or may not be their prerogative.

Bert: Right. I mean if you take that user and you only give it read access, there is no way they are going to be able to modify data, delete data, anything like that on your database. They might still be able to read the contents of a table but it will be limited to that table or that schema or that database depending how well you protect that log in there.

Steve: So one of the misconceptions that I came across. I’m just curious what are your thoughts on it might be. But around, let’s say it’s a web system and there’s thousands of webpages that are accessing the database and you got to go through obviously make sure that everyone of those is SQL Injection safe. But one of the misconceptions that I experienced in a management situation was that we found there were SQL Injection problems in a system. We presented it to the management team and their response was, because there were two pages in the site. There are pages that you can see before you actually log-in to the system and there are the pages you can see after you log-in to the system, so we know who you are. And the response was just, “Well, let’s just make sure all the pages that you don’t have to be logged in to see.” And this is a public site used by thousands of people across the world. But let’s just make sure the pages that are public that don’t require a log-in are SQL Injection safe. We’re not going to worry about the other ones because those are log-in users and they would never do anything like that.

Bert: Yeah. I mean, there are a bunch of red flags there, right? But yeah, I mean, you need to protect against SQL Injection everywhere. It depends on your application but I’m sure many people have created multiple Twitter accounts or multiple Facebook profiles. So what’s going to stop someone from creating a fake account into that system, right? And even though they are logged in it doesn’t mean anything. People who want to get into systems are really good at kind of covering their tracks. It doesn’t matter if they are authenticated into your app there unless you’re tying your users to their passports or something like that. You got some very high secure verified application where you’re not just letting anybody register. But even then you still want to protect against injection.

Steve: Right. And this is one where anyone could just sign-up for a demo account or trial account in the system they were in.

Bert: Yes, that’s a big red flag especially because nowadays, it used to be that, another misconception I’ve heard a lot is, “My website, my application is so small. No one would ever try to attack me.” Like I’m selling boutique garden gnomes online and I have 50 costumers a year and they are all really into garden gnomes. I know none of them are malicious, right. The fact of the matter is that it’s not like someone needs to be actively searching for injection vulnerabilities on your site by hand. Like going in to the log in form and trying different things. There are plenty of tools that hackers have to automate that kind of attack and they basically just go scan the internet and having these tools automatically test for injection vulnerabilities just to find which sites out there have them so they can potentially get the data and do various things with it.

Carlos: Yes, scary stuff.

Bert: Yeah, but it’s also cool. So like one of the tools I want to mention is called SQLMap. It’s an open source tool that’s used for automating SQL Injection testing. If you have an application and you really want to test it out, you don’t have to use these kinds of apps. These apps work kind of both ways. They help out the attackers but they also going to help you out on the defensive side to actually test your own applications to see very quickly and easily is my application vulnerable to SQL Injection attacks, so works both ways.

Carlos: Right, very cool.

Steve: Well, I guess if you’ve got someone who‘s listening and maybe this is their sort of first exposure to SQL Injection, the topic of SQL Injection. Is there anything that you might recommend or any tips you may have that may help with preventing it. Like where you would start first if this brand new to you?

Bert: Sure, so usually this is kind of how I evaluate and try to protect against SQL Injection. First thing is, do I need to be using dynamic SQL? Because like we kind of talked about earlier, a lot of times it could just be someone wrote a query that’s dynamic and is concatenating parameters because that’s just the only way they know how to do it. But if you’re just concatenating a WHERE predicate value, you don’t even need to be doing. Then just get rid of your dynamic SQL. You could just pass in a parameter to your query and it will evaluate perfectly fine without needing to build a dynamic query string. So honestly, that is by far the best thing you can do to protect yourself against dynamic SQL Injection attacks is just get rid of dynamic SQL. Always the first thing to check is do you actually need to be using dynamic SQL?

Carlos: Yeah, why was this put in place?

Bert: Just kind of a common sense sanity check. Can I write this a different way and still get the same result without making myself vulnerable because once again injection attacks only can happen with dynamic string execution. If you don’t have that dynamic string execution, you’re good to go.

Carlos: Sure. I guess I do want to make one point where we’re kind of talking about, you know, everybody needs to pay attention. One additional that I had and Troy Hunt was talking about this. This is more of the SSL certificates on small sites. But the idea was that, yes they may not, going back to the garden gnomes. They might not be trying to attack your site but they may be trying to get into your site to then send malicious stuff to somebody else. And that’s even bigger problem because now you get blacklisted and so your 50 customers go to 0. Who’s going to blacklist you and all other stuff.

Bert: Right, that’s a huge, that’s a great point. That was a great blogpost from Troy Hunt there about that. And just SQL Injection in general if you ever want to know numbers, that’s hard to get numbers, but Troy Hunt runs this website “haveibeenpwned” Which if you’re not sign up for, you should. It’s basically a notification and service for your username and data breach that gets exposed. But if you go to their data breach page there you can just do like a CTRL + F find on the webpage and search for SQL Injection. And you will see all of these companies, huge companies. I’m talking like Yahoo! and Sony who specifically have data leak because of SQL Injection attacks. This is like a really serious deal that affects everybody. Sometimes it’s nice, I mean not nice, it stinks for those companies, right, for their users data get released. It’s not just you by yourself. I mean this is a major problem that affects everybody.

Carlos: I agree. And then of course if you are subject to it, that’s not fun. That’s not fun for the managers because you don’t know the extent of it. You may not even know where it is. So then all of a sudden they are kind of throwing money at a problem to try and stop it and they don’t know it’s not worth going.

Bert: Yeah. I mean, so you want to do your best bet. And so option one is just get rid of that dynamic SQL if you don’t need it. After that, let’s say you evaluate your app and you do need dynamic SQL. Like you’re doing one of those things that is valid to use dynamic SQL in your database. The thing you would want to try to do is do something like sp_executesql which will parameterize your dynamically built queries. And so that is the safe way to allow input parameters that you pass in and executed as part of a dynamic SQL query string without falling vulnerable to that injection attack. Now, sp_executesql has its downsides though. Although you can pass in a dynamically generated SQL query into it to execute, you still can parameterize everything with it. So things like table names, right? You wouldn’t be able to pass in as a parameter even if using sp_executesql, it won’t work. A lot of times a table name might be something that you do want to parameterize.

Steve: Oh, and that’s an interesting one. Do you have a good option for how to do that?

Bert: Yeah. The best option would be to use the QUOTENAME function in SQL Server. And that just basically escapes characters by default. If you don’t pass in any parameters besides just the string that you’re escaping at as brackets around it kind of make it a system object name, and that will protect you for sure. The downside to using QUOTENAME. So QUOTENAME is the best solution if you can’t use sp_executesql. The downside is that it is limited to outputting only 128 characters. So if your input for some reason is longer than a 128 character, you need to start getting a little creative with what you do and that opens you up to potential problems.

Carlos: Right, then you start concatenating and putting all these things together. Yeah, you’re kind of back to square one.

Steve: So there is one example I saw where they are passing in a table name that needed to be concatenated in to a string. And there are only 5 or 6 options, 5 or 6 possible table names they could pass in. So the solution they came up with was instead of just concatenated them, it put inside of an if statement that is said, if it’s table name1 or table name2 or table name3 it injects not parameter but the actual text of what that table name was. If it doesn’t match one of those known table names it falls through and aborts of the store procedure.

Bert: And that’s, I mean if you’re able to do that, that is great. And that’s not just on the DBA side but if you’re working with your developers the first line of defense for these types of attacks is the developers. It’s the app code. They should be doing all these things too. They should be sanitizing their inputs. They should be checking that the input data is a valid entry. Like if there is only 6 table names, it should be one of those 6 names and then if you’re able to do that similar kind of check with an if statement in T-SQL then all the better. The problem starts to crop up when you have a more complex input, quantity of values that you’re inputting. If it’s 6 tables or 10 tables it’s pretty to handle. But once you get to the realm of many more than that and you start wanting to write maybe what at that time seems smarter validation functions or sanitizing functions that’s where you get yourself into trouble because it’s really hard to write a function that’s 100% secure that kinds of validates data like that.

Steve: Very good point.

Bert: And so what I’ve seen a lot is people will use like the replace function for example. Once common technique to prevent SQL Injection is to sanitize your input from single quotes, right? Because if you are trying to inject some code, usually that injected code is going to use a quote in it somewhere to end one statement and help start another statement. So what people will do is they will try to write a REPLACE function that replaces single quotes with a set of two single quotes to kind of escape that quote and prevent the attacker from succeeding what they wanted to do. While that works great for some scenarios, it doesn’t work in all scenarios. That’s the big caution with trying to kind of write your own sanitation functions in SQL Server using something with REPLACE because it’s not always going to work. And it’s not always right to think of every scenario that an attacker might try is impossible. Even if you’re somehow able to do it that doesn’t mean that some new feature of SQL in the future is going to stay on top of that forever whoever ends up maintaining your code. That’s just really a big problem there. And like we mentioned, locking down the user account that is executing your SQL queries makes a big difference. That’s something I would implement in all scenarios for sure.

Steve: Oh yeah. I don’t know how many times I’ve seen the web system at places that runs as the SA user. That’s one of the first things I always want to get changed because it’s just so dangerous.

Carlos: Oh yeah.

Bert: Yeah. I mean, that opens you up to everything.

Carlos: Should we go ahead and do SQL Family then?

Steve: Let’s do it.

Carlos: So Bert how did you first get started with SQL Server?

Bert: Well, I started my database expeditions in MySQL probably when I was 11 or 12 years old.

Carlos: Oh, starting young.

Bert: Yeah, just running a PHP website, coding my own log, having tons of SQL Injection vulnerabilities there and that’s actually where I learned. That’s why I learned about SQL Injection was actually with MySQL. I would look at the logs and say, “Ok, what’s all this weird 1=1 input that people are submitting.” That’s my start with databases. I obviously didn’t know much back then. I still don’t think I know much now but that kind of open the doors to get hired to a Microsoft shop where they have SQL Server. Yeah, sure, I know all about relational databases. I’ve built websites using MySQL and so that’s kind of how I got started there.

Steve: Pretty cool.

Carlos: If you could change one thing about SQL Server, what would it be?

Bert: Well, if you had asked me this question a year ago I think I would have different answers. But I’ve been really impressed with how kind of the speed of development has become with SQL Server in the past year. Like that would be my big wishlist item would have been just get more features out faster. It seems like they are doing that. I’m really satisfied with that. I guess the one thing I still like to change is for them to take like a release. Maybe not a major release but just take the time and really polish the existing things that are in there. I’m talking about things like maybe making error messages more user friendly instead of just telling me some data got truncated. Point me to that data so that I know so I don’t have to figure it out on my own. Or if I run out of space.

Carlos: Which line? Dang it!

Bert: Yeah. You know, for me if I’m using a tool everyday those little kinds of things make a big difference into how happy I am and how happy I am to use a tool. So that would be huge for me because I mean all the features are great. I’m happy with them. Mitch is polishing all the rough edges would be great.

Steve: Ok. I like that.

Carlos: What’s the best piece of career advice you’ve received.

Bert: My favorite career advice that someone told me once is I guess the popular one is, “Fake it till you make it.” But someone kind of has their own modified version. There’s this photographer chase Jarvis and he always talks about “Make it till you make it”, which basically just keep doing what you’re doing and eventually you will get to where you want to be just by kind of continuously improving and getting better at whatever your craft is, right? In his case it was photography. But for me it’s like I want to become better at SQL Server. The only way to do that is just to keep doing things with SQL Server, pushing myself to learn new things and blogging.

Carlos: Making mistakes and then learning from those mistakes. That’s the big thing, right? The fear of failure can hold us back sometimes.

Bert: Right. I mean, hopefully don’t do any injection mistakes in production but always get to learn.

Steve: Ok, so our final question. If you could have one superhero power, what would it be and why would you want it?

Bert: So thinking about this, I think I would want to be able like to control time. Not like be able to go back time to 10 years or 20 years or something like that. But if there is like an undo button for where I could just kind of go back in time some limited duration like maybe three minutes or five minutes. Not only will that prevent lots of “Opps” scenarios where I delete something that I don’t want but I guess I could always put in those last minute bets to in crazy sport events outcome that no one expects. I think that would be pretty cool. Then you don’t have to deal with all the ramifications of changing history and the whole future outcome is different. So I think three minutes back wouldn’t be too bad.

Steve: Ok, so the time control undo stack. I like that.

Carlos: Well, awesome. Well Bert, thanks so much for being on the program today. We do appreciate it.

Bert: Yeah, thank you guys. It’s a pleasure.

Steve: It’s great to have you Bert. We learn some things along the way, too.

Episode 119: Should I get involved with GitHub?

At first glance you might think our podcast topic is from way out in left field; however, as you think about it there are a number of reasons we should be talking about GitHub.  Even if you don’t need a public source control repository for creating code, GitHub is becoming the preferred place for creators to publish their content and the ability to interact with other is extremely value.  In this episode we give some of our thoughts around getting started with GitHub and how it might differ from some of the other options out there.

 

Don’t forget to leave your ‘Tips and Tricks’ on the podcast page.  If we get enough submissions, we will start that new segment in January.

Episode Quotes

“Although it’s not directly a SQL Server thing, it’s one of those things that is really important to be able to understand.”

“That’s an interesting way to use GitHub because you’re almost using it as a distribution mechanism.”

“It’s probably the most popular repository now.”

“When people see public repo on GitHub it’s really just assumed that it’s there for community distribution.”

“As long as you’re using source control to keep track of what you’re doing and you’ve got the appropriate ways to distribute it, GitHub is great for that, but it’s not the only tool out there.”

Listen to Learn

00:12 Introduction about the episode topic (GitHub)
02:34 Companero shoutouts
03:53 What’s coming up: Tip and Tricks and Database Health Monitor Webcast
06:53 SQL Server in the News
09:55 Show notes links
10:42 GitHub as a repository for DBAs and IT professionals and other usage of GitHub
16:40 Private or public repository – pros and cons
24:41 GitHub licenses
26:38 The “Read Me” file
28:30 GitHub interaction (Browser, Desktop App, Bash Shell commands)
31:45 RedGate SQL Source Control, Visual Studio
35:15 Defining differing terminologies
35:55 Thoughts about using GitHub if you’re the only one working on a project?

Lock down your Azure resources

Transcription: Should I get involved with GitHub?

*Untranscribed introductory portion*

Carlos: Companeros, welcome aboard the SQL Trail, this is Episode 119.

Steve: Yes 119, and today’s topic is getting started with GitHub. That’s quite a few things lined up to cover here today.

Carlos: That’s right, and so obviously GitHub being a source control product and there is a lot of flavors here, and when you think about GitHub you can probably put yourself in one of a couple of buckets here. One, you’ve never heard of GitHub.

Steve: Right, and at this point you are wondering what the heck we are talking about.

Carlos: So we hope to provide a little bit of information there and maybe some at the end of the program, if you haven’t got started, now you’ll have at least some direction to go further. Maybe the other bucket is you’ve heard about GitHub but you’re scared.

Steve: Maybe scared is not the right word but you might just not know where to go.

Carlos: Sure, yeah, intimidated is the word I would have used in describing myself, and I still kind of feel that way to a certain extent. So you’ve said source control, but you don’t know Git per say, and those folks who should be doing this episode with us.

Steve: Right, the real pros who want to share the information.

Carlos: Yeah, exactly, who are using it. I’m not sure how valuable this episode will be. I mean obviously we are going to share some opinions or thoughts on some things. But we are not going to be uncovering particularly an audio format, anything deep dark GitHub secrets. And then the last group, so those who have jumped to Kool-Aid and basically it’s GitHub or bust.

Steve: Right, yeah, and those are the ones if you’re using anything else you are unworthy basically.

Carlos: Our conversation is kind of, with an emphasis on GitHub but we admittedly are going to break away a little bit from that. We will be referencing things like TFS as well. And so if GitHub is your thing and you’re hearing other repositories is going to make your ears bleed. This episode is not for you either.

Steve: But before we really get into the details there, do we have any companero shoutouts this week?

Carlos: We do have a shoutout. I want to give a shoutout to Nikki V from the Netherlands. Nikki V left a comment on the website talking about Episode 115. He mentioned, “I love your fun and honest review of the SQL Trail.” And so we appreciate that and I hope to see you Nikki on the SQL Trail someday.

Steve: Nice, and then we have another one from Andy Levy. He liked Episode 117 which is our wrap up of Summit.

Carlos: That’s right and enjoyed kind of hearing some of the comments there and we enjoyed meeting him at Summit obviously. Another fellow that I met, I’m not sure if you’re connected with him Steve, but Justin. Justin who I believe is in Alabama now. We were talking and he mentioned a script that he had to help find potential foreign keys and he shared that with me. I haven’t been able to go through it just yet but I’m grateful to him for sharing it with me and may actually come in handy on a project that we are working on, so interesting idea. We mentioned it last week, we will mention it again, so now through the end of the year we want to start collecting your tips and tricks. We’re going to start adding tips and tricks section, very similar to SQL Server in the News. However, we want this section to come predominantly from you the listener.

Steve: And that could be really in two flavors there. I mean, one could be, here’s your tip or trick and how exactly how to do it or the other could be you saw somebody do this and it was really awesome but you didn’t quite figure out how to do it, and maybe we can help with those too.

Carlos: Yeah, there you go, any of those combinations. You can go to sqldatapartners.com/podcast. On that page you can either leave in the comment for submission of ideas or tips and tricks or you can use the online recording and record a segment of what’s your tip or trick is.

Steve: Yup. Then coming up on December 14th we have a special event plan.

Carlos: That’s right, and I should say probably a recurring event. Something else we’re trying or adding into the mix here.

Steve: Yup, and this is the Database Health Monitor Webcast. And when we say it’s recurring, we’ll probably do it every month or maybe every couple of months but it’s not going to be the same every time. We will cover different topics or different parts of the program.

Carlos: That’s right, so we’ve tested out our video equipment and it hasn’t broken just yet and so we’re thinking we will add a little video to the mix here. So there are going to be short 30-minutes segments, you know, very similar to the podcast but it’s going to be focused on Database Health Monitor and kind of monitoring those interactions with your system.

Steve: Yup, and with that it will be a live broadcast.

Carlos: That’s right. We will record it and make it available through YouTube or whatnot afterwards.

Steve: But people can attend live through the go to meeting connection, correct?

Carlos: Correct. So at sqldatapartners.com/webcast, you can go there and sign up for the next episode, and we’re going to those the second Thursday of every month is the plan.

Steve: So then if somebody has questions, we’ll do our part of it and then at the end we will open it up to Q&A. That’s going to be fun.

Carlos: That’s right, so we’re hoping everyone finds that valuable and just another way to connect with you all, something in a live setting and again not hours and hours. But very similar, you’ve seen other folks out there in the community doing something similar and we’re looking to put our spin on it. And with that, I think it’s time for a little SQL Server in the News. And it’s in this segment only because one, we were just talking about it with a client and then I happen to be looking at the Microsoft blog and there was actually an article about this last month. That’s why I said, “Oh well, if they are talking about it, we should be talking about it.” Ultimately, it’s this idea of securing your Azure VMs or Azure SQL Database and restricting it by IP Address. And so you and I are talking, and ultimately what we had is we had a scenario where a client wants to give us access to their VMs and we got a username and password. And I’m like, “Well, I haven’t given you my IP Address, how am I going to connect to this thing?” I’m like, well let me just try really quick and I could connect. And I was like, yeessh. So we talked a little bit about this and I’m not sure. I guess I was thinking maybe with Azure SQL Database but it sounds like they even changed that. Maybe, I don’t if it’s process now, right, we’re I’m locking that down automatically so that it’s just something that I do. I’m adding those IP Addresses, but basically the idea is that you can by default those are exposed on the internet. So if you know the IP Address you can basically start to try to get in there, and not a good idea.

Steve: Yup. But I think part of what they do is they have to do it that way so that you can get connected there to begin with before you’ve actually done anything to lock it down. So I think that just a lot of time people overlooked that and I think that’s Azure databases and with Azure VMs.

Carlos: Right, well I guess I feel like I can do that in the dashboard. I don’t actually need to connect in order to make those firewall rules enabled so I guess that’s a puzzling thing.

Steve: But I would bet that if they made it so that you had to set that up before you could actually connect so limit where you can connect from that a lot of people would not get pass that because it’s not entirely intuitive how to do it.

Carlos: You know I agree and there are a million ways to go ahead and do that, right, so like if you have a point-to-point VPN then that’s different than me being from my home and I just want to give my IP Address because I’m going to ask who’s going to be connecting to it. There are some complexities in there and I kind of see that but I think if that’s something that you’re working with, you are doing something in Azure, that’s a conversation you want to start having. Don’t start spinning up those databases and just leave them there, right? You need to make sure you’re talking to your network folks and making sure they get locked down.

Steve: Yup, absolutely. Alright.

Carlos: So our show notes for today’s episode is going to be at sqldatapartners.com/github.

Steve: Or at sqldatapartners.com/119 for our episode number.

Carlos: Ok, and so as we are putting this together and obviously we are recording just before Thanksgiving, so happy Thanksgiving. And hopefully everyone had a happy thanksgiving because this will come out after thanksgiving. And we were thinking about what we will be talking about and so this idea of GitHub at least in my mind. You think about the changing nature of the DBA position and I feel like I am hearing just a lot more about GitHub. Yup and so I thought we talk a little bit about why that is and then kind of get into it there.

Steve: Well, what’s interesting here Carlos is when you first brought up the topic of GitHub I kind of thought to myself, “Well we’re doing the SQL podcast. Why are we talking about GitHub?” And then after looking a little bit and thinking about it a bit more I realized how much we actually use it as DBAs. Although it’s not directly a SQL Server thing, it’s one of those things that’s really important to be able to understand what it is and how that works.

Carlos: That’s right. And I’m reminded, I’m not sure if the code is attributed to him but I heard it first from Andy Leonard and this quote that he had he’s like, “There are really two types of DBAs or IT professionals. There are those who are using source control and there are those who will be using source control after some disaster.” And again ultimately that’s what we are talking about is a source control technology but it almost seems like in a sense that that is almost like a defacto way that people are starting to push, and when I say people, particularly Microsoft, right. The documentation is now on GitHub or you can see it on the website but as far as being able to interact with it having people contribute to it like that’s now an option. That’s how the people who are writing documentation for Microsoft are actually contributing or pushing to this GitHub repository.

Steve: Right, and I think that’s an interesting way to use GitHub because you’re almost using it as a distribution mechanism. And some extent with the ability to get feedback and I guess if you’re accepting code changes from the community then that’s different because you’re actually taking changes in.

Carlos: Right, and I think we can get into that and maybe pros and cons or good and bad of what that means. But yeah, I think you’re right. We are kind of using at as a way just to distribute that information. It seems like anymore the code samples. If you want a code sample so they have come up with so there was worldwide importers or wide world importers. Whatever it was it was kind of available to the next version of Adventure Works, another sample database. But they’ve kind of actually come with another one since that and basically they’re like, “Oh, go and get it in GitHub.” I haven’t seen links for like, “Hey, download it here.” It’s like go to GitHub. There are all the samples, people are changing and they are kind of massaging it there. I think about Cosmos DB samples and things like that for example. It’s all there and you’re like, “Oh, gosh! I don’t know how to interact with that.”

Steve: And you know what’s interesting is it might be maybe a little bit more trusted there because you can go and see the history and go and get more information on it and it’s not just. I mean of course coming from Microsoft is different but if it’s coming from some third party tool vendor out there that you may have never heard of, do you trust what’s on GitHub more what’s on their website? I don’t know, maybe.

Carlos: Yeah, that’s interesting. And I think again kind of the influence of the open source community in that we should have visibility into how the soup is being made if you will. Yeah, I think definitely contributes to that, so that is interesting. So a couple of other reasons quickly, so if you’ve been using CodePlex for example. I know CodePlex, you could put together a tool, throw it up there, kind of share it with the community. As of December 1, that’s going to be read-only. So if you want to be doing things, like for example Pal Tools, one of the tools that I have used in the past. The creators or maintainers of those applications won’t be able to use CodePlex anymore and they are moving everything to GitHub. And the last one is, you see a lot of employers starting to ask for that in the interview process.

Steve: Yeah, this is one I saw a couple of years ago where they actually said, “I don’t care about your resume just give us your GitHub username and we will look at what you’ve done.” Which I thought maybe that was a little bit, you miss a lot of stuff because there are a lot of people who have done amazing things in their career that didn’t necessarily happen on GitHub publicly.

Carlos: Yeah, exactly. That stuff is probably an emphasis of maybe focusing too much on almost like a particular technology stack potentially is that a lot of people would go to the wayside. But I think you have to agree that it’s probably the most popular repository now, right?

Steve: Oh yeah, absolutely.

Carlos: And so because of that, it’s almost like if you’re not on Facebook, right? It’s like, “Come on!” Everybody is there. Even if you’re not posting there all the time just that ability to log in and kind of connect with the rest of the world. I kind of feel like that’s what GitHub has become in a sense.

Steve: I think that, I mean, there is a lot of different uses there for what you can do on GitHub. I mean one, is something big like Microsoft distributing sample databases. Another option is you’ve got something like Brent Ozar does with his sp_blitz and other scripts that he does where he distributes those but he also get a lot of feedback and changes from people. And I think that’s a great way to use it, to be able to take those changes back and be able to integrate them in your base code there.

Carlos: Yeah, I mean, DBA Tools is another example of that.

Steve: Yup. But then there is like let’s say you’re a company and you’ve got your private intellectual property that you want to keep private for whatever reason, you’re making a product. There are GitHub options that you can pay for that will give you private repos, private on-site repos with GitHub. That’s very different than sort of the free open source collaboration method there too.

Carlos: Yeah, exactly. And I guess that’s an interesting question. Let’s go ahead and dive into that for a bit. Ultimately, with GitHub you can have a repository, that’s kind of your folder, your project, that’s kind of where all the files are going to go for that for that project. And you can have private versus public. I think it makes a lot of sense. I am a private company, I’m selling a piece of software. I want to keep that private it’s the IP that I have. Now the question is the way we kind of frame it or talk about it is that it’s the open source. You want people to see what you’ve done. Even if maybe you’re not having people collaborate with you, you know that book of work may still be important to you. And so why would you then like does everything have to be public or do you still have things that are private versus public? Admittedly, everything that I have out there is public which is not very much. Let’s just say I have two repositories, so please don’t think that I am slaying all these code or anything. The two repositories are for the Zero to SQL book. I put up the scripts to create database and made it from a distribution perspective. Very similar to what we’ve talked about. And then the other one was our database setup and we are hoping people would contribute to that and maybe get that started.

Carlos: But there are other uses too for private repos. I mean, I have quite a few more than you do there. But some of the examples of what I do is I have one that’s called presentations and it’s private but it has every presentation that I have done at a conference, at a SQL Saturday, PASS Summit, whatever for the last 7 or 8 years I’ve got in there. So if I remember that, “Oh that was in a presentation I did 5 years ago I want to go find that code.” It’s all there on that private repo. Now, some people might argue that it would be better to put that out on a public repo but there is a lot of stuff that I’m working on there that may not be ready for public consumption yet.

Carlos: Right, and that’s kind of another interesting idea. It’s almost like the policy of your repository and the purpose because I think you almost like have little islands or little reasons/different reasons why people are putting out those repositories and they are not all the same. Like you mentioned, “Everything might not be ready for primetime”, and so that’s an interesting take. And so how you decide to set that up, I think you need to be clear about that if you’re going to make a public repository. You need to be clear like, “Hey, this is just for me”, “This is part of my body of work” or however you want to say that maybe a bit more eloquently. But make that known upfront. And another vain could be even as simple as like, “Hey, here is how we do certain or here is our policy on like case or formatting”, things like that, coding standards, there you go.

Steve: But I think when people see public repo on GitHub it’s really just assumed that it’s there for community distribution and that you’re hoping that people will fork it and then use it on, and then contribute and push back into what you’ve done. I think that’s kind of the assumption but that’s not always the case there with public repos.
Carlos: Sure, yeah. Maybe that’s a philosophical question at this point. But why would you put out a public repo if you didn’t want anybody to contribute to it?

Steve: That’s a great question.

Carlos: But we do know that there are people that are doing that.

Steve: If you have a public repo and you didn’t want anyone to contribute or add to it in any way. Would you be bothered if they did? Would you, like if somebody came along and came up with some super cool enhancement for whatever it was you had there and they did a pull request so that you could review it and maybe integrate back into your code base. I mean there are some out there I’ve seen where they’re just going to say, “No. I don’t want it. I don’t want anything, anybody contributes because that’s not mine.” That’s not my mentality but there are some of that out there.

Carlos: Well, and in their defense we’ve talked about this on other episodes like go back to community contribution episode that we did. We interviewed Bren and Crisy. It takes a lot of time. I mean, it’s one thing to write the code, so anybody who’s done that branching and merging all the stuff back in and then casting. You are now taking responsibility for all of that as the owner of that repository. There are pluses and minuses there as well too.

Steve: And some of that may depend on how complex it is. I mean there’s a lot of code out there on that DBA Tools project. It’s many orders of magnitude different from our setup scripts that we put in our public repo. I mean one single page and the other is gigantic.

Carlos: Right, lots and lots of functions and some of those. Ok, so I guess ultimately dealing with that if you do go private. You have that ability to say, “Yes, I want to share with the world” or “No, I don’t”. Like you’ve talked about, most of the people are thinking or the expectation is you go public because you want to share it, you want people to contribute to it but then there potentially some consequences with that. Having said that, I guess I would say, typically if you’re new. And I still consider myself to be new to that community. You know, we’ve put those things out there. We have like a couple of watchers but we haven’t actually had anybody contribute and so I don’t think you should be so afraid. Like I would let that stop you I guess is what I’m saying because it’s going to take a lot of time and branding and all of those things until it gets noticed or picked up. Almost like your blog in that sense.

Steve: Right. But the other thing to be aware of too is if you’re looking at someone’s repo there, public repo on GitHub, look like how often it’s been maintained. When was it updated? When was it was last changed? And I know for instance I had some project that probably are 6 years ago that was like a weekend thing that I thought, “Oh, this is going to be cool. I want to make it available for everybody and put it on GitHub and I haven’t touched it since.” I hope that people who are looking at that are not judging me on what I know based off of what I did on that project because that was just sort of like we weekend thing and never quite finish it you know. I probably should have gone back and deleted it but I never did.

Carlos: Yeah, that’s right, so there are all kinds of projects out there. Now, another thought or question I had was around licenses. At least in GitHub when you go up and create that repository it’s going to ask you about some licenses which admittedly I don’t know very much. I usually just take the default and put it up there. If you’re kind of just getting started and you want to start to put something up there, should they be concerned about the type of license we choose?

Steve: Well, I think only if you care about any kind of liability around your code or where it can go or who it can be used by whether people can make money out of your work. I mean if any of those things matter to you then yes you should be concerned about the licensing.

Carlos: Yeah, got you.

Steve: There are some that say, “Use this in whatever way you want but not just for commercial. You cannot make money off of it.” And then there’s others that say, “Use it in whatever way you want. But you can’t modify it.” There are appropriate licenses for almost every scenario you can imagine out there.

Carlos: Yeah, but again so I guess I wouldn’t get two bugs down into that. I think there are defaults which I can’t remember off top of my head anymore.

Steve: Yeah. Like there are some like new licensing, there are some of the MIT licensing. There are a handful of other licensing options in there. I don’t remember them off top of my head either. Just make sure you know. I mean you take a look at it at least to see what it is but don’t let that stop you. It might be that you create a repo and you realized, “Oops, that’s the wrong licensing. Maybe I have to go back and change that later.”

Carlos: Yeah, there you go and asking around never heard particularly if you know somebody else in the community. Your developer friend, they will be able to help you with it. The other thing that I think, I don’t know if it’s specific to GitHub per say. I’m trying to think about it. I’m not sure there is an equivalent in TFS but one of the things that’s going to allow you to do or that it can do for you when you create that repository just a little box just to create that “Read Me” file.
Steve: Oh yeah, and that’s super helpful.

Carlos: I think that “Read Me” ultimately is again because thinking under the premise of it being a public project is share the rules, right?

Steve: Right, but even more than that, that “Read Me” is really the main visual representation of all info around your project when somebody first visits your project page.

Carlos: The kind of why.

Steve: Yeah. And then that may link off to many other pages but it sort of like what is your project, why are you doing it, like what are the rules around it.

Carlos: Right, exactly. And you know put in some time and thought into that. Again, not to it to be fancy but at least letting people know, “Ok, the only reason this is here is because I want to add this to my body of work” or “Hey, this what I’m looking to build, I would love you would join me”, you know, that kind of thing.

Steve: Yup, definitely worth looking up for any GitHub project that someone else created that you’re viewing.

Carlos: Yeah, and that would probably another great way to get some ideas there, you know, peek around at some of the other ones. Like we mentioned, so Brent’s stuff is out there. The DBA Tools stuff is out there. I know that there are some others like all the CodePlex stuff that’s moving over. If you can find your project now in GitHub you can take a peek there because they are all going to have “Read Me” and things like that. Now, once we’ve kind of put all these together obviously at least my first interaction with GitHub and I would say 90% of my interaction with GitHub has been through the browser but there are plenty of tools that are available that you could then use to download and not to interact with the browser. I don’t know if it’s the most popular, the most common, I don’t know, this is according to Carlos. I don’t have any numbers to back that statement up, but the GitHub Desktop App. You can download that, get started and that will allow you to kind of easily upload and modify your files, interact with your repository.

Steve: Yup. Other things available there too are the Git bash shell which is what I personally use and it’s basically a bash type command shell if you’re familiar with UNIX or LINUX bash. And it has all the GitHub commands in there and you can do everything. Kind of like a DOS prompt but in a shell window that lets you not have to use your mouse and click through the GUI. I think it comes down to what you like more yourself. I mean, are you more of a mouse type person, or you are more of a keyboard person and for me the bash shell is great because I like the keyboard more than the mouse.

Carlos: There you go. And I think once you get the commands down then it becomes a little easier. Now, that is the one knock against the desktop app is that you’ll use it and you’ll never learn those commands. There is kind of a jump that you may have to take but a lot of tutorial like on YouTube and whatnot can go over the common ones. I mean, so bringing the repository down locally so you can work on it. Of course you’re saving the file and then getting it back up there. I think that’s half the battle right there.

Steve: And then sort of halfway in between those is Visual Studio. If you are working in Visual Studio you have an integrated Git project there, you can do a lot of the Git commands through just the normal Visual Studio code checking section.

Carlos: Yeah, those are nice things at least at least on the Microsoft perspective. If you’re looking to get started and you have those tools is that I think the barrier to entry is a little bit easier because it’s going to be in an environment that you’re more familiar with. Yeah, and we talk a little bit about those repositories already so now I’m curious, you came from a developer background. Did you even mess around with the desktop or did you go straight into the bash?

Steve: No I didn’t even try the desktop. I just went straight to bash. I mean, I’ve been using source control for 27 or 28 years now. I mean, most of it has been command line so that’s kind of what I learned on as a kid and grew up with.

Carlos: Right, ok. From a slightly different perspective so I did knock her up writing bash. If you’re familiar even with the RedGate stuff, their source code tool.

Steve: The SQL Source Control.

Carlos: Yeah, SQL Source Control, there you go that’s the name of it. That might be your first step. It’s a very SSMS driven interface that allows you to easily get those objects and kind of put them up, then you get into the and that innovation with GFS if you’re using that, and then kind of going into the Visual Studio stuff. I think more and more people are going to start going that route. If you haven’t already your teams are kind of expecting that, that’s their interface. I know we have problems for example and I’m sure there is a way around it, but it was difficult for us working with developers that were also writing the scripts putting in the RedGate tool. They wanted to use a single tool and at least at that time, this is a couple of years ago. At that time Visual Studio and RedGate are not quite compatible and so we ended up just having to use Visual Studio and I think that’s probably common for a lot of people.

Steve: Oh, interesting because I know I’ve used the RedGate SQL Source Control project in a software development environment where developers were making code changes and making it all in through Management Studio using RedGate’s product.

Carlos: Yeah, exactly. But then their code on the frontend side they’re going to check in through Visual Studio, right?

Steve: Oh yeah, absolutely. And some of that it was done through other tools besides Visual Studio. But that’s true, the beauty of it is that when you use like RedGate’s product it’s just part of a source control project. It doesn’t have to be that way for everything. You can use Visual Studio, you can use other stuff. Git is very open to a lot of different tools like that.

Carlos: Ok, yeah. There you go. I think where I was going is that they wanted, and for whatever reason, the developers didn’t want to have two tools to be able to check in code and so they just wanted to do everything through a single repository.

Steve: Got you, so that makes sense. Right.

Carlos: I guess we kind of talked about this but I’ll bring it back so if you haven’t gotten into that. Again, so I only have two repositories so it’s not like I’m a GitHub pro or anything. But I feel like get out there, at least create an account. Now, this brings up the question. Maybe you’re not quite ready. Maybe you have some things that you’re working on. You have some scripts, so again, I’m coming this out from a body of evidence perspective, I want my future employer to know who I am and be able to share some of the things that I’ve done, right? However, I am happy to share but I’m not interested in really the contributions perspective. Should we still encourage people to use GitHub if they are the only ones working on the project?

Steve: Let me back a little bit on a couple of terminology things before we answer that one.

Carlos: Ok, here we go.

Steve: Ok, so we’ve got Git, and Git is basically a source control product. And then we have GitHub who happens to be a hosting vendor that has free repos or paid repos or private ones. There are also other vendors out there like BitBucket which is also another Git hosted cloud based solution. Different UI tools when you’re working through the browser than you have with GitHub but still command line it’s exactly the same thing whether you’re working from the GItHub desktop. So when we say if you’re working on a project on your own and you don’t really want contribution on it. I mean, I would recommend yes you should be in source control. Does that have to be public on GiHub? No. Will it help you next time you’re in a job interview and someone wants to look at code examples you’ve worked on? Yeah, perhaps it will. But is there intellectual property that you’ve created in that source control that you don’t want to give away to the world then you should have it in source control but it probably shouldn’t be public in that case. Does that answer what you’re getting out there?

Carlos: Yeah. I mean, I think so. I think that question would be particularly if there is a scripts that you’ve written, and I think even mentioned in Justine’s script that he gave to me. I’m not sure that he is ready to show that with world just yet but it’s one of those things that you could put out there. You know, he didn’t put that together with the intention of making money on it. If you have those types of things that you could put out there, I think it makes a lot of sense to try it out. Be involved in the community and just help your name get out there.

Steve: Yup. You know I could think of one. At PASS Summit I presented, one of my presentations was on backup and recovery and I’ve shared with people a script that would allow you to build your backup script through your log chain. That was one that I have it in source control in my private repo and occasionally I just put it in a .zip file and put it out on my website. However, thinking through that that’s the wrong way to do that. I should just take that and put that one in a public repo because all the source code is available anyone who wants to use it. Just by downloading the script today and it would be a lot easier to maintain and if somebody has feedback that they want us change. That one would be much easier to do have in a public repo.

Carlos: Right, and so that’s interesting. We kind of talk about this, you know, actually before we started recording we talked about this idea of in certain cases I wouldn’t want to go to GitHub, I just download it from the person’s website. I think some of that was maybe pre GitHub, or pre open-source source control if that’s the right terminology because that was the only way to distribute some of that stuff. Now, we kind of have this defacto way of instead of me having to then take the code put it all together, compile it if that’s the stuff I have to take, and then zip it up and put it up in my website. Once I have uploaded it, now everybody else can just kind of take it from there. You saved yourself a little bit of hassle there. Yup and I think the difference there is like that restore script that I was talking about from my demo. Man, that’s a single file. If somebody wants to go and get it all you have to do is click one link to download it on GitHub or click one link to download it on my website. Either way you’re just getting a single file. But if it’s something more complex like take Brent’s blitz scripts where is that quite a few of them that different things, it might be easier to just grab it off of a website zip up versus grabbing a handful of different files to download. And I haven’t really looked at his stuff there too much but maybe he’s got a bundle or an install package now.

Carlos: Right, well I think even in that case so probably different scripts. Are you going to use all of them? It’s almost like I only care about this specific code right now instead of downloading four files I’m only going to use one of them.

Steve: Yup, and I guess I probably should have gone and looked at Brent’s GitHub setup before we talk about it. I would have been much more informed on that one. But I know I looked at it when he put it out there several months ago. I just haven’t been back since then.

Carlos: Sure. Ok, again, ultimately I thought kind of interesting conversation around how we can start interacting better with GitHub. Again, I have an account. It’s not like I have tons of repositories out there. But I feel like I’m connected. At least try to go through the motions there. I think more and more it’s going to become, I won’t say required. It won’t be required but I think we’ll just to continue to see it more and more. However, they are not the only game in town. There are lots of different, we talked about a couple, and I think ultimately our message is, “Yes, use source control and what makes most sense for you.”

Steve: Yup, absolutely. As long as you’re using source control to keep track of what you’re doing and you’ve got the appropriate ways to distribute it, GitHub is great for that, but it’s not the only tool out there. There are lots of them.

Carlos: Yeah. Ok, I think that’s going to do it for today’s episode. Our music for SQL Server in the News is by Mansardian used under Creative Comments. As always you can connect with us on social media. We would love to hear your thoughts and feedback. You can leave comments in our website and we do still allow reviews on iTunes. Those of you iTunes users we’d love for you to leave a comment there. You can connect with us on LinkedIn. I’m at Carlos L. Chacon.

Steve: And you can get me on LinkedIn at Steve Stedman and we’ll see you on the SQL Trail.

Episode 118: How has index maintenance changed?

After having attended PASS Summit and seeing all the new features coming it out, it can be very easy to overlook the basics–they are boring.  These features have been around forever.  This may be true, but they still play an important role and ignoring them won’t help get you those fancy new features any time soon.

In this episode, our topic is index maintenance, with an emphasis on what has changed or what might change.  Is the query store feature going to impact the way we maintain our indexes?  We invite one our favorite guests, Sean McCown back to talk with us and give some of his thoughts.

Episode Quotes

“You can’t really change the math of re-indexing and maintenance.”

“And part of the problem is not with that, but with not having time to do maintenance is allowing business people to run the show.”

“Rebuilding an index is absolutely rebuilding the index pages and putting them in the order that they are supposed to be.”

“It’s not the stuff that they add that I think is ridiculous. It’s the stuff they don’t put in.”

Listen to Learn

00:06 Introduction about the speaker and brief outline of the episode topic
01:12 Updates on minion ware and what’s changing on re-indexing
05:00 Problems that will arise of having no time to do maintenance
10:18 Fragmentation of indexes
15:04 Does SSD and other premium storages change the data access?
19:17 How to determine how the fill factor should be different?
22:51 SQL Server: Analytics on database
27:49 The difference between re-indexing, meaning rebuilding, reorganizing an index, or statistics
33:02 What stands out with minion re-index?
43:27 Re-index 2.0 features
49:00 SQL Family

About Sean McCown

Sean McCownSean McCown is a Certified Master in SQL Server 2008 and a SQL Server MVP with 20 years of experience in databases. He is also founder and co-owner of the MidnightDBA.com website, where he records free SQL Server training videos and co-hosts the popular web show [email protected]

 

Transcription: How has index maintenance changed?

*Untranscribed introductory portion*

Carlos: So Sean, it has been a little while, welcome back to the program.

Sean: Thanks guys! It has been a while but not that long. I’m still getting people talking to me about the last episode I was on.

Carlos: Is that right?

Sean: Oh yeah, I get a lot of people at SQL Sats and whatnot telling me how much they listen to you guys and they heard about me and the stuff I’m doing through you guys, so yeah, good for you all. I didn’t realize you are so popular.

Steve: Well, it’s always good to have you on the show, and I think that helps with the popularity.

Carlos: That’s right, our MVP guest. Yes, so I guess 20 episodes. It was episode 90 was the last time we had you on.

Sean: Oh wow!

Carlos: Talking about Check DBCC. Yes, good to have you back on and always trying to keep up with what you’re doing. So today, ultimately our topic is going to be a bit about re-indexing. What’s in store for that? It’s one of those, I think everybody has a story about re-indexing or an opinion about re-indexing. I think even the future of where that’s going might change a little bit. And so I guess let’s just first jump off, why don’t you tell us, give us a little update on the minion ware stuff. What’s changing on the re-indexing?

Sean: Wow, so first I want to say we’ve been watching a lot of Voyager recently so I want to say your opinions about re-indexing are irrelevant.

Steve: What? The resistance is futile?

Sean: That’s right. Well, because to me it’s like I see this a lot. Companies seem to take pride. I’m going off on a tangent a little bit but it may have even been the one I won on last time. But I see companies do this a lot because you’re talking about opinions. And you get these companies that brag about how busy they are. “Oh well, our business is so slammed, we don’t have time to do re-indexing. We don’t have time for maintenance. We are so slammed.” You know, while that’s a cute sound bite, you can’t really change the math of re-indexing and maintenance, right? I mean, it’s nice to think that you don’t have time for it but that’s like saying you don’t have time to take care of yourself but you’re going to have time for the heart attack.

Carlos: So in that event I guess is do you believe that that is one of the reason, an additional reason perhaps that people are looking to the no SQL world. Again, not to say there aren’t indexes but they definitely seem to be same class citizens.

Sean: Yeah, people were looking to the no SQL world for because of the hype. They are advertising a gazillion transactions per hour and it’s so much easier. It takes a lot less maintenance because it got so much less overhead an all of that. But now they are getting bit in the ass by what, the fact that there are no keys on the table so your isn’t enforce and whatnot. So they are like, “Hey but I got corrupted data now.” “Oh, duh, you think so. Maybe you should add some of that overhead backend.” The stuff is here for a reason. I mean, no SQL has its place. There are sometimes when it just doesn’t matter, when that integrity is a lot less important or when you have a threshold for that sort of thing, right? For having what we call logical corruption. And you need the speed more than you need to have absolutely everything lined up. But most of the world need some sort of integrity.

Carlos: Right, you want some protection, you want some help in assisting what’s keeping your data. If you want to keep it around, right?

Sean: Yeah, so no SQL is really more hype than anything if you ask me. Yeah, that’s all I have on that.

Steve: Ok, so jumping back then to sort of our businesses is so busy that we don’t have time to do that. I think oftentimes I hear that and I see that, and quite often it comes down to misunderstanding of some of the fundamental maintenance items you need to do there. I see oftentimes like somebody will go through and do a complete rebuilt of all their indexes and then right after that they will rebuild all their statistics.

Sean: I see, yes.

Steve: And they will rebuild the statistics with like a 10% sampling or something like that which when you rebuild your indexes, of course your statistics get updated too. So you’re actually being wasteful rebuilding this statistics right afterwards.

Sean: And part of the problem is not with that, but with not having time to do maintenance is allowing business people to run the show. I mean, you get PMs and you get Sales Managers, and you get VPs, you got C-Level people and they are saying they don’t understand the database and they say, “We just don’t have time for that. We just can’t do it. I don’t have time, I can’t do that.” “We’ve got customers. We need to keep them online.”

Carlos: Well, in their defense it’s because they’ve tried probably to do, they haven’t done it in 10 years then they try to rebuild it and it hits them because the business is impacted in some way because it runs over. It’s still running at noon and nobody can get into the database, and so that’s where they’re like, “We’ll never do that again.”

Sean: Or they have a DBA like and this kind of merges into what you’re saying, they have a DBA that’s just doesn’t know how to do maintenance well.

Carlos: Right, and we’ve all been in that camp. I think I may have actually told this the last time we were on. I had been hired by a law firm to help with a conversion of the financial application and that was like the flagship project that I had come on for. There were two DBAs. He was actually in London at that time, this was in Richmond, Virginia where we’re located, so he was in London helping with some office and they were doing some conversion over there. And then we had the document management have been having problems, so the decision had been, somehow somebody else. So the DBA in London had talked about reorganizing the index that’s what has been approved. And for some reason we’re talking and we decided we’re going to rebuild the thing instead of reorganizing it.

Steve: Let me guess on Standard Edition as well.

Carlos: Standard Edition as well. So the issue was not what you may think it was. The issue wasn’t locking everything else out. The issue was we’ve granted disk space. So it started to rebuild and it creates that second copy and there wasn’t enough disk space on the drive to facilitate both copies of the index and that had already taken like 2 hours and some such thing. An attorney had needed to get into the system to get this document and they couldn’t. And then we were waiting for it to rollback and so that caused a big hoopla because we were not paying attention to what we were doing. We were all tired. It was like midnight when this was happening. We were both involved in own projects and we made kind of in the moment decision when that probably wasn’t the best choice.

Sean: Right, I had a situation years ago with a big client who hadn’t done maintenance ever, and they were being charged something like $40,000 a month on missed SLA fees from their customers. And they were about to both close the site and then the other sites were about to go to Oracle because SQL just couldn’t keep up with their level of business. Ok, sure if you say so. And so I got involved and they were 99% fragmented on almost every table in the database. So I got their maintenance taken care of and even did a little bit of scale up for them. Put some indexes on different lungs and whatnot. And told them that, and they were a true 24/7 shop, and I said, “Now we have to go down 4 hours every Sunday to do maintenance. We have to do this.” And he was like, “I can’t do that. I can’t afford that time.” So okay fine, right?

Carlos: What about the $40,000 a month?

Sean: So like a week later, Sunday, they decided to skip their maintenance. That following Tuesday, they had to pull on an emergency downtime to do their maintenance because the database has gotten so slow. Now they have gotten the taste of the good life. And the database has gotten so slow they could barely work. And this is the way they’d worked for years. So they did their maintenance that Tuesday as an emergency. The following Sunday they got ready to do their maintenance and the Plant Manager said, “We can’t bring everything down. We can’t do this. We’ve got to skip it.” And the General Manager said, “Stop. No. Take it down to the maintenance every Sunday. Don’t ever miss another one.” So we made a believer out of him for sure.

Carlos: Yeah, that’s funny. I think maybe we should pause just for a second because I’m thinking all of a sudden about Eduardo Cervantes and some of our other developer friends who are listening. I guess I feel like maybe we should just revisit or rehash some of these terms that we’re talking about. And so I guess even the idea of index fragmentation, right, you mentioned 99%. Maybe let’s just take a couple of minutes and talk about the very beginning, so we have a table, if there’s a primary key on it, you have the clustered index and obviously you have other non-clustered indexes. How do we go about getting fragmentation on our indexes?

Sean: Right, so my favorite analogy to this is with the CD case and it seems to be the one that everybody can associate with.

Carlos: You’re showing your age there a little bit, Sean.

Sean: I’ve got 4 CD books sitting over there.

Carlos: Ok, for you millenials, right? We used to carry our music around.

Sean: Yeah, exactly.

Steve: Yeah, and it wasn’t on our phone.

Sean: That’s right. So let’s say you got a nice big CD case with all your movies in it back before Amazon had it all digital. And this is going to dovetail into fill factor as well. So you alphabetize all of your movies in there, so you’ve got them on a certain order. That by the way is your clustered index. All of the movies are physically in order in this book. And so let’s say they come up with another Batman movie, Batman forgot his comb on Penguin’s Lair, and that page is full. What do you do? So you go to one of the pages at the end of the book, you put the Batman in there, and then you put a little post it note on the current B page that says, “Bs continue on page 75.” And so you get enough of those where you’re having to put them in the back of the book and make different markers to different things, and that’s fragmentation, where you don’t have everything in order. You got to go here for part of it, then over here for part of it, then over there for part of it because everything starts getting out of order because you haven’t left room to put anything else in there. So that’s what we mean by a fragmented index.

Steve: And the effect of that being out of order is if you’re trying to find that Batman movie or something else that starts with B, it’s going to take you a lot longer to find it because you’re starting in your B section and you have to flip to the back and jump around quite a bit more than if they were all in the expected location.

Sean: Absolutely, and if you put different letter on that same Batman page and then you got another B, well then that would be way out of order too. And you’re going to leave a post it note on that one to say where the next B section starts. I mean it can get way out of hand. However, if you were to say, if your CD book held four CDs a page and you were to only put in two CDs on each page because you know you might get some more CDs. They might come out with another Batman movie of nowhere. So the first one, you will only have the first two Batman movies on that page and you’ve got two free when they come out with a new Batman movie then you can just slide it in there right past the last one you have and you still got room for another Batman movie. Anything after that and you’re going to have to put it on another page, right? So that’s your fill factor as well. So you’re leaving space for inserts and once it fills up then you have to either start creating new pages or you have to re-index and recreate it with that so. A good example of that would be, they come out with two more Batman movies and your page is full, then they come out with another one. Well, now you have to tear your CD case apart and redo everything leaving two free per page so now you’ve got your expansion again, right?

Steve: While you’ve got that all ripped apart, if someone wants to find a movie, it’s going to be very challenging for them to find that.

Sean: Yes. That’s why classically they lock it. Right, nobody is allowed. That’s why classically they lock the table because we don’t know where this is right now.

Carlos: Under construction, right?

Sean: Yes, we don’t know where it is.

Carlos: We got the yellow tape.

Sean: Exactly.

Carlos: I think another reason why there’s maybe some confusion or misinterpretation. I think what you’re saying is correct. I think on the flip side however, people are going to say it really affects you, so that lookup affects you when it takes a long time to do those lookups. So with the advent of SSD, all these premium storage does that change the game at all?

Sean: Well, it helps because what you’re doing now is you’re taking what should have been a sequential lookup and turning it into a random lookup. So here you’re looking everything that is The Present or The Batman Present then you’ve got a range there and you should have a sequential lookup which is going to be very fast especially by the index. But now it’s random because they are scattered across all these different pages and maybe even all of these different extents and you’ve got these pointers that’s going to have to go everywhere for it which SSD does really well. That’s a lot less important if you don’t have an actuator arm moving back and forth trying to access spinning data. But you’re still pulling extra I/Os. I mean, that’s still I/O that isn’t as efficient as it would be if it weren’t arranged. But it has made it a lot better.

Carlos: So ultimately, the idea of the overall speed of getting your data as the cracks of the problem, and fragmentation and fill factor goes into that. I guess there is one more thing we should say about fill factor and that is the idea of, so we talked about pages, right? So you mentioned leaving a little bit of space there. Now, SQL Server does read everything from memory so you have to be careful. I don’t want to leave either you want to leave, and again, I guess I’m thinking about my CD case that has those four slots on a page. I don’t want to just put in one CD or DVD, whatever and leave the other three blank because those are a lot of pages that I’m not using, right? When those pages isn’t going to memory that basically means I have a lot of blank stuff in memory, so I guess there’s a tradeoff.

Sean: So yeah, it’s trading one set of complications for another as I like to say about almost everything. You’re giving up space to gain the speed, and to gain concurrency and to gain efficiency in I/O but you’re giving up other resources to make that happen, so yeah.
Carlos: And I guess on that what’s right really comes down to understanding what are the constraints on your system. And if you’re already constrained on memory you’re going to make it even worst if you need more pages to be loaded to get to the data. But if it’s one of those things where you’ve got way more memory on your server than you ever need, the unicorn world I guess. Well, maybe it’s not so much of an issue and I think that more memory can mitigate the constraints of your disk if your disk is already …

Sean: You know, maybe you’ll be lucky and you’re on a system that can use BPE or something like that.

Carlos: I’m not familiar with that acronym. BPE?

Sean: Buffer Pool Extension.

Carlos: Oh, got you. So we’re using SSDs, you can extend that beyond your memory capacity.

Sean: Exactly. But I found it also, I’m compelled to say that one mistake that a lot of people make is thinking that every table needs to have the exact same fill factor as well. They tend to set that and that is propagated by some re-indexing solutions out there but certainly not minion which is where all of these started to begin with. Which is what we’re doing new in minion, right? But a lot of the re-indexing solutions out there you’ll find both free and paid is that they don’t make it very easy at all for different objects to have different settings.

Carlos: Well, give us the short version of how you make that determination as to how the fill factor should be different.

Sean: Right, so that is unfortunately that’s an actual discussion. The short version is, and also takes some work, right? The short version is you have to monitor the fragmentation on the table, so you have to have some sort of monger out there, pulling frag stats from the DMV, say on an hourly, or bidaily, or daily basis, whatever you think your threshold needs to be. And then see the rate of fragmentation per day or per hour that these tables are going through and when they start misbehaving. When they start misbehaving as in when performance starts to tank. Then you can note the fragmentation level and if you know that this thing has a fragmentation level of 5% a day I guess then you can start with the 90% fill factor and see if that can get you through to your next re-indexing. That’s what this is all about, it’s getting you through to the next big re-indexing and doing a little bit every day even. I’m not a very big fan of the big re-indexing every week. I’m a fan of taking them as they are needed.

Carlos: A more incremental approach.
Sean: Right, but setting the settings to what will I need. If a table needs to be defrag at 20%, I mean re-indexed at 20%, then why make it wait three more days for the Saturday night maintenance. It’s only going to get worst from here.

Steve: I think the key there is knowing when they need to be re-indexed. I think so often people take the approach of we have to re-indexed everything on a weekly basis and that’s one of those that, I don’t know who disagrees with that.

Sean: Right, and quite often, and of course the frag monitor that I just talked about is if you’ve got a big shot that can be overkill or if you don’t have the skill to write something like that that can be overkill. If you want to take just like the poor man’s swag approach you can set your big table to 90% and then if it misbehaves before the next re-indexing then drop it down to 85%. Then if it misbehaves before the next re-indexing then drop it down to 80%. Keep dropping it by 5% until it makes it through your next re-indexing. And the extra page loads in the memory don’t have adverse effects either. There’s going to be that give and take. I would never recommend to going something like a fill factor of like 60% or something, or 50%. I think that’s a little much for me.

Carlos: Yeah, that will be a little too much. So now it’s interesting because you mentioned that idea of kind of getting the insights to fragmentation and to making adjustments, right? One of the things that I asked when we’re going to put this together was the future of re-indexing and obviously you have the new feature or the new version of the minion ware out. So one of the things that’s interesting that I see, and we’ve talked about on the show before from SQL Server in the News perspective is that SQL Server starting to put the analytics into the database. Looking at your query plan it’s saying, “Hey, we haven’t talk about this with like your table value functions.” So like the first time it runs, it doesn’t know how many rows it’s going to be affected. It’s going to guess one. It runs and it says, “Oh, I actually had a thousand.” The second time it runs, it’s going to reevaluate that plan and say, “Oh, I’m not expecting one. I’m expecting a thousand doing the changes at all.” And I have a feeling that that may impact this idea of how it goes back and gets those records and things. Do you think that that feature, that idea of basically the database deciding what to do and how those plans are going to get generated is going to affect this decision at all?

Sean: The decision of what exactly?

Carlos: Of whether to re-index.

Sean: Oh, I see. You know, I think in a perfect world what’s going to happen ultimately. And they are nowhere near that right now. Because right now they can do, they can look at some plan regressions. We’re simple plan regressions right now based off of CPU. If it’s going to cause a regression of greater than 10 seconds I think, then you can kick it into force the last known good plan. And that’s really the only thing you can go by automatically based off of that or based off of auto tuning.

Carlos: That’s the next thing they have added, so there’s the auto tuning. Basically that says, hey my stats have changed and I’m getting some differences then use the old plan.

Sean: Now, you’re putting something into that I don’t think exist. You’re making an assumption. It says if the plan is going to regress not if the stats have changed. It’s looking solely at the plan. So it says if the plan is going to take longer than 10 seconds, I think it’s going by CPU.

Carlos: The execution of the actual…

Sean: It‘s based of the execution of the actual plan. If it’s going to change significantly I’ll say right now because I don’t remember exactly what it is but it’s 10 seconds or 10 milliseconds, something like that. But if it’s going to take like 10 seconds longer for the plan to run then it’s going to look for the last known good plan. That’s completely different. You’re inserting causality.

Carlos: What I think what I meant to say was it’s looking at the stats of the execution, not the statistics. I apologize, that was unclear.

Sean: Yeah, it’s looking at a stat of the execution.

Carlos: A stat of the execution. Yeah, that’s right.

Sean: Well, I take that back. There are two conditions that can kick it in. It can be kicked in by the plan taking longer and by error count. If the last error count of the good plan is won then it will say, then it has to be greater than one that the current error count has to be greater than one, so we’re looking at timeouts and stuff like that. So it can be based off of error count as well and they call that error prompt. And it’s just a bit. Is this plan error? Is this query error prompt? Yes. Then use the last known good plan in going about your business. Yeah, I think that in a perfect world they would have to be able to tell the difference. They would have to know why a query is going bad. Why has the plan regressed? And right now in SQL as an engine, at least not exposed to us, there’s no way to tell the difference between a bad query that’s bad because the stats are out of whack, or because there is fragmentation, or because there’s memory pressure or whatnot. There’s no place to look to automatically say, “Oh, well, instead of rebuilding stats.” I mean, instead of rebuilding the index I need to reduce stats. Right, there is nothing in there that tells you that. It’s just kind of jack of all trades sort of thing. It’s a shotgun at this point. That’s why so many people quote re-index needlessly.

Steve: On that point, Sean. One of the things that I think comes up in conversation a lot and people get a little bit confused on when they haven’t experienced it is the difference between re-indexing, meaning rebuilding, reorganizing an index, or what statistics are? And can we take a minute to talk about that because I think that place into the whole conversation here.

Sean: Absolutely. Rebuilding an index is absolutely rebuilding the index pages and putting them in the order that they are supposed to be. It’s like that whole taking your CD case apart and leaving the two slots free on every page and making sure that they are all in order. So it’s literally completely rewriting those and rearranging all the data so it’s completely alphabetize again if you will whatever alphabet you’re using. Whether it’s GUIDs or first names, or social security, whatever, right? So it’s literally just putting the data back in order and making sure that everything is contiguous again. But your stats is data about that data so let’s say that you’ve got an alphabetic thing or let’s say you have a numeric thing. And so that stats would be something like, let’s say the data in your page is the first row is a 1, and the second row is a 5. So the stats would say something like this value is one and the steps between, and there are four steps between this value and the next value. And if the next one is a 6 then it would say, there is one step between this value and the next value. And if the next one is a 6, then it would say there is one step between this value and the next value. And so if the next value is 10 or 9, it would say there are three steps between this value and the next value. So it’s data about that data to let the engine estimate where data is going to be located so it can know whether it has to do a scan or a seek. Because there are times, I mean, I see this all the time where people say, “You want to get rid of all scans.” You don’t want to do scans, you want to do seeks. No, not necessarily. There are some times when it’s actually cheaper to do a scan than just to do a seek. So that’s what stats are, is it’s data about the data. And where that starts going wrong is if the stats haven’t been updated in a while and say we’ll go back to this first one, let’s say you’ve got 1, 5 and 6 as a data set. And then you’ve got between this value and the next value is four steps. So you’ve got one 1, and one 5. And it says, well there’s four steps in between but 5 is the very next value. And then you insert twelve more 1s. Well, now your stats are throwing completely off because from that first one to that second one is only one step. Your metadata about that data is thrown completely off. And then let’s say that somebody comes in and deletes that file altogether. Well, now you’ve got 5 listed in the stats but it doesn’t even exist. So it’s going to get there and it’s probably going to have to do a scan in order to find it because the stats are completely gone, right? So when you delete those stats you are basically clipping SQL’s legs out from under it and saying that, “I want you to find this data and by the way it’s none of your business what the data looks like. Just go find it and make it as efficient as possible.” And they are like, “But I need information.” So it’s kind of like, the stats are kind of like a trimmed down index that tells the engine what the data looks like so it can access it more frequently, so it can access it more efficiently. The more the data changes, the more those stats are going to get out of whack because you’re going to have updates. I mean, if somebody comes in and updates that 1, 2 or 12. And stats are only updated after 500 rows and 20% by default. So imagine having a table with a million rows in it or 200,000 rows in it and trying to join that with another table that’s got say 40 million rows in it and your stats are extremely out of date and SQL doesn’t know anything about it, that’s a pretty long lookup time.

Steve: Yup, ok. So then let’s say, I mean I know enough to be dangerous and I’m going to go and use some built-in maintenance plans, do my re-indexing or a I know a little bit more and I’m going to build my own solution to do it or I go and grab someone else’s maintenance tool out there to go do it. I mean, there’s a lot of different things that can happened there on how those get rebuilt. But what is it because as you talk about your minion re-index, what it is that’s really different or stands out with what you’re doing.

Sean: So much and even in version 2. Dude, let’s not get of here without giving you a couple nuggets from version 2, ok? But right now, currently the whole thing is table based, so all of the settings are table based, so that makes it really easy to be able to have different settings both at the database level and at the table level without having to have any extra jobs or job steps. So it’s just an insert into a table. So let’s say I want the entire database to have a fill factor of 90. Well, I can set that in the database table and then let’s say I want certain tables in there to have different fill factors, so one at 85, one at 80, one at 75. Well, I can set those. I can just put in rows in the table table. And now they’ve got all their settings and those settings also extend to every single one of the re-index settings Not just a handful of it but every single one of them. Some of them can rebuild stats after a reorg. Some of them can be sorted in TempDB. Some of them can use drop existing. Some of them can have online re-indexing. Some of them cannot have online re-indexing and so on and so on. One of the biggest things that we do in that sense is to give you a high level of configurability without any extra jobs or job steps. I think that’s the main thing that differentiates us and we’ve done what we call prep only. I think we talked about this a long time ago but the big problem with re-indexes on large databases is, let’s say you’ve got a 4-hour window. Take you an hour and a half to two hours, and sometimes longer just to pull your frag stats. In a big database it can take a long time, right? So in minion, you can pull those frag stats earlier in the day and use them in your maintenance window. So you could start two or three hours before maintenance window starts and pull the frag stats. And during maintenance window you can say, “Use those stats that I’ve already collected”, and it won’t try to pull them again or use them. And now you’re using your entire window for actual maintenance instead of using half of it to find out the maintenance you were supposed to do.

Carlos: Right, so almost like that rules based chart or list gauge the ability to finally tune all the individual knobs and settings.

Sean: Right. And you know, something I wasn’t very proud of for a while in Re-index 1 in order to do that. That’s was one of the things that you did to have a separate job for. So you had to have a job to run the prep only and then another job to run the actual re-indexing itself. But now we’ve brought that into scheduler table. We just released 1.3 of six weeks or 2 months ago something like that. So we just released 1.3 and we’ve got that scheduler table now. So now you could say something like at noon I want you to run this is a prep only and then at midnight I want you to run the re-index as a run prep job to use the stats that I collected at noon. And now they are just entries in a scheduler table. So now we’re back down to having no extra jobs at all. You could run prep only for 40 different databases at 12 different times throughout the week and it will all be just be table entries. It’s all still just one job who’ll do the running.

Carlos: Got you.

Steve: Yeah, that’s really interesting because I know if you have a different job for each of those or even a different job schedule for those different events it can get really messy in the SQL jobs for the SQL agent.

Sean: We had a client for 75 jobs just to manage their re-indexes. On a SQL Server they had a ton of databases like 3,000 or 5,000 databases on their and that’s 75 something jobs just to manage the re-indexes because they had special things that they had to do in there. That was on somebody else’s solution.

Steve: Wow.

Sean: Also in 1.3 we gave you the ability to, we could always do this but we made it a setting to re-index heaps. So now it is a setting how you want to handle heaps both of the database and the table level so you can handle like it always has been and just re-index the indexes themselves. Or you can re-index the heaps themselves.

Steve: Yeah, and you know that interesting because that’s something a lot of people don’t think about when they are re-indexing as well. What’s going to happen with the heap?

Sean: And like I said, you can always do it but it followed the, I don’t know if I want to call it the hack attack method. We’ve always had this way of being able to alter the run. Now, you asked me before, not on film but the other day when we were talking on Skype. You asked me if we were going to be able to do something. If we are going to be able to do, if we had it coming where we’re going to be able to limit the re-indexes to like to page counter.

Carlos: That’s right, so basically my small index is I don’t care about.

Sean: Right, exactly. Now, I can actually do that today, and the way we do that is actually pretty clever. But it’s the way that you had to do the heap re-index before, so you will get the idea. The method to do that now is to do a prep only and that takes all of your information and puts it in a table. And then insert a step that deletes rows out of that table based of any criteria you want.

Carlos: Oh, see, do the little prep and purge anything you don’t want and then let it run in the maintenance window and you got it covered.

Sean: Exactly. Now, that gives you so many things, right? Yes, you can do it off of index page count, you can do it off of row count. It allows you to reorder the indexes because that’s another thing that we really do well, is that we allow you to do your tables in any order you want, so you can order the tables.

Carlos: But I guess, I still don’t like that idea that I have to be the one to go back and go through there and do it. I just fell like that there should be some minimum thresholds.

Sean: Hold on, I’m getting there.

Carlos: Ok, ok. Here we go.

Sean: Now, so there’s a couple of things going here, right? I was talking about ordering because we talked about this a long time ago. I remember where I was telling you guys that that’s the problem I have with re-index jobs that stop after 2 hours is very seldomly is that actually necessary. What you mean is I am not able to control the orders of the tables go in, and so in order to keep my important tables from running after that 2-hour time window I have to stop my own team. Whereas if I make sure that my 7 or 15 most important tables get done first then I don’t care if those other little tables run over two hours. Who cares, right? They don’t take long enough and they are not used enough, so being able to order your tables is not a big deal. So within this no man’s land, I guess we’ll call it purgatory between the prep only and the actual run. You can not only delete stuff out of there but you can also reorder the tables based off of anything you want and we’ve got some SPs that we’ve written that I don’t know where they are if they made it to the community zone or if they made it to the queries folder or what. But we’ve got some SPs that we’ve written to help you with this so that you can reorder tables based off of, or you can reorder the tables based off of usage, so you can make sure that the most used tables get done first, or the least used you can reorder them by schema, you can reorder them by anything that comes up in the stats DMV, or any other criteria that you would like. And this is kind of where I’m getting at Carlos is right now with other solutions. I hate to keep saying that but that what keeps coming back to. Currently with other solutions you get a hard coded, you can do this with this page count, above this page count, or below this page count. And that’s it, that’s the only decision you get to make. But what if I want to do it based off of like I said usage? What if I want to order them based off of usage or what if I want to delete the ones based off of usage or whatnot. Anything that gets below a certain usage don’t bother even re-indexing and I just wanted to just kill that altogether, right? And I don’t want to put exclusions in the table because that’s an exclusion list that I have to manage. I wanted it to be more dynamic so we have a big dynamic component to the way we’re doing this. So right now you can go code your own solution or you can alter one of the solutions that we’ve already done. We give it to you in SPs so you can just pass in a couple of parameters and you’re done. But in Re-index 2 that I’m working on now, I am following that exact same method. So what I’ve been telling my users to do all this time I’m building them into the product. So if you look at it right now, the way it works currently is it pulls all the frag stats and then in the exact same SP it runs the re-index. So frag stats gets pulled then it runs the re-index. Well, that’s no difference from if I were to do a prep only earlier in the day and then during the maintenance window I run the job, I run the re-index. Now we are only arguing about the amount of time that happens between the prep and the re-index, right? So in Re-index 2 I’m going to have two steps in the job. Well, maybe three but we’ll talk about that in a second. So the first step is going to do a prep only. Every single time without fail even if you’re just running the re-index it’s going to do a prep only. The next step runs that prep only. So that’s the exact same as pulling the frag stats and running the re-indexes, only now I have split it up in two different job steps. But one runs after the other so you’re talking about a difference of milliseconds. And so what that’s going to allow me to do now is build into the system these inserts, these purgatory inserts. So now, let’s say that you want to limit the run by anything that’s only over 500 pages. Ok, great. It will be a checkbox for you and behind the scenes after the prep only it will run that SP that deletes anything out of there that’s under 500 pages. And then let’s say that you also want to order it by the most used index, well after that, it will run the one that orders them by most used index. And let’s say you only want to do the ones that are in this schema. Well, hopefully you would have passed that into the table as a parameter but you see what I’m doing. So I can put in ghosted records, I can do anything I want, in that no man’s land because the prep has already. I’ve got a list of tables and indexes that I’m going to run and then you can delete, you can add, you can subtract, you can rearrange. You can do anything you want to before that run actually happens.

Carlos: These features you mentioned, is this 1.3 or is this talking about 2 now?

Sean: Well, ok, so it’s available on 1.3 but you got to code it yourself or get the code that we’ve written. In 2.0 that I’m writing right now is where I’m going to build that stuff in and I’ll build in a handful of those purgatory SPs. I’m going to stick with that for now. I’m going to build that handful of purgatory SPs and then I’m going to try to build in a mechanism for you guys to write your own and even upload them into the community zones so that everybody can share. So you can see the method, it’s going to be much more flexible than anything else you’ve gotten now because you can do more than index pages or row count. You can do anything that crosses your mind. I mean even going down to saying, “This index took this long last time and that’s too long this time so I want to delete it from the thing.” And the last thing I want to say about Index 2.0 as a little bit of a teaser, look for some good multithreading.

Carlos: Ok, there we go. Do we have an estimated date yet? Or you’re still on the works?

Sean: Oh, I’m still on the works. I’m wouldn’t even come close to having a date but I’m working on it diligently. But I will say that I just got, because one of the problems, remember we were just talking about frag stats and how long it can take to pull. I just got the multithreaded frag stats engine worked out. So you’re going to be able to say you’ve got a database that’s like 4TB, 6TB. You’ll be able to pull frag stats in as many threads as you want so I want to split it up, and I want to split the database up into 10 pieces and pull frag stats for 10 tables at a time. And you can do that in tens and just work through that database and cut your time drastically. I’m saying beautiful times where I’ve got a table with something like 53,000 tables in it on one of my test boxes and it takes, well on my laptop, it takes like 2½ hours to pull all the stats for it, pull all the frag stats for it. It gets done in like 12½ minutes when I stripped it up to like 10 frags. That’s a beautiful thing. So I’m looking forwards to it and more.

Carlos: Shall we go ahead and do SQL Family?

Sean: Let’s do it.

Steve: Yes, since you’ve been on the show a few times before we sort of shake up the SQL Family questions a little bit this time.

Sean: Alright, and I haven’t heard any of these, so this is going to be great.

Carlos: Yes, that’s true.

Sean: You we’re supposed to type them on the chat and I haven’t seen anything so this is. I like to spot an idea, let’s do this.

Carlos: Do you want to go first, Steve?

Steve: Yeah, so I guess you’ve been around SQL Server quite a while and you’ve done a lot with it and the first one question I have is not what would be the one feature you would change but what do you think is the most absurd or ridiculous feature that was ever added to SQL Server?

Sean: You know, it’s not the stuff that they add that I think is ridiculous. It’s the stuff they don’t put in. Things I’m going to call the half ass features. Things like not forcing us into dynamic SQL by not allowing us to use variable names as objects. You know, SELECT * FROM at table, being able to do stuff like that. Or forcing us into XML for everything and then not standardizing that XML. Like if you’ve ever tried to parse programmatically a deadlock XML graph and have 14 nodes in one, or 14 attributes in one and then some of those attributes don’t exist and then the other ones attributes don’t even exist, so not standardizing that XML. I mean put them there, have them know but put them there so at least they are programmatically for me to parse. Stuff like that where they just don’t go all the way and let us do things we need to be able to do. They force us into ridiculous decisions. We don’t even have thread safety in stored procedures.

Carlos: So this is going to take you back a little bit, right? But the first time that you ever failed to close the transaction.

Sean: I can tell you the last time. Just the other day I was like, “Why the hell is this taking so long?” And I went into the DMV and I was like, “Now, what is blocking this? What is blocked?” Yeah, that was just last week. So the first time? I really don’t know, man. You know, I’m going to tell you honestly. I have always been in the class of what I called deep shit coders. I make so many ridiculous mistakes. I troubleshoot stuffs for half a day before I realize I’m troubleshooting on one server and writing on another server. I do that kind of stuff all the time. When was the first time? Dude, I wouldn’t even begin to know, so many times to count.

Carlos: Oh, ok. I guess it was very public for me so that’s why I guess I things that’s on the dev server, that’s something different, right? You know, that first time that you did it and the email from the manager comes.

Sean: I can definitely tell you the first time I brought a cluster down.

Carlos: Ok, let’s hear it.

Sean: Ok. I was working for Pilgrim’s Pride and we had some like 80 something locations all feeding into a central location and we only had 2 DBAs at the corporate office, me and the DBA manager. I was fairly new to clusters and I didn’t really understand the concept, and I didn’t really know what I was doing was clusters and I went in there and I did something that I shouldn’t have done and brought the cluster down for everybody, because every site in the entire network feed into ours, right? So transactions across 80 something sites were failing in the busiest part of the workday. And I went to tell the boss what I had just done because I didn’t know how to get the cluster back up. I just walked in there and I said, “Hey, dude I got to tell you something.” He goes, “Ok, what?” And his phone rang and he goes, “Can you wait just a minute?” That’s actually for me. And he looked at me and said, “What did you do?” And I was like, “Oh god, uhmm, I brought the cluster down. It’s down right now.” And yeah, that was back when you had to take 25-45 minutes to spin up all the disks in the array. It wasn’t a pretty picture. At least I didn’t corrupt anything but it was not great.

Steve: Ok.

Carlos: So we’ve had your wife Jen on the program here before as well, so a family friendly version of how you two met?

Sean: Wow! Ok, you know, that is actually a bitter sweet story.

Carlos: Oh boy, do we not want to include that one, Sean, we don’t have to.

Sean: No, no. There’s nothing wrong with it. We met when I was in my late teens. I was 18 or 19 and she was 13. We were both going to the same youth group. And we became friends because she was fairly introverted and I was, I don’t know if I was introverted or not, but I wasn’t a cool kid, and there were some definitely cool kids in our youth group and I wasn’t one of them. So we just kind of started hanging out together and we became friends and we were friends for, I don’t know, 20 years I think before we actually got together as a couple.

Carlos: Wow, ok, so late blossomed.

Sean: Yeah.

Carlos: All is well that ends well.

Steve: And in that which of you is the first one to dive into SQL Server?

Sean: Me.

Carlos: Even with the chef thing.

Sean: Oh yeah, yeah. I quit cooking. Well, I had my own catering company even after I left my chef who I apprentice under for so many years. I had my own catering company for a few years even after I was in IT. So I got her involved in SQL. I don’t know how long I was in it before I got her involved but she was an intern at FedEx at that time and I started talking to her about SQL because I guess she was dissatisfied with the way her current position was going. We have to ask her specifically what the catalyst was but knowing her, probably just to please her man.

Steve: Well, that wraps up my SQL Family questions for today.

Carlos: That’s right. Thanks Sean for being on the program. We do appreciate it.

Sean: Anytime because I love talking.

Steve: Yeah, and as usual it’s been a pleasure. Thank you!

Episode 117: PASS Summit Retrospective

Have you ever had a week without a Wednesday?  Me neither, so for this episode, we recap our experience at PASS Summit and talk about all the amazing compañeros we met and give some thoughts around our very first meetup.  It was so much fun to meet everyone and both Steve and I gave sessions, which was a great experience–and a first for me.

One, we both realized after the fact is, we are not good at social media or taking pictures!  We apologize we don’t have a bit more to share on that front, but hey–we do have faces for radio.

Episode Quotes

“I find when I’m at a conference I get so busy and I enjoy talking with people. I just don’t think to take out the cameras down for shot.”

“It’s nice to have live music but at the same time it made a little difficult to hear sometimes.”

“Watching these keynotes from the hotel is super convenient.”

“Good questions that’s always make you feel good as a speaker.”

“Be prepared, bring two laptops and a backup plan, and a backup to your backup plan.”

“The last 5 minutes before a session starts, even if everything is ready to go and going smoothly, the last 5 minutes is the stress factor.”

 

Listen to Learn

02:32 Companero Shoutouts

07:47 SQL Server in the News

10:50 PASS Summit Retrospective, being on social media

12:47 Tuesday night at Seattle – Carlos’ Mardi Gras suit, the live music

15:56 Volunteer Party18:46 Thoughts about the livestreaming of keynotes

20:13 Wednesday sessions, SQL Trail Mix event

25:10 Thursday – Steve’s session on Database Corruption

31:15 Thoughts about the Game Night

32:29 The Sponsor Area

34:07 Friday – Steve’s session (Backup and Corruption) and Carlos’ session (Establishing Baselines)

40:23 End of conference and unfortunate story at dinner

Transcription: PASS Summit Retrospective

*Untranscribed introductory portion*

Carlos: Companeros! It’s nice to have you on the SQL Trail again.

Steve: Yes, and welcome to Episode 117. This is our PASS Summit retrospective episode, which I’ll tell you we got to meet a lot of companeros last week.

Carlos: Yes, and it was great meeting a lot of you and getting to chat with you. I feel like I’ve been doing a little bit of retrospective, a couple of retrospectives here. It is the conference season so we’re kind of giving our thoughts around the conference, what went well and what we enjoyed being out there in Seattle.

Steve: And I know I say last week, but yeah, it was last week when we’re recording this but it will be two weeks back when it actually airs.

Carlos: That’s right. Man, how time flies.

Steve: Yeah, and I guess with you being on the East Coast and me being on the West Coast we don’t have that many opportunities where we’re actually physically in the same location.

Carlos: That’s right. And we did, again you’ll see as we kind of get into this just some of the activities that we had. But our schedule was jam-packed and some client issues kind of rose and we didn’t have as much time as I was hoping. So for example we didn’t get to do an episode, I don’t want to say live, but face to face, which has been something that we thought about but didn’t plan well. But this works and hopefully the companeros will enjoy it.

Steve: Maybe next time we’ll have to have you come a day earlier or something just to do that. Or I’ll go a day early depending on where we’re going to.

Carlos: Right, there you go. There is always next year, right, sound like a bad sports team. Ok, so getting into some companero shoutouts, and because of Summit and running into so many of you. We’re actually going to break this up a little bit, so if we don’t mention your name now don’t freak out just yet because we may be working some of that into the actual story or our retrospective. So first we have Justin Wailey, who I think I incorrectly identified as being from Georgia. His Twitter account says he is in Alabama. It was good to connect with Justin. I had met him two years ago. I think it was his first Summit back then and it was good to reconnect with him.

Steve: Yup, and we’ve got Tom Norman on the list too. I don’t remember meeting Tom, do you?

Carlos: Yes. He was on our Trail Mix, but I did run into him at the opening social. He was a buddy and so we chatted. Tom is here from the East Coast and it was nice to connect with him again. Now another one that we both talked to kind of in sessions so actually in the convention center, and it’s Brent Unruh. I’m pretty sure I’m pronouncing that right. He told me how to pronounce it. Hopefully I’m getting that right Brent. But BrentU on Twitter and he is the one who brought us the question because many people talked about flying and his question was, “Would you still choose flying if you can only go 3 miles an hour?”

Steve: Oh yeah, that was long time back on the podcast, SQL Family session wasn’t it?

Carlos: That’s right. So you’re long time listener Brent. Thanks for reaching out and connecting with us.

Steve: Yup, and then we also talked with Andy Levy from Rochester.

Carlos: Yes, and we’ll probably bring him back up maybe when we get to the sessions but nice to meet him. Contributor on the DBA Tools team and it was nice to connect with him.

Steve: Yup, and I know he and I have been back and forth on LinkedIn a little bit since the conference talking about Database Health Monitor.

Carlos: Oh, got you. Ok. Another DBA Tools regular is Constantine Kokkinos. It’ was good to meet him. And he is the one who got me the, well I guess so Andy had them. He was the one who brought them but I love the DBA Tools.

Steve: You know, there were quite a few members of the DBA Tools group there and it was good to meet Constantine.

Carlos: Yes. I don’t think Claudio was there but I did talked with Rob. Unfortunately I did not meet Chrissy, wanted to do that but for a reason didn’t happen.

Steve: Oh! Yeah, I got to meet Chrissy in the speaker room. Sort of they are practicing. I heard her talking and I did not know what she looked like but I just totally recognized her voice from the podcast.

Carlos: Oh, got you. Ok.

Steve: So I got to say hi and met her there as well.

Carlos: Oh very nice. And then a couple of our MVPs want to give shoutouts to Argenis Fernandez, Brent Ozar, Cathy Kellenberger, so thanks for coming out to the Trail Mix event and talking with us, giving some of your thoughts. I know everyone appreciate getting your thoughts on some things and mingling with us so we do appreciate that.

Steve: Yeah, it was an interesting group to get to know there.

Carlos: Sean Barry is another one. Good to meet him. Kevin Wilkes, long time supporter and previous guest on the show. Adam Jacobson who is actually someone who have introduced us to a client and happen to be there, so it was nice to be able to catch up with him.

Steve: Yup, I did not get to talk to him as much as I would like to but we did talk briefly.

Carlos: Yes, and that was a reoccurring theme I think for Summit is, and that was a surprise. I mean, particularly with the podcast and that’s really where and at the SQL Saturdays I feel I want to be connected. That’s one of the things that I want people to know me for if you will is being connected. And I was surprised to just how many people I wasn’t able to talked to, so I got like empathic, Oh yeah, I got to follow up with them and ended up, I’m at home.

Steve: Well, you know, on that point. I mean, you talk to some people and they think, “Oh, you’re going to a conference for the week. It’s like a vacation. Time to relax.” But, holy cow, it was busy. I mean, one, it never stopped it seem like which is awesome. I mean, I really enjoyed it but it’s certainly no vacation. It’s busy.

Carlos: That’s right, that’s right, and then from our perspective as well we are trying to, like everyone. Not saying that it’s unique to us but trying to understand the ramifications of all the new features that Microsoft is dumping out there on us, the keynotes. We had that keynote on the Cosmos DB. That’s something that I think all of us are trying to understand, ok, what’s our future is going to be like. What’s all looking like and understanding some of those concepts. That takes brain cycles as well.

Steve: And speaking of that future, I mean does that bring us right into SQL Server News?

Carlos: It does. Yes, let’s do the SQL Server in the News. So we ran into Travis, and Travis he’s the PM for the Linux migration and I’m not exactly sure how his role has evolve. I think it’s still something that he is still doing. But we were chatting and of course they had mentioned many of the new features. We won’t cover all of them today. We’ll save some of those and kind of spread them out for future episodes. What he mentioned to me and he said that they may be announcing everything that they have just put out and SQL Server obviously became live at the beginning of the month. They are going to announce VNext as potentially as early as next week.

Steve: Wow! So if you’re not on SQL Server 2017 yet you will soon be behind.

Carlos: Yeah, that’s right. You just made to 2018.

Steve: VNext will be taking over in the near future perhaps 2018.

Carlos: Yeah, so again, just kind of continuing that cycle. And you used to think about the platform going on to Linux and all the additional features that they are trying to integrate there and what the monumental challenge that is.

Steve: Oh yeah. It’s really interesting because part of it is there are so many new things going into SQL Server at the rate that it’s going out right now and other Azure related databases and things. I mean, it’s really tough to figure out, ok what are the things that are really going to stick and are going to be the fundamental pieces that we’re going to need to know going forward, and what are the things that are maybe a little bit of hype and maybe don’t end up being that big of a deal.

Carlos: You know that’s right. I think the comment is we don’t need to feel. And even us as consultants I feel like we’re in a pretty good position because we get to be lots of different environments and touch of this new technology sometimes. But a lot of times I can feel like I’m in the Stone Age because all the new things that people are trying to do. You know, cloud this and cloud that. I think companeros you don’t need to be afraid if you’re not on VNext. Don’t despair.

Steve: Right. And I think, but that’s part of the benefit of the conference too is you go and you’re exposed to a lot of this new information. And it sort of pushes you out of that comfort zone that you’ve been in for the last couple of years perhaps.

Carlos: Sure, exactly. And if nothing more like to be educated how things are changing and the types of problems that people are trying to solve. Your current organization may be not trying to solve that problem but if you find a problem that you’re then interested in again you’re creating value and then trying to go out and solve that for a different place or a different organization. Who knows what will happen, but being in a position to be able to do that is valuable.

Steve: Yeah, absolutely.

Carlos: Ok, so ultimately today it is Steve and I are doing the retrospective, we will have some shownotes. Now, I would say pictures and I guess we can get into this a little bit but we as busy as we were you could tell that we’re not great social media people, and do not take nearly enough pictures.

Steve: Yup, and you know, that’s the thing I find when I’m at a conference I get so busy and I enjoy talking with people. I just don’t think to take out the cameras down for shot where like there are others who say, “Oh look, I got a picture with this person with that person.” Wow, I just didn’t have time to do that. I think I need to make time for that next time.

Carlos: I think we are showing our age there a little bit, right? We’re not the selfie first kind of people.

Steve: Yeah.

Carlos: And that’s like for Twitter. I mean, I like to be on Twitter and put some things out there but I’m definitely not one of those people that has to put my every waking moment detail on there. And then you get busy if that’s not the habit, right? You haven’t trained yourself to do that then the day goes by and you’re like, “Opps.” In fact today I was actually replying to some tweets that happened during the Summit, so that was kind of embarrassing.

Steve: Yeah, it’s like you need an extra 4 hours of each night to go back and catch up on everything.

Carlos: That’s right. My old man disease is already kicking in pretty good. Particularly in the West Coast time, right, I’m there in Seattle or being that my body is on East Coast time rather. I’m in Seattle, I’m ready to go to bed. But the shownotes episodes are going to be at sqldatapartners.com/summit.

Steve: Or at sqldatapartners.com/117 for the episode number.

Carlos: Yeah, so as far as some of the other things that we did. So Tuesday was kind low key, actually we’re in Seattle. I know that there was like the user group meeting or the chatgroup leader meetings or whatnot. We actually had some client engagement meetings so we took advantage some of that time particularly with me being on the East Coast to do a little bit of that. But we didn’t get to check in and I started meeting people.

Steve: Yup, and on Tuesday night there was the Welcome Reception for everyone, and we went to that. That was a lot of fun and got to see a number of people that I hadn’t seen in a year or so.

Carlos: Yes, and if you missed me, I’m sorry I was trying to stand out as much as possible in the Mardi Gras suit.

Steve: Right, some people might have thought you were an entertainer of sometime with that suit Carlos rather than actually an attendee.

Carlos: Yes. I was accused more than ones, “Are you hired to…” “Nope. Just me.”

Steve: Well, that was a pretty awesome suit and if you haven’t seen Carlos in his I guess Mardi Gras suit we could call it?

Carlos: Yes, that’s right.

Steve: Yeah, take a look online and there are some pictures of him on that.

Carlos: Yes, we actually get those up on social media. Now, one thing I thought was interesting and I don’t remember. Now, I missed last year’s Summit. I don’t remember them doing live music at the welcome.

Steve: No, that was different because last year I think there was not a live band last year. It was recorded music and they were doing like slideshow or something like that I think last year.

Carlos: So I thought it’s nice to have live music but at the same time it made a little difficult to hear sometimes.

Steve: Right, and we are standing chatting with someone and you realized that it’s so loud you’ve got to be six inches from their face just to hear them. It makes it tough to communicate. If I had any say in it, I would love to say, maybe we’re getting old, but turn down the music. That sounds like an old man story there.

Carlos: Yeah, I really have to rename this “The Old Man Review”.

Steve: I guess we’re showing through.

Carlos: I agree, it’s one of those things. I don’t know if we could have a little bit more time. Of course people come in and out of that as well, but maybe pausing that music or giving us an hour before it plays. I don’t think they did take a rest, but yeah, I don’t know.

Steve: And of course, Tuesday night was the Volunteer Party.

Carlos: Yes, and so again more old man. I stand at the welcome reception a little longer than you did. I know you and Derek went over to that. I did not want to walk over there. I guess I was too cheap to take an Uber. Oh and that was when Andy Leonard actually gave me a book. So I had this book that I’m like, “What the heck I’m going to do with that?” So I ended up taking it back to the hotel room to drop it off and then deciding, “No, I’m not going to make it out there.”

Steve: Yup. Well, so I ended up going to the volunteer reception or volunteer party I guess. What I like about it is a couple of things. One, free food. So getting to meet some of the other speakers, some of the other PASS chapter leaders, some of the other volunteers, I mean, it’s a great group of people. What I would say is sign up and volunteer in some way if you’re not part of that because then you could be part of it.

Carlos: Yeah, that’s right. And you meet the Costa Rican guys, right? The Hispanic guys?

Steve: Yeah. I guess there were 4 or 5 of them and I think I ran into them that same party last year. Yeah, good group of guys. Bright guys from Costa Rica.

Carlos: Yeah, good times. And I did the mistake of then trying to go out afterwards because the DBA Tools Team had like a reception I saw on Twitter. I’m like, “Oh, that’s just right here. I’ll go the block.” When I got there they were actually all headed to SQL Karaoke which I then felt stupid because I just got in there and it was ending. Here I didn’t want to walk to the volunteer thing so then I ended up walking all the way to SQL Karaoke and if you know where that is. It’s like on the borders of Seattle, so I walked a lot more than that what I wanted to that evening. But it was all good, so one of the guys I got to meet in line actually. Again, feeling stupid again because I didn’t hit to that ticket to get into SQL Karaoke so there was that whole thing. But the SQLBits, no not SQLBits. SQLBits is Rodney from Florida. They guy who wrote the SQL Server 2016 song.

Steve: Oh yeah. I forgot his name but he was at SQL Saturday at Redmond last year.

Carlos: Right, so he was in line there behind me and so we started chatting and that was a lot of fun.

Steve: And he was going to Karaoke even though he is a real musician.

Carlos: That’s right. But we ended up, “Ok let me see. I’ll show you how this is done.”
I also ran into, so in the opening reception another name, Bert Wagner. It was funny because I walked up to Bert and like. I know you, I can’t remember where I know you but I feel like we’ve met. And then it wasn’t until Wednesday he actually came to our SQL Trail event and like, “Ah! Now I remember from the sign up list.”

Steve: Oh yeah. And I remember meeting Bert as well and talking a little bit about his job and what he does. Yeah, definitely cool.

Carlos: Right. So one of the takeaways and one of the things now, I’m not sure that we’re maybe promoting this idea but we found that watching these keynotes from the hotel is super convenient. Now, that does mean that you’re kind of miss out on breakfast where you have to kind of go on your own way for breakfast. But that’s something that we did and just worked out really well.

Steve: Oh yeah. And what I liked about that was because normally you go to the keynote and it’s jam-packed full, and it’s hard to see. I mean, depending on where you’re sitting in it could be a challenge. So just watching from the live broadcast and watching from the hotel room then you’ve got the chance while you’re doing that when they mention something then go look it up. Go online and take a look at it while you’re watching it. And I think that I like doing it that way rather than being there where you’re just sort of stuck in a seat and glued to the room.

Carlos: Right. I’m not sure if it was my tablet or what. mood of your laptop. It seem to be fine but you do sometimes hit that streaming issues. But once they got it going, PASS did a good job on their side to at least make sure that plenty of streams were available.

Steve: Yup. So then Wednesday there were some great sessions and I know I got to see a couple of those. Then later in the day on Wednesday we had the SQL Trail Mix event.

Carlos: Yes, we weren’t quite sure what to make of this, right? How this is going to turn out and special thanks here to Sean and Jen McAllen. We actually reached out to them to get their thoughts they’ve been doing several events there at Summit. And we thought, ok, because you do hear about all these different events would there be room enough for ours.

Steve: Would people want to go to ours?

Carlos: Would people come? That’s right. So it’s Steve and I sitting there eating peanuts, so these are all kind of thoughts kind of growing through our head. So we said, “Ok, let’s do it. And we’ll reach out obviously to the companeros and to our podcast guests and we’ll kind of go from there. I think we were pleased with the turnout. Who was there, thank you, so if you were there companeros it was great to meet you. I guess there are a couple that we want to specifically point out here but we had a great time chatting with you.

Steve: And I think what really surprised me was the small amount of promotion that we did, the small amount of time we had to promote it, and how many people actually showed up in that small amount of promotion time.

Carlos: Right, so that is true. If you didn’t get an invite for whatever reason, so we apologize because we did fill up way faster than we thought it would. So again, we were kind of thinking 25 people would be super successful. That’s was kind of our number. We knew some people wouldn’t come. We ended cutting off registration at 45 and that happened in a matter of about three hours, so it was super quick.

Steve: Yup, and I guess just a little bit of background for those who don’t know that the SQL Trail Mix was. This was an event that we hosted where it was a restaurant/bar where we reserved some space. We got a number of tables and we bought appetizers for everyone although we didn’t buy drinks for everyone. Everyone was on the hook for their own drinks. And it was just show up and chat. Show up and meet other podcast listeners. Meet other podcast guests and give us feedback on the podcast. I had a great time. It was awesome.

Carlos: I agree. It was good. For example, rubbing shoulders with James, so James Youkhanis He has been on Twitter. We’ve exchanged emails. He has suggested several podcasts topics and so to finally been able to meet him, rub shoulders with him was actually pretty cool. The guys from Connecticut, Will Conklin and Chris Albert, long time listeners of the podcast, so we shoutout to you guys. Thanks for coming up and chatting with us. And then Josh Simar. I can’t remember all of a sudden where Josh is from, but he had mentioned that he’s been listening to the podcast and it was great to chat with him as well.

Steve: And then also, Andre Ranieri is a friend of mine that I’ve known through PASS events for quite a while and he showed up at the event and have a chance to catch him up a little bit.

Carlos: Right, and that was an interesting thing. While we had our registration, I don’t know, we probably ended up with 30-35 people, because people did come in and out. Registration kind of help but it didn’t indicate everyone so I guess there is some thought we need to put into how to do that. We just want to make sure that we could provide for everyone who did show up.

Steve: Yup, and I think if we’re going to do it again or I hope we do it again. I think maybe going a little bit bigger but not massive would be good.

Carlos: What was interesting because I think kind of worked to our favor potentially is that we didn’t realized that we’d also be going up against seven world series game. I think that impacted just the bar itself because when we talk, “Yeah, out Wednesdays are dead. Why don’t you come it would be great to have a group.” And then we get in there and I’m like this is dead.

Steve: Oh yeah, it was packed. It really was.

Carlos: Ultimately we would very much like to continue to meet up with you guys and I’m not sure how well this would work at other events but if we have opportunities to something like this of course we’ll try to do it again.

Steve: So then that brings us to Thursday.

Carlos: Yes, that’s when Summit got real in the sense because you had your session. You had the first of two sessions.

Steve: First of two sessions. This was the first time at PASS Summit that I actually had two sessions to present. The first one was on Database Corruption, and I was on early afternoon I think. It was one of those things where as a speaker you always worry about getting connected, at least I do. Is the presentation going to work? Is the laptop going to work and all that? The laptop I’ve had for about 2½ I’ve never had a problem. Everywhere I’ve gone, you plug it in, it work just fine. It’s even work fine at two former PASS Summits on similar equipment. But still every time we go I tried out. There is this speaker ready room that they have to try it out on their projector and make sure it works. Try that all out ahead of time and get there and it will not for some reason the multi-video output will not connect to the projector. And of course, I’m the kind of guy when I present, that as soon as the previous speaker is off the stage I am there taking every minute I can to make sure that it’s going to work. And it didn’t work, and it didn’t work.

Carlos: And the AV people came up and they were trying to help you.

Steve: Yup and some of our friends Derek Bovenkamp and Randolph West. I mean they were in the audience jumping in trying to help with, “Try this, try that. Here is the USB stick, things like that.” And finally we are about 7 or 8 minutes late when I started my session which I apologize for that and I felt so bad for doing that. But I finally just put it on a USB stick, put it on a different laptop and did the presentation from PowerPoint on the other laptop. Not optimal but what I learned on that I guess is be prepared. Back to the Boy Scout motto, right?

Carlos: We’re going to the Boy Scout motto, there you go.

Steve: Be prepared, which I thought I was but be prepared with two laptops, which isn’t a financial thing because I had an extra laptop sitting at home. I could have prepared and brought up with me. But be prepared, bring two laptops and a backup plan, and a backup to your backup plan because whatever you think might go wrong, whatever could go wrong would go wrong eventually and it did in that session. I think I got a lot of feedback at the end that people were happy with it but I just felt bad getting started 7 or 8 minutes late because of that technical difficulty.

Carlos: And thanks to PASS for providing another laptop, right, PowerPoint loaded that you can move over pretty quickly. I think it worked well. The way that you gave that was kind of a story based idea, concepts. It was an introductory session, and so as you told the stories that kind of walkthrough some of those things. I thought your audience was super interactive. They were engaged, they were, “Urgghh”. When you tell a struggle they made it their own struggle or you could sense that they have been there, those kinds of things. And so I thought that it went really well.

Steve: Yeah, and what was different with that session was an introduction to database corruption. I hadn’t plan to do that sort of deep dive type bit level corruption fixing that I’ve done in the past. If I had planned that it wouldn’t have gone so well with the technical difficulties we had. But because it was more sort of the high level what is corruption and how do you deal with it. I think it worked out there really well and it was different than how I would have presented corruption in the past as well. I had a good time with it and I think people enjoyed it. At the end of course, though everyone comes up to ask questions to the speaker and you’re trying to get off the stage and all that because there was another speaker coming in we took the conversation outside to the outside of the room and continued for about 20 minutes with different people that had questions about corruption. It was really cool. I had a good time with that.

Carlos: I think that’s when we ran into Brent as well, Brent Unruh. Yeah, so good questions that’s always make you feel good as a speaker. People would come up afterwards and want to continue the conversation. It’s a mix bag, right? As an attendee you want to get that conversation in, then there’s other people in line and you want to go off to your next session as well. And then as a speaker you want to try to accommodate as many people as you can but we know that some of you probably slip out without having to have that conversation, so we apologize. I think another reason why we want to have an event like that is so that we can have that connection time in a less time constrained environment.

Steve: Right. And also I’d like to give a special thanks on that to Randolph West because last year at my corruption presentation and this year with the corruption presentation, at the end when you’re done speaking and everyone comes up to ask questions he helps filled some of those questions. And that was really awesome because he gave me a chance to get a drink of water and listen to what are some of the other questions were while I was packing up my laptop at the end. Thanks Randolph, I appreciate that.

Carlos: Yes, very cool. So Thursday night, a couple of shoutouts to the guys that helped organize the game night. Now ultimately it’s done through PASS but Kevin Hill was instrumental. I guess that’s how I found out about it. No, that’s not true. I found out about it through you, and then after investigating it Kevin Hill was kind of helping promote it and organize it a bit. And then he and Matt Cushing, one of those I have interacted with on Twitter. It was nice to meet him and shake hands with him.

Steve: Yeah, the game night was interesting because you come in and it’s just a bunch of tables and board games. And you first walk in the room and it’s a bit intimidating to look at all the people playing board games and wonder, “Ok, where I’m going to sit down?” And you just kind of wander just around a little bit, “Hey, there is a game that’s starting right now. I’ll jump in and play.” And it was awesome. I had a good time.

Carlos: Right. I think that worked really well. I think there was some confusion because people didn’t realize they had to pay to go. And I think that $12 was to buy a drink. Again, as somebody who doesn’t drink alcohol maybe they could have done without that, but I think it’s one of those things people liked it. But it did cause some confusion for folks.

Steve: Right, and I think also some of the $12 of that Game Night goes to the cost of the staff coming in and taking down regular seating and putting up tables because that was in the presentation rooms, so they had to come in and change out the room for that event.

Carlos: Right, and that makes sense, so the venue probably have cost associated with that as well. You’re exactly right. That makes a lot of sense. And then all of a sudden we’re at Friday. Well, the other thing I guess I do want to say about Thursday before we get to Friday is that I didn’t spend a ton of time in the Sponsor Area but Thursday was the day that I was able to do a bit of that. I guess special shoutout to the Windox guys, so Paul and his team. Got to meet with his partner and talk with him a little bit. It seemed a little bit smaller this year that in years prior, the Sponsor Area.

Steve: Yeah. That was interesting and I wonder why. I think the big players were there that you normally see and they had big booths. And then some of the smaller players that you see like Sean and Jen from MinionWare. I mean, they had their booth there. But it sort of the overall it just didn’t seem quite as large as what I remember in the past. Maybe it was just a different layout that made it appear that way too.

Carlos: Yeah, perhaps. On the other hot side is that we’ve also gone to some very large conferences like the Microsoft Partner Conference or Ignite. These are like 25,000 people conferences, and so all of a sudden you get that magnitude, right? And not to say that, I can’t remember what the attendance was but between 4,000 and 5,000. Not to say that that’s small but you’re talking about a quarter of a size and so it can pack things pretty quickly.

Steve: And then Friday, so we both had sessions. I had a session that was right around like 11:00 – 12:30 so right before lunch, and that was on Backup and Corruption. And although my laptop didn’t plugged in quite as easily as I would hoped because I spend hours working on it the night before to make sure it was going to work. It worked, it was just something to do with the video switching, Switching from full screen PowerPoint over to Management Studio. I had a little difficulty with there but it was better than the day before, and the topic was on Backup and Recovery. The thing that I had a lot of fun with was automatic restore script where when you write your backups, it would write the restore script at the same time. So that if you ever have to recover from backup you just find the restore script associated with the backup or transaction log file, open it and then uncomment a couple of lines and you’re running your restore. That was sort of the crowd pleaser I think in that presentation and it was a lot of fun.

Carlos: Oh very nice. Yes, so I was running a bit behind and I know that I had offered my laptop as a substitute if you needed it and by the time I got in there, you’re on the agenda, the presentation was fixed, and your corruption session was full. But the backup session was even fuller. I mean, there were lots of people standing and I thought, “Hmm. I’m not going to be able to get in this one.”

Steve: No, no. So the laptop problem was resolved and the presentation went smoothly from that perspective so that was a lot of fun. So then your session, Carlos.

Carlos: Yeah, so I was at the end and.

Steve: Establishing Baselines, right?

Carlos: Yup, through monitoring or managing performance through baselines was that kind of idea but taking baselines and thoughts around that idea, and so the last session of the day. I mean, obviously at that point and people are Summit is now wearing on them and everybody is a bit tired. So first I will say thanks if you were there, so again, to Will and Chris. I know who were there. Of course you were there, Andy was there, Mariano came, so thanks for you for coming out. That was my first time speaking at Summit and I feel like I do pretty well in group presentations. I’ve given lots of them and feel at home. I wasn’t so nervous and so I actually got up on the platform. And for whatever reason, that visual like being elevated. It was like, “Whoah.”

Steve: I’ll tell you. I personally think the last 5 minutes before a session starts, even if everything is ready to go and going smoothly, the last 5 minutes is the stress factor, right there. And then once you start talking. I mean I could see you getting nervous ahead of time, once you start talking then it really smooth over at that point and everything was cool I think.

Carlos: Right, so to help us some of that. Like I tried, which didn’t seem to go pretty well was creating a PowerPoint or some way to potentially engage the audience a little bit beforehand. Now I started just asking some questions so I had a little PowerPoint. I call it “Where in the world?” just like a photo and then the idea was you guess where it was. That didn’t seem to engage to many people which I thought was curious.

Steve: But there were a lot of interesting pictures that kept people occupied for that 5-10 minutes before the session started.

Carlos: Right, I guess there is that. But then once we get in there I felt a bit more comfortable. You know, you’ve given that session several times and that’s kind of when the habit start to kick in. I was happy. One thing I was happy about with the session is that we were able to still had some conversation. One of the things that I don’t particularly, I should say care for. My style I guess that’s a better word. I like my style to be a more interactive. I feel like if I’m just sitting there the only one talking in a presentation then I’m doing something wrong. I was glad that we were able to get some feedbacks, really some comments, right and kind of start a little bit of dialogue there.

Steve: Yeah, with that it almost seems like the larger the room is, the more difficult it is to get people to interact because they are afraid to speak out in the bigger room.

Carlos: Exactly right. That’s right and I guess, to that point we had. The room is, it wasn’t that humongous room because some of those rooms were big and I don’t remember exactly how many people would fit in there but we probably had, I don’t know, I’m going to say 50 people in there but I guess that was just on the cusp of still people being able to interact with each other. Once you get more than that it is like that Backup and Recovery session for example would have been a lot more difficult to do, I think.

Steve: It was a good session and I think it was the end of the conference session which is a tough timeslot but it went really well and I enjoyed it.

Carlos: Yeah, well thanks.

Steve: I thought one of the things I caught that’s really interesting is you’re talking about different products and tools and you bring up DBA Tools. I forget exactly what feature you mentioned but something with DBA Tools that you demoed and then Andy Levy, afterwards comes up or he said something about it because he works on it, and afterwards he came up and he said he actually worked on that thing, that feature that you were showing. That’s right so he had check in code. To him it was kind of a cool thing that the code that he has worked on has been shown at Summit. Only the best for companeros, right? So kind of cool to be able to show work and then that was it. We kind of went home. We went out to dinner with Derek and I headed to the airport.

Steve: Yes, so unfortunate story at dinner. We made a new rule that anyone who chokes and needs the Heimlich Maneuver has to buy dinner.

Carlos: Oh man, that was a bit scary.

Steve: We sat down for burgers and onion rings and I took the first bite of an onion ring. It wasn’t that big of a bite and somehow it got lunged in my throat. I wasn’t breathing. I did not have a working airway at that point.

Carlos: It was blocked. I was talking with Derek and you started kind of making a noise. I looked at you and your face was read and I was like, “Hey, are you ok?” I kind of pat you on the back and looked at your throat and I was like, “Oh man, we have a problem.” And we were in a booth which made it worse, we’re sitting next to each other and I was actually surprised that how quickly you were able to get out of that booth.

Steve: I wasn’t breathing. I had an extreme motivation at that point because I had an onion ring lunged in my throat.

Carlos: Yes, so that boy scout training coming back. That was a bit of a scary moment.

Steve: Yes, so very special thanks on that one, Carlos. I really appreciated that and I ended up buying dinner because of it.

Carlos: We’re glad that you’re still here and we hope that we never have to invoke that rule at dinner again.

Steve: Yes, I hope that it never happens again either, to me or anyone I’m dining with.

Carlos: Yes, that’s right.

Steve: But what a way to finish off a week.

Carlos: That’s right with a bang, that’s for sure. Ultimately that was our conference. We appreciate you companeros for sharing those of you who were there. For those of who weren’t, we’ll hope to connect with you at some other time.

Steve: Yes, and maybe next year at our SQL Trail Mix.

Carlos: That’s right, yeah. I think that’s going to do it for today’s episode. Our music for SQL Server in the News is by Mansardian used under Creative Comments. You can reach the shownotes for today’s episode at sqldatapartners.com/summit.

Steve: Or at sqldatapartners.com/117.

Carlos: And as always if you have thoughts about what you want us to be talking about on the podcast, you can reach out to us on various social media methods. One of them being LinkedIn. You can reach out to me I’m Carlos L Chacon.

Steve: You can get me on LinkedIn as Steve Stedman and we’ll see you on the SQL Trail.

Episode 116: Are people still using PSSDiag?

If you have ever worked with Microsoft support, you probably came across the PssDiag tool they use to collect information about your environment.  My experience with the tool is it always seemed, well–a bit clunky.  Now, it does collect lots of information and seemed to be a bit overwhelming when I was first a DBA.  Fast forward a few years and PssDiag was not a tool I use anymore and was not sure if anyone outside of Microsoft was using it.  I run into Jared Poche in Raleigh and he mentions he really likes the tool and mentions some of the improvements they have made over the years.  Well, you can guess where this is heading.

We talk with Jared about his experience with the tool and cover some of the basics about how you might go about using this tool in your environment.  There are two pieces to this tool, the first is the utility that collects the data and the second is the reporting piece SQL Nexus.

Are you using PssDiag?  Let us know in the comments below.

Episode Quotes

“It’s a really good tool in that it captures data from a lot of different sources and you tend not to miss much with PSSDIAG.”

“It is absolutely the go to tool for Microsoft.”

“Again using PSSDIAG and the Nexus will give you very visible data.”

“My biggest concern with PSSDIAG is people being too aggressive with the data they are trying to return from it.”

“Running it over the network is a really bad idea.”

Listen to Learn

02:47   What is PSSDIAG? What it runs and its usage?
04:54   Is Microsoft’s PSSDIAG and the publicly available different?
05:38   Using PSSDIAG in a small shop or in a large shop.
06:51   SQL Nexus
08:19   Improvements on PSSDIAG
09:42   How often Jared uses PSSDIAG?
10:13   Compelling reasons why to use PSSDIAG
16:04   Hand off point of getting information about the system, transitioning to smaller data set
20:52   PSSDIAG or DMVs? Which is a great place to start with baselining
24:12   Other common parameters in PSSDIAG, light capture vs. detailed capture
28:14   Tips and suggestions for starters
32:02   SQL Family questions

About Jared Poche

Jared Poché began working with SQL Server as an instructor for certification classes and has a passion for teaching and performance troubleshooting. Jared spent 10 years providing customer support at Microsoft, most recently as a Sr. Support Escalation Engineer. He is currently a database engineer working in Morrisville, NC and is leveraging his extensive knowledge to develop online coursework for SQL Server.

Transcription: Are people still using PSSDiag?

*Untranscribed introductory portion*

Carlos: This is Carlos Chacon.

Steve: I’m Steve Stedman.

Jared: I’m Jared Poche.

Carlos: Jared, welcome to the program.

Jared: Thanks for having me.

Carlos: Yes, it’s good to have you on and we’re kind of taking a break from our usual suspects at ChannelAdvisor. We met at the SQL Saturday in Raleigh and we’re talking. Ultimately our conversation is on PSSDIAG, but always good to talk with the ChannelAdvisor folks and your team is growing. You’ve added a few new community members that I know of and that’s always fun.

Jared: Yeah, Anders spoke at the SQL Saturday in Charlotte recently. I think we have 5 people there.

Carlos: Ok, very good.

Jared: I’m still kind of astonished by how active the DBEs and DBAs are in the community.

Carlos: That’s right, and so companeros, for those listening and I go back to a comment that Brian made, one of the DBA Managers over there. He said, “Some of the folks at ChannelAdvisor are very bright people and you don’t hear about them in the community.” And while Jared you’re getting out there a bit more of late, I kind of feel like you may fit that mold. Lots of the companeros may not know you unless they are kind of in the Raleigh or North Carolina area but hopefully that will change particularly with this interview.

Jared: Yeah, I’m trying to fit that mold. Yes.

Steve: Yeah, and I think the podcast is definitely changing that for some of the ChannelAdvisor DBAs that have been on.

Carlos: That’s right.

Steve: I think ChannelAdvisor has the record now for the company that has had the most guest.

Carlos: Unless we’re going to count Microsoft. Microsoft may technically still be ahead with all the PM they’ve had on.

Steve: Oh sure, yeah.

Carlos: Other than Microsoft… those two different layers are way ahead of everybody else. So ultimately one of the conversations we had, again kind of going back to that Raleigh conversation, and we were talking about performance and collecting information about the database. I can’t remember exactly what your comment was but you mentioned PSSDIAG. And I think my comment was, “Ugh, does anybody still use that?” And you said, “Yes, it’s great.” And I thought, “Oh, we’ve got to have you on the program to talk a little bit about it.” And so for those who haven’t use that, I guess let’s do a little groundwork here. Let’s just talk about PSSDIAG, what it is, and maybe even a little bit of history of it.

Jared: Ok, so PSSDIAG is this fairly own composing tool that Microsoft has developed and makes publicly available. It’s up on, I can’t think about the site is currently, but it’s publicly available for download. And this own composing tool that is used primarily for SQL Server performance issues. There are a few other odd circumstances you might want to run this but mainly you use it for, and Microsoft encourages to use it to gather data about performance issues they are having with SQL Server. And it runs a performance monitor output in the background. It runs a profile or trace on the latest version an xEvents trace if you want. It runs a bunch of queries to get information in DMVs. It will produce like 50 or 80 files of output in the folder that produces and it can produce quite a large volume of data. And the idea is you run this thing, you might run it for half an hour or whatever or if you had a different window when the problem occurs. And you know the problem might happen sometime in the next hour or so, you want to run it and capture data while whatever problem is going on. It’s a really good tool in that it captures data from a lot of different sources and you tend not to miss much with PSSDIAG. There are a few odd things occasionally that you might want to add on to say the normal configuration but it’s a really own composing tool and it puts a lot of data at your disposal.

Carlos: Right. Now, you are a former Microsoft employee, right?

Jared: I am. I was working at the CSS site in Charlotte for about 10 years. And a little bit of a contractor as well, a year and a half.

Carlos: I have heard, I guess maybe you can clarify for us. So traditionally you’re going to be exposed to PSSDIAG for the first time, at least I was. If you’re company has Microsoft support you call them up, you know, you’re looking at a performance issue. This is the go to tool, right, for them?

Jared: Yes, it is absolutely the go to tool for Microsoft.

Carlos: One of the things that I had heard was that if you go to their support site and what’s publicly available is slightly different, true or false?

Jared: They have an internal version of the tool, yes.

Carlos: Ok, but from the nuts and bolts perspective ultimately the same?

Jared: Ultimately the same. There might be some slight differences in some of the specifics of what options you have available. GitHub was what I was thinking of earlier. The current publicly available version is available on GitHub, and in the past there were some slight differences in kind of the custom configuration options you had in the internal tool and the publicly facing tool but they are 98% the same.

Carlos: Got you. Ok.

Steve: So then, I mean if you’re doing a support ticket with Microsoft they are going to ask you to run this. But if you are a DBA out there in a small shop and you’re having some performance issues, is this something that’s going to be easy to use and just that they could run and dig through the results and get some value out of it or is it more just for Microsoft to use?

Jared: It is something that you can use. As far as somebody who is in a smaller shop, I would actually have less concern about somebody in a smaller shop using it than somebody in a bigger shop with very big, very active, very busy servers. They don’t know what’s configured. The question that is being asked many times, “Will PSSDIAG affect my performance?” And the answer is possibly, and it depends on how you configure it and how busy your servers are. So in a smaller shop I’d be less worried about it. When I started using PSSDIAG in 2004 I think, I wasn’t very versed in performance troubleshooting at that time either and so I spend a lot of time looking at the perfmon manually. I’m looking for the profiler manually. There is also basically a sister tool with this which is SQL Nexus which is also available on GitHub. And what SQL Nexus does is basically grind through a lot of the data that PSSDIAG provides. Does some analytics on it, you know, figures out what’s the biggest resource consuming queries are. Your big wait types and puts it into a nice GUI set that you can see that stuff and consume it much more easily. You can select through it manually but SQL Nexus does make a good job of making that easier to understand.

Carlos: And worth noting in the beginning, I can remember being in that same state as far as just kind of getting my feet wet so to speak from all the tools that are available and taking a peek at that. One of the things that is a bit I’ll used the word overwhelming, or can be overwhelming of PSSDIAG is because it’s collecting all that data. It’s almost like where do you start.

Jared: It is difficult to know where to start.

Carlos: Yeah, and you kind of have to. I think if you have a good understanding of what all of those pieces are. It makes a little bit easier but starting there like your first troubleshooting starting place. Yeah, it’s going to take some time to get through, so be patient.

Jared: It can be intimidating at first. I do think that SQL Nexus makes a lot of that easier by kind of figuring out what’s important for you in doing a lot of the analysis by itself.

Carlos: Well now, let’s talk about some of the improvements. Again, so my experience with PSSDIAG is probably, gosh. You know, 7-8 years ago at least now. I remember SQL Nexus being a bit, I hate this word, clunky. But I felt like it was beyond maybe what I could do at that time. I felt like I didn’t have the experience to get it all set up. I never quite seem to fit that right. Is that getting better? Is that easier to use now?

Jared: For me it has gotten a lot better over the years. The last couple of releases have done more, there is more things exposed such as they added in something to get out the details of say sp_configure which is something that PSSDIAG has gathered for a long time but it will flag things in that that are unusual and things you might want to reconsider. I think that the GUI interface is better than it was. I think there is a lot to positive say for that tool, so I feel really good about SQL Nexus. And I kind of feel like I was doing myself a disservice because when it came out at first I was just kind of like, “Ahh, I don’t need that thing.” Because at that point I’ve been using PSSDIAG for a couple of years and I knew what to go and get and what to look for although it does in the long run it did saved me a lot of time using Nexus.

Carlos: I see, interesting.

Steve: How often do you end up using PSSDIAG in your job?
Jared: Currently I do not use it very often. I mean currently if I’m looking at a performance issue, and this is something that I started doing a lot when I was still working at Microsoft. A lot of times I’ll just use DMV queries and figure out what I can from that about an ongoing situation, and I feel like that gives me information very very quickly I can use to diagnose problems.

Steve: Ok, great.

Carlos: Now, again going back to our conversation. One of the things you got to mention was one of the things you thought PSSDIAG was really really good at. So we’ve talked about kind of collecting the metrics and whatnot, so what are some of the compelling reasons of why you use PSSDIAG? Other than just collecting a whole bunch of metrics, like you everything that you might need.

Jared: Well, it is all encompassing and doesn’t miss a lot so that’s a positive. The perfmon collector gets you everything you’re going to need in the vast majority of cases. The fact that it gets the information from the windows event viewer can really be helpful on occasion too especially if you’ve got a situation that you have disk issues. You might have errors that will come up and say the system log. That’s not something you’d necessarily think of unless you had a tool that’s helping to expose that information for you. One of the things that I got to really be fond of. In the latest version they have improved the ability get xEvent traces, so that’s something that’s been in the last couple of releases. But the last one that I saw before the current release, it was till kind of a stub, like there was a tab for configuring profiler and you really want to get away from profiler. I wouldn’t even think about running PSSDIAG with profiler on the servers on ChannelAdvisor in production. There’s no way.

Carlos: Interesting. I guess I want to come back to that, right.

Jared: Well, that’s a matter of overhead. I mean using xEvent traces and I’m more familiar with looking at profiler traces but I’m getting pretty familiar with xEvent traces. And the xEvent has a lot less overhead and the overhead of profiler can affect the performance of our system. That’s the thing in the past. And the busier your server is, the more likely that is to be a problem. So the fact that they’ve improve it because the xEvent thing used to just be a stub. And actually I remember one of the releases a couple of years ago there was a tab for configuring an xEvent trace and if you try to use it it would say, “Wait, this isn’t actually working yet.” So now it’s fully functional, now it’s fully complementary or equivalent to what you could get out of profiler so that’s good. One of the things I really really like out of it is in the output file there’s, it names a lot of things based on the name and the system and then the version of SQL Server, so the file is really long. There’s one file that is the perf_stats_startup.out, and what the script does it has like three different sets of DMV queries that runs. And it does one every minute that will do things like getting information from DMOS wait stats and it has another one that run maybe every 30 seconds that will get information about memory consumption. But the main loop, the thing that it runs like every 5-10 seconds runs queries against dm_exec_requests to tell you what all the active queries are especially the ones that are waiting or causing waits in the system. It does a second query, that for all the things that it got with the first query. The second query will return the statement that’s being run and the procedure it’s a part of which is very helpful for narrowing down what you want to look at and finding the section of code you actually care about. And the third query that tells you what the head blocker is if any blocking chains you might have. One of the things I would do at times to diagnos the situation very quickly is I would just open up that file, look at the information we’re getting from dm_exec_requests about active queries. I would have that open in Notepad++ or Textpad and I would just scan through that very quickly. Because it will run these queries like, I don’t know it it’s every 5 or 10 seconds. But each one you’re just getting one snapshot of what you’re running queries and what your waits look like. But if you see one snapshot, and another and another and you can thong through them all really quickly, it’s easy to come up with an overall impression of what’s going on. And to say, “Oh well, yeah I’ve got some I/O waits here and there but that’s not a big of a deal. But I’ve got cxpacket waits lined up for days. I need to figure out what’s going on with that.”

Carlos: That’s interesting as you mentioned that and I agree that that idea of the snapshot is very important. One of the things that I actually will have done at the PASS Summit is talking about baselines. So how do you, I guess thoughts around leveraging a tool like this which is kind of a snapshot but in a sense to give you, you want a broader than just what’s my current CPU, right? You want a little bit more history than that but it’s not something that you’re going to be running for weeks at a time either.

Jared: Yes. I would really consider using Nexus with that because of the fact that you can gather PSSDIAG whatever time you want to. If you want to gather an hour or two worth of data at whatever time of the day you feel will be much appropriate, go ahead and do that. But then use Nexus with that and you’ve got a very visible, you’ve taken that data set and made it very visible and very easy to look at and to refer back to it later because it’s like you’ve got all the perfmon data is in there, and average out. And you can say, “Ok, for these two hours this is my CPU consumption. This is what disks look like. This is how much free memory I had. Here’s what my main queries were, here’s what my mian wait types were, here’s what my resource consumption per procedure look like and you can have that kind of information for multiple sources and have it easily accessible through SQL Nexus.

Carlos: Ok, so you mentioned you kind of switch off of it, so when do you feel like it’s the hand off point? It seems like it’s a very handy tool particularly when you’re, I don’t want to say going in blind, but again if you think about the origin, right? So I am a support person, somebody calls me up, I don’t know who they are, I’ve never seen the system before, I want to get as much information as possible. Where do you see the hand off or the evolution perhaps of I need to get information about the system even if whether I’m new or whatever. I’ve just been tasked to this database to using PSSDIAG to, “Ok, now I know something about it. Maybe I don’t need to use all the pieces. I am more comfortable with the DMVs or something like that.”

Jared: Well, I think the more that you know the environment that you’re dealing with; the easier it is to transition to using a smaller data set. I think part of the reason PSSDIAG is as usual it is for say a Microsoft engineer is they don’t know what you environment looks like, and they don’t necessary know what they’re looking for. They don’t necessarily know what say unusual sp_configure settings you had. There is a lot of things that could potentially be the problem and there are more things that they might need you to consider. Now, in my case, part of the reason I made the transition, started doing more of my troubleshooting with DMVs at least were possible and to me there are still circumstances you just can’t replace PSSDIAG because of some of the things that it does. But part of the reason I made the transition was just for efficiency. So let me give you a hypothetical. Let’s say that you’re the customer and I’m working as Microsoft. We gather half an hour of data off of your very busy production server. You end up with say, three or four gigabytes worth of data in your output folder. You start zipping that up, you’re going to have to upload that to Microsoft. There’s a tool within Microsoft that when you upload that it will go ahead and unzip it and run it through the SQL Nexus output and I’ll eventually as the support engineer would get an email with basically the Nexus stuff already run. And the Nexus analysis that could take some time, a large data set, that can take a couple of hours. So I was actually on a critsit, if you’re familiar with the term. I was on a critsit with somebody in one case and they had a high CPU issue. He had been troubleshoot it himself and actually the problem went away before he contacted Microsoft and I was still working with Microsoft at that time. But the time I get on the phone with him the situation has been over for two hours. Now, PSSDIAG, I could configure PSSDIAG and send it to him and say, “Ok, if your problem happens again start running this and give me a call.” But I kind of wanted to see if I can do something more than that. I was thinking and I said, “So how did the problem go away? What happened?” And he told me, “Well, it stopped on its own. I was trying to figure out what was going on and it just stopped. I didn’t do anything.” So I asked the guy, “Ok, did you restart SQL or did you say flush the procedure cache?” Because that’s something a lot of people will do and it was kind of frustrating when you’re a Microsoft engineer. “Is the problem still going on?” “Oh no, I rebooted the server.” “Oh well, I guess I can’t gather any actual data.” But it hadn’t restarted, it hadn’t flush the procedure cache and so I thought to myself, “Well, whatever plans were consuming all the resources, consuming all the CPU, they should still be cached.” Let’s go run a query against dm_exec_query_stats that I had and I ran that and we looked at the output. And he had about 5-6 queries that were consuming significantly more CPU than anything else. Like you look at them in descending order and after about query 5 the CPU dropped by three digits. That’s actually unusual very often when I see a high CPU case and I’m looking on the DMVs. You’ll see that there is one or two things that’s really killing it. But in this case, I used the DMVs, I’m able to see the cache plans. We can look at the cache plans. We can go find the procedure that is related to it. We look at the table, we look at the indexes. I was able to get to everything I needed just from running a query basically against dm_exec_query_stats. And I didn’t have to have him gather data and upload it to me and I spent hours waiting for the data and then analyzing the data and then get back to him. I had an idea very quickly of what was going on. And after that one, that made a really compelling argument to me about the efficiency of using the DMVs.

Steve: Ok, so then just sort of comparing that efficiency and then getting all the information there. I mean, if you’ve got a new server that you are now responsible for you’re going to take over maintenance on. Would you say that, I mean, PSSDIAG would be a great place to start to get that sort of baseline and so that you know what’s going on with it and then later deal with the DMVs or you just sort of skipping it sometimes and go directly to the DMVs.

Jared: I think you could do either approach? Again using PSSDIAG and the Nexus will give you very visible data. I think the way it presents things might be easier for some people to see and understand and realize what normal and how this is different from that. But with the DMVs I think one thing that would be effective would be to have ongoing normal processes where you collect information from say dm_exec_query_stats or dm_exec_request and put that into a permanent table that you’re willing to keep for x amount of days. We some of this at ChannelAdvisor to keep track of say dm_exec_request information so that we can see waits that are going on inside the system and we can look back at a given period of time and see what kind of waits we normally see on a Wednesday at 2 o’clock in the afternoon. We can see that. You could do either approach. Like I said I like Nexus for the fact that it has a decent GUI on it. But you could also just take the DMVs have automated things to gather that sort of information. It depends on how long you want to retain that information. You have to be a little careful about trimming your tables if you’re taking that raw DMV information and storing it somewhere as opposed to say running the PSSDIAG and running the Nexus. Now you have the nexus output, you can actually get rid of the PSSDIAG data, however any gigabytes that is. You can also, Nexus takes the data and puts it into a database and it actually needs a database within SQL server that can connect to and juggle the numbers around. But you can backup that database and then at whatever point you can move it to another server. You can point SQL Nexus at it and it’s very effective in that fashion. Ok so for baselining to me you could use either one. It just depends on your preferences.

Carlos: So we talked about wanted to hit some of the new features and I think you’ve mentioned a couple of them, obviously Nexus being one. So as far as like, I’m sure even the documentation has improved a bit so you mentioned some of the parameters or settings you could have. So if I just run PSSDIAG with no parameters I’m going to get the event log, I’m going to get the SQL Server log, I’m going to get the profiler trace. I am assuming it’s still the default. You mentioned I could get xEvents, now I’m assuming parameter change and then I’m going to get the performance monitor metrics. So what other common parameters am I using when I am talking about PSSDIAG?

Jared: Well, the thing currently is and this is where people go wrong with it or can go wrong with it is now there is basically default packages or default scenarios or scenarios that you can just basically say I want to gather general performance or I want to gather detailed performance. And one of the things that I would suggest people not to do with PSSDIAG especially on the production environment would be to go in and start by telling it to do a detailed performance output before you’re familiar with the tool and have an idea of how much data it’s going to capture in your environment. I would go with a general or light option. The big difference between a general or a light capture and doing a detailed capture is that when you do a light capture you will gather the events for whether you’re on profiler or xEvents, you will get RPC started and completed and you will get SQL Batch started and completed. And you will get errors and you will get a bunch of other things as well but the main things will be interested is batches and stored procedures started and completed. When you go to detailed, you’re going to gather a lot more events because you’re going to gather statement level events. You’re going to gather every statement starting and completed within SQL Server and you’re going to gather execution plans of whatever type you’ve selected or whatever type is the default for the detailed package currently. And think about it, if you got a store procedure and that store procedure has 50 statements in it then by going to a detailed trace you’re going to gather, instead of gathering two events for that stored procedure – starting and stopping, you’re going to gather those two plus 50 for each statement starting, 50 more for each statement stopping and for each non-trivial statement that you’re going to capture the execution plan. So there is a big difference in terms of how much data that you’re going to be gathering and thus it’s going to be more likely to affect the performance of the system in some way, and it can. And if you’re SQL Server is pushing 2,000 transactions per second that’s probably not a problem. If it’s pushing 25,000 transactions per second the same package might bring that server to its knees. And especially if I’m not familiar with running PSSDIAG in my environment I would start off by doing a general or a doing a light and seeing how that works before I try to crank it up.

Steve: So I guess from that perspective it also makes sense if someone is just learning it if they have access to a test server that might have far less load to just give it a test there first to make sure you understand what do light versus the more detailed is going to do.

Jared: Absolutely. And there are some interesting circumstances like there are things in there that are made for gathering information about always on or gathering information specifically about memory issues or things like that. I think there is used to be one for, like there is still a collector for service broker and there are circumstances you might want to gather those things because it provides a lot of information. There is one for link servers that will run all the queries to get information about what link servers you have set up. So there are a number of useful little things like that. But my biggest concern with PSSDIAG is people being too aggressive with the data they are trying to return from it because it can affect performance. I had cases when I was working at Microsoft that I would go in with the PSSDIAG package and we’d run it and we gather data and I analyze the data and realized, “You know what I can’t use this because all I can see is the SQL trace waits.” I can’t tell what else is causing problems on the system because the tool is gathering too much information that were waiting for that information to be written out. And I can’t see any other bottlenecks because of this bottleneck.

Carlos: Ok, so I guess then at this point for someone who is about to go and give it a shot maybe after listening to the podcast. I mean, besides keeping the sampling light there. Any other tips or suggestions to help someone get started?

Jared: One or two that’s big. Another thing that affects performance is running this over the network as oppose to running it locally on the server. Running it over the network is a really bad idea. I would dissuade you from doing this if it all possible. The idea is that SQL has these delays when it’s trying to write the information out. Now, PSSDIAG does server side trace but if you’re going to be using profiler or using some other tool to do your tracing, I would really suggest not doing it remotely. You can run PSSDIAG remotely and tell it to connect to this server and this name but it introduces a much longer latency in terms of how long it takes for the trace to write out that data because now you’re… And that is affecting how the SQL Server engine operates now, so that’s a whole different magnitude. The other thing that I’ve seen that causes problems and it’s not specifically with PSSDIAG is not to run a whole lot of traces at one time whether you’re using xEvents or profiler. There used to be commercial tools. I won’t name any, and I probably couldn’t remember them all of them. But that would run four or five or more profiler traces at a time and that would affect the performance of the system because SQL Server is trying to write out things and keep track of things for each one of those different trace files. Especially when you’re doing multiple and you’re doing it over the network, that can really be consequential.

Steve: Yup, interesting. Well, ok so a side note put on that running profiler traces. I mean, what would your take be if someone wanted to run a profiler trace like 24/7/365, all day, all year just to keep track of every query that’s ever being run? How big of an impact something like that can have on performance?

Jared: Again it would depend on how busy the server is. On a busy production environment I would ask them to find exactly what it is they are trying to find out here because I don’t think that’s what they are trying to find out. I think that’s a lot more information than you could reasonably use. How would you aggregate that information? What are planning to do with it? And in any case I would stay away from using profiler at this point in any case because xEvents is now a super set. It has been since SQL 2012, has been a super set of what profiler can do. We have more kinds of events of xEvents now. It provides more information to you and it’s more efficient. It was designed from the ground up to be faster and to have less overhead than profiler. I really encourage you. If you’re more familiar with profiler, I entirely understand. But I would really go ahead and pay that price because I think it’s more than worth it.

Carlos: Well now I’m interested to find out what happens with profiler. I don’t think it’s going away and the reason I say that is that they just announced. Or maybe not just but one of the things we talked about SQL Server in the News is they’ve added query store as a source for profiler.

Steve: Alright, shall we move in to the SQL Family section?

Jared: Sure, go for it.

Carlos: So Jared, how did you first get started with SQL Server?

Jared: I got started when I was a part-time community college instructor. I had been teaching for one semester and my older sister actually work at one of the colleges I was working at and she asked me if I knew anything about databases and my answer was no. At that point the college and this was Forsyth Tech in Winston, Salem. They did some certification classes for SQL Server and one of them has actually in one of their 2-year degree. And they needed somebody to teach this classes for the next semester, so she came to me and asked if I could do that and I said, “Ok, I’ll look into it.” And I basically picked up a book and taught myself enough of SQL Server to get certified in it and then I taught the classes there for about three years. And from there at that point, the interesting thing about teaching is whenever you’re trying to teach people something you have to know it to a greater degree than your teaching. And also they’ll ask you questions about the subject that you’ll never think to ask yourself. So it’s like you as a teacher end up learning even more from having students because you’ve already figured out all the questions you had in your head but now you’ll get a whole bunch more questions and you have to figure out how to answer them too. So I taught that for about three years and that was when I applied for this helpdesk e-job that I saw on Monster which actually turned out to be a contract position to go work at Microsoft at Charlotte.

Carlos: Interesting. So it’s very similar almost to like getting started with presenting in a sense, right?

Jared: Yeah.

Steve: So if you could change one thing about SQL Server, what would it be and why?

Jared: That’s actually pretty difficult. Trying to think of, you know, I’ve had pain points here and there over the time. I’ve seen people do some horrible things with service broker. I’m trying to think, what else? At the moment I guess the biggest thing I would come up with is page latch waits because there is such a big consideration and a kind of a thorn in the side currently at work so I guess I would try to some find way to eliminate that level of pessimistic concurrency. We’re trying to move to doing a lot of things doing in-memory optimized tables and whatnot to move to more optimistic concurrency because with our throughput, with our load, we need to have that. We need to get rid of the contention we get from page latch waits. That’s a big consideration.

Carlos: Got you.

Steve: Ok, very nice.

Carlos: What’s the best piece of career advice you’ve ever received?

Jared: Difficult to say, I think one of the things I would go back to is when I was still contracting, when I was still starting off at Microsoft. I was speaking with one, my tech lead at that time was Bill Carroll. He is now an Escalation Engineer. He’s been at Microsoft forever. I was working on some performance case and at that point hadn’t a lot of experience working. I had no experience before I came to Microsoft working on performance cases. I was finding them difficult and Bill Carroll said, “We will always have room for somebody who knows how to handle performance cases around here.” And so that I think really probably was the best piece of career advice because at that point it gave me a niche. It gave me something to figure out and try to focus on and I’ve done a lot with that so I’ll give Bill Carroll the nod there.

Carlos: There you go.

Steve: Alright, and our final question. If you could have one superhero power what would it be and why would you want it?

Jared: That’s funny to me in part because I’m an avid video gamer and my wife and I used to play City of Heroes so we have played superheroes many times. For selfish reasons I would like to say either flight or teleportation because that would really be cool. But honestly in real life if I could have a super power that would be the power to heal other people just because who I am as a person if I could do something that could make life better for somebody else. If I could take away something horrible that’s happened to them that would be a great thing to do.

Carlos: Very cool. Well, awesome.

Steve: Alright.

Carlos: Well Jared, thanks so much for being with us today. We appreciate the conversation.

Jared: Well, thanks for having me.

Steve: Yeah, that was great to be able to chat with you, Jared.

Jared: Same here.

Episode 115: The Companero Conference Retrospective

Companero ConferenceIt was a beautiful idea.  Gather together with a group of our podcast listeners in a conference type format and find ways for us to connect and help each grow.  I had seen it down in other events; however, I wanted to add my own flavor.  I wanted this to be different.  While the event turned out great and we got good marks from the attendees, we didn’t have the response we were hoping for.  In this episode of the podcast, we have our companero conference retrospective.

We try to give our honest feedback about what we did well, what could have been improved, and what the attendees reported.  I can’t say enough about the help I got from our speakers–Jonathan Stewart, Kevin Feasel, Randolph West, Tracy Boggiano, and Doug Purnell.  They were awesome and the event was much simpler because of them and their commitment.  They are truly my companeros on the SQL trail.

WHAT SHOULD WE DO WITH OUR CONFERENCE?

 

We would like to send you a survey on what you think the future of the conference should be and whether we should keep it around.  We look forward to hearing from you.

Episode Quotes

“I wanted a way that people could come together, could talk with each other, connect, build those relationships.”

“We felt like we were able to create that intimate environment where people could participate.”

“You’re not just a face in the crowd. You’re actually there participating and I think that’s the difference.”

“I think the pull of those bigger conferences was just too big and lot of people ended up going to either one of them.”

“I don’t really like the word conference… because we’re going for the unconference idea.”

“I think we learned a lot. I think I’m glad that we went through it and thanks obviously to those who attended and to the speakers.”

Listen to Learn

00:39   Companero Shoutouts
07:25   SQL Server in the News: Import Flat File Wizard
09:06   SQL Server in the News: Service Packs to Cumulative Updates (Pros and Cons)
13:45   Links of the show notes
13:57   The reason/objective of putting up the Companero Conference
17:59   Challenges that happened
20:57   Conference outcomes – Are the conference objectives met?
25:04   What are the big challenges?
36:23   Thoughts or changes for future events, to do it again or not in 2018?
45:30   What to look forward to? What’s next?

Import Flat File Wizard

Transcription: The Companero Conference Retrospective

*Untranscribed introductory portion*

Carlos: Companeros, welcome to episode 115. It’s good to have you on the SQL trail today.

Steve: Yes it is and it’s good to see you Carlos.

Carlos: Yes, and it will be actually good to see you in a couple of weeks here we go on to Summit. We don’t get to see each other as often as we would like.

Steve: Is this where we let out that we’re not actually in a recording studio.

Carlos: Yeah, oh boy! Was that a secret that we’re keeping there?

Steve: Yes, yes.

Carlos: Yes, in the MGM Studios or on Park Avenue or whatever it is, or the MBC Studios.

Steve: Yeah, so we’ll actually be seeing each other in a couple weeks as well as many of our companeros who maybe attending PASS Summit as well.

Carlos: Yes, that’s right, and we do have an event that will make a little bit more sense which we will talk about at the end of this episode. So today, we actually want to do a Companero Conference retrospective. We’ve been kind of evolving this idea of the conference right in front of our podcast audience and we went ahead and had the conference this October, and so we want to talk a little bit about it. How it went? Maybe some of the lessons we learned from it, the challenges that we had and then what if in the future it might have.

Steve: Yes, but before we do that. Do we have any companero shoutouts this week?

Carlos: We do. I want to give a shoutout to Kira, and you’ll forgive me but I don’t have her last name but the organizer of the girlsanddata.org site. She had put this together. I thought it was interesting in a way because there’s like girls who code at code.org, lots of information about coding and teaching kids how to code. And she felt there were lots of opportunities in technology particularly in data that you don’t need to be a coder, you don’t need coding skills to do. So she connected with Mindy Curnutt of Dallas. And Mindy has taken the ball running with this and they were kind of just in the Milwaukee area, kind of doing things locally, and Mindy has connected with them and they’ve done events now in Dallas and in Charlotte this last weekend. And I went down there for the SQL Saturday actually for the purpose of putting two of my girls attend the event. They had a great time, I thought it was phenomenal what they did. You know, my girls are not, they are kind of that age where they are resisting anything that dad tries to get them to do. So there’s a bit of that, right, going on they have that to overcome. And so my oldest daughter, I should say Andy Leonard’s daughter, if you want to hear really great things you should go hear Andy Leonard’s daughter. She was absolutely over the moon with it. My daughter is responsible as well it didn’t blow me to death. And so which may not sound very good but some of the languages she’s been using lately, that was a pretty much as good as it gets.

Steve: Ok, and what age is she at?

Carlos: She’s actually 15. The girls at data.org is actually directed more middle school girls, so the 6th to 8th Grade girls. She is a bit older than that but I thought, “You know what, I’m just going to put her in there,” regardless of what I think it will be. It wouldn’t be that bad. May other daughter is 13. She is an 8th grader currently, and again, I think she enjoyed it. But some of that feedback was hard to get because she couldn’t say. She wasn’t super negative about it but I think she couldn’t be super positive about it because for fear that I would sign her up for something else.

Steve: Interesting. You know what I think is really cool about that program is that, I mean there are so many different programs out there to try and introduce younger people to programming. Like you said, data can be very different than programming. I think that in this school system as well as like community college and university even further up on that level the education on data is usually pretty weak compared to the education in programming.

Carlos: Right, exactly. And so I think it was an interesting that she put together, you know, focused on that. They are using tableau as their reporting source and they have several pieces of information they put together and do different things there. And so I thought it was very interesting. They had an interesting mix of people, so in that class they were 15 or 17 girls, probably nearly half of which parents were attending SQL Saturday. They had actually made the announcement to several of the middle schools and middle girls camp so other folks weren’t currently tied to SQL Server were still attending that. So I thought that was good.

Steve: Alright. Can we just pause for a second I need to turn off some background noise.

[PAUSE]

Alright, I’m back. Sorry about that. Hey Carlos, I’m back, sorry about that.

Carlos: Not a problem.

Steve: And out next companero shoutout is from Eduardo Cervantes.

Carlos: Yes, Eduardo on the West Coast reaching out to us and reminding us that some of our listeners are developers. He mentioned, “I do most of development. I appreciated the podcast”, and connecting him with new features and things. So thanks Eduardo for reaching out and for also just responding to something on messenger. I’m sure none of our companeros are doing it but I have people reach out to me from time to time they want to connect on LinkedIn. I’m making a personal habit to respond. It’s pretty generic but you know it’s, “Hey, thanks for connecting. I hope you’re well. Let’s chat, like what do you need help with?” It kind of bothers me to no end when I just don’t receive a reply. Even if it’s just a, “Hey thanks” or something, right? Let me know that you’re there, that you have a pulse. So with that I guess a little SQL Server in the News is on file.

Steve: Yeah, so what’s up with this import flat file wizard you mentioned?

Carlos: Yes, so one of the new features they’ve put in and let’s really just. I don’t want to say dumbing down but they have the data import option so kind of using it in the background with SSIS like package that will get import data. So what they’ve done is they kind of slim that down a little bit and made that specific to flat file data so CSV and text file data. What it helps people do is to walkthrough the wizard because before on the import data you had to go in and kind of like, I can’t remember exactly what the button was, but to see the conversion with the columns are going to be. You don’t have to drill down a bit anymore. It will do those conversions. So first you got a preview of the data actually, so again very similar to features that are in SSIS. It’s going to import that data. You’re going to get to look at it, make sure the columns are all lined up. The next screen is, hey I had to change the column types based on what the wizard is telling you it thinks it should be and then you can import. Yeah, I think it’s just a little bit easier and make it a little bit faster. Do those little one offs since like we’re always importing just random Excel data for whatever reason, look up data and things like that.

Steve: Oh yeah, and there are so many different ways of doing it I think. And that’s just one of them that makes it easier so that’s great. Another SQL Server in the News items that we have is around service packs.

Carlos: It got very loud on your side or something.

Steve: Hold on a second, Carlos.

Carlos: Sure.

Steve: Alright, I’m back. Sorry about that.

So Julien let’s cut that out we’ll just go back when we start talking about service packs. I’ll explain afterwards what’s happening, Carlos.

Alright, so another SQL Server in the News item we have is on service packs. This came out a couple of weeks ago with SQL Server 2017. Around that time they also announced they are going to change the way that service packs work. Instead of doing service packs they are just going to go straight to cumulative updates. And for the first year, it sounds like the plan is on SQL Server going forward they’ll have… They are not going to have a service pack, they are actually going to have a cumulative update every month. So you get 12 cumulative updates and about at the end of the year those CU12 should be like the equivalent of what would normally be SP1 in the past. I think that there are some interesting takes around that because one it sounds like we’re going to, and this actually happened for that last couple of years now, is that cumulative updates have been as robust as service packs. Didn’t used to be that way years ago. But I think it gets away from service packs having sort of people to wait, I’ll wait for SP1 or I’ll wait for SP2 before I upgraded that version. Now there will be no SP so maybe people will say, “I’ll wait till CU1 or CU2” but who knows.

Carlos: Yeah, we’ll see interesting I think. I feel like this is kind of the dev ops coming, I mean not to the database perspective but that dev ops mentality making its way into applications. Obviously it’s kind of everywhere people are talking about it and now it’s just making itself involve or connecting with what we do on a regular basis. And so more applications like this will kind of go this route.

Steve: And I think it really highlights the importance of the software insurance.

Carlos: Oh yeah. We talked about that before we started recording and I think with this rapid updates, again, people are already talking about SQL Server 2018. It is interesting to see what will happen. I feel like, again, kind of with the Azure mentality of everything being in the cloud then releasing software so quickly that you’re going to almost have to have software insurance just because have you know when to upgrade anymore, you know just pick a year. List some feature that you’re really really interested in kind of those rolling updates. Yeah, it will be interesting to see what happens, what enterprises do with. So let’s just say in 5 years, and I’m making some assumptions here, but let’s just say there is a release of SQL Server every year through 2020. What do you there? It used to be like upgrade so like a big deal like you plan time every couple of years. Now I think we’re going to see a lot more versions out there than we ever have before.

Steve: Right, yes, which I think has some pros and has some cons. I think for the people who stay up to date they’re going to have more work to keep up to date there. And then I think for the people who don’t keep up to date, well you’re going to get outdated I think much quicker.

Carlos: Much quickly, yeah that’s right. So the gap is going to get a bit harder. But to their defense, Microsoft has been very good about the backwards compatibility and I think that’s even getting better.

Steve: Oh yeah, and I think hopefully with the more frequent versions it will make it easier for people to jump into the next version because you don’t have 5 years of changes or even 2 years of changes that you have to worry about compatibility with you’re dealing with once a year.

Carlos: Exactly. Yup.

Steve: Alright.

Carlos: So some interesting stuff. Ok, so our show notes for today’s episode will be at sqldatapartners.com/conference.

Steve: Or at sqldatapartners.com/115 for the episode number.

Carlos: So ultimately what we wanted to do today is talk a little bit about, and I guess have that retrospective for the conference that we put together. I feel like we should start with the premise of why we wanted to put this together. Admittedly, I was heavily influenced by my own experience, so getting involved with SQL Saturday, with the User Group, going to SQL Cruise, going to Summit. So 2013 was the first year I had gone to the Summit. I just had different experience then with other conferences outside of the community events. I felt like there was a way to continue that idea of having people participate more in the conference. This kind of goes back to Allen White, so this is all Allen White’s fault. With that idea that we all have something that we can share with each other and those traditional conferences, in my mind they do a great job of expelling information but as from a collaboration perspective and from how this apply to my own unique perspective, they don’t do a great job. And so I wanted a way that people could come together, could talk with each other, connect, build those relationships they could take home with them, things like that.

Steve: Yup, and that’s the key, the connecting and the relationships. I mean we stressed that as one of the most important factors in the conference from day one.

Carlos: Right. What it turns out is a little bit of a tough sell to a wider audience. I think as technology people they, I mean like they behind the screen and just consuming information. And so I think it takes a little bit of a challenge. It’s a bit of a challenge to reach out and say, “Hey, my name is Carlos and want to connect.” And that’s something that we’re kind of trying to work through. And what are the other things that I thought? Again, I’ll reference SQL Cruise which is now Tech Outbound. I think one of the challenges they had was just being on a cruise itself. A lot of people that I talked into, I would talk about it and then they’ll like be, “Well, it’s on a cruise.” And then they do like, “Nope.”

Steve: Oh yeah. I remember hearing that on many occasions like where I wanted to go and pitch it with the company I worked for at that point in time and they are like, “What? You want us to pay for a cruise?”

Carlos: Right, exactly, right? So they think there’s a bit of a challenge there but some of the concepts were really good. One of the things that I miss or I lack about the SQL Cruise experience was that ability to kind of give back, to give my own ideas or thoughts around some of the topics. And so the rise of the unconference has played into this idea and I actually really like the premise of this idea of, ok we’ll come up with a track so here are the things that we can talk about. But then you know what we’re actually going to decide what we do talk about when we get there and then play it accordingly. And so that idea resonated really strongly with me and so that was what we want to put together.

Steve: Yeah, and I think that, I mean we went in that direction and I think that really is what we ended up putting together.

Carlos: Yeah, so I guess maybe let’s just jump to those outcomes. I feel like we were able to meet our objectives. We’ll talk about the challenges. We ended up having as we had 8 attendees, we had 5 speakers, myself and my wife, so there’s 15 of us in total. And there was some concern about the size and we went, we kind of had this thug of war as to whether we were or weren’t going to have it. And then really it came down to the speakers. I reached to Kevin, Jonathan, Randolph and Tracy, and I said, “Look guys, this is where we are. Do you still want to do this thing?” And they said, yes, and I’m like, “Ok, let’s do it.”

Steve: Well, and that’s one of those were I think to speaking of reaching out speakers. I mean, I was originally on the list as one of the speakers there. And in doing that I’d plan on attending but then when we got to the point where we’re sort of making the go, no go on whether we’re going to proceed with the conference or not it came down to just the overall amount of workload we had combined with the cost in getting there – coast to coast flight. That’s when we realized that we don’t have enough people showing up to be able to break even on it so that’s when I decided to opt out at that point and hopefully next time we can be in a position that it makes sense for me to be there.

Carlos: Yes, so that’s challenges from us as partners, only having two people hold down the fort. We have a project that needed some attention so that’s also a role. And then that week we ended up having two corruption issues with companies that we had not previously done work with and so that made for a very very challenging schedule.

Steve: Yup. So Wednesday, that day of the conference, that first day of the conference, that was the day that I spent pretty much the entire day fixing corruption for new client. And it was one of those things that had we both in there and hadn’t anyone left to cover anything. We would just have to say sorry we can’t do that we’ll have to help you another time would not been a good answer for that client.

Carlos: Yeah, that’s right. So that’s still something that we have to figure out from our end. But I play it to the reason for only having one of us there. But I’ll just go back to the objectives for a second. We felt like we were able to create that intimate environment where people could participate, get to know one another, and I think there were two major reasons for that. One, again because of our numbers we ended sitting in kind of a horseshoe shape, the traditional U. Attendees really like this because when someone started speaking even if that was a speaker which at the beginning it was mostly just speakers talking, so all the speakers were in there. They for the most part knew each other or at least of each other so there were some relationships already there. When people started speaking you don’t have to turn around to engage them. A slight tilt to the head you’re looking at that person and you could engage from there, so people really really like that idea. And then the other was the way we actually start the conference is that we started the conference with the four rules and then we did the three questions. So the four rules, some ground rules as to what you can expect out of the conference. And if you don’t have those things just speak up and then the next portion is just introducing yourself and actually having some questions to go off of. They are open ended to invite some discussion and to start making some connections. You can find who your people are. I think the combination of those two things helped to increase that engagement. Again, with the speakers being there, and making that commitment to be there for the two days we felt like we could make our objectives so that’s ultimately we went through with it. The other thing there and I think from an objective perspective is that the attendees were able to get to know each other, and we had great content. Right, from all of those things we felt like the objectives got met. I guess we didn’t feel like we were promising more than that and that’s why we were able to deliver. I was little bit nervous and I told the attendees beforehand that we’re going to have a small group. And even at 60 people that’s not a ton of people. I mean, when you think about other conferences that’s very very small comparison. But we’re going to be a smaller group and at first they were kind of like, “Huh, this is a little bit strange. I thought we had a few more.” But by the end I actually had two people say, “Hey this is right sized”, and we’ve actually again from a future perspective we’re going to actually come back off of that 60 number and it looks like maybe 30 is our new target. And again, we want everyone to be able to sit in that horseshoe shape for people to face each other basically with a desk and that kind of factors into that idea.

Steve: So you’re not just a face in the crowd you’re actually there participating and I think that’s the difference. And so then that’s was the other piece participating, so one of the ideas. We had a mix of traditional conference and unconference. So we had the speakers they each had a dedicated session that’s what we had promoted on the website as well. These are the things that they are going to talk about but we spice up that a bit and we actually solicited input and we said, “Hey, what it is that you guys want to talk about?” Everybody wrote those down on index cards. We put them up on the wall ad we actually had everyone vote on what they want to talk about. And one of the things that made it up unto the list was SSIS.

Steve: Interesting because that wasn’t even one of the things that we had anticipated that people would want to hear about.

Carlos: Exactly and so this was interesting for two reasons. It stemmed the discussion into two ways. One, so the card you set SSIS. We started with some questions and then we asked who wanted to talk about it, and one of the attendees, John. I think we mentioned him on last week’s podcast. John actually said hey I’d like to talk about that. So he and Doug actually lead that discussion and this is on Day 2 in the afternoon and because we already have that time together everyone had gone familiar with each other and so we were able to have, I don’t want to call it impromptu. It wasn’t as polish if you will again as a traditional session but it was very specific to what people wanted to talk about. Doug happen to have a VM, he pulled up his packages and we were able to do some demonstration and actually talk through, here’s what I do, here’s what we do and there were conversations all over the place and I learned some things just about logging that I didn’t know about. And then that turned into a discussion on [term unclear – 26:15] During all of that to which Randolph was like, oh I impressed. He hadn’t really seen it and he mentioned I don’t know that I would have spent as much time. Maybe it was only like a 30-minute discussion, right. But I don’t know I will spent 30 minutes on [term unclear – 26:32] But now that I’ve been exposed to it and we had in that context I fell like that’s something that I want to learn a bit more about. And so I really like that component of it. It was a very organic conversation that we didn’t have to worry about time necessarily and we kind of go with what people want to talk about.

Steve: Yup. Ok, so a lot of good outcomes there. As far as challenges what were the biggest challenges?

Carlos: Yes, so challenges, I think in the beginning we didn’t do a great job of getting user feedback. I think I know originally we kind of started with the idea of the lone DBA which I’m not opposed to. I think we knew we were looking for a segment of people we wanted people to easily identify whether or not they would come to the conference. And we have the lone DBA might be a good way to do that. Getting a hold of those people was a bit of a challenge and then even you companeros, right? We asked for feedback time and time again and we got not as much as we would have liked. You could also say, well, the way that we went about it wasn’t good. There were things to learn there. It felt like there was enough enough interest but we didn’t do a great job of connecting the dots and saying, “Hey, what it is that you guys really want to do and would be willing to pay for?”

Steve: And I think part of that, I mean, early on we were trying to get feedback there and I think that we just didn’t have the right venue for people to be able to present that feedback to us I think.

Carlos: You know, exactly that’s right. A lot of that initial feedback was I was contacting people directly and asking them, and I was reaching out to managers. I was very concerned about this idea of getting approval for people to come. And so I wanted to make sure that I could tear down from, and so when the manager looks at the website all their boxes would be checked. If they employee would like to come then they would get approval. And so from that perspective I think we did ok. I think maybe with that last mile of who should attend and why? I also think so having it in October so we knew that we were not Summit, and we are not Ignite, and we’re not you know. That’s not what we’re putting on even if our capacity 60 people we were not putting on that type of conference.

Steve: Right, that’s a very different type of conference what we’re going for.

Carlos: Oh yeah, exactly. However, having said that I don’t think that we fully realize the pull at least from the people in our network, right? So the people who are listeners, those are the two kind of we’re reaching out to, so speakers, SQL Saturday people as well. I don’t think that we had good data or the data that we had was wrong basically. We heard a lot of people saying, “Oh, I’m not going to Summit because it’s over Halloween.” We thought, ok, well maybe this is the year to try it. Even it’s not the same, we’re not saying it’s the same but let’s try to do something different. And I think the pull of those bigger conferences was just too big and lot of people ended up going to either one of them, with Ignite being the week before and then Summit being a couple of weeks later. The plans that they had in April and May changed and people ended up going or other things happened.

Steve: Right, and I think that’s a great sort of learning thing from being our first year doing it. How can we anticipate that back in April or May when we’re doing a lot of the planning? But I think now we’ve learned.

Carlos: And I think that, yes we talk a little bit about marketing, and so the other thing there is just a standardization. I think people have gotten very very comfortable with the way PASS us the events. Yeah, if you’re in that world already I think change is not, people seem resistant to change a little bit there. But I think one of the nice things is that we’ve had the event we can now have people, in respond to it give feedback on it. You can see it. Jonathan wrote up a very very nice post about it. You know, the speakers have come, they’ve seen it as well and so hopefully some of that will filter into the community.

Steve: The other challenge that we need to talk any about the financial side of it.

Carlos: Yeah, so that’s right. At the end of the day, a slightly different, so I guess this kind of goes into this standardization process. I’ll take a SQL Saturday haven’t been involved with those and putting those on particularly that it’s a free event. Because of the standardization that PASS has done, so PASS is sponsoring, Microsoft is now sponsoring, and you have all these vendors. It’s kind of a known quantity because they’ve put all that time and energy into explaining what it is. Having people go and whatnot. That from a sponsorship perspective, the entire event can run off of sponsorships and then lot of volunteers and all these kinds of things. So because we’re going with the SQL track model, we knew that sponsorship would be very very difficult because there wasn’t a traditional, hey you get to have a booth, talk to the attendees, raffle tickets, things like that. Let alone from the numbers perspective. And so that was a challenging idea as well. And so as a result we depended entirely on revenue from the attendee tickets. It ended up being about $4,000, our expenses were about $6,000, and that was another thing that’s bad decision. I knew that when we made that decision for Steve not to come out and to move ahead with it that we couldn’t cheat the people who had come and basically budget it, right? Because I didn’t want them to feel like they were getting short ended. I wanted to make sure that we were meeting the commitments we’ve made to them. But from a business perspective so as an entrepreneur you can’t contain to do that all that time and energy and then come out $2,000 in the whole doesn’t equate to long term success.
Steve: Now, if we were like a software development company and we have a whole bunch of products that were being sold out there and we could ride off that loss as an advertising expense perhaps or something, then yeah we can keep doing that indefinitely. But I think we really don’t have that. I mean, other than our consulting business we don’t have that other revenue stream.

Carlos: Or outside investment, things like that.

Steve: Yup, yup, so I think being able to set it up in way that it works so we don’t lose money to do it I think is a key factor to make this sustainable going into the future.

Carlos: Yeah, and so I guess we can talk about that future and I feel like we were pretty close. I mean, it’s not like we were at the Ritz-Carlton and doing all kinds of really crazy over the top things. Although we went on the cruise, we had a nice buffet dinner that was very very nice. We had plenty of food, the room was nice and all these things. It wasn’t over the top I think if we got from 8-16 we would have been in a better place. So I guess talking about the future, so a couple of takeaways. I guess another challenge. I knew it would be slight challenge to use the word companero. I don’t think I understood how challenging that was going to be.

Steve: And that’s one of those things that in retrospective it’s easy to see that now. I think just looking back at our podcast Episode 100 with Kevin slaughtering the name companero. Comp… however you say it in the beginning. I mean, maybe that should have been a clue but we kind of have fun with it. But yeah, I think that’s good point. It’s a tough one to say or even to spell for a lot of people. Even me.

Carlos: I don’t really like the word conference, right, particularly because we’re going for the unconference idea. I’d like to stay away from that word conference. And so some of the feedback that we got, some of the thoughts that we have about potentially putting on if we were to do an event in 2018, some of the things that we thought about. So one, renaming it to SQL Trail. SQL Trail, much easier, it’s kind of already part of our brand as well, right. “We’ll see you on the SQL trail”, we’ve said that on every episode since Episode 0. That has been part of the podcast. And so I feel like incorporating that would be a bit easier.

Steve: Oh yeah. And I’d be curious to hear what people think about that. I mean, does SQL Trail make more sense than Companero Conference? We’re always looking for feedback and those kind of things.

Carlos: That’s right. The reason I like it even and again kind of using, even SQL Cruise or SQL Saturday, they are not using that word conference. They have that short name that can be used to identify what it is without using the word conference. And so that’s what I would like to do, and so SQL Trail would be a potential.

Steve: Yup, I like it.

Carlos: The other change that we were looking for too going forward is we had the event for two days. What we did is we use, and it only ended up being about an hour but we did an hour of introduction. So maybe an hour and a half in the introduction, and then the session selections for the Thursday. Part of the feedback was, hey, let’s push that to either before the conference basically or separate it from the first day of the “conference”. And so what we were thinking of doing is making that 2½ days. So we will start for example on a Wednesday, at 2PM. You have the welcome, everybody comes, introduces themselves. You know, introducing that idea of the social into the start of the conference and then choosing sessions that afternoon and then having a dinner. Right, so then at the end of the day on Wednesday, you know the sessions, the schedule for Thursday and Friday. And then on Thursday and Friday you come and it’s more, I won’t say traditional but from a schedule perspective you kind of at least know of what to expect at that point. And then if people are travelling or whatnot, again they can come in the morning for whatever reason they are a little bit late. That component of it is taken care of. And then the other thing that people really really, everybody talked about was they’d like more hands on and we did talk about a hands on. Well, nobody criticize I guess I could see that being one thing that we kind of tweaked a little bit from our original session. The hands on, that lab idea sounds really nice but it is a lot of work. It is super hard to pull off well because you’ve been in the lab, some people breathes through it, others have no idea, some are in the middle. How do you work with all of that?

Steve: And do people bring their own computers or do we supply computers?

Carlos: Yeah, exactly, again if we are supplying computers and all of a sudden it’s much more expensive, and logistics and things.

Steve: And if everybody brings their own then we got to figure out how we get the right software installed.

Carlos: Yeah, exactly or are we installing software things like that, all those kinds of questions. So one of the thoughts that we have and we’re looking for the right people is to that might be a way to partner with our sponsors and to have a couple of things that we say ok in the conference you’re going to be able to come away with. Let’s just take as an example, you’re going to build an availability group, create a container in SQL Server, create an SSIS package and use some DMVs or do some database monitoring. You got to get hands on on those four areas and then have the sponsors come in and help support those things and again I’m just using an example because I’m talking to them. Like the folks at [term unclear – 40:41] Having them come out, they can help with the lab in setting up the how to, this is how I setup my container in SQL Server. They will make that commitment but then they will also be able to then say, ok you’ve seen how that’s happened. Now here’s how we can help make your life easier.

Steve: Yup. You know what I think that would be incredibly be valuable because so often when you’re at the conference and you meet with a sponsor or a vendor and you talk to them and you think, “Wow, you’re product or your stuff seems really cool.” But then you get home then you realized, “Oh, how do I try that out?” or “Where do I go now that I want to try it out”, and you realized of there is not trial to download or I got to talk to a marketing person or something like that. But if you could just have like hands on demo with more than just here’s a lecture from a vendor. Like, here’s how we actually make it work hands on, that would be awesome.

Carlos: But in conjunction also with, ok, so here’s how you could do like SSIS. Let’s build an SSIS package and some basic things. Now here is how you could make that easier. And those conversations and if the sponsors are willing to commit to come to the conference and spend the time to make those relationships then I think it will be easier to have those conversations when it comes for lab time towards the end of the conference.

Steve: So we should be thinking about what sponsors would be interested in doing that.

Carlos: That’s right, and so companeros if you have ideas let us know. And so this kind of gets us to the question of should we try this again in 2018? I can say that I had a blast, right? I enjoyed it. I enjoyed getting to know Erin and Gretchen and Bryce and Jeff and Dave. Good thing we gave him a companero shoutout the other day. Getting to know those guys and of course the speakers in a different way. But ultimately I think we want to see if this has power. Is this something of interest? We’d like to hear from you. I think you can do that in a couple of ways, right? Obviously, social media is one. The other is we’ll make available on the show notes page. We’ll put in a little section. I don’t if poll is the right word. But we will give you the ability to sign up, to put your email address in and say, “Yes, I’m interested in being part of the conversation about the 2018 conference.”

Steve: Yup, and we will not add your name to any kind of spamming list. We will just use that for discussion of where we want to go, or what we want to do, those kind of things.

Carlos: That’s right, and many of you have trusted with your email address and we hope that you’re not seeing that as, you know, we are providing good content and obviously there is a subscribe button there and everything which you don’t want to. But I think if we can get to, and I’m not sure what the right number is. But I think if we can get a certain number of people to sign up to say, “Yes, I’m interested in providing feedback at a minimum.” And would be interested in being contacted or being informed about when and if we do this in 2018; if we can get a certain number that I think that will be one of the criteria that we’ll use to determine if we should go forward.

Steve: Yup, I like it.

Carlos: Yeah. So I think that’s the retrospective. I think we learned a lot. I think I’m glad that we went through it and thanks obviously to those who attended and to the speakers. I guess I should say in that number, so we talked about those numbers, those expenses. The only thing I provided for the speakers was the ability to attend, obviously their food, and the cruise, a t-shirt and a notebook. Randolph came in from Canada, just all these flights, hotels, all of it was on their own. So we can’t say enough thank you to them. If we had those additional cost obviously it just wouldn’t have happened.

Steve: Yup, so what are we doing next?

Carlos: Yes. I guess with that we’re testing out this idea of the SQL Trail. We mentioned this event to talk about at the end. So Steve and I thought, again kind of in that idea of engaging with you companeros and continuing the conversation, we are putting together an event at Summit. So this event is going to take place on Wednesday evening. So we’re actually going to bundle it up. It’s going to start in the middle of the last session of the day on Wednesday. I don’t know we go to 7PM. We’ll put out the details, again, this idea of SQL Trail. I was thinking about, you know some SQL Trail should that be something that kind of call our defense. This will be included on that. We’re going to provide some food, it will be at the, which I also can’t remember the name. But we will provide advertisers and things and we’ll have raffles for drinks and whatnot which we will explain later. But I was thinking, you know what, SQL Trail and then I was thinking about food and my wife actually said you should call it Trail Mix.

Steve: Ah, so it’s the Trail Mix event.

Carlos: There you go, that’s right. It’s the SQL Trail Mix event. So that’s what we’re going to call it and we’ll hope you’ll come.

Steve: And how can people find out more about it?

Carlos: So it will be on the shownotes. If you’re on our mailing list you’ll get an announcement about that. And then through social media we will be publicizing that as it comes around. We’ll invite you to sign up for Eventbrite so that we know approximately how many to expect and again all the other details will be there as well.

Steve: But even though it’s through Eventbrite there’s no admission fee or anything like that. It’s just trying to get people the RSVP that way.

Carlos: Yeah, exactly so just we know and ultimately it will help us with the raffle for example because we’re going to raffle up some of the SQL companero shirts. And the notebooks and things and so that would just the easy way we have your name. We will preprint them and then we can just pull it out of a hat and go from there.

Steve: Yup, awesome.

Carlos: Ok. Well, I guess that’s going to do it for today’s episode. If you have other questions obviously reach out to us. We would love to continue to talk with you. Our music for SQL Server in the News is by Mansardian used under Creative Comments. You can always reach out to us on social media or connect with us on LinkedIn. I’m @carloslchacon.

Steve: Or you can connect with me on Linked @stevestedman and we’ll see you on the SQL Trail or at PASS Summit at SQL Trail Mix.

Episode 114: How do you start consulting?

One of the advantages to a small conference is the ability to take attendee feedback and put it in place during the conference.  We actually made time for the attendees to pick topics they wanted to discuss and this episodes comes from one of our attendees Aaron Hayes.

How do I start consulting?It can be very tempting to think of the good life of consulting.  I almost liken it to playing the lottery–what am I going to do with all that time and money?  While the odds on successful consulting are a bit higher than the lottery, just saying you are a consultant won’t automatically bring in the clients.

The reasons data professionals get into consulting are varied and in this episode we are joined by Randolph West and Jonathan Stewart, former podcast guests, to talk about the reasons we started consulting and some of the challenges along with our decision.

From my own personal experience, working for yourself–whether you consider yourself a contractor or a consultant–is very rewarding but demanding work.  There is no one to tell you want to do, but there are very few security nets as well.  One of the most important ideas I can suggest for those who want to own their own business is–forget the technology, how are you going to help other people?  If you are ok with the idea of focusing on others, then there are great opportunities in store.

Do you have thoughts about jumping into technology?  Let us know in the comments below.

 Episode Quotes

“I hate the lost productivity involved in sitting at a desk all day.”

“Becoming a consultant allowed me to… choose my customers, choose what I want to work on.”

“You can do other things but be known for something”

“word of mouth turned out to be a very bad strategy.”

“A consultant is I’m helping you solve a problem, a contractor is getting some job done for x number of hours.”

Listen to Learn

10:26 Challenges in consulting
15:45 Having your own branding or niche as a consultant
19:08 Difference between a contractor and consultant
42:57 How consultants find their particular customers? What are some of the strategies?

Transcription: How do you start consulting?

*Untranscribed introductory portion*

Carlos: Companeros, welcome to Episode 114. It’s good to have you on the SQL trail again.

Steve: Carlos, Episode 114, sounds like a fun one.

Carlos: Yes, so we are piggybacking a little bit off of a conversation we had at the Companero Conference now that we are on the other side of it and recording sessions afterwards. One of the interesting things that we did at the conference that I’d like to continue and we’ll talk about more of this in our post mortem, but the idea was we wanted to leave some time or some unstructured time where attendees could submit topics that they wanted to talk about. Which we did, and then the attendees voted on the topic, so everybody got to contribute and then we voted. One of the topics that came up was this idea of “How did you get started in consulting?” And so we recorded this, we have it during the conference and then we thought we would make it available to you the podcast.

Steve: And this one was suggested by Aaron Hayes.

Carlos: Yeah, so Aaron of Chicago, shout out to him. Thanks Aaron for coming.

Steve: Speaking of shoutouts do we have other companero shoutouts, Carlos?

Carlos: We do, I guess so keeping with the conference trend so John Szewczak–I think I’m saying that right. I spent two days with him. John, if I didn’t get that last name right, I apologize. This was an interesting event that developed as well in that some of the other conferences that we go to and again the event was two days, so you get to know people. We were heavy on interaction and one of the other sessions that was developed was SSIS, so John who was an attendee had been doing quite a bit in SSIS and as the conversation had jumped up he had responded to a couple of things so we actually had this session. We actually invited Doug who had given a session previously and John up and they led that discussion on SSIS. There were no slides. It literally started with a couple of questions and they just started bringing up a console and just kind of, “Hey, this is what we do in giving thoughts and experiences.” And I thought it was very very cool that contribution can come from anywhere. If you are following the Twitter notice, and we will put it up on the show notes episode for today but you can see a picture of John up there leading that session.

Steve: Well, maybe next time when we do our call for speakers we should open it up to the previous attendees first.

Carlos: That’s right. In fact, he did say afterwards that he would be interested in coming back and speaking, which we are going to make some changes there to that whole process as well so it would be interesting. Another shout out there, way too many to mention, but this week I attended our local high school for career day. And I’ve actually done this for a couple of years now, so I lived in a very blue-collar area. You can take that as you see it. So lot of families, so dual income, and the majority of the folks in our area are not college educated, so that’s kind of how I am looking at that. The high schools they have a lot of trouble from a demographics perspective. We tend to be on the lower end of the socio-economic scale. This idea of kind of being able to break out and, these kids have an uphill road. Anyway, I was there and we’ve been talking and I wanted to get across or extend this idea of the power of the network and why making connections is important, and then connecting with the right people, and there is an investment in that. We’ve talked a little bit about that on this program on the podcast before. And so what I did, it was kind of through the moment. It wasn’t as planned as what I would have liked to but I went ahead and took a selfie with the class. I posted it up on Twitter and said, “Hey, this is what I’m doing.” I put in the #sqlfamily, and then said, “Please reply, like or tweet”, to let people know kind of that power of community and where it goes, and it was super interesting. Obviously lots of comments came in but then just seeing I think the breadth. So while I was at the school, I was at the school for 5 hours and while I was there and then the tweet continue to get likes and whatnot afterwards. It went as far as Brazil, people commenting, which they thought kind of interesting and then all the various states, and so we had several people just reply, some of the people retweeted and then of course liking. And you kind of do some analytics on that tweet and they were able to see, again, that idea of so of my followers, which I think I have around 700. It went way beyond my own network, right? But it was because people knew who I was and I was asking for something that was really easy to do and they could feel like they were helping. Yeah, it was an interesting little experience.

Steve: Ok, really cool, so I guess the thanks goes out to all of your followers who retweeted and all of their followers who come in and retweeted it as well.

Carlos: That’s right, who connected and got in on that. I really appreciate it.

Steve: Interesting experiment there.

Carlos: That’s right. Ok, time for a little SQL Server in the news. There are a couple of more announcements for Ignite that we haven’t quite gotten through but this one I thought was interesting, and we’ll see how it goes. My understanding is that it’s currently in preview, and we’ve talked about Azure data factory on the program before. Although it was really in its infancy and has definitely gained traction a bit now, but they are now allowing to deploy SQL Server integration packages to Azure. There are some parameter around it but the Azure data factory is ultimately what’s going to be powering that and there are some limitations as to what you can connect to and whatnot. But that idea of being able to get SSIS in Azure is picking up some momentum and I think they will continue to work on it and it will be a little bit more usable for those who are more familiar with the GUI.

Steve: So perhaps it’s time for us to do an episode at some point in the future on SSIS in Azure and get someone who knows a lot about that.

Carlos: That’s right, that’s right. Which interestingly enough we are getting ready for summit, and so that is the time that I like to go out and make connections to some of those program managers, and the MVPs and whatnot. And so I will definitely put them on the list of people to reach out to. We should probably go ahead and mention, we don’t have all the details yet, but we could probably go ahead and mention what we’re trying to do at summit.

Steve: Yes. Well, we’re both are going to be there and we’re trying to sort of get an after hours, some kind of a gathering together. Still working through the details on that right now but a companero and podcast listener get together where we can just hang out and chat for a while.

Carlos: That’s right, do kind of a happy hour type of thing. Again, those details are still forthcoming, however, I will give you a little sneak peek and that is it will be to your advantage to leave a review on iTunes. So we are going to work out some incentives. Those of you who’ve already done that, thank you. If you haven’t we invite you to go over through iTunes and leave a comment or review. That will be key to, not key, obviously you could come out. We’ll do something for those who would love feedback.

Steve: So if somebody wants to attend that after hours drinks, whatever we are calling the event, how can they reach out to us?

Carlos: Yes, so obviously through social media, you can hit us up on email as well. So probably I guess through Tweeter or LinkedIn. It’s probably going to be the easiest way. One of those two I’m assuming you’re on that social media. We’ll make the information available and we’ll put it up on our show notes page for today’s episode and future episodes. Today’s show notes episode is going to be at sqldatapartners.com/consulting

Steve: Or at sqldatapartners.com/114 for the episode number.

Carlos: Yeah, and so that would probably be the best way to get that information and we promise for next week’s episode to have that.

Steve: Alright, excellent.

Carlos: So now that I’m thinking about it I should go ahead and plug, so at sqldatapartners.com/podcasts, you do have the ability to join our newsletter and we can make sure that it goes out through those channels as well. Ok, well let’s go ahead and get into the conversation here.

Carlos: We’re actually here at Companero Conference. One of the sessions that came up, one of the interesting things that people wanted to talk about was a little bit of the question of how we got into consulting, so we have Randolph and Jonathan here, and so we are going to have a little brief panel discussion and talk a little bit about it, and then maybe open it up for questions or how we decided to go. First, we will let you give your, the brief story of why you decided to become a consultant.

Randolph: I become a consultant because I hate working full-time. I hate the lost of productivity involved in sitting at a desk all day hearing people have arbitrary conversations that have nothing to do with work and not getting their work done. I hate the idea of a 9:00 to 5:00 or an 8:00 to 5:00 shift where you are expected to be sitting at a desk and looking at a screen or doing stuff when your productivity might be better later and during the day or even at night in my case. I found myself to be far more productive just working from home or finding a quiet space as just as shared workspace that’s in Calgary that I sometimes go to. And I can get more done in an hour of quiet time than I used to be able to do in a full day of work working full time. Plus I get to choose my customers. I get to work as much as I like or want to and I get to travel.

Jonathan: I’ll give a slightly more PC answer than Randolph gave. There was a lot of hate in there.

Randolph: Intense dislike.

Jonathan: Ok, sound guy, you fix it. Let’s see you edit that. I became a consultant because lot of the same reasons that Randolph said too for me. I hate to not be thinking. I know right, I intensely dislike to not be thinking so one of the things that happened a lot of places that you will do a project and a project will be great, will be interesting. You will do a lot of work and stuff too and then it sees lulls. In those lulls sitting at my desk, you know, you can only read the internet for so long and then you start getting bored. And when I got bored, it’s just really bad when I got bored because I don’t like to not be thinking. I’m always thinking, and when I can’t control what I’m thinking about then I begin to think about other things too like, “How much I’m getting paid?” There are all kinds of other things that would happen when I wasn’t thinking. So for me, becoming a consultant allowed me to, as Randolph said, choose my customers, choose what I want to work on, be in a load up as much as I want, be a tech time when I want because I want to do work, I can do work. I do work in the middle of the night because I don’t really sleep a lot, so it let’s me add work to it and lets me choose the things I want to work on. As technology moves forward I could move forward with it and not be stuck based off of a company wants to go forward with a new product or not. I can choose what I want to work on. I can choose if I want to move more toward predictive analytics or if I want to move more towards visualization. It’s my choice so it allowed me to control my career and be happy with it because literally if I don’t like the work that I’m doing that’s my fault because I chose what I want to do. So that’s the short story of how I got into consulting.

Carlos: I told the story a bit before. It’s always been my desire to be an entrepreneur, the idea is very sexy. We are Americans, right, we build stuff. We make our own things.

Carlos: My first company was actually like a sort of computer repair. My partner is Jeff Morrison, called it Mor-Tech. he kind of started it and I wanted to help him and then that kind of fizzled out because, anyway, it just didn’t work. You are always afraid, you are afraid to make that leap particularly in technology. Like, what if somebody asks me something I don’t know so that helped me back for a while. And then I was working for a law firm and I was in a position where I was seeing the contracts of these consultants that were coming in, and then you get to a point where they start coming to you for questions and you’re like, “Hmm…” Admittedly, some of them were in California so a bad basis. I was seeing California rates coming in and I said, “Well, gosh, I’ll do that. Maybe it’s time to make the leap.” The timing just happened to be such that my wife finally is going to give me the green light and so that’s kind of how we started. I think that was, and I looked back at that and while the desire was there I had some semblance of what I wanted to do or kind of seen what was there. I did not have a great footing or foundation of what it meant to be a business owner, and I wished I would have waited, not waited but maybe did things a little bit differently there initially.

Carlos: Ok, so Steve, do you want to give us, how did you get into consulting?

Steve: Well, I got into it a couple of times. The first time I wanted to try being a consultant and this was 12/13 years ago now, maybe. I sort of quit my job, hang out a shingle, started talking to people and my previous employer ended up hiring me. I ended up doing work for them. It was really kind of a weird consulting scenario because I ended up working for them almost exclusively for 1½ year or 2 years.

Carlos: After you had been a full time employee.

Steve: Right, right, and then when that gig came to an end that was when I kind of learn a little bit about making sure that you don’t put all your eggs in one basket which is one client. And I made rules for consulting from there on, and I broke them a few a few time, but then I ended up getting a couple other gigs with other clients and then ended up going full time with one of those clients as an employee which took me out of the consulting ring. And then about 2½ years ago it was when I got back into it. So where I left that job and went full time. I mean, for me, it was really. I mean, just jumping back in and wanting to work with different clients rather than the day job. And I think that, I mean, enjoy being a consultant because it changes the dynamics of what you do every day. I mean today, any day, I usually get up and started early and I work early and oftentimes by 9AM. I’ve completed more work for clients than I ever would have in a full time job by noon, or maybe by the end of the day. And it’s just the nature of consulting that allows you to do that.

Carlos: Right, right. It is interesting that I think a lot of clients or a lot of consultants will start that way. That was my experience as well with a former employer and you get in to it for whatever reason and those rose colored glasses helped you jumped in, and then there comes a point where you’re like, “Holy cow! What did I just do?”

Steve: Oh yeah, absolutely.

Carlos: I think from there let’s go in and I guess talk about, let’s do challenges first, so challenges of consulting. So you mentioned lulls, you know, there’s lulls at work and I think there is definitely lulls in consulting, right? So what are the challenges that you find being a consultant?

Randolph: Scope Creep, so you sign up for a contract that’s going to be three weeks, four weeks, and then suddenly you’re working 6 months. It’s not a bad thing but it might be not the work that you thought you were going to be doing. That’s the good side of challenges that face you. The bad side is where you’re not working for 6 months at all which happened to me two years ago. And that’s why I got more into acting and making films because I had all the spare time but the challenges that I had to pay for it somehow. I found myself compromising; I only do performance tuning to start doing development again because I used to be a developer, so I had to get back into that and I was rusty. So the challenge is that I wasn’t be able to do the work that I wanted to do but at the same time I was keeping my old skills fresh and learning new stuff as well. I was like upside there and that’s kind of how I got involved with a little company called SQL Data Partners. You may have heard of them. Good bunch of guys, the one is a bit odd, then there is Carlos. Hi Steve! I’m kidding, Carlos is the weird one. The challenge is I didn’t work for six months, that was rough. I won’t lie.

Jonathan: Since they’ve already touch on those challenges too, I’ll add another challenge too because we work for ourselves. Another challenge too is something that I didn’t realized until I was doing it is, people don’t like to pay you when they said they are going to pay you. One of the hardest things about making a jump into independent consulting is understanding that for that first month, two, three, depending who your first client is. You may not get paid. You get paid but you get paid down the line.

Carlos: When they want to pay you.

Jonathan: So not just getting paid and being prepared for that and then to understanding how invoicing works and stuffs too. All of my clients are really, they’ve helped me invoice them because there is a lot of things setup invoice, track your time, and stuff like that too. But at the end of the day, you know, been able to go through and invoice somebody and then able to change the invoice in the fly, something happens and stuff too. For me that was a struggle because that wasn’t my strength. Accounting wasn’t really my strength so I actually hired an accountant. Let them do that. So there is a lot of ways to be handed on your business and stuff there too. Some people do it all by themselves and I commend them because that’s a challenge. But there is a help out there so don’t be overwhelmed. You can even contact me. I’m sure Randolph and Carlos as well, if you got questions you want to do it. I see your card, Randolph. You can be Randolph, and I can be … I’ll make a normal $1 bet, we’ll showing our age. But no, the challenge is though is there is other challenge too besides just getting the work and stuff as well, it’s the stuff behind the scenes. The thing is that from taxes and stuff like that and understanding the things outside of your normal day to day task. Those are some of the small things that come being an independent consultant.

Carlos: Yes, as you mentioned tasks so as an employee unless you get to Kevin’s level, right, or until you get to Kevin’s level. I won’t say they are not micromanaging but there is a certain level of expectation, so as database professionals that obvious that you’ve been given is to take care of the database. Maybe they are not giving you every single thing you should do but your tasks is there and assigned. Once you then kind of make the leap, there is nobody giving you that task anymore. The expectations are we want, “make us happy”, and how do you that? The other struggle there is what should your tasks really be? When you first start, you think, you kind of working as an employee like you do anything. So performance tuning, you write a report, you go and talk to whoever about there connecting to a database, right? Lots of the breadths of your assignments is very wide. Now as a consultant it’s slightly different because of that idea of what value are you bringing? Initially I was like, “Well I am a database person you can hire me to do any database problem.” “I don’t have a database problem.” And you’re like, “Wait a second.”

Randolph: Let me find one.

Carlos: Yeah, exactly. I think coming up and actually kind of narrowing the scope a little bit or defining, having a well defined set of what it is that you want to do is very difficult because you feel like all of a sudden, “Oh my world is shrinking a little bit.” But it’s one of those things were you have to go down. I think of Alice, when she takes the potion and shrinks down, you kind of have to shrink. You think about, you could apply this to, when you think for example David Klee. What do you think about?

Randolph: VM guy.

Carlos: VM, VM work, right? When you think about Pinal Dave, what do you think about? Performance, right?

Randolph: One blog post a day for 10 years, that’s what I think of Pinal Dave.

Carlos: You know, when you think even of, so Patrick LeBlanc, or some of these other folks. There is one thing that they are kind of known for. Do they get to do a lot of other things? Sure, but they have that one little niche. Or Jonathan Stuart, what do you think about?

Randolph: Visualization.

Jonathan: My hair. But no, that’s actually the point. That’s one of this I was thinking about too is, and it actually goes falls right align the things. So Randolph was talking about he wants to do performance tuning, so as you figure out what you want to do become known for something. Brand yourself. Your branding is huge. So I chose my hair to make my logo as my branding, but even still visualization is my thing although I do data warehousing and all kind of other stuff as well. You have that niche that gets you in the door and then you can do other things. But be known for something and then you can do other things as well because what happens is that if you’re just a generalist nobody thinks of you specifically to do that one thing when that comes up. If you want to do that one thing, be known for that one thing and then, “Oh hey, we have X, Y, Z as well.” You may not be able to do X, Y, Z but then I can call Kevin, I can call Randolph, I can call Carlos to help me out do those other things. But that gets you in the door. Be known for that one thing, be very good at that one thing. While Klee is known for virtualization he does all kind of other stuff as well. But the virtualization has got him in the door because that’s what he is known for around the world. When you say Klee, you think of VM. VM ware has changed its name, Kevin say as KleeM ware because it’s what it has become. But that gets him in the door, that’s his brand. That’s what he is known for but he is phenomenal with all kind of other stuffs as well. Be known for that one thing, but be able to do other stud as well. Even with Randolph, like you said, he got to do performance tuning but he can do other stuff as well. So if performance tuning gets him in the door and he goes like, “Hey, I want to do X, Y, Z and expand to Azure SQL Database.” He can do all that as well. So once it gets him in the door he could say, “Hey, I had all these other services as well.” But that’s what got him in the door, that’s his brand, that’s what he is known for.

Steve: Right, so I think some of the challenges and I think some of the others mentioned this, things like scope creep. I mean, oftentimes you will start on a project that you think is going to be one thing. And then after meeting with the client and understanding what they want, it ends up growing into two, or three, or four or ten different things that you never even expected. And that could be good or bad depending on how you look at it. I mean, it’s good and it brings in more work but oftentimes it can be bad because one of my rules is never do more than, well hopefully more 30% of your time with any one client. Sometimes I’ll break that and go to 50%. But when the scope creep goes the point that they want you to be there as like all your time with one client, I just don’t go for that because that’s way too risky. Because when the relationship comes to an end with that client which it always does eventually then you’re sort of out of work at that point. I kind of fight that scope creep by pushing back and prioritizing things but then limiting my time amongst multiple clients. Which I guess sort of brings up one of the other challenges that have come across in consulting is, when you’re working part-time with teams that are working full-time, and oftentimes they kind of expect that you are on the same full time schedule they are, even though you are a part-time consultant, and trying to sort of balance between the different clients so that you can responsive as needed but also so you can spend the right amount of time with each client each week.

Guest: What is the difference between a contractor and a consultant?

Randolph: Well, a consultant I feel gets to choose what you want to do, a contractor gets told what to do. You still have billable hours and you still have to fill in a timesheet. It’s all the same thing. It’s just who you are working for is a little but more blurry because a contractor might work for a contracting house like I use to and then I get told, “You’re going here tomorrow. You’re going to work six hours, you’re going to bill eight.” Hey, I’m not going to lie. That’s the difference for me. A consultant is I’m helping you solve a problem, a contractor is getting some job done for x number of hours. The religious work comes in whether contractors are actually doing anything useful, and some are and some aren’t. It’s just depends who you’re with I guess.

Carlos: You think these goes to that idea of like of the branding? This idea of are you being paid for your hands or for your brain in a sense, right? So if you’re being, like brought in, here’s the work we want you to do. I tend to think of that as the contractor route. If it is, “Hey, I have this problem how do you think we should solve it?” Ok now, I’m in putting it into the process and again not a generalization but that’s my delineation. You feel good about that?

Jonathan: That’s exactly how I say it too because some people say that’s semantics is the same way but a contractor is like a staff person. You are filling a role, you are filling spot so they can just throw work at you. As Randolph said, you are being told what to do whereas the consultant is solve a problem, help me solve a problem. You know, like management and consultants, they are all helping solve problems whereas the contractor is somebody who is filling a seat for a specific time and a role.

Randolph: Yeah, there’s nothing wrong with doing contract work if that’s what you enjoy.

Carlos: Yeah, and I guess that’s an interesting point. It’s not like the one is necessarily better than the other. What are you constraints? What are your box? Where do you want to be and then that’s going to make your decision there for you.

[unclear conversations]

Jonathan: I used the same approach in regards to vendors. So we are going to do an imaging project and we sent RFPs out and have them to kind of do demos. I look for things like contractor versus consultant in the vendor aspect. Are they are going to help us solve problems or just put the imaging system in and leave. I think I can use it both ways in regards if you’re interested in consulting like myself. You can still see that in the vendors you deal with. One of the things too that a consultant has to worry about and a contractor doesn’t even though they can actually be the same person at the same time as well but the consultant has to work on managing relationships as well, if that’s at the end of the day you don’t want as Douglas is saying, you don’t want to just come in and fix something because that’s the end of the gig there. I want to develop a relationship so I would rather spent four hours with you every month than spend five hours with you one time upfront just to do one thing. That’s another big thing there too so.

Steve: I think that, I mean, as a consultant you are coming in to do something that sort of project or problem base where you are trying to come in and create something or fix something or come up with a solution, whereas, a contractor you are punching time on the time clock. And not that you’re not getting things done but you’re showing up and you’re there on the clock for whatever is needed. And I think the comment was, are you paid for your hands or your brain? And I think that that was a great analogy there in that, if you’re there just sort of on assignment for a certain amount of time and you will do whatever needed in that time, that’s kind of the contractor role. And when you’re there to help get the job done to focus on a specific task then I think that’s more on a consultant where you’re either building something or advising on something or coming up with solutions, and that consultant role is the piece I really like.

Carlos: Great, and again, so if you we did mention it, I should mention it here that neither is right nor wrong. The labels are not really important per se. I think it is however more of a philosophy of who’s kind of driving the boat in the relationship in the sense of who’s dictating what work will be done, and that can be a fine line sometimes. I think kind of going back to your comment about the expectations of the client dictates quite a bit about of what role you’re taking on.

Steve: Right. I think some of that comes from just what the client has been used to working with. I mean, if they’ve been used to working with a staff type position where they get a body and can work specific hours. That can be very different than getting in a consultant who’s there with a specific specialty like we have to solve a problem or fix something or help them with their performance whoever it may be.

Kevin: Kevin Feasel, professional podcast guest. You guys just talk about… it’s better than being an amateur. So you guys just talked about brand, about building up skillset but I would like to bring in the next question as somebody who has absolutely no interest in doing consulting work, hello employers, how do you go about finding the particular customers that you want to work with? How much of that is them finding you, how much of it is you finding them? What’s the strategy behind that?

Randolph: Most of my customers were from LinkedIn which surprised even me. I thought I’d find people through word of mouth, those were terrible customers because what happened is somebody says, “Oh, you know computers, I know somebody who could use your help.” And then you end up working on MySQL doing index tuning and then after 20 minutes you got no more work for them because… They are only useful in certain context. But the point is that word of mouth turned out to be a very bad strategy. LinkedIn where I specified this is what I can do, this is what my company does. People who need it usually go there because they are business peoples so they are looking for a problem solver for that particular problem. I do give my cards out that’s why I have them up here because if anybody hasn’t got one yet I’m going to give it out because I want people to associate me with the fact that I am available to do almost anything. Because the chances are 5% of the people call me back and out of those 1% or 2% of those 20% of those people will actually turn into a paying customer. So I have to go and do the whole mail shot or whatever so that I can get 1% back to actually pay me to do something. There is a lot of targeted advertising I could do but I don’t have the time or the money to do that right now, and I don’t want to because I don’t want to work full time.

Jonathan: Because if you work full time he will never be able to play dead in TV shows and movies. It’s acquired arts skill. Don’t you play dead with me sir. There is a wide gamut of ways that you can acquire customers. Most of my customers I do partnerships with a lot of people. A lot of people find me because I speak a lot so I go places and stuff too and people say, “Well, come up to …”, and I talk to them too. A lot of these are business owners. They have their own SQL practices as well. But it’s like, “Oh hey, we might want to partner with you. We want you to do business intelligence. We may want to do visualization. Can you help me with this client and do such and such.” Speaking is a great way to get your name out there, to be known for certain things. So for me, like I said, a lot of my clients come through not just word of mouth but from partnerships that people I’ve met on the speaking trail, or heard me on SQL Data Partners Podcast, right, I got people contacted me in that way as well. It’s not really hard to find me because I’m the guy with hair. See it’s a branding thing again, right, but have a brand. Everybody has a way to be branded you just haven’t thought about it yet. So if you really consider doing this, think of a way to brand yourself to get out there and then there’s multiple ways. As Randolph said, he gives his card to everybody. Anybody can give us cards because that’s a cheap way to advertise. You know, me, it’s not really a cheap way because I’m flying everywhere to do this. But there’s so many different ways. There is not just one way. You got to think outside the box. You know, and there’s more ways than we talk about. I’m sure Carlos has another way as well.

Kevin: Write a book.

Carlos: Initially we tend to suffer. It’s very similar to the idea of finding a job. How many of us has just taken a job that has come our way? Do we actually think about the industries that we want to work in? They type of people that we want to work with? The type of projects that we want to take? We might say that, “Oh yeah, I want to do migration”, so SQL Server 2017. At the conference several people have mentioned, “Well, they approach me with this job. I wasn’t really looking for it but it paid me x percent more than I was making”, and so you take it. We follow on that same mindset that we want to spread the word, the cards are great but ultimately it is trying to, with that niche, finding what you want to do, we’ve had our best success then defining a vertical which is healthcare and then going after those people, right? And so, yes we still like I still hold the conference, I still do the podcast. Our health care people, in fact I don’t think if there is any health care people here?”, so we’re still doing things outside of healthcare before my marketing and like where we’re going to look for customers. We spent all of our focus. In fact, we’ve actually even gone as specific to customers of GE. And that’s who we’re targeting, trying to establish those relationships, increase our network width. We want to become the SQL Server people for GE centricity customers, and then once we do that we will go on to the next phase.

Steve: I think finding customers, that’s the hardest in the very beginning because you start out, you have no history there other than with your full time employer before that perhaps, and you’re trying to find customers. I think that getting the on boarding process for new customer the very first time always seems to take far more time than you ever expect because that first customer is everything to you, and without that first customer you have no income, you have nothing there. And then I found overtime once you’ve got that first customer, as long as you’re not breaking the rule of putting all of your eggs in one basket with just a single customer, the next customer is little bit easier because you have the work from the first customer to sort of subsidize the time while you’re waiting for that second customer to get things signed. And then once you’ve got more customers you end up getting repeat work. And I think there are some customers that you may work with that once you found them you’re getting work every single week for years to come, and there are others that once you found them and you’ve done work with them it may be you come back and work with them every six or eight months depending on what their needs are. I think that finding them, and I think a lot of it is really just the specializing. And I think in finding the customers you just find out and say, “Hey, I’m a computer tech guy. Hire me.” That is I think the wrong approach there because then you’re competing with everybody that’s a computer tech person anywhere. But if you specialize, like my specialization has been in database corruption. There is not a lot of people to compete within that area so that’s an area that people find me now when they come across corruption. And because of that specialty I’m able to take it on and fix them and get happy customers really quick right there or not always real quick, but generally as quick as we can. The other areas like the database health assessment. A lot of that has come out of Database Health Monitor. I think that just getting your name out there, whether it’s like what we do with the podcasts, or what we’ve done with the database health monitor or blogging. I mean just getting out there, social media, LinkedIn. Things like that to get your name out as someone who very rarely when you’re doing those kind things turn into an immediate like you do something and within 24 hours you have work. But it turns into you become that trusted name and then 6 months or two years later somebody remembers, “Hey, that’s the person who specializes in that. Let’s call him.”

Carlos: It’s definitely an investment, right? You have to wait a long time for those returns sometimes. It can be very very difficult. Ok. Well, thanks again Aaron for the question. I thought some interesting insights and conversation if you’re interested in talking a bit about this more. Maybe have some of questions, of course we much being at the summit or you can reach out to us via any of our social media channels and we’ll be happy to have a conversation with you. Give some thoughts around that.

Steve: So one of the things that I wanted to do was call out an interesting comment that I like from Randolph where he was describing us at SQL Data Partners and he says, “One is a bit odd, then there’s Carlos.” Thanks Randolph, I love it.

Carlos: Special thanks to Randolph and Jonathan for providing some insights there as well. Our music for SQL Server in the News is by Mansardian News under Creative Comments. If you have other suggestions for topics or suggestions about things we should be talking about, you can reach out to us on social media. You can find me at LinkedIn, I’m Carlos L Chacon.

Steve: Or you can find me on LinkedIn as Steve Stedman, and we’ll see you on the SQL Trail.

Episode 113: Standing on the shoulders of giants

“If I have seen further, it is by standing on the shoulders of giants.”  Those words are attributed to Issac Newton and they are fitting for the way I was feeling recently as I thought back to those who have helped me in my career.  While we might always want things to be better, as technologies we have it pretty good.  There are unnumbered people who have dedicated untold amounts of time so we can have the tech available to us.  We take a moment to think about those who have paved the way for us to be where we are now.

Whose shoulders are you standing on?  Let us know in the comments below.

 Episode Quote

“This idea that the way he was able to look at the world is based on all the previous work that has been done”

“I guess “gratitude” is the right word of those who have gone before us and enable us to have those who are in technology we have a pretty nice career.”

“There are just a lot of people out there who just want to help and just want to better things with what they do every day.”

Listen to Learn

00:38 Companero shout out to David Stoke
02:11 SQL Server in the News – SQL Server 2017 is out
02:35 Interleave execution for multi-statement table value functions
05:34 Artificial intelligence, analytics, machine learning
07:58 Today’s Topic: Standing on the Shoulders of Giants – having that gratitude to the people who have gone before us
11:27 Carlos shares a documentary he watched about bitcoin
14:50 Stop complaining
19:56 Who are “giants” in the life of Carlos?

Transcription: Standing on the shoulders of giants

*Untranscribed introductory portion*

Carlos: Companeros, welcome to Episode 113. It’s good to have you on the SQL trail again.

Steve: Yes, it’s good to have everyone who’s listening. Good to see you, too, Carlos.

Carlos: Yes, always good to connect back with you, Steve. Today, we’re going to be talking about, kind of changing the subject up a little bit, and the idea is standing on the shoulders of giants. We’ll get it to more what we mean about that in a bit later but that’s the idea of our topic for today.

Steve: Ok, sounds good.

Carlos: So we do have a companero shout out. I want to give a shout out to David Stokes. Now as we record this we actually have not had the Companero Conference just yet. That will happen, actually as soon as I hang up I’m going head down there and have the conference. But I want to give a shout out to David. David is the leader of the Norfolk user group. He was actually the first person to buy a companero ticket and has been a great supporter of the podcast and of the conference. I want to give a shout out to him and thank him for what he’s done.

Steve: Yup, you know the interesting thing about David is that he is the very first user of Database Health Monitor that I have ever met in person.

Carlos: Oh ok, interesting

Steve: I met him at Charlotte, North Carolina for PASS Summit, was that 3 or 4 years ago?

Carlos: That’s right, 2013 I believe

Steve: 2013, ok, so 4 years ago. Yeah, it was really kind of cool to meet him and listen to what he had to say about Database Health Monitor back then and he’s been using it ever since. David is a friend of Database Health Monitor.

Carlos: Yes, very nice. Ok, as mentioned, I’m sure the conference went great. You already have that and we’re looking back to some feedback. We will probably have some kind of post mortem about that. But now I think it’s time for a little SQL Server in the News. We’ve been going back and forth a little bit about this and one of the things I wanted to talk with, of course we know that SQL Server 2017 is out and should be up, right? I’m sure you spent your weekend installing that. But one of the things that I wanted to discuss a little bit here which has been mentioned I think it’s expanded a little bit here and that is the interleaved execution for multi-statement table valued functions.

Steve: Oh yeah, this sounds pretty cool because there’s been so many concerns over the behavior of multi-statement table valued functions over time.

Carlos: That’s right. Yeah, so ultimately the bad news is that in previous versions of SQL Server, and I guess in 2017 as well. I mean, there is a fix for it but the optimizer can’t always determine how many rows the table valued function is going to affect. As a hard time just in for that fact, right, and so in previous versions basically it would guess a hundred and then it would really be 5,000 and so we have a bad plan, and that was kind of the issue. So what they are going to do here with 2017, they are adding some analytics and some machine learning basically into the optimizer, so that when the first time the plan comes in with the table valued function it’s going to guess again a hundred rows when it executes it’s going to have 5,000 rows. The difference now is that the second time it runs it’s going to take a peek at that previous plan and say, “Hey, how did I do?” And it says, “Oh, I did horrible. Let me adjust my numbers and take in the previous execution into account to see if I can come up with something better.”

Steve: You know, I wonder as I hear that if that is actually going to help or hurt because. I mean, if you table valued functions commonly have the same number of rows they are processing it might help you. But what happens if you’ve got such a wide variety of data in your database that every time that gets called the table valued function has a very different set of data that it’s processing depending on a client or customer or whatever it may be.

Carlos: Right. Yeah, and so obviously we have skewed data it makes it difficult to solve for the outliers. There still probably an outlier case. I think here what they are trying to get at basically is do I use a hash or do I use nested loops? Which way that I’m going to go about that? I think, again, after you execute it a hundred times you’re going to have some data there to then be able to say, “Ok, well, 80/20 rule. This is the way I’m going to go with.” I guess the implications here are not suggesting all of a sudden that everyone start using table valued functions and this is going to solve everyone’s problems there. I guess what was curious, or what was interesting to me is that I thought this was a very interesting way because we hear a lot about artificial intelligence, analytics, machine learning even. This is now a problem that we deal with all the time and now Microsoft is putting that analytics, that machine learning into the product that we love. And I thought, you know what, I meant that’s kind of a very specific scenario. I wonder if we couldn’t start taking some of the same thought and then applying it to other areas of our business so that our users or our customers might also benefit, so that was kind of my thinking there

Steve: Right, so it will really be interesting to see if somebody comes out with like an AI package for tuning SQL.

Carlos: Yeah, something like that, right. Or even that idea of, “Ok, I have this data. My customers keep asking me for whatever use cases. Can I apply some analytics here to give that better information? So it will be interesting to see how this continues to evolve. I think this idea is going to affect those of us who continue to be administrators or gatekeepers of data. These are going to be problems that we’re going to have to start solving, so it will be interesting to see how other tools crop up and try to address other problems.

Steve: Yup. Yeah, and I think it’s been easier with just sort of fix rule sets previously to be able to definitively say this is how something behaves. But with AI built in on how those things are being handled and how it’s tuning itself almost, it may be harder or maybe more work to figure out exactly what’s going on when there is a problem

Carlos: Sure, and I think that’s goes back to you are still going to have to know your system for those outlier cases. What’s going on? Why are you doing what you’re doing and is there a different way to potentially go about it?

Steve: Yup, interesting.

Carlos: Ok, so today’s episode, the show notes are available at sqldatapartners.com/giant

Steve: Or at sqldatapartners.com/113 for our episode number.

Carlos: Yeah, so ultimately this idea, and maybe a became a bit nostalgic which we can get into in a second but Sir Isaac Newton is quoted in 1675 as saying, “If I have seen further it is by standing on the shoulders of giants.” And ultimately this idea that the way he was able to look at the world is based on all the previous work that has been done, right? Helping him to arrive to where he is now. I got a bit, as I mentioned, nostalgic about this idea where I ran into to somebody who work with a former co-worker of mine. We started talking, and so this former co-worker is actually the person who convinced me to apply for my first database job.

Steve: Oh, I remember that story from one of our previous podcast.

Carlos: Yes, and I wasn’t actually completely qualified for it. But he convinced me and he coached me and said, “Hey, I think you do great.” And I think back to that, obviously, you know, there are thousand different I could have gone but I think about where I am now and ultimately kind of that first step of making that decision. And so I think about it, I guess “gratitude” is the right word of those who have gone before us and enable us to have those who are in technology we have a pretty nice career. Yes, it’s not all roses and sunshine all the time but the fact that those giants have gone before us, many of whom we’ll probably never know. We have to look up in Wikipedia, and the fact, even the first people who put together the rules for the transactional database. I know he was brought up in a keynote, unfortunately I can’t remember his name, obviously on top of my head. But, I mean, they put in place what we have today. And I think it’s easy, and not to discount. I don’t mean to discount them at all but it’s easy to look at the Bill Gates, and the Steve Jobs, and the, what is it? Oh gosh, Linux is going to kill me now

Steve: Linus Trovalds.

Carlos: There you go, thank you.

Steve: I don’t know if I pronounced that right

Carlos: If not, I’m sure you’ll get corrected. Again, not to discount their contributions but they are not the only ones. You think about all the teams behind them that helped them do what they wanted to do.

Steve: Yeah, I guess with that, I mean you look at a product like SQL Server, it’s really been like a Microsoft product since 1989/1990 but it was being worked on for years before that and then before anybody even started working on what became SQL Server, there were people in the 70’s that were theorizing on how these databases would work and the rules around it. I mean, 50 years worth of work, and foundation that has gone into what we have today in SQL Server 2017

Carlos: Right, and how amazing it is. It was interesting to kind of [term unclear – 11:26] here slightly. I was watching a documentary on bitcoin, and so bitcoin is the digital currency that they are trying to advance and I didn’t realize but, oh gosh, this Swedish guy who started Wikileaks. Whatever his name was, he was part of the original group of this eight people who put together sort of theorizing about digital currency, and they came up with a block chain idea and methodology. It was because of his situation and what Wikileaks was doing that really kind of put bitcoin on the map because when PayPal took their services away and said, “Hey we’re not going to let you use PayPal services.” That’s when he was like, “Ok, well, bitcoin, Internet can you help me,” and kind of launch that whole idea. In a way it was very interesting. But anyway, in the documentary they go and they talk about and they say, “The person through the door with a new idea get shot.” Anytime you have something revolutionary, the first person through get shot but somebody has to go through the door. So they talked about some of these first people like the first companies that started in New York City trying to exchange currency. For example, dollars to bitcoin and things like that. Well, he’s going to jail. Long story, but you can watch the documentary. Anyway, but I thought it was interesting. I guess going back to this idea that all of the ground work that was put in to enable us to enjoy what it is that we enjoyed today.

Steve: Right. But what was interesting, I mean, he was going to jail for Wikileaks side of things not for bitcoin side of it.

Carlos: Yeah, I’m sorry, so Julian Assange. That’s his name, the Wikileaks guy, and he is going to jail for something else. I don’t know it. But the guy in New York City who was doing the bitcoin exchange, so he was actually trying to get people on the bitcoin and basically sell them, bitcoins this idea. He is going to jail because one of the guys, unfortunately, illegal activity tends to push a lot of innovation. And so when you have something that is not monitored there was some illegal activity going on. He knew about it, and he was basically selling bitcoin to people who were doing illegal things and so that’s why he’s going into jail.

Steve: Oh, interesting. Ok

Carlos: It wasn’t that he was doing anything illegal but he was kind of aiding, and abetting if you will in a sense.

Steve: Wow! I hope I’m not considered aiding and abetting by investing in bitcoin. Or maybe the word is “gambling” not “investing”

Carlos: Yeah, that’s right. It is still a bit shaky, again to progress for a minute, the value of how that gone up it was pretty impressive but obviously it has come down I think in 2011 or 2013 something like that. And so where I was going a bit with this as well is this idea of it kind of grits on my nerves a bit, and we all like to complain. We all find ourselves in situations we don’t want to be. When people will say, “Gosh, I they had only done this right. Why they have to do it this way?” Basically, I’m inconvenience and I don’t really appreciate all the things that I have

Steve: Right, and sometimes that inconvenience comes from just not understanding what the people did before you and just complaining about it. I mean, I’ve done that but I’ve seen that happen plenty of times.

Carlos: Yeah, that’s right. You have that “Aha” moment where you’re like, “Oh, so that’s how it works.” Sure, you may have developed it differently; you may have architected it differently all of that jazz, great. But, you know, you work there, right? And now you’re now kind of taking advantage or taking over in some case. No I’m not saying it’s all always great but the decisions that were made there before you have you put you in a position where you can now either take over or ride that bus if you will. Yeah, it’s kind of interesting.

Steve: Yup, and you may look at something and say, “Wow, that’s stupid. Why would they have ever done that?” But then you look back 10 years ago at what was being done, or 15 years ago, and that was cutting edge technology and the fact that it even worked at all was completely brilliant.

Carlos: Exactly, exactly. Yeah, you know it is funny particularly as database people, like yourself Steve, there are developers among us or recovering developers or whatever you want to call them. But we like to complain quite a bit about you know, and the stupid developer if you’d only done that. Well, I can kind of find that we’re being a bit of a hypocrite because we use software all the time, on our phones and on our computers. I mean, we wouldn’t be able to do anything that we do now if it weren’t for software. We need to be mindful of where we are, what has been invested and give people a little bit of break when they are not upgrading to the latest thing. I guess in our last episode even was about patching. You know, just staying up and keeping with the latest and can kind of get a little tiring sometimes, and so you’ll forgive people a little bit if they are not quite there to where you are.

Steve: I think some of that comes back to what I learned in a class. It was called the Dale Carnegie class that I took several years ago. It was based on “How to Win Friends and Influence People” book as well as some other books, and it was a simple statement that said, “Don’t criticize, condemn, or complain.” It’s just that one of those things that just comes up and if I find myself criticizing, condemning or complaining, I try and stop myself. I try, so it doesn’t always happen but I try.

Carlos: Yeah, it’s one of those things, the reality in today’s world with social media everybody has a voice box or a [term unclear – 18:11] and they want to get up. It’s not to say that reform is not needed, that we can’t make progress. There are times when change needs to happen. There’s no question on my mind. I think it is just more about the way that we go about it and then recognizing what we have and how do we go from there.

Steve: Yup, and you know, no matter how bad the day may be we’ve got things pretty good right now. I think that’s so often overlooked in, well you mentioned social media, I mean, if you’re on social media and it’s almost like everyone is out there just to complain

Carlos: They are either having the best day of their life at the beach with the vacation photos or the world is against me. I think those for you companeros, those who are listening, going to be in technology, yes, you may not be where you want to be. And I’m not saying that you should settle. That’s not what we are saying either but I do thing that you if you take a minute to use the phrase from our culture if you will, is to capture many blessings and just to kind of appreciate where you are and know that there is a path ahead. If you will continue to work there, like we talk about in the beginning, all these artificial intelligence and analytics; yes, is it going to affect you, is it going to change what you do? Absolutely, but hopefully that will be to your advantage and not to your decrement.

Steve: Right, yup

Carlos: Anyway, we thought we change us up just a little bit and share some of our thoughts on that topic. I guess we’d like to, again, thanks to all those shoulders we stand on, so Robert Pollard, he was the guy who commits me to get to that job interview. I even think about Matan Yungman and Guy Glanster. The other guy is the SQL Server Radio. They are out there doing the podcast. Listening to them made me think, yeah, let me try this. Right, let me do this as well. And then of course all those in the community who have give freely of their time. I guess especially thinking about that to all of the speakers who came to the Companero Conference. They put a lot of time and energy and I’m super appreciative to them and what they’ve given.

Steve: And you know, it’s just interesting because one of the reasons that I enjoy what I do so much I think a big part of that is because of the SQL Community. You don’t see, at least I have never seen a tech community similar to what we have with the SQL Community. There are just a lot of people out there who just want to help and just want to better things with what they do every day. And I think that’s awesome

Carlos: Yeah, that’s one of those things. I think standing on the shoulders, again, I’m sure there will be a lot of people out but I kind of, if there’s one person I would point to, Steve Jones. So Steve Jones, probably one of the most humble guys you’ll ever know. He has done quite a bit in the community, kind of a very public figure but he is always looking to help and do so in a way that he’s not boastful. He is just trying to help you along and he has taken his breathe of experience and freely gives of that and all things to do this.

Steve: Yup, and what’s interesting with Steve Jones is that when he presents and gives back and shares with people. He does a great job in the tech side but he also does a great job on like the personal improvement side. Like, how do you get out there and improve the position you’re in – how do you improve your social media footprint, how do you improve your value, all those kind of things. I’ve seen him present a few times and definitely another giant to call out there.

Carlos: Yeah, it was funny, so we have him on the podcast that was one of those first ten episodes and looking to expand your game. And one of the things that I really liked about Steve, because again, he is one of these internet famous folks and he is also one of the few of that genre that will come up to you and say, “Hey, I’m Steve, what’s your name?” You’re like, “Ahhh. I forgot, I know who you are.” Of course he is in a Hawaiian shirt. He has a great brand but part of that brand I feel like is his contribution, and because of that he has made it the norm or almost the expectation of this is what the community does.

Steve: Right, yup.

Carlos: So awesome, so that’s our episode for today. Companeros, thanks as always for tuning in. Our music for SQL Server in the News is by Mansardian used under creative comments. If there is other topics you would like for us to talk about or ideas, you can hit us up on social media. I am carloslchacon.

Steve: You can connect with me on LinkedIn stevestedman, and we’ll see you on the SQL trail.