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, [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.