Episode 110: How do I build trust with my team?

 

As you work with other people, you will experience resistance when you suggest changes and want to implement something new.  Why is this?

One podcast listener shared an experience where a problem was occurring.  The suggested a solution.  The idea was rejected.  No solution presented itself and the idea was submitted various times.  When nothing else was working, the idea was finally implemented.

The suggested change successfully helped the situation.  Victory!

Later, another issue arose and a similar suggestion was made.  The expectation was the suggestion would be well received; however, this was not the case.  The idea was still met with hostile resistance.

Have you faced a situation like this before?  Why wasn’t there an trust built because of the first recommendation?

Our conversation for today’s episode centers around the idea of building trust on the teams you work with.  As most our listeners are in the data platform space, we thought it would be a good idea to reach outside our community to folks we might receive the most pushback from–developers.  We are happy to have Richard Campbell on the show with us today.  Richard is the co-host of the .NET rocks podcast and you might recognize him from one of his many Channel9 videos.

We chat with Richard about how we can build trust and some common ways we attempt to show authority can backfire on us and actually cause more problems.  We talk about some of the ways those we trust have gone about earning our trust.  I know you enjoy this episode.

 Episode Quotes

“Mandates are one thing, execution is another.”

“Being successful is about persuading people to want to do it in a way that is usable by everyone”

“Position or power actually undermines your ability to persuade.”

Listen to Learn

04:07 What makes up trust in a team?
05:07  DBA’s responsibility in keeping the data safe and aligning business goals
08:37 Setting team standards, collaboration, being flexible to the needs of the team
11:52 How to be effective and create influence by giving value to the people in the team?
13:35 Becoming a consultant that takes away the tension of the problem inside the team
15:20 Understanding the whole person, the whole effort, the whole team
17:01 Attitudes when encountering failures
19:35 Other ways on how do you save yourself from quitting?
20:06 Why lunch is the most powerful moment of the workday in terms of improving trust?
25:30 Understanding the other team’s workflow
27:00 Discussion on best practice, showing the work, and getting an effective team
31:45 Team building activities and time together in strengthening the team
38:28 No level of position or power enhances the ability to persuade people to do something
40:46 SQL Family questions

About Richard Campbell

Richard the co-host of popular podcasts .NET Rocks (www.dotnetrocks.com) and RunAs Radio (www.runasradio.com).  Richard  is the founder of the Humanitarian Toolbox (www.htbox.org), an organization designed to let developers around the world donate their skills to disaster relief organizations by building open source software.

Today Richard is a consultant and advisor to a number of successful technology firms as well as the co-owner and content planner of the DevIntersection (www.devintersection.com) group of conferences.

Episode 110 : How do I build trust with my team?

Carlos: Richard, welcome to the program.

Richard: Thanks guys. I’m happy to be on board. Thanks for inviting me.

Carlos: Yes, all the other podcast that you’re hosting and have been on we’re happy that you came to our side of the pond and if you will and willing to chat with us a little bit today.

Richard: Hey, it’s nice to just be a guest because I know from some experience that hosting is hard work. You got to remember and keep track of a lot of things. I just have to answer questions like this is luxury.

Steve: Nice.

Carlos: How many podcast episodes would you say you’ve done?

Richard: Like little over 2,000.

Carlos: Wow, I was thinking closer to 1,200.

Richard: There are 1,465 .NET Rocks! episodes. But I came on board at show 100 so there is 1,300 and something. There are 545 RunAs episodes so that gets us up into the 1,800 range, and there is about 150 Tablet Show episodes.

Carlos: Oh, there you go. That’s right, I forgotten.

Richard: Yeah, so we are coming right around 2000. Now the Tablet Show was this little interval in 2011 to 2014 when it didn’t seem like .NET rock that much, and we hedging our bet but we were still doing .NET Rocks! but we are also building another show, just in case because it seems like Microsoft was moving away from .NET for awhile there. And then it was clear they weren’t, we just rolled the Tablet Show back into .NET Rocks!.

Steve: Ok, interesting.

Carlos: Well then we’re happy to announce this is your 2000th episode.

Richard: Yeah, ok, I’ll let you have that number. There you go.

Carlos: Well, so Richard, I think many listeners are going to know from the .NET Rock show. We actually have and one of the reasons we want to have you on the show today was a listener chimed in and had a question about trust and we thought that we might role play a little or talk a little bit about how this idea of trust works in teams in our environment. The reader actually goes to say, “I’ve been listening to your podcast, collecting best practices and I’m the DBA in an organization. I’ve only been there in a couple of months.” But they had the SQL Server, it was having problems. I was recommending changes to the SQL Server based on best practices, right, things we’ve been talking about in our program and others. The developers were like, “No, no”, because they’ve kind of managed the database, were responsible for that environment. Well, finally they relented because they were having so many problems and they had an improvement in that server.

Richard: Wow.

Carlos: That’s right, so everybody goes happy. Well, then he said, “Ok, well hey let’s adopt this as best practice and let’s go put this on server #2.” And they say, “Whoah, whoah, whoah. Time out!”

Richard: Don’t get crazy now.

Carlos: That’s right, and so his question was if I was able to show value and demonstrate that I could be of assistance why didn’t that apply to a more general trust of me and my recommendations ultimately. And so this kind of becomes then the basis of our conversation today, right, building trust among teams and particularly our audience is our SQL Server folks, and how they can work with developers like yourself to build that trust and to kind of move the organization forward. And so I guess we’ll start there and what do you think makes up trust in a team.

Richard: Well, looking at your particular scenario my concern always is folks look at one win and call it random chance. It kind of takes three wins before folks really like, “Hey you keep being right.” Like maybe there is an actual trend here.
Carlos: Sure. There is that saying that I like, “A data point of one does not a trend to make.”

Richard: Yeah, right. That’s kind of a narrow curve you’re looking out there, so it’s usual to build more points. I think it takes a lot of patience to be kind enough to allow people to learn themselves. So let them collect empirical data around their own mistakes. The challenge is to sit down your hands and not say, “I told you so.” At the same time I think it is also important, I think it is a trap we followed to is tech people is to keep talking about the practices around the technology rather than practices around them. The responsibility of a DBA is to protect and curate data irrespective of the technology involved. In the end this is an asset of the business. The developers are building software to allow us to bring that data in and perhaps to do some analysis on it, but ultimately the hat you really wear is the one that says, “I am responsible for making sure this data is safe and thriving and useful.”

Steve: Yup, and I think with that data that you’re keeping safe and thriving and useful is oftentimes considered by many of the organizations like their crown jewels. I mean without that they would have significant business impact if it was damaged or gone.

Richard: I hope that folks see that although a lot of folks don’t. That’s where, you know, when you talk about breaking down walls between teams. It’s not talking about technology; it’s talking about business goals, and so being able to speak in those terms so that people get aligned. When we really talk about that alignment we’re all pulling in the same way. It’s that we recognize, “Hey, our data storage is worthless without your software being able to feed into them.” So I don’t want to be an obstacle to your ability to put that data in. That data is only valuable if it’s usable and it’s safe so that’s why we have security and why we care about organization of the data and whatever form that looks like. It’s about making sure that data is actually valuable. You can’t succeed as a DBA without allowing your developers to be productive, that’s a necessary part of the equation. But vice versa if they are writing software that doesn’t store data effectively their software is not that useful either.

Carlos: We’ve talked about this on the show before kind of going from the CIO perspective or the managers they want the technology team to understand that the business value, what problems are we trying to solve and I think to you point sometimes we get sidetrack a bit by the technology and fighting over. You know, what we are or not going to implement when really we should be aligning what we do with those business goals.

Richard: And often we have pain too, scars. We’ve had a data corruption, or we’ve had a failed recovery, or we’ve had a badly behave piece of software where we… on a table. And so you can be a little jumpy.

Steve: Yup and I think some of that too comes from history, and I think with the example of this listener I think it was someone who was new to an organization. They probably had someone there before, I don’t know anything about the background but there is obviously some reason the developers were a little bit shy of making changes. Maybe they had a bad experience before that and it might take a lot of successful wins before that trust will be regained.

Richard: Yeah, and one win does not a trend made. Like, congratulations, you are walking the path but you got to keep walking it. And at least now you get to refer back to some success that’s already happened in the organization, some talking points. Blanket concepts around overall practices, those take longer to grow, and I think especially development organizations, you need to look at that from the perspective of how do you folks normally set standards around your practices because often we have a lot of teams where there isn’t a standard set of development practices. Essentially you’re going have to work from team to team to see how they like to interact, how they want to work with data, how they want to work with the system as a whole and essentially tailor a data access solutions to the team.

Carlos: So that sounds like something that might have to be, I don’t know, maybe not mandated but ultimately you have to be a little bit flexible. I guess maybe the first component of trust is being flexible to the needs of the team and ultimately being, which maybe against your traditional idea of a DBA, but being flexible to their needs rather than being quite so rigid in certain instances.

Richard: I think you need to be aware of how those teams currently work. If you do have an organization with say a strong enterprise architect group that does want a press standards upon everyone then talking to an individual team about a standard doesn’t make sense. You do want to go talk to those architects about how they developed those standards, how those measurements have done and how they are implemented so that you work within the system rather than inventing something new. And if it is team based, if we’ve got an environment where “agile” or we’ve got a variety of teams working in different ways there’s many ways that happens and works that way then it’s more of a team based. So I think there should be an awareness of how everybody works makes a huge difference in your ability to convince one way the other. I really want to emphasize this idea and you said this like, “Mandates are one thing, execution is another.” I’ve seen plenty of cases where we have a set of documents of this is how we build software and this is how we interact with data except for that part where nobody actually writes that code that way, those are great documents. You’ve got to actually look at how the organization actually functions as well as what it says. And being successful is about persuading people to want to do it in a way that is usable by everyone. That they are productive, you are productive and you achieve that real goal of delivering the data, so I would much rather persuade than mandate.

Carlos: Right. I think and so we kind of almost get into a soft skills discussion because this is again not about technology but this is about how to make friends and influence people.

Richard: I don’t disagree and I’ve done this long enough now in one form or another. It would basically come into conclusion that there is no situation where the technology can’t do this. This is invariably can the team do it? Yeah, this is marriage counseling.

Carlos: As a database administrator we think that we have kind of add some value to the team. First thing we want to do in establishing trust is really just getting away of the land and asking the questions, what are the problems people having, how do they go about doing things and then determining whether or not you can influence or make a change in some of those processes.

Richard: Absolutely, and so this is an old Stephen Covey’s 7 Habits of Highly Effective People thing. If you want to change anything, you want to influence anyone; first you have to understand before you’re going to be understood. So spending that time understanding how the team actually works. What motivates them, how they build their code, how they interact with data first and foremost that’s the first step because once you have a grip on that you can finally speak a language about how data is handled inside of the team and reflect that back on how you see it arriving in the database. That understanding is a step in trust too. The first hurdle to get over is this idea that they matter, and that they know you see them as people that matter.

Carlos: And that they are more important than the problem. It’s just kind of an area where the internet kind of fails us in the sense of we can publish this information out there, best practices, this is the way you “should” be doing things. But then we try to force that on people and say, “Hey, this is crazy. This is the worst environment I’ve ever seen. How do you guys work like this?” You’re not making any friends there.

Richard: Well, I’ve seen plenty of consultants where their names actually come from con game and insult. It is super easy to tear down other people’s work. The reality is nobody goes to work with intent to ruin things like today is the day I’ll make a mess of everything. That’s not the goal. I start with the fact that people generally have good intentions and then make the best decisions they could with the information they had at that time. Invariably when we look back we have more information and so we’re always going to see it differently. But if you recognize that you made the best decision with what you knew at that time and the constraints that you’re working in, some of which are going to be visible and some of which are not, then you’re a little bit kinder. I think the number one thing I’ve said most of the time when working with teams as an external is, “Guys, what I see here is very normal.” I have the good fortune to work with lots of different teams, there are always problems. The question is how well do we recognize them and what do we want to do about them. It’s just super normal. And just take that energy out of you’re not a disaster, this isn’t the worst thing I’ve ever seen. Like suck the air out of that talk about. We are all trying to do the best we could with what we had, here is where we are. We know we can get better. We can’t do it all at once. It doesn’t have to be immediately. These are incremental improvements overtime while maintaining the requirements, whatever they has to be productive and continue to ship features.

Carlos: Sure, right, so kind of taking out the Facebook approach if you will. Facebook everybody always post the best things, all the highlights and then you compare yourself to that and you’re like, “Ahh, I’m not good enough.”

Richard: No, and you always, out of the 150 store procedures you wrote you’re going to show me your best one but the other 149 exist. I think that’s part of this being a trusted team is a group of people that know we have good days and bad days. We have our strongest work and our weakest work but as a whole we deliver. Understanding the whole person, the whole effort, the whole team, that’s the value proposition. It’s hard to get there. We want to only look at shiny but we have to look at the hole.

Steve: And I think really the key to what you said there was understanding the team and understanding what the team is expecting. An example of that is doing consulting. We’ve work for a lot of different teams and some team I’ve work with, their number one goals is stability in uptime and if you’re pushing out a new feature and something goes wrong they would rather you abort the deployment of that new feature that means it’s going to give stability in uptime versus whether the new feature goes out on time. Others I’ve worked with, it’s all about we got to get the new feature out on time, and if you don’t get the new feature out, well there’s a problem.

Richard: Well, and often you’ll see those two positions inside the same team. The person who gets rewarded for shipping new features is really not worried about uptime and the person who gets rewarded for uptime features other worst thing that could happen to your uptime.

Steve: Right, and I think that you may get an organization and I’ve seen this where you say, “We try to do this but something unexpected happened. We rolled back and aborted that project or that deployment and here is the plan of how we’re going to take it on next time.” And some people respect that and other people look at it as, “Wow, you just slowed me down. You got in my way.”

Richard: Yeah, and you also find places where any kind of failure at all is such a big deal you never want to talk about it again. We cover up the fact that roll back happened.

Steve: Yup.

Carlos: Right, then we get into situations so analysis paralysis, so people are so afraid of making that mistake that they just don’t do it.

Richard: Yeah, deploying more software, making those updates would be the worst thing that could possibly happen which is just not worth the risk.

Carlos: Exactly.

Richard: I think it’s a challenge to get to a place, you know, we talked about modern environments. What we’re really pressing on is not know failures but minimum pain from failures and rapid recovery. Fail but don’t fail catastrophically that you’re able to be able to test it in the field. You know you can get back quickly. And it’s not a big deal to do that recovery that you know when you fail. It’s not like science pop out of the server going, “Opps.” How do you actually know there is a problem? If you have to wait until a customer complains then you know it’s been going on for way too long, so how are we measuring success or failure? Do we actually know when we’ve done it right?

Carlos: So thought around, let’s say again to our audience say database administrators we do have a couple that maybe managing other database administrators or team leads something along those lines but most of them I’m going to say maybe don’t have the authority if you will to start again say, “Hey, this is the way we should start doing things.”

Richard: And let’s be clear you are never going to have that authority. Get over it. It isn’t going to happen, so don’t count on it. Don’t wish for it, find other ways. You need to be able to get to that end goal of being able to have safer, reliable data that’s valuable to the organization. You’re never going to get it with the stick of power so you’re going to have to do it in a different way.

Carlos: Yeah, so I guess let’s talk about some of those other ways. Obviously we talked about reaching out when you’re on a team that is dysfunctional. I guess thoughts around how do you save yourself from just throwing up your hands and saying, “Yeah, we were not moving forward. I’m out.”

Richard: Well, and lots of people you either quit you go somewhere else, or you check out. Just tell me what you want. I will do whatever you want then nothing is my fault. And if you want to be more positive than that, more constructive than that
you have to start establishing some relationships and some trust that you have inside. And I would argue, you know, we described a very simple thing, understand the development process. Well, turns out that’s really hard to do because understanding development process needs interrupting a lot of people that are working, and they will be annoyed with you. You can’t do it during the workday. You have to do it around that and at the simplest level. The one window you’ve got is lunch. You know, when you give it workday the only time you really can talk to people where you won’t be take them away from work is lunch. And on top that, humans are hardwired to trust those they break bread with. Lunch is the most powerful moment of the workday in terms of improving trust. We just don’t take it seriously enough because we’re hungry.

Carlos: And how frequent it is that we meet people consulting for the first time over a meal. I think it would apply, I feel like maybe I have applied this in other scenarios but it takes them getting used to. It can be awkward, right, the first couple of times.

Richard: Absolutely, so just go and ask, “Can we have lunch together?” One of my measures of sort of the health of an overall team is to say who’s having lunch with whom. If devs are only having lunch with devs, and DBAs are only having lunch with DBAs, and ops guys have only lunch with ops guys. That’s an isolation. You actually want to encourage folks eating with each other. A lunch and learn where we talk through broad concepts on software and not a particular technology. More of where we want this application to go, where we want ops and data and dev to be together in a room. You want an interesting experience you put together three lunch and learns for each one of those team, all teams go to it as long as you’re buying pizza people are going to show up, right? Each team presents their view of an application, so developers are talking about the feature they’re building the stuff that they’ve deployed so far, the DBAs are talking about how data goes in, how data goes out, how data it’s currently organized, how it’s backed up and restored, operations talking about network traffic, how security works, how it goes to the firewall, because generally speaking the other team just don’t know what those guys do all day. I don’t understand why your job is hard. And so just having a moment where each of the other teams gets to see the folks that do the other piece and go, “Oh, that’s tricky.” The average dev has never seen a network diagram. Their network diagram is client, cloud, or internet server, maybe database behind it, so they reduce the operation guys entire job to a couple of lines. And the DBA guys to that cylinder on the drawing. There is more to it than that, and so actually starting to understand it a more deeply that’s the beginnings of trust, of some insight into the system.

Carlos: Can you think about all the effort we put in to just learning technology, you could take a lunch or a couple bunch of lunch, you mentioned three, to go to the pains of actually learning what your environment looks like.

Richard: And learning what your data storage environment looks like and what a recovery looks like. I want you to get to a place, I’m trying to banished the concept of that’s easy to everybody because everything is easy once you know how to do it. I want you to understand and remember these things are hard. And the side effect of that would you say to someone, if somebody ask you about a tech, oh that’s easy. You hurt yourself in two ways. One is you’ve undermined your value. Clearly you can do it because it’s not hard, and then same time you’ve also harmed the person you ask because this like, well you might be stupid because this is easy. Saying easy is just a terrible thing to say. There is no upside to it. Now, you don’t want to go, “Oh my god, that’s so terribly hard you’re going to die.” But it’s like this takes effort but it’s worth it. It’s a much more reasonable thing to say. All of our jobs take effort.

Steve: You know, I can remember being asked recently about a specific test that I have done and they phrase it as, this is what we want you to do. That’s going to be easy isn’t it? And how much does it take you to get it done? The administrator at a very high level without even exploring any of the details on it, and as soon as they said it’s going to be easy that makes it very challenging to be able to come back with anything that’s going to be realistic to their expectations.

Richard: Yeah, it’s terribly undermining, and not actually constructive. Either you lie or you lie, like you don’t have any good answers at this point. And even the process of estimating effectively is going to force people to actually understand more which often they don’t want to do. So just this encouragement to try and understand more to see what’s easy and what’s hard. I mean, there is another interesting truth which when we start poking at each other’s workflows there are skills that each team has that can make those other workflows simpler. Devs tend to solve stuff with code so often what they really understand a workflow on a DBA side there is like there’s some things we could do in code that would simplify that process for you now that I understand it. So there is often low hanging fruit between the teams that until we have some understanding of how everybody does their work, you don’t see that. We do have different view points on the same project. Once we see each other’s viewpoints you pick some simple things.

Steve: I think the key there is seeing the other person’s viewpoint, and almost like you’re in their shoes to understand what they are trying to say. And so often that’s what’s lacking. People just say this is what I’m going to do because I’m a DBA, or this is what we do because we are developers.

Richard: Well, we hide behind phrases like best practices. I don’t have to think about your need because I’ve got best practice. And again, it’s the other form of easy. You’re undermining the other person’s work, so why are you surprised when they resist you?

Carlos: Alright, that is an interesting question.

Richard: Let’s tackle it this way. We will only get to call it a best practice when all of us agree it is.

Carlos: Right, so the team itself has to agree on what the best practice is not you bringing in some external source and deciding for everyone, “Hey, this is the best practice.”

Richard: And it’s not like you have to invent from scratch. There is no problem with bringing something externally and saying, “Hey, I read this from this credible source. They recommend these things. How would this fit with our plan? Do you see value in it?”

Carlos: Right.

Richard: I hate coming with such certainty, and it’s a disease we have as technology people. We’ve had careers. We’ve had our time in school where we liked the result. We want the answer. We hated showing the work. Remember that, how much you hated showing the work. Here’s the bad news, when you actually want to influence people you got to show the work. And we keep coming with solutions and people want to understand the path. That’s why you present the best practice rather than a set of ideas that get to a result. One is showing the solution, the other one is showing the work. And without the work you can’t influence.

Steve: Yup, interesting. I can think of this specific example where I ran into that, where I was debating with a development team on the use of GUID as primary keys and the clustered index. Of course these were random GUIDs not sequential GUIDs, and I went and I found some blogpost from Paul Randall and Kimberly Tripp talking about this. And I went and examine them, wrote up an example that showed exactly what they’re article said and showed it to development team and then they came and said, “Oh, that’s just some blogger talking about it.” I mean everyone out there got an opinion on it. And the mistake I made was I didn’t set who Paul and Kimberly were in that example. They were just two unknown bloggers to this developer.
Richard: Not the guy who wrote DBCC.

Steve: Yeah, exactly. And I completely blew it on that one because I didn’t set the stage for where the data was coming from that I was using in my proof there.

Richard: And you can leverage that blogpost to map it to the problem, we’re seeing these issues. Let’s face it, like there is lots of controversy around randomly generated GUIDs. It’s not free. It has certain advantages for certain circumstances and it has a certain cost. Sort of upfront by looking it both, so the things to say, is this a cost we’re willing to pay to get this benefit. I do simply don’t believe in the one right way. I’ve had too many years writing too much software to actually believe there is a right way. There is the way that works at that time with the tools and skills that we have. We can get better but if there was one way through these journeys we’d all be taking it. The fact that a bunch of us have been successful in a bunch of different ways is sort of proof, and that no tool will save you. You take the same set of tools in a different problem and fail.

Carlos: Yeah, we look at that in data all the time, maybe like to make fun of the reporting tools. This new reporting tool is not going to help you unless you’ve got good plumbing, good infrastructure underneath. How many times do we try to do the same thing like what we’re talking about, the best practice or some other solution to say, “Hey, this new thing is what’s going to save us.”

Richard: Yeah. No, I want the big red button and then when I hit it, everything goes well.

Carlos: Exactly.

Richard: It’s a lovely dream, it’s just not true. In the end the tools cannot save you, it comes down to people in practices and since people make the practices it’s only the people. That’s all there is, you have to deal with the people that you’re working with. And if you understand them and see what their strengths are, value those strengths and put that puzzle together, put those pieces together so that everybody’s strengths are emphasize and their weaknesses are compensated for. That’s when you get an effective team.

Steve: So one of the things that I’ve seen in organizations were like management will try and get cross functional teams, operations and developers to work better together is they’ll do some kind of an outing that involves trust exercises and team building and that stuff. What do you think? That does kind of thing help or is that?

Richard: Yes. I’ll tell you why. It’s almost not the activity, it is the time together. In fact, the more mundane the activity, the better in some respects. Packing boxes at a food bank is not intellectual work. And the good news is then you don’t have to focus on it, maybe you’ll start talking to each other.

Steve: Or even if you took the whole group for an afternoon of bowling. It might have the same effect.

Richard: Yeah, same effect for that very much the same reason. And again, introducing food into the equation because it does plug into our brains to connect more, time together without the pressure on the work. Now, I’ve gone an angle on this as well. A few years ago help setup a charity called Humanitarian Toolbox where we build open source software for disaster relief organizations. In my mind it was, I know developers want to volunteer their time in a constructive way and use their skills. What I realized was it was also a very powerful team building exercise because yes we’re still writing codes but we are not writing the code from the office and we’re not on those deadlines. We’re in this volunteer mode. And so the dynamics of the team shift around and you sort of see each other differently. All of those exercises external to our regular day to day work, so the pressure of delivery, the pressure of a 40-hour work week are off and we’re in that different mode, that invariably can strengthen a team.

Steve: Ok.

Carlos: And I think some might complain now that you’re asking to do extra things.

Richard: Without a doubt, and this is part of understanding people. Look if you’ve got a 3-year old and a 5-year old at home you probably too busy to take a weekend off. Those kids need care. I think it is part of a good organization to recognize where our team members. How do we support them? On the other hand they love to have a weekend when they didn’t were constantly managing kids. So the fact that you’re offering some child minding as part of that equation at the bowling alley? Brilliant! How do I actually get the whole team to show up and be engaged? Well, if I understand them, I know what motivates them. I know what challenges they have so we can work on those things.

Carlos: And I think if we take the approach of I am making an investment in the team, I am trying to form those relationships and not, “Oh my gosh, another meeting,” just something I have to slug through that’s not helpful either.

Richard: The team building exercise will continue until morale… Really, we’re going to mandate that? One of the organizations I worked with, there was several people that were very bent out of shape about leftover food. They would take it down to the soup kitchen they would make sure utilize, and that to me was cute. Like there’s a strong social conscience in at least a portion of this team and so when you put energy into that, let that be more visible, it was very easy to say, “Hey, most of us care about this stuff. What would you think about doing an afternoon at the soup kitchen packing sandwiches?” It was already a thing where we’re
aware was important to many of our team. So validation there, add some time away, a good practice, doing a little corporate citizenship were our company did something collectively, you know, that was important. And the side of that was increased trust in the team, a shared set of values outside of today’s work. And some time to finding out who’s really good with plastic wrapping and who sucks at it.

Carlos: And then all the inside jokes that come along with that.

Richard: Exactly. You know, everyone of those inside jokes is a reminder of trust. Every nickname kindly given is a reminder of trust. That’s what good teams are built from.

Steve: Right, ok good stuff.

Carlos: Yeah, now I feel like we need to go to the soup kitchen, Steve.

Richard: Well, you’ve got to find the thing that works for your team, whatever that may be. Part of this is exploring the values that exist within your team outside of the day to day work. I’ve done Habitat for Humanity as well. My father was electrical engineer. I know how to do wiring so I was wildly productive and enjoyed myself. And everybody was surprised, I’m kind of a geeky guy. And then you give me a pair of lineman strippers and some rowmax, and I’m putting light switches in, surprise.

Carlos: Yeah, that’s right. And I think I guess, and again we’ve kind of been coming it from the vantage point while you may not have the ability to put those things together in your interviews and your annual interviews or when you’re asking for feedback, right, these are the type of things you should probably pushing for and making those suggestions. Even going back to the lunch and learn, I’m a huge fan, I don’t know why more organizations don’t do it, and I guess they look at this like a cost but again for a couple of pizzas. I mean, it’s well worth the investment.

Richard: Sure. You can do this ROI. My goal here is to make a stronger team; team is more productive for the price for a bit of food, it’s ridiculous. There is an argument about wanting to take a break, time away. But taking a break to think about something else is still a break. So it’s just what does that look like. I know a group of developers that what they did on their break was cycling. They loved riding their bikes. And so the fact that we would help organize for them a place for them go riding over lunch maybe huge difference to them. Again, valuing the distinctive aspects of those teams so that they can get stronger and get that level of validation that we care about the whole person, not just the 8 hours.

Carlos: There you go.

Steve: Yup. Good point.

Carlos: Yeah, Richard thanks for those insights. I mean so great points. Again, no easy button, right? But there are ways to go about.

Richard: And I guarantee you it is very persuasive in your own mind that someday I’ll get this promotion I’ll be able to make this happen. There is no level of power that will make it happen. CEOs all the time throw up team building exercises and nobody shows up. Just because you’re the boss doesn’t mean it’s just going to happen. You will never do this from a position or power. And I would argue that a position or power actually undermines your ability to persuade.

Steve: Yup. I would completely agree on that one. I’ve seen people get into that position or power where they believed they had the ability to command and totally loss the ability to persuade anyone to do anything.

Richard: They were probably more persuasive before they got the promotion because they actually try to persuade. Leaning on your title it’s never going to work. Just actually care enough to understand and help people understand you, you’ll get results. Nothing is fast, nothing is simple, it just takes time and it’s worth it because the improvements last. Once you get into this righteous cycle of a stronger team adding new members to the team, it’s easier to hire. People recognize a healthy, happy team and want to be a part of it. When you already have a culture that people value more people adapt to that culture. This is a righteous cycle, it gets easier over time. The first steps are the hardest steps but further down the path, you wonder how you were never there before.

Carlos: Right, I think using that title again, if the approach is different like you mentioned that idea, so there’s that saying, “People don’t care how much you know until they know how much you care”, kind of going back to the title right. If the approach then changes from caring about people to kind of getting things done, “Hey, I’m going to implement this thing”, then again you’ve lost, you’re kind of coming with the best practice again and you’re back to square one.

Richard: Well, it’s amazing how much people build like that because nobody uses this like, “Wow, that’s really well built. I’m sure you’re enjoying it all alone.”

Carlos: That’s right. Well, awesome. Richard, thank you so much. Shall we go ahead and do SQL Family now?
Richard: Absolutely.

Steve: Yeah, let’s do that. So what was your first experience using SQL Server?

Richard: Well, I’m the old guy, right, so I actually had the 421 installation on NT3 1, the non GUI version of NT. It was bomb proof, man. That was a solid piece of hardware. I think it was probably 486, maybe a 386 and just ran every day, all command line, total vintage; and the app I was writing was Visual Basic 2 with VBX library called SQL Sombrero.

Steve: Oh, I hadn’t heard that one.

Carlos: I like the name.

Richard: SQL Sombrero, that is like some vintage stuff. When we got to VB3 then we’d actually have RDO which was a data objects layer for talking to SQL Server, remote data objects but before that it was third party to actually get to SQL Server. But that’s old, that’s original client-server stuff, man. It was great great software to run. It was very straightforward but getting all the pieces put together and getting it working. That’s actually pre ODBC, that’s how old that is. It would be later that I would have ODBC errors.

Carlos: Oh man!

Steve: Yeah, there were no ODBC errors then.

Carlos: No ODBC.

Richard: There was no ODBC. Microsoft developed ODBC because they were trying to get traction to SQL space and everybody has their own drivers. The whole idea of ODBC was a universal driver. I had a customer once tell me, “I want you to get ODBC up all these machines because all it does is generate errors.”

Carlos: Wow, so all that time working with SQL Server and with .NET, right? SQL Server has changed quite a bit since those days, right?

Richard: Oh for sure, yeah.

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

Richard: Well, even though I understand why .NET existed inside SQL Server I really wished I didn’t. I’m happy that it’s off by default. You should leave it that way. The number of scenarios where running .NET inside of SQL Server is a good idea are so vanishingly small. And of course Microsoft can never take it away because people have taken dependencies on it now. Like that’s going back to 2005, and their justification was this was a big data problem long before big data was really a thing. And today we would solve it in much more coherent ways but here we have this feature that just persists and it’s painful. It’s so dangerous. And anytime I have some a pop on the head, maybe I’ll just to turn on .NET. Don’t do it!

Carlos: Step away, step away.

Richard: Step away from the process, don’t touch it.

Steve: And I’ve never seen that work well by using .NET in the database.

Richard: It’s a very tricky thing to do and honestly I get so old now I don’t know it makes sense. There are so many better ways to solve problems that that was supposed to address than with .NET inside SQL Server.

Steve: Ok, so what is the best piece of career advice that you’ve ever received? You are not your customer. And I mean that from a perspective of your developers are not a DBA, and you’re a DBA, so just presume they are not thinking like you do. You’re not the customer, and so every time you think you know you’re wrong. You have to ask the question. You have to be influenced. You don’t use the software the same way the customer uses the software. You don’t operate the database the way that anybody else looks at the database. Your viewpoint is unique to your space. You might be able to talk to peers but your peers aren’t your customers either, so just stop fooling yourself. You have to ask questions.

Steve: Sounds like good advice.

Carlos: Richard, our last question for you today. If you can have one superhero power what would it be and why and why do you want it?

Richard: Well, I guess I want to fly. Doesn’t everybody want to fly? It’s almost a cut out
00:45:00 really. That way I could tell the airlines where to go and fly myself. I’m in metal tubes in the sky a lot and I’d like to do less of that and still get the chance to work with the people I get to work with, go to places I can go.

Carlos: There you go.

Steve: Isn’t that your superhero power Carlos, that you wanted?

Carlos: It is, yeah.

Richard: Fly or invisibility. I don’t want to do time travel because I don’t want to be responsible for anything, right? It’s really those three, that kind of great superpower.

Carlos: It depends on which plane I’m in, how much I can work with my laptop or my tablet right but that would be the one thing, the next thing to figure out once you can fly is how do I get to get some work done flying around.

Richard: Well, obviously that’s going to be enjoyable enough that we don’t care to do anything else. Never that simple.

Carlos: Well, awesome. Richard thank you so much for being with us, today.

Richard: Guys, tons of fun. Thanks so much for having me up.

Steve: Thanks Richard. It was a great time.

Richard: You bet.

Carlos: Yes, we do appreciate it.

Richard: My pleasure.

Episode 109: To Certify Or Not to Certify?

In episode 64 we interviewed Patrick Thomas, the program manager for the MCP program which includes certifications.  There continues to be quite a bit of discussion around certifications, so it only makes sense that we give our take on the matter.  Both Steve and I are certified, so this may give away some of our thoughts.

Certification is a tricky thing.  Much of the value you get out of a certification is dependent on where you on in your career and what your next step is going to be.  This is going to be different for every person, so this decision is one that only you can make.  This can make a decision on which certification to get and even when to get it a challenge.

Our conversation really revolves around what a certification will get you and what it won’t help you with.  Do you agree with our list? Leave your comments below.

Here is our list of Pros and Challenges with certifications

Pros
  1. Measure your skills
  2. Foot in the door
  3. Exposure
  4. Confidence

Challenges

  1. May not really measure your skills
  2. Not the only way to show skills
  3. Lots of prep work, with little way to gauge your progress
  4. Lateral Help
  5. Text Anxiety

Episode Quotes

“Having those certifications listed on your résumé may make you stand out as a little bit better.”

“It gives you exposure to more stuff that you may not have thought about trying in the past.”

“There are just a lot of features to know, and then try to create a test that can cover all of that is just daunting task.”

“The blogging… or the speaking at SQL Saturdays allowing you to create that portfolio, certifications are no longer the only way to do that.”

Listen to Learn

00:28   Introduction of the topic: to certify or not to certify
01:41   Companero shout outs
03:28   Updates about the upcoming Companero Conference
04:20   A little teaser of who will be next episode’s speaker
05:39   Show notes links
05:56   The pros of certification
06:10   Pro #1: Giving you an opportunity to measure your skills
08:30   Pro #2: Getting your foot on the door
10:51   Pro #3: Giving confidence to the technology workers and the employers
11:45   Pro #4: Exposure
12:28   The challenges of certification
12:37   Challenge #1: It may not help you measure your skills
13:48   Challenge #2: Your exam exposure and the employers expected exposure may differ
15:53   Challenge #3: Creating a test for too many features will be a very daunting task
18:53   Challenge #4: It’s not the only way to show your skills
22:18   challenge #5: There is a lot of prep work required that is very time consuming
26:16   Challenge #6: Lateral help
27:08   Challenge #7: Test anxiety

Transcription: IT Certifications

Carlos: Hello companeros! Welcome back to Episode 109. It’s good to be with you again.

Steve: Yup, it’s good to be here. This week’s topic is to certify or not to certify.

Carlos: Yes, as we get back in here to the studio, Steve and I are chatting today. We kind of came with this topic. We’ve talked a bit about certification in the past but we thought we would explore this idea a little bit and kind of talk about some pros and cons of why you may or may not want to certify.

Steve: Yeah, I think what’s interesting with this is there are a lot of pros and a lot of cons and a lot of varying opinions out there depending on who you talked to.

Carlos: Sure, sure and maybe we’re going to squash some of this conversation but the reality is that you got to have them. I mean I have passed certification exams, you’ve passed certification exams. You know, we might gripe about them a bit.  Our conversation is not going to, I maybe used the wrong phrase there earlier, maybe not why you wouldn’t certify but maybe some of the challenges you will face around certification, that’s probably a better model there. I don’t think there is too many people out there saying, “Oh, don’t do that.”

Steve: Right, but before we get into the details there. Do we have any companero shout outs to mention this week?

Carlos: Yes, we do have a couple of companero shout outs, one coming from New York, so Alexander [name unclear – 1:53]. Sorry Alexander. I apologize. I should have practice that before we went on. But you know who you are, so Alexander is a long time listener. Got us after a little bit or he reached out to us when we had that little pause and encouraged us to get back on the air and to keep going, and so we appreciate that.

Steve:        That pause by the way was our summer vacation. It wasn’t like us giving up on it or anything.

Carlos: Yes, that’s true.

Steve: We also had a shout out from Aaron Hayes, and he just wanted to reach out and say that he appreciates our time for the podcast and he always listens to it on his commute and it keeps him up to date with some of the latest stuff in SQL Server. Thanks Aaron we appreciate that and Alexander.

Carlos: Yeah, we actually appreciate all those comments. Steve and I were talking before we started here. You know, we push this out into the internet, we laugh and we joke and we have a good time with it and then all we kind of see is download numbers. And so it’s nice to get just a little comment even if it’s that. We hopefully could sometimes engage in longer conversations but just to have people let us know that they are listening we appreciate it.

Steve: It’s definitely good. We don’t always know from those download numbers whether people are enjoying it or whether they are just thinking we’re crazy or what.

Carlos: Yeah, that’s right. Hey, check out what Carlos and Steve said in this, you’re never going to believe it. Oh boy! And we should note, so Aaron is actually going to join us at the Companero Conference.

Steve: Oh, speaking of which, what’s happening with that now?

Carlos:    It is coming up, and companeros you have two weeks left to register. So next episode comes out, that would be the 13th, September 27th is out cut off day for registration to just allow us to get ready. Yeah, so we hope to see you there. If you haven’t checked out the website – companeroconference.com, we will be down in Norfolk. If you are a regular listener to the podcast I think we’ve gone through the idea, we have a great lineup of speakers, we’re going to be doing peer conference. We have structured and unstructured time. We’re going to be going out on a cruise and so we think we have put together something that’s compelling and hope you’ll join us.

Steve: Alright, and this week it’s just you and I on the episode, Carlos. But next week who do we have that will be joining us?

Carlos: So next week I’m looking forward to Richard Cambell, so the Richard Cambell from the .NET Rocks podcast. Probably you’re familiar with them. He’s been in lots of different areas. You see him a lot on the Channel 9 as well. They are involved with the SQL Intersection or Dev Intersection Conference, and so he’s kind of all over the place. We had a user or a listener requested idea for building trust on teams and so I thought, hey let’s reach out to a non DBA, a developer type, and kind of get their take. I had a conversation, it was fascinating.

Steve: Yup, and I think that’s an episode that we’ve already recorded but it will be coming a week from now. I had a good time with that. It was interesting because he is probably the person that was on the episode who has had more podcast and experience than anyone we’ve had on any episode.

Carlos: Oh, no question.

Steve:  Because he’s done I think something like over 2000 podcast episodes.

Carlos: That’s right. In fact, we claimed that that episode was his 2000th episode. We claim that number. Yeah, that’s a lot of podcast. So for this episode on certification, if you want to get to the show notes, the show notes for today’s episode is sqldatapartners.com/certification.

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

Carlos: Yeah, so I guess let’s go ahead and jump into that conversation, right. So we have this idea of to certify or some challenges that you face in certification. So let’s go ahead and start with the pros. Number one, certification is giving you an opportunity to measure your skills.

Steve: Yeah, it’s interesting because looking at that it’s one of those that even just preparing for it you can go through and see these are the things, the topics that maybe included on the exam. And you can go through and figure out, “Ok, where I am strong and where I am weak. What areas do I need to improve on?” It’s sort of a good way to build out your experience or build out your knowledge so that you’re a little bit more rounded on that specific certification.

Carlos: Yeah, that’s right. I mean, certainly you’re getting a broad exposure to all of the features and technologies that are in the database. Most of the time they could get sold in a book, you could take a peek there. I think some of the prep tools are getting a bit better to give you a better chance to get your hands on some of those things before kind of putting them in your environment.

Steve: I think an example of that for me was several years ago when I took the 70-461 exam. And I went through the prep material ahead of time, the whole XML in the database was a piece that I hadn’t done anything with at that point in time. But after doing the prep work I realized, wow there is a lot you can do there. Now, I’ve done a little bit with it since then but it’s not one of those things that I would have necessarily just stumbled across in my day to day work.

Carlos: Exactly. I think that kind of goes back to this idea, again we’ve talked a lot about on this program is sort of marriage of the technology and the business requirements, and that is as you begin to become more familiar what your business is looking for, the problems that they have, right. How can the technology help solve some of those problems? I think is you are able to combine those two then you have a very successful, you have a very interesting package there. You can be very successful as you begin to connect those dots.

Steve: Yup, yes indeed.

Carlos: So Pro #2 is getting your foot in the door.

Steve: Right, so what exactly do you mean by that?

Carlos: Yes, so I think a lot of times, and I knew this is the case for me particularly when you’re new, having the certification allows you to be able to say, “Well, hey, I’ve passed an exam. I kind of know what I’m talking about.” And so as you look for employment it just seems like particularly in the DBA space, the data platform space certifications are important to employers.

Steve: Yup, and I think that seems that’s an area that’s important at that time that you’re writing your résumé or preparing to jump to that next position. Just having those certifications listed on your résumé may make you stand out as a little bit better than someone else who doesn’t have those on their résumé.

Carlos: That’s right. I think it’s particularly prevalent or most needful. Let me try it again, sorry, Julien. It is especially important when you’re talking about career changes, right? If this is in technology so for example you’ve been a developer and you want to come over to data platform or networking, same thing and system administration. Or if you haven’t been in technology and you want to come in. You know, certifications are that way. Again, if nothing else just demonstrate that you have the vocabulary to be able to speak the language.

Steve: Yup, and I think an example of that, I worked with a guy who have been doing mostly like tech support type work but he wanted to do some more database thing so anytime there was a database project he could take he did it but then he wanted to prove that he knew enough about database so sort of jump out of that support role so he went and did the 70-461 exam. Took him maybe a year of prep work to get ready for that from where he was at and he eventually passed it and sort of prove that he could play in that area.

Carlos: Right. I think that’s maybe another point that we should make is confidence there. We didn’t have that on our list, maybe we should make that as another point. Even I remember going back and talking it in Episode 64 when we were talking with Patrick Thomas who was over the Microsoft Certified Professional Program at Microsoft, and that idea of they are trying to give confidence to the technology workers, not only the workers but also the employers. And so I think again, we’re going to circle back to this and sometimes those goals or what they think of the exams don’t always line up but it is important that both of them are looking to them and again want to gain that confidence. And then our last one which we kind of touched on a bit already I think is just exposure. You know, we mentioned getting into the new features. What’s there I think particularly now as new things begin to come out. What’s in the database that you’re not using and could you take advantage of it. I think exams help to do that really well.

Steve: Right, and I think I kind of jump to go on that under the measure your skill section with my story there on XML. But yeah, I mean it give you exposure to more stuff that you may not have thought about trying in the past.

Carlos: So those are our pros and interestingly enough we have a few more cons, or may not cons again, but challenges. We should change it to challenges. So as much as we talked about measuring your skills, our first challenge is that it doesn’t really help you measure your skills.

Steve: Right, and I think that might be better phrased as it may not help you measure your skills. I mean, the fact is there is some confusion out there and there’s things people can do to get the certification without really knowing what they are doing. For instance, people can cheat by going out and finding some of the actual exam questions online. I know after I took one of the exams there was a question that I wanted to look into a little bit more and I did some googling on it. And I find out after I had passed the exam that wow there is this exact questions that are published out there. And really if you have a good memorization skill you could go through all of those and memorize them and not know anything about the topic and passed the test. I hope that doesn’t happen very often but I’m sure it happens occasionally.

Carlos: Right, kind of along those same lines is the idea that we talked about employers wanting the certification, so what the exam is going to give you exposure to and what your employer thinks a certification is going to give you exposure to maybe slightly different. They are not always in harmony.

Steve: Yup. Now, I can think of an example there where I knew someone who was newer to SQL Server they had been working with it for a couple of years and they went and passed the 70-461 exam, and after passing that the employer’s take on it was that they could now do everything, anything with SQL Server, and that person could do all the skills of someone with like 20 years of experience. In a conversation with that person we discover that there is a lot of things that you do every day that aren’t part of that 70-461 exam test. Like everything that’s covered in 462 for instance.

Carlos: Yeah, and this is kind where the blessings and curses. Even when we think about SQL Server, right? It’s great for us because there are so many features that come with that but then when we talk about SQL Server we’re lumping in SSIS, SSAS, administration, performance. I mean there are just too many tools in there, and then from an employer’s perspective they are like, “Oh, SQL Server certified. You should now understand all of these things.” You’re like, “Well…” Yeah, it could be a bit challenging.

Steve: Yeah, and I think part of the key to that is really understanding what that certification has covered and being able to convey that in a way that whoever is looking at it, like your employer, understand what that means.

Carlos: Right. Another one that I added here and that is with the new versions, there are just too many features. The database now, I mean it can slice, it can dice, it can chop your onions, serve your veggies and all the other stuff as well. And so it’s interesting I think, and again we’ll see how those certifications come out but the 461, 462, 463 are still going back to 2012, 2014. Even there were two versions removed from that they haven’t quite abandoned that and I think it’s going to be challenging just with the sheer number of features that we have. I don’t know if certifications are going to change. Maybe we have new exams, smaller exams, pocket exams. I’m not sure how that will come out but I think there are just a lot of features to know, and then try to create a test that can cover all of that is just daunting task.

Steve: You know, I think some of those features that they included in the test are almost like features that the marketing team for SQL Server wants to have covered so the people know how to use the latest and greatest rather than knowing how to use what they need to use to get their job done. And those can be very demanding on occasion.

Carlos: Or the ones like you mentioned even the XML that kind of continue to get in there every version, then you’re like, “Really guys?” Having said that I guess, I should say full disclosure. I have actually participated in with an organization that reviews the tests and we go through, so on the actual exams and then on the practice test, practice exams. And writing test questions is really hard. And so you have four people, that at least four people, on this call with the moderator going over a question and all the answers and we’re debating back and forth, and we’re looking at documentation and trying to make sure the wording is just right and then you change something and you’re like, “Oh well then that’s going to affect this other thing. And what if they think of this and how they are going to interpret that.” It’s very difficult and I think sometimes we might complain a little bit about how the exam questionnaires are worded. But believe they are spending a lot of time and energy to try to get those questions just right.
Steve: Yeah, they certainly are. I think that we oftentimes forget that when we’re taking a test or when we’re practicing for a test.

Carlos: Sure, right, because I think as we get to later, when we’re in there sometime all that time and energy doesn’t help us and that can be frustrating.

Steve:  Yup. So another one of the cons that we have on our list is it’s not the only way to show your skill and that there are a lot of other things that you may be able to do and maybe even be able to do with less of the time investment to be able to show your skills. There’s whether it’s blogging, or speaking, or taking on a side project, or looking on open source project or any of those kind of things might be just as valuable in showing that you know what you’re doing versus taking a test.

Carlos: Yeah, that’s right. I mean, our pros was that idea of getting the foot into the door. Right, having that on your résumé, that’s not the only thing that you could put on your résumé to differentiate you. I think we’re going to continue to see it more obviously in the developer space. We’re seeing it a bit more but even like Github, right, so what your Github handle and allowing employers to go out there and looked at the code you’ve checked in, you’ve participated in, whatnot. That is another way to increase your profile and I think that to our advantage and it hasn’t been too far in the past that I’ve actually need to work through recruiters and like try to find a job. Oh man, what a hassle that it. Like I can almost, the back of my throat is like having this vile taste in it remembering what that process was like. Luckily, that’s changing a little bit and so it’s more of extending your network and trying to connect with other people. That will be the best and easiest way, maybe it’s not the easiest way to get a job, but obviously the most successful path. And so all of these other things like Steve mentioned, the blogging and whatnot or the speaking at SQL Saturdays allowing you to create that portfolio, certifications are no longer the only way to do that.

Steve: Right, and I think that may vary greatly with every employer too. I mean I had a job interview I remember several years ago where it’s like, “We don’t want to see your résumé. What we want to see is your Github profile.” And that was it, that’s all they wanted to look as a starting point. And at that point in time I hadn’t really done anything publicly on Github but I’ve done lots of other public stuff but I just completely struck out on that one. They didn’t care about certifications but then you go into another place and they do care about certifications. Certifications are the only thing, you got to be a little bit more well rounded there I think.

Carlos: Yeah, exactly. It’s not a one size fits all. And the other component there is what type of organization do I want to work for, right? I think that plays a role into how you build your portfolio. What’s going to be attractive to them is also attractive to you. That increase is a likelihood that you’re going to go there and be happy, and be able to contribute to the team.

Steve: And you know, it’s interesting that interview that they only wanted to see your Github profile. I was a bit disappointed when that happened but then I later learned more about it and realized I’m kind of glad that I didn’t get that job after I learned more. Another of the cons on the list is really around there is a lot of prep work required that is very time consuming. Now, it may be people are at a different skill levels or different points in their career that that time consuming part may vary. But I think that no matter who you are there is some prep work required to go in and make sure you know everything to be ready for that test. And for some people that might be a year of prep work if they are new, for others it might be a few days or week of review to be able to just get up to speed on that.

Carlos: Whoah, a few days! That’s pretty aggressive, Steve.

Steve: Well, I guess it depends like if someone has taken the exams over different versions for the last 15 years they may have just to catch up in latest things before going and taking that next exam.

Carlos:  Well, it’s funny that you say that because that actually happened to me on the 461 exam. I thought it’s writing SQL, like how hard could that be? So my challenge there was I actually really didn’t study. That was the problem I used to, “Ha, I’ll go in there and I’ll just do it.” And you have to have a 700 to pass and let’s just say my score was in the 600 somewhere, right. I was like, “Argghh”.

Steve: Let me guess, did the merge statements throw you off in the exam?

Carlos: There was a group of questions that I guess shall remain nameless that I think threw me off, that ended up spelling disaster there for me. But then I’m like, “Oh, ok I got a bone up on that.” So I guess I made the backwards mistake of instead of using the test material to find that out I used the actual exam to find that out.

Steve: Yeah. Which you know, I mean that’s one way to do it because oftentimes you can get a retake that’s free or not very expensive.

Carlos: Or reduced.

Steve: And with that, I mean not all the prep material is all that is prep up to be. I think that I did some work for a book publisher a few years ago. That was on the prep work for the DBA series, the 70-462. One of the criteria that they had for everyone who was contributing on that book and video series was that you could never have taken the 70-642 exam because for people who would actually taken the exam they sort of lead in to like giving away exact clues of here’s how you pass the test. So in order to get around the legal hurdle of getting sued in some way they said, “Well you just have to study what Microsoft says is going to be on the exam and then we write a book around that.” I did eventually after doing the work on that book. I did eventually go take the test and it was much easier to take after preparing that book or work on that video work for them but I can see that there were things that are very different in the exam versus what you sort of expect from reading some of the prep material ahead of time.

Carlos: Right. Yeah, that’s one of the challenges with the prep material. I’ve used the books before and it’s like they just go over everything and you’re like, “Huh…”

Steve: And they have to go over everything because you don’t exactly what’s going to be on the exam.

Carlos: Exactly, exactly, that’s right. And then the way they get worded and all the other stuff I guess that’s another hurdle, but just a sheer amount of material that you have to go over is a challenge. Let’s see, so the next one we have is lateral help.

Steve: So really that’ sort of saying that in most situations it’s probably not going to help you in your current position but it may help you if you are jumping somewhere.

Carlos: Right. Or you’re looking for something, either promotion. It seems like it’s a plus. I mean, it’s not to say that education is not good. We’re specifically kind of honing in on the certifications. Obviously listening to this podcast is a way to increase your knowledge and what’s in your skill set. But yeah, it seems and this is just a generalization but it seems in our experience it’s been, I want my certifications for my next job not so much for the one I’m currently in.

Steve: Right, so I think leads to another one on the con list is test anxiety. And I think that there is a lot of great DBAs and database programmers out there who are just awesome at what they do but they don’t always do well on test.

Carlos: And the format of the tests are changing. I mean, they are difficult, right, I mean to put things in order. It’s like A through J. It is knowing some of the syntax. It’s very very tricky. Now, this actually takes me back to Episode 10, the very very beginning, Thomas Frank, who runs a blog for college students. We actually chatted with him about some ideas of preparing for exams and kind of dealing with some that test anxiety, and so that was kind of interesting. You’ll have to forgive that episode that was one of the very very early ones so the audio quality is not the greatest there. But if that’s something that you’re thinking about it may be worth taking a listen to that Episode 10.
Steve: So another one of the cons as well is you can just keep taking the test until you pass, meaning as long as you’re willing to pay for it. I remember another exam that I’ve taken different from technology here was I’m a volunteer firefighter and EMT, and when I took the EMT Exam, that was way more stressful and way more detailed than anything I have done with the SQL Exam. But the criteria with that was if you don’t pass it you’ve got to wait a time period before you can retake it, so I was extremely motivated to make sure I pass it the first time. Whereas with the SQL Test, if you don’t pass it go back the next day or the day after that and take it again. It depends on your budget how often you can take it I guess.

Carlos: That’s right and luckily so there was I think was the 463 Exam, the Data Warehouse Exam. Oh man, I took that one at least three times, maybe four times, so good and bad. I mean, some of that again is just you try and balance what you need to know with what you have to get to pass the exam.

Steve: Yeah, and I think that and maybe that’s an approach you could take. You could just go take it and find out what you don’t know and then fill in the cracks. But also if you do it that way maybe you don’t enough and you just keep retaking it and filling in the cracks you’re really teaching yourself what you need to pass the test, not teaching yourself what you need to be a rockstar in that area for instance.

Carlos: Exactly, also true.

Steve: Which might be the way to do it for 463.

Carlos: Unless Data Warehouse is your thing, right?

Steve: Exactly, yes yes.

Carlos: Ok, so I guess the recap. Our pros, we have measure your skills, foot in the door, exposure and confidence.

Steve: And our cons were around it does not necessarily measure your skills, not the only way to show your skills, there is a lot of prep work that could be time consuming, it may help you if you’re jumping laterally but not in your current position, those with testing anxiety may have the challenge with it, and you can just keep retaking it until you pass.

Carlos: So there you go companeros, what do you think our list? Of course we are interested in your feedback. You can drop as a line on the show notes page which is going to be sqldatapartners.com/certification.

Steve: And you know just to follow up on all these. I mean as we’ve been through the pros and cons, and we hit the cons as our second half of the list basically. I mean overall I think it’s a good thing. I just think that there is a lot of positives and negatives with anything.

Carlos: Sure, and I think we change cons to challenges, right? So we are not saying that it’s a con to take the exam. Again, we’re both in there, so just challenges. Yes, so what do you think of that list. We would be interesting in hearing from you and comments that you have. Of course if you have other ideas or things we should be talking about on this podcast please let us know. You can reach out to us. I am on Linkedin @carloslchacon.

Steve: And I’m on Linkedin as stevestedman. And we’ll see you on the SQL trail.

Episode 108: The Future of the Relational Database

After a brief hiatus, we are back on the air to continue the conversation and let me tell you–we have a great conversation lined up for this episode.  The discussion around what will happen to the relational database, and by extension us as administrators continues to get quite a bit of traction.  Even within SQL Server, we are starting to see more features that don’t fit the traditional relational mode and a podcast listener inquired about getting our thoughts.  As I thought about a guest for this episode, I didn’t want to get someone tied to a product.  They, like me, would be biased and I wanted to get someone a bit removed from the situation.

Our guest today is Andrew Snodgrass, the research vice president at Directions and we chat about the future of the relational database and what the future of the data environment we manage might look like.  I hope you will find his insights valuable as an outsider.  While we don’t get into the specifics of what databases are mostly like to be around, Andrew does give us administrator some ideas on what technologies we should start exploring.

What are your thoughts around the future of the relational database?  Join the conversation and let us know!

 Episode Quotes

“So these things have come out as a natural result of trying to solve a problem that we weren’t able to actually do with SQL Server.”

“I think what’s going to happen is SQL Server is going to be there for traditional structured applications and database.”

“The great thing is that we can operationalize an R script in SQL Server.”

“If you’re SQL Server and you want to look at big data… you’re going to learn Hadoop.”

Listen to Learn

01:41 Thoughts about database evolution and transactional system
02:05 Ideas about big data and database tools and services
05:40 Single platform for data: is it a fair approach?
12:00 JSON data, storage cost and compute cost
12:48 Effect on the Data Team
14:15 R script on SQL Server
14:46 Role of the Express version increasing in organizations
16:44 Azure services, data lake analytics, U-SQL on organizations
19:05 Technical skills that are robust in line with big data and data warehousing
21:33 Segway about Power BI
22:28 Azure vs Containers
27:13  Andrew’s advice for executives investing in their data platform
30:57 SSIS, SSAS, Power BI
35:56 SQL Family questions

About Andrew Snodgrass

Andrew SnodgrassAndrew Snodgrass is the vice president of research for Directions, a organization that help guides IT executives on technologies, strategies, product roadmaps, and licensing policies.  Andrew leads the research and analysis of emerging trends in enterprise applications (primarily ERP and collaboration tools) and database management technologies with a focus on Big Data, business intelligence solutions, cross-platform mobile application development, and cloud hosting services.  Her is currently and adjunct professor for the Albers school of business and Economics at Seattle University.

Transcription: The Future of the Relational Database

Carlos: Andrew, welcome to the program.

Andrew: Thanks!

Carlos: Yeah, we appreciate you coming and taking some time to be with us today. We have kind of an interesting topic. Ultimately, our audience, our SQL Server folks, and we think about SQL Server, you know, we think about transactional system. It’s been around for a while although it has definitely evolved a bit. But one of the common questions that we get is what about all these unstructured data and even in SQL Server now we are starting to see that influx sort of kind of with XML and now we’ve got JSON, 2017 is going to have graph databases, Python, R. All of a sudden we are starting to see this intersection of what we could traditionally think of unstructured and structured data commingling in the same environment that has a lot of people of questioning; and not to mention the rise of other databases, right? It’s 2017 and Microsoft comes out with a new database technology and they are not the only ones. There are databases almost sprout out as often as applications all trying to solve a particular problem. And so ultimately our conversation today is going to revolve around this evolution if you will and kind of where the transactional system is going to end up. So I guess maybe take us through some of your thoughts there and kind of what you’re seeing and maybe what some of your customers are asking you about the same idea?

Andrew: Sure, along that line I think some of the rise of the M structure, the big data, semi structured, all these stuff have come around naturally and I think that the introduction of database tools and other services and capabilities have come about because as traditional structured, traditional relational database guys keep trying to force fit unstructured stuff into structured databases. And so these things have come out as a natural result of trying to solve a problem that we weren’t able to actually do with SQL Server. And so I don’t know that it’s necessarily that we’re seeing any kind of decline in traditional databases or in the relational industry that we’ve always been dealing with. I think those types of workloads continue to exist. I think they are still going to continue to exist and you still got financial systems, you still got procurement systems, and these things lend themselves well to a traditional, relational structured database model. But they don’t do everything. One of the things that I’d always look at was how do we handle situations where you need some kind of lightweight database management system. And SQL Server despite some attempts to give us lightweight database management didn’t always do a good job of it and so when JSON came about, XML you could say a little bit, but really when JSON came about and the idea of saying let’s go mobile. Let’s push data out to self describing. These seem like natural solutions and now that we’re seeing SQL Server come back and say, “Well, we’re going to try to make so that you can tap those sources.” I don’t know that I would necessarily think that SQL Server is trying to be everything although Microsoft might like to portray it that way. I think that SQL Server is trying to tap everything. And so it’s one of these things where I’ve got my traditional product list, my customer list, all my transactions that are in a nice, structured environment. We are working an application that people know and trust and love but I need to be able to get into other areas. I need to grab data from other areas and push data out to other solutions. So they are putting these tools into our SQL Server environments so that we can interact with those things intelligently and more seamlessly so we don’t have to push to an ETL tool or we don’t have to do some kind of extraction and then modify the data to push it out somewhere else and then bring it back. We can kind of put all these things into one environment and control it there.

Carlos: We’ve definitely drunk the KoolAid. I’m Microsoft kind of through and through if
(00:05:00) you will. Is that a fair strategy as I go and try and talk with businesses? One of the things that I like about this idea is that at least it gives me a common tool set from someone who’s been in SQL Server now for 15 years. That I don’t have to go out and learn all these other platforms, right? Now, obviously there are some differences, R or Python, so there is a learning curve but at least from I know the data in SQL Server there are some administrative things that I don’t have to relearn. Things like that and I think it will be easier. But it gives me the ability on a single platform to have all of that data. I mean, is that a fair idea or a fair approach?

Andrew: I think a fair assessment on it is I think that from an additional enterprise standpoint, I think it’s very attractive and I like it personally as well, large organizations, medium organizations, anybody that has applications that they’ve developed over the years where they’ve invested. This isn’t just an investment in skills and application creation and the rest of that but they actually have servers and they have licenses and all kinds of other stuffs that go along with that and the idea of saying, “If I can take advantage of what I already have and expand on it instead of trying to recreate it. That sounds very attractive. Here is a great analogy on it. There was somewhat type of when we went from client-server to web-based apps. There was this need to recreate all these applications so they work in a browser and it would have been nice if we could have kept our applications to simply have more kind of browser. And that’s kind of what we’re getting at here is we have all of our data sources, all of our capabilities, but we want to extend it. And that doesn’t mean you need to learn how to write web apps and it doesn’t mean you need to learn JSON and convert data. It doesn’t mean that if you want to go to Hadoop you’re going to have to learn Polybase and understand how to do a scale out compute environment and being able to tap those resources and manage that data. You might need to learn some MapReduce at the same time. But this is learning additional things and not replacing what you already have. And that’s I think part of the attraction of beefing up SQL Server to do more than it was before.

Steve: And I guess the way I’ve seen this happen a few times that I have talked with different people is that they’re using SQL Server, they’ve been using SQL Server in their shop for years and then they realized. “Ok, now we want to do unstructured data.” Big data as they often times end up calling it. And then they decide, “Ok, well we’re going to go with Cassandra or DocumentDB or CouchDB or DynamoDB.” Or one of those that is totally separate from anything that is SQL Server. I find that sort of the story goes with those oftentimes is that it’s kind of a decision. Are we kind of keep doing SQL or are we going to go with this other database or are we going to do a merge there or sort of using both sides of it. IT sounds like what you’re describing here is taking advantage of what’s being added into SQL Server so that we don’t have to go and look at those other technologies. Is that where you’re going?

Andrew: No. Actually, Steve, I don’t think that’s kind of the case. I think the merge is what’s going to happen ultimately. It’s kind of like buying a stereo system that’s an 011. You basically get a very generic set of components. If you really want the best you buy separate components. And I think what’s going to happen is SQL Server is going to be there for traditional structured applications and database and stuff but people are still going to be looking at Cassandra, cloudera, Hadoop, any of these for a true multiprocessing, parallel processing capability that they really don’t want to spend the money to try to make SQL Server do. The idea of that is if you go to a bid data solution you have a distributed computing environment with a lot of servers out there. Each with some local storage so that the computing the query happens locally on the data and you use parallel processing to do a lot queries across a lot of unstructured data at once to return the said information. If you try to do SQL Server to do a
(00:10:00) scale out and that same type of configuration your licensing cost alone is going to kill you. It doesn’t make any sense to say that you’re going to have 50 or a thousand servers each running a SQL Server Standard/Enterprise edition license. Oh my god, I can’t imagine the amount of cost that you’re going to have there. So what you’re actually looking at is you got a SQL Server environment that’s very powerful that has some really good SQL Server capabilities on it that uses Polybase to go out and tap that unstructured data that’s actually in a proper Cassandra, Hadoop deployment. If you really need to get in big data you’re going to have to look at a proper big data solution to host the data and run the compute. But you don’t have to run those stand alone. You don’t have to just do a MapReduce to get a data set that then you have to go through Integration Services or some other ETL in order to get in what your structured data. You will be able to do that directly from SQL Server so that you can query your structured information, your product list, your customer list and you can do sentiment analysis against a bunch of unstructured data out in Cassandra and bring those together in real time. That’s where I think the merging and the intelligence is going to be.
Carlos: Interesting. I think kind of with that a common point is like IoT, right? So you’re collecting, so all these sensors have all these information. SQL Server probably may not make the most sense for the repository for all of that information, right? But you have these sensors, you know, caches that on the web which you can then pull in to a Hadoop cluster or whatever else and then start to do that aggregation or some of that analytics on it and then pull in kind of the summary information and that maybe what you store in SQL Server and then of course then there is the reporting on top of all of that.

Andrew: Right, and that follows, Carlos, in the same line with the idea of JSON. It’s great that you can some native JSON stuff within SQL and you can push data out and pull data in. But from a storage cost and compute cost do you really want to store JSON data in blob columns within the SQL database? You are going to run some cost and performance issues that you might not want to deal with so I don’t think that’s necessarily the road with the JSON side either. I think store it where it make sense, where it is optimized and where it belongs. But I want to do stuff with that data and I want to do it in real time and that’s where bringing it into SQL Server and attaching it to business data make sense.

Carlos: So then let’s talk a little bit about the effect, are the data team just going to get bigger? I mean so we have like cache and some of this other caching layer that the programmers are already kind of doing. So now we start adding Hadoop and we’ve actually done an episode on Polybase. Now Polybase is still kind of an emerging technology. They kind of came up with the v1 but those worlds are still kind of very different so does that mean that I’m now going to have a SQL Server person, I’m going to have a Hadoop person, I’m going to have a Cassandra person or are you seeing organizations say, “Ok, guys. Now, it’s time to go learn Hadoop and you’re going to be responsible for both.”

Andrew: In some, and it depends on the size of the company. The really intelligent ones are investing in data architecture. It doesn’t matter just of saying we want big data you know everybody says that. How do you do that and how do you do it without spending too much.

Carlos: So the plumbing, the connections have become very very important. How’s that data is going to move and flow.

Andrew: Right, and so and the same thing is true now with machine learning. It’s great to say that we have R and Python in 2017 but what are you running this against? And what type of performance that you’re going to take on your servers with it. The greatest thing there isn’t suddenly we are able to do R scripts within SQL Server. The great thing is that we can operationalize an R script in SQL Server. And it’s that I’m going to R Studio. I’m going to build my script and I’m going to test it out in my data. I want to make sure everything is the right way and then I’m going to put this into a stored procedure and make it part of my application so that it performs the R script on the data, the right data, at the right time and then gives my users something useful.

Carlos: Exactly. I think now, so having said that do you see the role of the Express version increasing in organizations? I feel like adding some of that functionality
(00:15:00) to Express was a direct result of the rise of open source and particularly MySQL. And so to that idea now that I have the capabilities, and when we start creating these models maybe we don’t need 10 years worth of data. I need a month’s worth, a quarter’s worth. And then I can push that off unto an Express version let my data scientist or analyst or to whoever run all the items they want and then once they’ve decided, “Oh, I think this is where we want to go.” Then again kind of that integration, that’s in SQL Server already I could just move that into my production environment and have a more seamless experience.

Andrew: That’s a really interesting question on that. Most of our customers tend to be larger enterprises and so they have their MSDN Visual Studio subscriptions and even the data scientist they are putting MSDN Enterprise edition on or Standard and running it from there. I don’t know any answer for that. I think you probably could.

Carlos: Sure, the limitations that you’re going to run into. You know, 10GB or whatever. But again it’s that I can move that off my environment with the flexibility I get the tools so I guess I’m kind of interested to see how that plays off or plays out. Obviously, if you are Fortune 5,000 Company maybe that doesn’t makes sense but for these smaller organizations they have a couple of people trying to work on this. It will be interesting to see what happens I think.

Andrew: You know, on that line opening the door on that one for some of the may have customers both large and small we are seeing the use of Azure services spinning up capability there, moving data out, testing it out and then scaling down is a way of getting around local deployments and testing. Like Azure SQL Data Warehouse, all of a sudden you’ve got somebody who has 10 or 15 years or 20 years with a historical equipment data or performance data and they push this up to an Azure service and then run one of the analytics services against it. You know, they are doing some machine learning and some bigger analytics that they would never attempt on premises.

Carlos: Sure, because once they’re done they could just toss that out or spin it down.

Andrew: You know, and along those lines you’ve got Azure Data Lake analytics which was supposed to be originally just for going against the Data Lake storing environment with a bunch of unstructured data and all of a sudden they said, “Well, you know, we could tap into SQL database. We could tap into blob storage. We could tap into data warehouse.” And Data Lake analytics is all based on U-SQL and so we’ve got a really nice environment here for people that are comfortable with traditional SQL Server query capabilities to go out and run some really cool analytics and learn just a few new functionalities that are required for U-SQL and you got a scalable system out there to run these queries on that you don’t have to do on premises.

Carlos: Yeah, it’s very cool. I still think that some of that is like you mentioned for the larger organizations. I think as we start talking with people, I mean, there are those who are putting quite a bit of time to learn the U-SQL and take a look at Data Lakes. And I think obviously the bar is lowering but that’s still an investment right to take the time to learn some of those things and understand how they work. I guess like everything, all the technology continues to evolve and change.

Steve: Yup. It’s interesting because when we had our podcast talking about building your technical skills few weeks ago, one of the things we talked about was sort of the type of things that you can learn that stick around for a long time and the type of things that you can learn that are maybe a little bit riskier but they may go away or they may change rapidly. And it seems like some of the unstructured and big data and data warehousing pieces today are kind of within that riskier category because they are still sort of changing quite a bit. I mean are there any of these that you see that are kind of more robust to the point that or more mature might be the better word to the point that they are not changing quickly
(00:20:00) these days?

Andrew: Yeah, actually. Especially with the folks that are going to be listening that are SQL Server folks. You know, along that line if you take a look at Microsoft’s database landscape and say you’ve got structured, unstructured and semi and everything in between, the two that you would go into that are not SQL Server would be Hadoop and JSON. Hadoop is underpinning so much to the big data investment that Microsoft does into that if you want in SQL Server you’re probably comfortable to start learning Hadoop and understanding that environment. That was the whole concept behind Polybase was to touch Hadoop. If you actually look at the Polybase scale out concepts, this is a Hadoop cluster methodology for doing compute nodes. If you look at the Azure services you got Data Lake which is a Hadoop environment on the backend. You got Cosmos DB which is actually a Hive environment in the back end, and then you’ve got HP Insight which is multiple Hadoop type clusters that you can choose from there to do any kind of scale out environment you want based on the type of workload. Yeah, if you’re SQL Server and you want to look at big data and you want to say comfortable with Microsoft you’re going to learn Hadoop.

Carlos: Ok, interesting. There you go companeros. You’ve been warned. You heard here first.

Andrew: Yeah, and while you’re at it, learn some Power BI because Microsoft is investing in that.

Carlos: Oh yes, that is true. What it’s interesting I think that still kind of goes back to the plumbing. We’re actually starting to see traditional SQL Server people/folks trying to spin up Power BI environments and you’re like, “Hey, really cool reporting tool.” You know, the functionality is there, web browser and all that but then tickets are a little too big and they are like, “Hey, how to make this go faster?” And you’re like, “pull that out .csv file.”

Andrew: And then you do a Power BI premium where you have dedicated capacity or you end up doing a Power BI report server on premises.

Carlos: Yeah, that’s just coming out and it will be interesting to see that integration with Reporting Services that continues to evolve. I wanted to circle back quickly if we could because you mentioned Azure. We’ve actually talked a little bit about containers and containers particularly in the Linux world has been all the rage but it almost seems like maybe, I don’t know if competition is the right word, because ultimately the technology that they can give are different. Well, Azure versus containers. So if you’re a large organization and have an investment or haven’t made the move to Azure to that whole spin up for the way idea. I feel like that’s what containers are also trying to provide you. So is it an on premise Azure competition there when we talk about containers?
Andrew: Like yeah, that’s an interesting one. Maybe I’m too old struggling to find out a really good set of examples of doing containerization of apps and saying we’re going to put this out here especially when it is database related app. It seems like there is an off light you’re going to put in that container in order to make it work. But you know it’s interesting in Azure right. Microsoft’s whole move towards Linux anyway with SQL Server 2017 coming on Linux and the containers and all the rest of that. Azure has the Azure container services which has been out there for a little while, a year or a year and half, that allows you to spend up VMs and containerize things and then everything in it. But they’ve also just released a thing and it’s in preview right now called Azure Container Instances. And this is one where it’s a true platform as a service type. They provide complete control in the infrastructure so you don’t know the backend VMs. All you do is you’re seeing computing resources. I mean this many CPUs, and this so much RAM and here is my container image. Pow! Put it up there. Scale it as much as you want, run it as long as you want and then kill it. You don’t worry about the VMs or any of the backend stuff. You can throw SQL Server into this, and you don’t know if it’s Windows or Linux and doesn’t really matter because there is web access to it anyway. You get the URL and you just run it and you go for it. And so that’s another one where Microsoft is just trying to say we don’t really care on the
(00:25:00) platform. We know it’s the service you want and so here is how to go about doing the container and you don’t care what’s happening on the backend side of things.

Carlos: Now, I feel like the best example, and so we had Andrew on the program, different Andrew containing about container. The thinking was and I guess I’m inclined to believe it is that, at least from a database perspective. I can’t speak to the apps. You could do lots of different things with the apps but the best or the classic case for containers in the database is a development workshop. If you’re building like .com type of thing, right? If you’re a actively doing development that whole idea of, “Ok, once you’ve published or once you’ve pushed code. Ok now, let me take that snapshot, create a container, push that down back to all the developers.” So now I know they are developing against what is in production and it helps eliminates some of this migration issue.

Andrew: Yeah, that sounds very internal.

Carlos: I don’t think we’re quite, although I have heard a few people talking about it, I don’t think we’re quite saying that we’re going to throw containers at production databases just yet but.

Andrew: Yeah, you know, sometimes I hear so much about containers right now and it takes me back when we first heard about big data and then about artificial intelligence and it’s like, “Oh, I’ve got to get me some of that. I don’t know what I’m going to do with it but I got to get me some of that.”
Carlos: That’s right, so having said that what your ultimately with the directions, your organization, your consulting CIOs on technology choices. Now, obviously your mileage will vary right and it depends. It’s going to come into here but what’s your, I know we covered some of this pieces, and I guess you’ve already mentioned if you feel comfortable with SQL Server, you feel pretty safe gambling on Hadoop, not gambling but learning Hadoop. You know, taking the time to that could be your next foray. What else are you advising these executives in making investments in their data platform?

Andrew: So the things that we’re pushing or talking people about now is really along the lines of how do you take advantage of not only big data and machine learning capabilities…
Andrew: There you go they are implementing IoT and so that’s one of the things that we tell the folks is not necessarily to jump in because these guys aren’t going to do major investments or major overhauls. These are like organizations that have to plan out years in advance of what they are going to be spending their money and then get authorization for it and have to justify it. They’re getting a lot of CFOs and other CxOs that are reading too many magazines on airplanes that are coming back and saying, “Why aren’t we in big data? Why aren’t we doing artificial intelligence? What about IoT for our organization? How come we are not doing better analytics and all these stuff?” And so we’re trying to show them what Microsoft approaches to a SQL Server and data overall, and in fact we’re doing one here in another week on business analytics and business intelligence and how this are all working in the vision going forward and where the investments are happening and where should you spend your time now and where you should do it in the future? And so we think machine learning is actually far more mature than people like to think to this. Especially in a traditional business environment, there is a lot of stuff that is out there that the community has developed over the years either on R or Python. Now is the time. If you were ignoring it before saying we got to wait and see, now is the time to jump into it. The other area that we’re telling them to get into is taking a look
(00:30:00) now at better business intelligence and data sources on premises. This whole Power BI Report Server if you guys haven’t spend time in it we’re telling them to bring that in and take a look at it. One of the other one that is kind of surprising is any traditional ETL workloads that you have going on were you’ve been suing integration services for years. You know, ETL is changing, the idea that SQL Server’s database engine has the ability to go out and tap these resources in real time and do transformations. You might not be doing ETL the way you did for the last 10 years. Integration services, I don’t see getting a whole lot of investment. I see a lot more going into analysis services. We’re going to do real time queries and being stuff together and transform it there not in Integration Services. And so I think that’s going to be kind of a change for organization. You know, and that’s going to be one if you’ve invested in SSIS you’re not going to go out and swap it next month or next year. This is going to be like a multiyear thing. New stuff we bring in, we’re going to actually bring in directly. We are not going to run it through an ETL.

Carlos: Well, now see you’ll forgive me I guess maybe that my connections aren’t quite there. How am I getting the data? When I think of SSIS, I am thinking about it is that glue that takes me from my transactional system, into my cube, or it’s doing some transformation there for me. There are additional components now. I mean, we’ve talked about Polybase, so SSAS, the analysis services, now has these connectors to actually go out and grab this information in an ample time.

Andrew: Oh yeah. 2017 baby! Here we go, so Analysis Services in 2017 supports the M language. So all of the sudden all of the stuff you we’re doing in Power Query and Excel and you’re bringing stuff in and grabbing different sources, and maybe going out and getting a file list instead of just another data source that’s all coming in 2017 Analysis Services. So the big reason for that isn’t necessarily to make analysis more functional which was kind of nice outcome from it but they wanted to bring Power BI, and Power BI is all based in Power Query. And so in order to support that with Analysis Services in the new Power BI report service thing which is basically Reporting Services plus they had to bring in the Power Query engine capabilities.

Carlos: Ok, so now there is another dot connected because I know at least the initial version of Power BI Desktop, so the new version that you can download and install locally that only supports connections to SSAS. And I guess it makes more sense now if SSAS has a bit more functionality in it for you to take advantage of some of these other data sources because I think initially when I looked at that I thought, “Oh, that’s all going to be all cube data. I don’t want to always do that.”

Andrew: Yeah exactly, and it’s not. So what is Power BI report server when you bring it down and you install it basically we work on that reporting services plus because it’s just really the same thing. There are a few other things. You’re on a modern life cycle for updates which I don’t particularly like but that’s reality. But you can host your PBIX files and so you’ve got that capability. But the PBIX files that’s the only one that’s limited right now, the rest of the RDL stuff and KPIs and all the roster are full capability. So the PBIX files that you have there can only go through an analysis services cube which actually winds up being your gateway which then you have to think of in that scenario analysis services being like SSIS. It’s the glue that goes out and grabs everything and brings it in, right? Now, that’s the first three to Power BI report server hosting so you’ll see that expanding. They are going to all kinds of data sources for PBIOX files so I don’t expect that to be limited for more than a few months.
Carlos: Ok, very good. Andrew, thank you so much for visiting with us, great conversation. I wish we could go on for a bit longer but I know that we need to wrap it up here. I guess last thoughts. Steve, do you have any last questions.

Steve: No. I think that there is just a lot going in this space and keeping on top of it is going to be interesting I think. Like we are talking with Power BI so Microsoft is investing so much in Power BI these days that everything it touches is sure to expand down the road in the near future. So I think that’s a pretty safe bet. I
(00:35:00) think there is a lot of interesting things to come here in the near future.

Andrew: Have you guys ever wanted to talk about that one in a separate one. Power BI is one of the other cover, not for this broadcast obviously but in the future.

Carlos: Yes, of course we’d love to. Again, particularly for this conversation we wanted to have somebody who was obviously familiar kind of covering that beat if you will but didn’t work for Microsoft.

Andrew: They are not even a customer of ours.

Carlos: To see just how that continues to play out. I think we touched on it a bit but we as database administrators we have our homework cut out for us. I think it will be fun to see how that continues to evolve as time goes on. Shall we go ahead and do SQL Family?

Steve: Yeah, let’s do that, so Andrew how did you first get started using SQL Server?

Andrew: Up until 2008 I was really kind of a Sybase kind of guy. I know it takes me back a while. We had a need for standalone database engines and Sybase was one of the early ones that allowed you to have a decent relational database environment that you can have on a separate machine. We have a bunch of travelers so they needed that and I did not want to go into Access or FoxPro which is where we originally started. So there’s Sybase and then as things progress we saw the direction for SQL Server and that became our standard and we moved over in 2008 and never looked back.

Steve: Ok, interesting.

Carlos: There you go. Now, as you’ve been covering SQL Server since then and then working with it. If you could change one thing about SQL Server what would it be?

Andrew: Yeah, you don’t like this. I changed Management Studio actually.

Carlos: Oh boy! Here we go. You know, because they have broken that out. They have their own development cycles now. Let’s see. What don’t you like about SQL Management Studio?

Andrew: Well, I like a lot that’s in it. I think the thing that would benefit them is as if they would make it easier for new entrance. One of the things that I find that is most difficult with new developers and new database people we bring in isn’t learning SQL or learning database structures. They’re getting all kinds of education on that. But setting it up, accessing it, using it, you know, some wizards something in there for the new guys to be able to get in and use it would be nice.

Carlos: Sure, kind of updating that almost to like a web type interface which is a little more intuitive. We’ve have all those buttons everywhere and you’re like what kind of I’m looking at.

Andrew: Yeah, and they got some stuff that’s nice but few years back we would seek new people and say, “If you really want to see how to build a query on this? Let’s do this in Access. See this nice wizard, we draw all these tables together.” Oh look at that that great query. You know, the query works on but into Management Studio and running it as a query there, “Look it works.” Why can’t they do that there?

Steve: Very good point. And I think, I mean personally I love what you can do with Management Studio but I have definitely seen the challenge when you have someone who is first time ever using it. They sort of get dumped in and don’t know where to go. There is this huge hierarchy in the preview in the left side that you can expand. How do I get to other query? Can be a little the bit daunting in the beginning?

Andrew: Oh yeah, the wizards for setting up jobs was nice. Being able to go through and take a look at especially some of the new views and performance capabilities. These are all great stuff but I got new people that we would like to get engrained in this just to make their lives a little easier so they get addicted as well.

Steve: Alright, so what is the best piece of career advice that you have received?

Andrew: Early on someone told me, “Don’t get worked up about office politics.” I said, “Look, focus on doing a good job and the people you really want to work for will notice that and promote you and those who don’t care about the good work you do.”

Carlos: Good things will come to those who wait.

Andrew: Oh yeah, and if they don’t then you’re in the wrong place.

Carlos: Sure, yeah, and then think of all the time that you don’t have to spend worrying and talking about, “Oh, what are they going to do with.” You know, I don’t like this person.

Andrew: Exactly, yeah. Just don’t worry about it. Just go to your job.

Carlos: Very good! I like it.

Steve: You know, that’s one of the things that I like about on the consulting side is
(00:40:00) you’re there to do the job and not to be in the politics. I like being on that side of it.

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

Andrew: I know he gets ridiculed all the time especially on big bang but I really like Aqua Man. And his ability to breathe under water so that I could basically do scuba without any kind of limits. That would kind of eminent. Lots of people is cool too but this ability to breath underwater that’s my thing.

Steve: Hey, if you figure that out let me know. I’ll go with you.

Andrew: Yeah, but not around here. It’s too cold. We’re going to go south.

Carlos: I said that would be the last question but just one follow up. Where is your favorite place to scuba dive then?

Andrew: That’s going to be the Caribbean and basically anywhere, 7 mile off coast of Mexico, any of it. Just go down about 70 to 100 feet and forget the world exists.

Carlos: And you’re there. Wow! Andrew thanks so much for talking with us today. We really appreciate it.

Andrew: Oh, you bet guys, that’s a lot of fun.

Episode 107: BI for the Little Guy

BI for the little guyData analytics is all anyone seems to talk about anymore.  We’ve even caught the bug at SQL Data Partners and have started working with data platform MVP César Oviedo from Costa Rica.  César has been helping us on some projects and we thought it was time we introduced him to everyone.  On the Spanish front, he has been very busy putting out YouTube videos and publishing content for BI LATAM (Latin America).  We invite him on the show to introduce himself and our conversation turns to the idea of BI for the little guy–smaller organizations that need to take advantage of analytics and how the ecosystem is changing to support them.

We are happy to introduce César to you and know you will enjoy hearing from in this episode and future episodes.

 Episode Quotes

“The problem that business intelligence has had in the past is the thinking… Ok, the BI is for Enterprise. The BI is for huge companies.”

“Again a small or medium or huge company needs an architecture behind to support all your solution.”

“Another piece that is rally fancy today is data science… Everybody is trying to understand and talk about data science.”

“I think the days of the DBA who’s only responsibility is backups have come and gone.”

Listen to Learn

00:30 A little background on César
02:00 General view of today’s topic – Business Intelligence
02:52 Companero Shoutouts
03:59 Announcements about the Companero Conference
06:44 Microsoft released updated drivers for PHP to connect to new SQL Server
08:41 SQL Server 2017 – High availability support for SSRS
10:46 Why should small and medium organizations care about business intelligence?
14:14 What are the available options that you can take advantage of some business intelligence?
17:25 Tips or suggestions on PowerBI
21:12 Other things to look at when implemnting business intelligence: data warehouse, data lake etc.
23:51 About analytics and data science, and the pendulum swing of job roles
27:00 SQL Family questions

YouTube: https://www.youtube.com/bilatam
Facebook: https://www.facebook.com/BusinessIntelligenceLatam/
LinkedIn: https://www.linkedin.com/in/cesaroviedo/

About César Oviedo

César OviedoCésar Oviedo is a Data Platform MVP with more than 15 years of experience working in multidisciplinary projects running roles such as Scrum Master, Project Manager, BI Developer and DB Administrator. With experience leading teams and projects with more than 30 members. Currently focused on Data Science and Big Data platforms. Community Manager at “Business Intelligence LATAM” (BI PASS Chapter Leader for Costa Rica and PowerBI User Community), international speaker and trainer for Data and Cloud technologies.

Episode 106: Temporal Tables

User: What was that record before it was updated?
Me: I don’t know.  Maybe I could restore a backup?
User: Really? You have to do all that? It is just one record.
Me: (under my breath) Yes, a record YOU updated.

If you have ever had a conversation like this you know how difficult it can be to implement auditing of records as they change.  With temporal tables, a new feature, we have the ability to track point in time information about a record without the huge expense of setting up auditing and tracking.  While you won’t want to use this on every table, when there are tables with sensitive data you want to audit from time to time, this feature will come in handy.  We are happy to have Randolph back on the program with us to talk about this new feature.

Episode Quotes

“That ability to kind of see what was there before it was updated, that seems to be the focus of temporal tables.”

“It’s all effective dated, so every row on a temporal table has to have a start and an end timestamp.”

“You don’t know how much space it’s going to use until you start doing some actual real life testing… That’s the biggest limitation for me.”

“When you create a temporal table the history table only takes the clustering index, so mainly the primary and the structure of the table.”

Listen to Learn

03:28 Temporal Tables
05:44 Auditing relative to user tracking
08:20 What are other reasons that people putting in temporal tables?
09:49 Start time and end time parameters for temporal tables
11:39 Using system_time in querying
12:50 Recovering data
13:35 Flushing the history table
14:49 Temporal Table in Management Studio
16:17 History table and clustered index
18:01 Modifying/Altering Temporal Tables
21:17 Data consistency check dealing with corruption
22:44 Scenarios where SYSTEM_VERSIONING = ON
27:50 Other limitations of temporal table
28:39 Is system time applicable when querying joined temporal tables?
31:06 When should you use temporal tables?
32:23 Tips on current feeding side of temporal tables
43:52 SQL Family questions

About Randolph West

IndexesRandolph West solves technology problems with a focus on SQL Server and C#. He is a Microsoft Data Platform MVP who has worked with SQL Server since the late 1990s. When not consulting, he can be seen acting on the stage and screen or doing voices for independent video games.

Transcription: Temporal Tables

Carlos: Ok. Well, Randolph, welcome again to our program.

Randolph: Thank you for having me. I’m surprised I survived the last round but this is going to be interesting.

Carlos: Yes, we actually split that into two. We had you on for a double header and so it’s been about 30 episodes and so we wanted to have you back. Of course we’ve been planning with you for the upcoming conference. The Companero Conference and so we’re excited to have you speak there.

Randolph: I’m excited to speak as well. It will be very cool because the cloud is all very rara shiny but some reasons why you shouldn’t just jump into that.

Carlos: Sure. It’s interesting you bring that up, so I just happen to be reading today, so Microsoft used to be $4 billion, they said $15 billion last year in the cloud.

Steve: So we will be covering how to appropriately take advantage of that $15 billion.

Randolph: That is exactly right.

Carlos: Being milked for a big percentage of it.

Randolph: Well, some of that budget is going towards trying to convince people to go across to the clouds so it’s an interesting time.

Carlos: That’s true. But today ultimately our topic is temporal tables.

Randolph: Temporal tables, yup, one of my favorite features of 2016 and upcoming 2017.

Carlos: Right, and I think ultimately or what we’re kind of getting at here is they try to give us some additional ways to do auditing, right? They tried with some like change data capture and maybe even some other components. I think we all have a lot of the home grown components for audit but that ability to kind of see what was there before it was updated and whatnot. That seems to be the focus of temporal tables. Would you agree with that?

Randolph: I would agree up to a point because temporal tables do not keep track of the user that performed the change.

Carlos: Got you. Ok, very good. So that is kind of the distinction when we talk about the word audit, that the person itself was not captured only the value.

Randolph: Yeah, so temporal tables I’ll quote kind of what Microsoft is saying is that it is for auditing in forensics but you don’t know who the user is that made the change so you will still have to keep track of that. But it also helps, and this is where I really like it for, data that is accidentally change or accidentally deleted. Plus it also provides a point in time analysis of what the data looked like in the past, so you could say, “Show me what the table looked like at midnight three weeks ago”, and then I can see what changed during that time using trend analysis or some type of complex query and we can analyze what’s happening over time using the data that’s in the history table compared against the data that is in the current table.

Steve: Yup, and you know that’s a real important feature there I think would be on the auditing side of it because so often you’ll get a report that shows like the last 30 days sales or something like that and then once a couple of days have passed it may have change drastically and oftentimes you don’t have a good ways of seeing what that looked like last week, or the week before for comparison and temporal table seem like a great way to be able to do that.

Randolph: That’s why it’s my favorite feature of 2016 because now exactly that you can back in time and say, “Hey, this record changed.” And we can see exactly when it changed and if you are keeping track of your logins and things like that this is where the auditing comes in but also with the tracking changes in a more sensible way. There are obviously limitations to how this works because you’re eventually going to run out of space but we can get into that if you like.

Carlos: Do you think, I guess, are those features going to continue to be separate do you think and I guess we’re kind of prognostic getting a little bit here, right? Does the user going to make it to the temporal table feature or is that going to be remain outside of what this feature set is.

Randolph: I think if they want to pursue an auditing feature then they do need to include the user at some stage. Saying that though there are a lot of systems that don’t use system IDs for tracking users. They have their own. For example, when you log-in into a web application your user account may just be a table entry inside the user table so a row and a table. There is no necessarily way a good way of checking which user that is from the temporal table point of view because it’s not a system user that accessing it because there is only one system user if you like. They probably will bring it in eventually. I don’t know. I’m no using any MVP magic here. I don’t know. But they probably will bring it in but at the same time you should be checking that anyway if you have your own table of users that you’re using to check logins. I think you can combine the two and see who’s doing.

Carlos: Well, that’s an interesting point, I guess one that I haven’t considered so having not use temporal tables before. But, yeah, that idea of let’s just account has access to the application but then the application talks to the database with a single account. That architecture, that should change a bit to take advantage of some of that, right?

Randolph: Well, I don’t think you have to change the architecture. I think you just have to be aware that the users accessing the data are internal to the database or to the SQL instance and in that respect you would have to keep track of them separately. Your architecture must have to change but I don’t see temporal tables’ architecture changing if they do add system user tracking at some stage.

Carlos: What are other reasons that people putting in temporal tables?

Randolph: The big reason for me is history, keeping track of history. So one of the systems I built awhile ago was to keep track of all records changes that happen in the system. So whenever a row is deleted or updated, I keep a copy of what it’s used to look like in an auditing table and then if I ever needed to go back into the history I would have to go and query with horrific XML queries to try and figure out exactly what is going on. Because for example if you’re keeping track of all the history for all the tables, you can’t just go and create a beautiful table structure on that, you have to use XML. You’d have to shred the XML to go and read that, and that would either require an index that is 5x the size of your data or a very slow query as it goes read everything. So what this does is in a way that you would create a trigger on a table to keep track of the old row and then store it somewhere. This does exactly the same thing out of the cover. So when you create a temporal table and then you create the history table that is linked to that whenever a row is deleted or updated in the current table the old row will be kept in a history table just like you would do with a normal trigger. The functionality out of the cover is exactly the same.

Carlos: The advantage is it’s going to give you a little bit more detail. It’s going to add that begin and start date or the date parameter too. It’s going to take care of all that.

Randolph: It’s all effective dated, so every row on a temporal table has to have a start and an end timestamp. It’s datetime2, the full 8 bytes, so datetime2 goes down to 100 nanoseconds. That is the smallest granularity and that’s the granularity you have to use for system date times for the temporal tables. So there is a start time and an end time. When you create the table the first time your default value for your create time will be system date time, so current date time if you like. And then the end time will be some time in the future that you’ll never see in real life so the year 9999 December 31st for example. And then if you see a row in the history table that has an end time and then you do not find that row in your current table that means it was deleted. Otherwise if the row exists and the current table it was never deleted, it was just changed. So a little bit of trickiness to understand how things work but it’s all date time specific.

Steve: So then take the example of someone who is not quite on SQL Server 2016 yet and they want to use something like this. They could just create the same thing with their own trigger and with the similar naming convention on the start time and end time and all of that. However, where we get challenging it seems like we haven’t talked about querying it would be when you’re trying to query that data.

Randolph: Well yeah, so the query uses a different type of querying than we’re used to. There is a keyword that you have to use, system_time, so you have to use the keyword system_time in your query so, SELECT whatever from the table FOR SYSTEM_TIME, and then you can do an is at a certain timestamp. You could use a BETWEEN. There is a number of different things that you can use that will automatically figure out the timestamps are between the current table and the history table.

Steve: And to me that seems where some of the real value is and that even though it just triggers under the scenes and all that the value is the way that SQL Server has built that in to be able to query for specific point in time or range or set of values.

Randolph: Yeah, and that’s a value for me as well so I can go and see exactly what the data looked like at that specific point in time down to the 100 nanoseconds. So if we find out that somebody deleted a whole bunch of rows we can go back to the exact point in time before it happened. We don’t have to go and restore an entire database or a file group restore for doing Enterprise edition. But we don’t have to do that anymore. We can now go into the history table, recover all those rows, then flush the history table because otherwise you’re going to have 3x the data that you originally had, and I’ll explain that as well. So if you’re recovering data you go into the history table, you get those rows out, you flush the history table, and then you input the rows back in to the current table then you’ve got your consistent data before the delete happened and you reconnect the history table to the current table again.

Steve: Ok, so you use the term flush the history table there. What exactly does that do?

Randolph: Well, the history table will grow and grow and grow until you are run out of disk space. That’s by design so there is no aging out of the data. You have to do that yourself which is good because if you’re auditing you decide how long you want to keep your data for. Same that though the data is stored in a compressed formats. So they use page level compression on the history table. And because since 2016 Service Pack 1 we don’t have to worry about compression because it’s part of the product in Standard Edition as well so we can have page compression for the history table so it will use less space than it would normally which is one of the advantages of the feature on 2016.

Carlos: Now, remind me, because I guess one of the things that we’ve kind of implicitly said here is that we have a table, now we want to add this history to it. We’re going to use an ALTER Table that’s going to change and we’re going to say, “Hey, this is now the temporal table.” It’s going to create this history table. Now, we
mentioned querying it but from seeing that history table like in Management Studio, is that visible to me or is that still an object that I’m managing or is that kind of just behind the scenes.

Randolph: You can see a temporal table in Management Studio as long as you obviously have the right permissions to view them but it will show up as what’s called a system versioned temporal table because the system is versioning the table. I mean, this is Microsoft’s naming conventions between O and love. What you’ll see in Management Studio is you’ll have the current table. Let’s say for example, dbo.Account, and then in brackets behind it will say system versioned, and then if you expand that out it will show you the history table under that first. And then it will show you the columns keys constraints and all of that stuff that you’re used to. So the history table, you can name it yourself, and put it in its own schema which is what I recommend. Let’s say you have a file group that is on slower storage or cheaper storage whatever and then you put your schema attached to that file group and then you put the history stuff inside that schema then you can manage the history in a different file group which might cost you less money. It will be a little bit slower but it’s history data so you would not have it in the first place.

Carlos: You’re not expecting super fast performance.

Randolph: Exactly. And because it’s compressed you might actually offset some of that performance problem by having it compressed and then you’ll have the list of columns that would look exactly the same as they would in the current table except you cannot obviously have a clustered index. I’m going to change that statement. You can have a clustered index but you cannot have a primary key in the history table because the primary key is in the current table. So the history table does have a clustered index it just doesn’t have a primary key because the primary key is in the current table. Otherwise, the structure is identical.

Steve: And I think the key behind that is that when you’re inserting into that history table if you had the primary key on the same thing as the original table it wouldn’t allow you to put those multiple rows in there.

Randolph: Correct, so what’s happening in the history table is that you have a clustered index that is just not unique because it’s in the pinned only way of writing to the history table.

Steve: So then if we’ve got that history table and I want to clear out some old history in there can I just go in and delete data out of that table?

Randolph: You have to break the link between the current and the history table to be able to do that which isn’t complicated at all. There is just a command and if your editor will bear with me. It’s probably going to be Carlos, right?

Carlos: No, Julien.

Randolph: Hi Julien! Bear with me here.

Steve: ALTER TABLE table SET (SYSTEM_VERSIONING = OFF).

Randolph: Yes, that’s the one, Steve. So modifying temporal tables, you have to have administrative rights to be able to alter a table. You need the admin role to do that. So you cannot alter the objects until you have administrative rights so that’s the first thing. Anybody can read and write from the temporal table and write to the temporal table but only the administrator can modify it. And what you do there is: ALTER TABLE name of the table SET (SYSTEM_VERSIONING = OFF). Then you can do whatever you like inside the temporal table with the full understanding that your auditing trail is now broken.

Carlos: Sure so almost like a maintenance window type.

Randolph: Exactly. So this is exactly the same thing as changing your database from full login model to simple login model is the recovery model so now you have basically truncated or change your history so now you will have to recreate that connection once you’re done flashing the table or deleting rows from there that you need or changing column names or changing columns themselves, deleting columns, updating columns, whatever. Because you may change the structure of your current table and you want to have that change reflected in the history because unfortunately although you can do an ALTER TABLE which changes a column or drops a column on the current table that will take place on the history table. But if you have decided to keep the history of a certain column and you do an ALTER TABLE on the current it will drop that column from the history which you don’t necessarily want. Then you have to break the connection, go into the history table, SET that column to NULL, and then recreate the connection again once you drop the column from the current table. So that way you can keep history of what the column used to have in it but then the column does not exist anymore in the current table so you still got that history retained. Does that make sense or does it just confused you all?

Carlos: No, no.

Steve: I think it make sense.

Randolph: Ok, good. There is also another feature which I have never turned off. When you create the SYSTEM_VERSIONING on a table you can alter an existing table and create a temporal table out of it, which Carlos alluded to earlier, or you can create a new table from scratch which is a temporal table. And to change an existing table you have to ALTER the tables SET (SYSTEM_VERSIONING = ON) then you can assign it to history table name if you want to create something in your own schema that we spoke about. And then there is another feature called data consistency check = ON. The documentation says this should always be ON to ensure that the history table follows the correct structure format as the primary table. I’m not sure why you are going to turn that OFF. So I’m not sure why the feature is even presented as an option.

Carlos: Right, because you made a change to that table, data type change most likely or I guess you can add a column.

Randolph: But why have the history table if you’re not checking the data consistency? I’m not sure why that’s there but I’m just letting you know that it is there. It is required with the ALTER TABLE statement if you’re going to use SYSTEM_VERSIONING = ON but I’m not quite sure why it’s there.

Steve: So one thing and I haven’t use the data consistency check one way or the other but one thing I was reading about on a blog post was that it was there to help deal with corruption at some point which immediately I perked up a little bit and thought, “Well I need to understand this one.” But I haven’t had a chance to dive into that yet. Have you heard anything about using that around corruption?

Randolph: I have not but I would imagine that if you have a corrupt table, and this is all conjecture on my part. If you have a corrupt table and you have a history table that contains all the history, the history should probably be fine because it’s stored in different file group for example or different part of the file anyway. You would have access to that history without necessarily reading or writing whatever is in the current table using just standard queries. I believe that the work was a bug in one of the Cumulative Updates did fix a bug in data consistency check for certain tables, so I’m not sure if that’s what you’re referring to, Steve.

Steve: Yup, and I guess that’s something that I need to look into a little bit more.

Randolph: Because I do know that there was in Cumulative Update 1 there was a bug with data consistency check so maybe that’s what you’re referring to.

Steve: So let’s take an example here then. Let’s say we create a table and we have SYSTEM_VERSIONING = ON and we have the history table associated with that then we insert a single row into that table. Does that row then exist in the table as well as the history table at that point or does it wait to get to the history table until it ends up being changed?

Randolph: An INSERT will only affect the current table. The only time that the history table is involved is if you modify that data. So when you insert a new row into a table there is no reason for it to have changes at all so you’ll only see it in the current table and then when you modify it the timestamp will be kept track of and the original row with the start time that you created the row in and an end time of when it was changed will show up in the history table.

Steve: Ok, got it. So then if you’ve got a table then for instance it’s something like bank account transactions which is probably the type of table that’s not going to change frequently. It will have a lot of inserts but very rarely I would hope our transactions in the past being modified.

Randolph: Well, I’m going to stop here and say that’s a bad accounting practice if you’re changing because you should be posting changes in an append only model with accounting.

Steve: Sure, exactly. But if you wanted to track to see if anything was actually changing it, you could turn this on, go back and look and see what point in time things were changing if there were. But then I was going to sort of flip it around to a different example of let’s say you have a table that’s like your users table and that user has username, and a last login date where every time that user logs-in it will update the last login date. That would be one that if temporal tables were turned on every time the user log-in it would be inserting another row into that history table, and it could grow very quickly over time.

Randolph: It could, yes. And that’s why the page compression is going to be very helpful from that point of view because that data should compress very well. But saying that it may not be a good choice for a temporal table although it might be a good choice if that’s what you want to do is audit and see how often people are logging in and things like that.

Steve: Right. So then in the case that you have a very narrow table, maybe there’s just username and last login date, something like that, primary key, whatever you need there too as well but if that’s probably going to work better in a history table than if you’ve got a user table that might have a 150 user attributes to find in different columns. Again, a poorly defined table there because when it logs to the history table, of course it has login the entire contents of the row. Is that correct?

Steve: Correct, so wide tables are going to be slower because it’s exactly the same effect as if you were taking from that and using a trigger to write it to a different table. It’s still going to be a wide table, possibly wide columns as well so it’s going to take awhile to write and you are going to have a performance impact on your current table because it has to commit the row to the history table first obviously. Let me just confirm that. Actually I do a blog series in November 2015 so it’s fairly fresh in my mind. I just want to double check here. The row will be modified in the current table and then it will be modified in the history table. It’s part of the same transaction though.

Steve: Alright, so are there other limitations that people should be aware if they are going to try this out. The big thing for me is running out of space because it’s very easy if you’re not use to what kind of data or what the…

Steve: I think we’re relying on Julien a lot on this one, Carlos.

Randolph: Well, how many times did we pause the last one? That was pretty crazy.

Steve: Quite a few.

Randolph: Can you repeat the question, Steve.

Steve: Ok, what I was just going was other limitations?

Randolph: The big one for me is the disk space because you’re going to run out of space quickly. The other problem is if you are using this in a staging environment and you have more than one table with a foreign key relationship and both of them are temporal tables. You’re going to have a hard time trying to pin down history queries to say what the data looked like at this specific point in time because of those foreign key relationships so it gets a little bit hairy in that respect. You don’t necessarily want to have every table in your database a temporal table because it’s going to make your queries complicated and it’s also going to slow you down because it’s going to be querying the history over time.

Steve: Ok, so in the SELECT statement then when we were querying for temporal data where say FOR system time as of and specific date time too. Now, if you do that in SELECT FROM one table INNER JOIN another temporal table, so we have two of them there. Is that system time parameter going to apply to both of the tables and the join?

Randolph: What a fantastic question. I’m going to say that that time is specific to each table. What I would do is I would run an experiment and then find out because I don’t know.

Steve: Ok, it’s one of those new features where there is a lot of experimentation to do there I think.

Randolph: Where the challenge comes in is you may have history on a lookup table for example. So the lookup table has a whole bunch of stuff that might change once in a while and you don’t necessarily look at the history when you’re joining that to something else, but you might have to do that and it gets a little but hairy. It’s not difficult, it’s just time consuming for the engine to go back into the history table as well, and then because when you do a temporal query. I’m going to call it a temporal query and not a history query. When you do a temporal query against your table it’s going to combine the results from your history and your current. If you’re asking for everything so it’s going to do basically UNION ALL or a UNION because the data shouldn’t be the same. When you combine the two, if you’re going to do a point in time query it’s going to go and look in the history to see if anything is there so it’s always looking in that history table. So if that is on slow performing hardware then it’s going to take longer, that’s kind of where I’m getting to.

Steve: Ok, that makes sense.

Randolph: I was just going to say when should you use temporal tables? You should use temporal tables on data that needs to be tracked and that you were tracking in an interesting way before. For example, my system that was keeping track of the XML data version of history. I needed to check that for auditing reasons. That is a good reason to use temporal tables. But I wouldn’t just go turning it on everywhere because eventually you’re going to forget and eventually you’re going to run out of disk space somewhere.

Steve: Yes, and hopefully you know about that before you run out. Hopefully you have alerting in place.

Randolph: Right, you should have alerting in space but if you’re playing with it on a test environment or something and you forget about it. You may need a massive amount of space to handle that data. Some people might have to retain 7 or 10 years of history. And then you may have to age in the manual process anyway. So you don’t know how much space it’s going to use until you start doing some actual real life testing. That’s the biggest thing that I have to say. That’s the biggest limitation for me. I think I said that a hundred times.

Steve: How about any other tips on sort of current feeding side of temporal tables?

Randolph: Well, back to the disk space that’s being used. If you are creating a lot of churn and you’re temporal table is growing excessively you may want to think about going into the history, breaking the connection and deleting or aging out data that is older than a certain number of months or years. And it’s got indexes on it so you might want to do some index maintenance as well. You can apply indexes to columns inside the history table manually if you like by the way which is pretty cool. But you cannot change the clustering key so you may want to do some index maintenance from time to time on the history tables.

Steve: Ok, so if you have a history table associated with your temporal table and you add indexes, non-clustered indexes to that for instance, do they automatically end up in the history table or do you need to go and add those manually if they are needed?

Randolph: When you create a temporal table the history table only takes the clustering index, so mainly the primary and the structure of the table. They should be no reason for it to take the indexes across because that’s additional maintenance that’s not necessary because you’re not necessarily be querying that table a lot. So it will only copy the structures, so the columns themselves and whatever your primary key is will become a clustered index on the other side. There will not be any indexes that get copied across. If you do want to query that table frequently you would have to add your own indexes.

Steve: Right, so in that example if we are querying for a certain point in time and filtering on a column that needed an index, if that history table got large it could be quite slow if we don’t add that index themselves.

Randolph: Yes, and there is a whole can of worms here with filtered indexes which don’t always work the way you expect them to which we won’t get into. I think we covered that in Episode 81.

Steve: Yeah, great, great. So any other current feeding tips to add at this point?

Randolph: When you make a change to your current table the change if it is structural, so you’re deleting a column or modifying a column may affect the temporal table as well, the history table. And the reason for that is because it wants to keep a consistent check between the two. That’s the data consistency check that’s happening. So if there is data in the history table that doesn’t satisfy the requirements of the new column, for example you may have just deleted everything from the current table and then alter the column. If it is attached to the history table still and you do and alter column and it then tries to change the data type, if the history table contains a data type that is incompatible with the new change it will fail.

Steve: Oh, so you either purge the history table or know that column in the history table perhaps in order to be able to modify it in the original table.

Randolph: For me personally I would break that connection completely, rename that history table so that it’s archived and then create a new history table going forward if the data types are that significantly different because effectively it’s a new table. If you do want to go and query that history you still can by querying the history table directly in the archived version. But if you’re going to be making structural changes to your current table, I would break the connection, rename the history table and just create a new history table going forward. The other thing that I wanted to mention, we touched on very briefly earlier, is if somebody deletes, let’s say runs a delete statement against the current table without a WHERE clause so every single row gets deleted. What you will find is that the original version of that row for every single row will find its way into the history table. So if you’ve got 10 rows, that’s fine. If you’ve got 10 million rows, you’re going to have 10 million rows in your history table as well. So if you want to recover that data you can just copy that over from the history table into the current table but I do recommend breaking the connection first because any modifications you make to any rows after that will also be written back to the history table and you still got millions or 10 million changes in that history table. So either flush those rows out first or rename the table or do something but don’t try and go and copy those 10 million rows back into the current table from the history table without doing some maintenance on the history table because otherwise you’re still going to have those 10 million records there waiting in the history forever and ever.

Steve: Yup, so it really seems like it just keeps coming back to the history table can get really big depending on what you do and it’s maybe slow if you don’t index, it may run you out of disk space. I mean there may be any number of issues associated with that history table becoming gigantic.

Randolph: Well, it’s a usual story, how much are you querying it? Does that mean we’re going to be using a buffer pool for that table? All of those things that go along with that because even though it is compressed on disk whenever you read it into memory it’s going to be uncompressed.

Steve: So another question around this. Let’s say you’re querying your original table and you’re not using the system_time for system time parameter, you’re just doing a direct query of your source table and your temporal table without the history. Does that work exactly the same as it did if you did not have a temporal table?

Randolph: That’s next in question because it brings up another thing that I didn’t mention. Yes, it works exactly the same as if nothing had changed. So when you create a new temporal table you can create the start and end time using the datetime2 data type and you can mark those columns as hidden. There is another feature in 2016 for hidden columns and this is specifically for supporting this feature. So you hide those columns so that any queries that do, and you shouldn’t do those what people do a SELECT *. Those columns will not be visible in a SELECT *. They will not be visible in any existing applications that don’t know that they are there because the system is managing them. It’s as if you’ve created a column with a default value because that’s effectively what it is.

Steve: But those have to be specifically marked as hidden for them to not show up in the SELECT *.

Randolph: Correct. But if you do that and you can do that very easily when you’re doing the CREATE or the ALTER table to make it a temporal table, you can mark those columns as hidden. So it has no effect on any current applications or any queries that you might be using currently. It will work exactly the same way as it did before. So unless you’re using that system_time query keyword it’s going to be carry on reading from the current table as if nothing else has happened.

Steve: Ok, great.

Carlos: Good deal, lots of good stuff today. As we’re going to finish up here, I know we’ve done SQL Family before, but can we go ahead and hit it again?

Randolph: We can do it again because my answer may have changed.

Steve: So Randolph how did you first get started using SQL Server?

Randolph: Accidentally, as with everybody I know. Nobody I know has ever wanted to be a DBA and then they realize they’re being silly because it’s actually what they wanted to do all along. I used Access, I used MySQL, I used Ingres. I used SQL Server when I was working with PeopleSoft back in the 90’s. I didn’t know what it was. It’s just one of those things, oh this just keeps your data, it’s in an Excel
spreadsheet basically. Lots and lots of Excel spreadsheets. And then I started reading about corruption because that happened to two of the clients. The databases either got dropped or corrupted in some way and I start of getting interested in that way. And basically it’s just, it was there, it was there, it was there and let me have a look at it and then I started loving it. Access was really my entry point into SQL Server because Access was so terrible.

Steve: You know, that’s so interesting because at first glance if you don’t know databases very well, Access looks pretty darn cool.

Randolph: Access has some very nice features for front end development for a data backend. Well, it did. I mean it is 2017 now.

Steve: Sure, but back in time it did.

Carlos: Well, they have it all in one application too, right? Two things.

Randolph: Exactly, and it was easier to use in FoxPro. It was cheaper than FoxPro and you didn’t have to be a programmer to write a query because it had that cool query designer that you could do. I believe SQL Server has the same thing but I never used it. It was just really neat to be able to picture your data with a graphical interface.

Steve: I wonder if Microsoft realized that access is really a stepping stone to get to SQL Server from… I’m sure they do.

Randolph: It’s a gateway drug.

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

Randolph: Last time I think I said that I want to do a TempDB for every database. I still kind of think I want that but I’m not sure if that’s the one thing that I want to change now because firstly Azure SQL Database has come a long way and in a very short time so if I would change one thing. It would be making Azure Database cheaper. Because I think there is a lot of potential for people to use for single application usage. For example using an Azure Database to replace what people use MySQL for now and that would be web applications and things like that. If it were a little bit more cost effective with all the feature set that they have because everything that’s in SQL Server 2016 is in Azure SQL Database including temporal tables on the Premium Edition and in-memory OLTP and all those things. So I think temporal tables are actually supported on the Basic Version for what it’s worth.

Carlos: I think you’re right. It’s interesting because you look at the, what’s the edition that only allows you to go 10GB.

Randolph: That’s Express.

Carlos: Express, thank you.

Randolph: I have a funny story about Express off the topic. A customer of mine had a customer because they are a vendor. A customer was running their product in Express Edition. They had 20 databases and of course Express has auto close enabled by default so a typical diagnostic session which takes 5 minutes normally run for 2 hours because it had auto close on every single database. So every time a query run there we close the connection then open it again for the next query. It was fantastic. So what were you are going to say about Express Edition?

Carlos: Well, I guess I’ll say because it’s free, right? At least my impression is of making like Service Pack 1 some of those analytic features available in the Express Edition. That is a direct response to MySQL ecosystem, so I think to your point of like, “Well, hey that’s free.” Now there is compute, right, so I guess there should be some cost there. But a database under 10GB should be pretty close to free, right?

Randolph: Agreed, but…

Carlos: That might be the change there. But I don’t think a lot of people are going to take that into consideration because even if they had to pay a penny it’s still I had to pay something and this is “free”.

Randolph: Exactly. The other cost with Express Edition which I love is the 1GB limit of memory so non sense.

Carlos: Oh right, I forgot about that.

Randolph: MySQL doesn’t have that limit obviously but it is a strong consideration for not using Express Edition. You’ve only got 1GB of RAM.

Carlos: Is that still a case in 2016?

Randolph: Yes.

Carlos: Ok.

Randolph: 4 CPU cores or one socket which are very small, 1GB of RAM and 10GB database size.

Carlos: Got you.

Randolph: I would like to see a better costed version of Azure SQL Database for the Standard level if you would like. And get rid of Basic because I don’t see the point of Basic. 5 DTUs isn’t enough for anybody. I’m sure I just coined a new phrase. And then just have Standard and Premium, and have the Standard do more for less basically because I think the adoption would be higher if they did that.

Carlos: Well, they’ve been coming down. I know the sizes have been getting larger and so, yeah, and so maybe another year or so we’ll get there.

Randolph: Maybe by the time this podcast goes up.

Steve: You’ll never know. What’s the best piece of career advice that you’ve received?

Randolph: I’ve been working for so long I probably forgotten most of what I’ve heard but the one piece of advice I give to people because I do mentoring as well is to say, “Be honest”. So if you screw up tell somebody that you screwed up. It’s much easier to handle a problem if you’re completely honest and say, “This is what happened, this is how I created this problem.” And then it’s much easier to fix than lying and say, “Oh, I don’t know how that happened”, or “I did not kick the power supply out of the server”, or “No, I did not accidentally bumped this hard drive off the disk”, you know stuff like that. Just be honest and it’s just easier for people to handle the fallout. And if you’re honest it shows that you are human and there is nothing wrong with doing something wrong. I make mistakes all the time the difference is I own up to them. I’m not like, I’m not going to say it. I’m going to get political. I’m going to stop here.

Carlos: But it’s interesting on that one because I’ve heard people say that they are sometimes afraid to be honest because the environment they work in is too aggressive or management doesn’t have your back on anything.

Randolph: Well, then I wouldn’t be working in that environment. And being honest and saying, “Yes, I did this”, gets you fired. Well, then any place is better than working in an environment like that. Toxic work places are very bad for your health and you will die young.

Steve: Yup. And I think what I was going to say around that is that if you’re on that toxic work environment, and I’ve been there in the past, honesty is even better at that point. I mean, it’s always good but it’s even more important at that point because you’ll know exactly where you’ll stand.

Randolph: It’s refreshing. It might not be the outcome you expected but it is refreshing for everybody to say, “Well, this person is telling the truth and they’re not going to compromise whatever for the sake of looking good.”

Steve: Yup, very good advice.

Carlos: If you could have one superhero power, what would it be and why do you want it?

Randolph: Last time I said, I have a superpower wherever I walk into a server room a machine will either crash or not, that’s a superhero power I didn’t want. But there’s been a whole lot of superhero movies recently, a whole lot of stuff that I don’t enjoy because everybody is destroying everything. The one superhero I do like is Batman because he is not a superhero. He is just a guy. He does have compromised morals and he’s very very wealthy. I don’t want to be wealthy like him because that also corrupts you. But I do like the idea of just being a decent person. I’m not necessarily killing bad guys but if you see something going wrong, speak up say something. You don’t have to be a superhero to be Batman. So you don’t have to be a superhero to say, “That’s wrong, this is not cool and do something about it.”

Carlos: There you go.

Steve: Yes. I like that.

Carlos: Thanks so much for being on the program today.

Randolph: Thanks for having me and I’m sure this would have been a lot longer without the edits. Thank you for making me sound good.

Carlos: No, it’s great to have you on again and chat with you. And yeah, we look forward to seeing you in October.

Randolph: Sounds good. I’m looking forward to the Companero Conference in Virginia, in Norfolk, on the 4th and 5th of October, 2017. And it’s a single track so you just have to stay in the same room and listen to people explain how awesome SQL
server and the cloud is.

Steve: We’ll see you there.

Carlos: That’s right, you took the words out of my mouth.