Episode 06 Build Your Personal Brand

1400Steve Jones, the editor from SQL Server central and I talk about the need to build your own brand and we share a few ideas on how to do that.  We talk about Steve’s brand and how he built that over time and some options available to allow you the most opportunities possible.  The New York Times said in 2007 Nike spent 678 Million Dollars on advertising–A company almost everyone already knows.  What are going to do help establish your brand?  This episode is a must have for those looking to kick it up a notch on the opportunity meter.

Show Notes

Steve on Twitter
Red Gate SQL Prompt
The Modern Resume
SQL Server Central

Transcription: Build Your Personal Brand

Carlos Chacon: Welcome to the SQL Data Partners podcast. My name is Carlos L. Chacon, your host. This is episode six.

Today, I’m super excited to have someone on the show that you probably already know. Steve Jones from SQLServerCentral.com is with us today. Steve is the Editor-in-Chief of SQLServerCentral.com. He started working with SQL Server in 1991, which I’m pretty sure wasn’t called SQL Server at that time.

Today, we’re going to be talking about branding. Steve had a great brand, and I’m super excited that he’s been willing to share a little bit of his time, and thoughts about building a brand. Ultimately, the goal here, or the idea is that you can take some of these branding ideas, put them to use in your own environment, and hopefully, additional opportunities will come your way.

If you like today’s episode, we invite you to give us a rating on iTunes. Give us some of that feedback, and let us know what we’re doing. If there’s something you’d like to hear, you can hit me up at [email protected] or of course I’m on Twitter @Carloslchacon.

If you like these episodes, we invite you to subscribe to our iTunes podcast. We are also now on Stitcher, we’re excited about that and as always, welcome to the show.

Children: SQL Data Partners.[music]

Steve Jones: Thank you, thank you, I’m glad to be here. I still run SQL Server Central. That’s my primary day job. That’s a lot of what I do on a day-to-day basis. I’m always writing and producing the content there. You’ll certainly see me there.I work for Red Gate software as well. They employ me to run SQL Server Central as well as to be an evangelist, which means that they send me around to a variety of events, starting to be all around the world.

It’s mostly in the US but I’m doing more in Europe. I’m at a number of conferences every year and a bunch of SQL Saturdays. As many as I can fit in with busy family life and all the other responsibilities I have at work.

Carlos: Sure, there’s no question that you keep a full schedule there. One of the things we wanted to talk about today was, standing out from the pack, right. Building your own brand.We aren’t necessarily going to talk about getting famous but we do want to talk about some ways that data professionals can present themselves in a little better light with the hope of having some more opportunities come their way.

The reason I wanted to have you on the show, Steve, is because of your own individual brand. You’ve got a great brand and if folks are just starting out and haven’t met you, they’d probably be surprised when they first meet you that one, you’ll be in a Hawaiian shirt and two is that you’ll walk up to them, you’ll extend your hand, and ask them a few questions.

Steve Jones: Yeah, exactly.

Carlos: I think that’s a great brand and I think you might have some ideas for our listeners that they can try to apply to their own brand.

Steve: Absolutely. What you just said here, just introducing yourself, that’s networking. That’s a great way to just meet somebody else and get to know another person in the community. Start to build a little bit of a bond. Maybe make a friend. Maybe make a contact of some sort. That’s one of the things that has helped me over the years.As a young man in high school and college, I was really shy, didn’t know a lot of people. There’s been plenty of times I’ve gone to events for schools, or yearly in my career where I didn’t introduce myself, I kind of just sat in the back, and found that I wasn’t really growing my career as much. There’s always so much you can do, in terms of being just a very smart young man or young woman today.

It’s great to be good at your job, to have a lot of technical skills, but really a lot of hiring, a lot of decisions for promotions, reviews, for bonuses. All these things, they come down to personal context and people liking you.

Just being friendly is a great way to kind of grow your brand a little bit and meet other people. Just walk up to somebody, “How you doing? My name is Steve. What do you do here? Why are you here today? What’s your job? What are you interested in?” Just a couple questions, just like you might if you’re in a party or something else, some other event there. That’s probably the best thing I think you could do for your career these days.

Carlos: I think one of the big misconceptions about networking — particularly maybe five, seven years ago — was that you wanted to do that to advance your own purposes. As you just mentioned, ultimately, it is about extending your network, and you need to find out about what other people are doing. Asking those questions from people just to get a feel. Start that conversation going, and then, potentially down the road, something may happen that may be of your benefit, but it doesn’t necessarily need to be the goal.

Steve: Right. Certainly, you might help somebody else. We have a lot of people in the soup community that are very open, friendly, and willing to help others. Maybe you’ll meet somebody you can help. Or maybe you’ll meet somebody who just can bounce an idea off later. Ask you questions of.I meet people all the time that are good at SSIS, reporting services or DAX, or things that I’m not very good at. When I have questions, there is somebody I can just call on — that I’ve had a conversation with — and say, “Hey, can I ask you a question?”

Carlos: When we talked a little bit about events and we talked about getting to know people. Another thing that I’d like to encourage people to do is to get to know the vendors a little bit more. This is kind of with the eye towards adding that into your network, particularly the product folks, “Hey, what is it that you do and what problems are you trying to solve?”It may be, for example, a Dex vendor. I don’t know if there’s one out there like that. You may have nothing to do, that’s not even on your radar, but the next person that you talk to might have something. To be able to say, “Hey, you know what? I just talked to so and so at this vendor, they might be able to help.”

You’ve provided value to that person and the likelihood of them remembering who you are skyrockets.

Steve: Exactly. Somebody at work might ask you, “Hey, we need to do this thing with Dex. Do you know anything about it?” You go, “You know, I met somebody a few months ago. I’ll ask them. I’ll call them back.” Certainly, getting to know vendors is a good idea because some of us go to work for vendors. Some of us leave vendors and go somewhere else.There might be an opportunity for you to go to work there. Like you said, just answer a question for somebody else or at least have an idea of what could work.

Carlos: Right. I think the idea there tied in with the brand is that you’re creating a brand of being able to provide value, of being knowledgeable about different things. Having that network that people can at least run ideas by you and that they’ll know, “We’re kind of stumped with something. Let’s go ask Steve, see what he thinks.” Right?

Steve: Exactly.

Carlos: As we go and we do this, we’re networking. Individual events sometimes is difficult to get to, can be cost prohibitive. Are there other ways to grow that network? Social media, obviously, is one that has exploded here lately and allows us to scale our networking opportunities.

Steve: Absolutely. Yeah, social media’s a great way. There may be groups in your local area. I mean, there’s obviously pass groups, SQL Server user groups in your area, but there are often industry groups as well. For example, in Denver when I first moved here, we had a group called First Tuesday. It was basically a happy hour, restaurant or bar, on the first Tuesday of the month where IT professionals would get together.Everybody from CTO’s to DBA’s and Sys admins would have the chance to have a drink, just meet other people and talk. Certainly there’s those opportunities as well as more formal events that are industry specific. Social networking is a great way, it works the same as an in person event. I know it seems silly and seems like we’re not interacting with people when we’re not doing it face to face, but you could build some great relationships across distance by using Twitter, Facebook, Instant Messaging, even forum places like SQL Service Central or MSDN, the place where people ask questions and answer questions.

You can really build these strong bonds in the same way that we did it 100 years ago, by having pen pals, by writing letters back and forth. Except today we can do it in real time or near real time.

Carlos: One of the things we want to, I guess at least encourage folks, from my standpoint is that you can use social media for many things but we want to convey a professional air, if you will, when we’re trying to build our brand from a data professional perspective, right? I think there are certain social medias like Facebook, for example, I know a couple of people out there that are like, “If you’re going to invite me to your wedding or you think I might invite you to my wedding, then we can connect of Facebook.”Otherwise, we need to go to other mediums to interact in that professional way. Separating the lines, in a sense with, here’s my brand, here’s who I am in social media from a data professional perspective versus here’s my kids and my grandma at the family reunion.

Steve: Exactly. You certainly need to draw some separations. Denny Cherry is a friend of mine. He’s a well known consultant in the US who does a lot of SQL Server work. He is across all social media areas where he posts. Recently, he actually made that separation, where he set up a professional Facebook side for his business and for his career. Then he broke that with his personal Facebook group because he posts his opinions and different ideas on Facebook that aren’t work related.Certainly, you can cross the line, just like at work, when you’re actually in an office, there are some topics religion, politics, things that you don’t necessarily want to get too involved in at work because it’s not necessarily appropriate. You may offend somebody or you may get yourself too worked up and get upset. [laughs] You may say something inappropriate. I always encourage people to think about separating those things out.

For example, my Facebook is completely set to private for only my friends. I don’t share anything out there professionally. I typically don’t connect with professionals on Facebook. It’s typically for family and friends. There is Twitter, and my blog, and Linked In and other places where I do connect with other professionals.

I’m aware of what I post there really reflects on me as a DBA and a SQL Server professional. I don’t necessarily want to post things there or advocate for things that aren’t appropriate for my career.

Carlos: Sure, and ultimately again, we’re talking about that brand, and so you can be a political moderator if that’s what you want to do, but do that in that space.

Steve: Absolutely.

Carlos: If you’re going to create the social media space to build your brand as a data professional, then the topics and the related information needs to somewhat align with that. Now sure, you go to an event and you post pictures and stuff, that’s all, again, involved with that brand.

Steve: Exactly, we’re all conservative, or liberal, or independent, or something. We all know that but, when I’m trying to decide if you’re the person I call for an interview, or you’re the person I want to hire, I don’t really want to see that because it’s serves no real purpose. It’s as likely to offend me, or upset me, as it is to attract me to you.We want to present a positive, very professional image to potential employers. Recruiters, employers, HR people, hiring managers, somebody that’s going to look at our profile on social media we want them to see somebody that they want to hire, that’s going to fit in that position well.

If you want to be a political commentator, or you have a band, you’re a musician, or something, have a separate Twitter account, a separate Facebook. Just have a different place where you put that stuff.

Carlos: It can be overwhelming, particularly to see some of the other folks in the community who appear to be on social media 24/7…[laughter]

Steve: Yes indeed.

Carlos: They’re posting a lot of stuff out there. I know, I look at it, I’m like, “Wow.” Do we need to be on there all the time? What’s the balance? I’ve been meaning to ask some of these folks that host a lot, how they do that.

Steve: It’s certainly is, it’s amazing sometimes how many tweets, or Facebook posts I see from some people, it’s incredible. Certainly there are some tools that help you post in multiple places, or automate things that can maybe make it look like you’re more active than you are. Really, I look at social media just like I look at other social situations.At the office I may get up and go get a cup of coffee and I have the chance to chat with somebody in the hallway, or in the kitchen, and along the way, or I may catch somebody going in or out of a building and spend a couple of minutes there. That’s really how I treat social media, I try not to be too bogged down in it.

Part of my job as an evangelist is to keep track of it so I certainly do…we have some professional accounts for SQL server central and at Redgate, that are on social media. We monitor those a little more heavily.

For my account I may pop it up and look at it, and I just go on about my day for a while and maybe I take a couple minutes just to see something later, go on about my day. If I’m at an event, or if something is caught my eye that I’m reading, I may post that over there, as well. I try not to…the purpose isn’t to be on social media, the purpose is to be on the actives with other people socially, at your schedule, at your pace, whatever works for you.

I don’t set goals, and in fact I make it a point to turn things off, like I don’t have Twitter up today, it’s just off because I’ve got other things I need to accomplish today. [laughs] and I don’t need a distraction. Just like at work, I might put on headphones and just sit at my desk for a while because I need to actually get something done.

Carlos: Twitter can definitely be a big distraction.

Steve: It can.

Carlos: I also think, just like with the community, we shouldn’t necessarily be reserved in trying to share some other things. If we start building our brand, and start trying to help others, some of that…well, so most of us, particularly myself, there’s nothing that I’m going to share that a lot of other people don’t already know.But there may be other folks in my network that aren’t as familiar with it. Let’s take an example, like high availability. I maybe come up with something about high availability, always on. There’s books on it, there’s people, MVPs that are in that space. However, my network may still benefit from some of those things, so I don’t think we should be nervous, or feel like the content that we’re trying to provide is less valuable. Because again, that building our brand of folks who are willing to share that information, it will be helpful to someone.

Steve: Absolutely, I think one of the things that we don’t realize sometimes is how absolutely huge the world is. It is stunningly large and even something that’s incredibly popular on Twitter, really only makes it to a small fraction of the people that are out there. Because they’re not available at that time, they’re busy looking at something else, they just skip by it because there’s a whole list of other posted things that are there.I completely agree you shouldn’t get too bogged down in the idea that everybody else has seen what you’ve seen. The way that we personalize things these days, the way the software allows us to customize the views means that it’s entirely possible a lot of the people that you know haven’t seen something. Or they may not have seen the exact thing that popped up three seconds ago, but if you post it, it’ll appear there, or if you blog about it, it’s there.

To me, I always recommend people think about, what does this look like when somebody’s looking at me, not just what the world sees. Because potentially, an employer that look at my blog, or looks at my social media timeline, is looking for me to find out information about me. So it’s important that they see something about how I interact with the world, how I think, what my knowledge is. Rather than in the context of what everybody else has done.

Carlos: That’s a great point and again, building that brand. They’ll look at your book of work they can see it in totality. Let’s talk a little about your brand, and actually, as I get into it, it’s quite diverse. We talked about the editor at SQL server central. You’ve got “Database Weekly,” you’re with Redgate Software, you also host another site for “ModernResume.com.”

Steve: Yes.

Carlos: You blog at “Voice of the DBA.” You actually put out some podcasts under that same name, as well.

Steve: Yes.

Carlos: So, @way0utwest, your handle for Twitter perspective, and then your Hawaiian shirts.

Steve: Yes, exactly.[laughter]

Carlos: I went through it and I thought, “Holy cow.” That’s a lot of stuff, sounds like a lot of work. How do you keep that up?

Steve: How do I do that? Sometimes I look at my life and the amount of chaos that’s involved is stunning to me at times. I think I’m really just getting through the week, some weeks.[laughter]

Steve: The Hawaiian shirts are easy. I buy two or three a year, so that builds up over time and then it’s just a question of grabbing a clean one that’s ironed and I put it in the suitcase…[laughter]

Steve: That one’s easy. Some of what I do in that variety is because it’s a little bit of experimentation, it’s a little bit of trying to understand what works and doesn’t work, and it’s a little bit of trying to see how different parts, different areas there may reach different people. So I may get responses, or comments in different places on similar things I’ve posted. Some of it is a little bit of experimentation for me.For somebody that’s building their brand, for the most part it’s just about them. They’re not trying to advocate for anything other than their own career. Whereas, I worked for companies so I’m obviously advocating for them a little bit with the podcasts, and the events, and some of the other things I do.

The reason I have the Voice of the DBA as a blog, as opposed to just having SQL Server Central is I recognize I may not maintain this job forever, or I may want to go to work elsewhere so I want my own brand, my own place where I can have copies of all my work there. Really, I maintain that blog there specifically to build my career, if I actually have to go look for a job at some point that’s the place where I would try to send everybody. My professional career is, kind of, the SQL Server Central stuff and that’s what I do.

Database Weekly was a spin off from there as a way to kind of diversify our business. Fortunately, I don’t have to do that every week. We do about every third or fourth one depending, throughout the year, because that’s Red Gate Project SQL Server central and Database Weekly. I have a staff of people at Red Gate that help me do different pieces, and they do some of that.

The modern resume is a little bit of my volunteering effort. It’s a little bit of trying to give back to people to help them improve their careers. Certainly, I go to some of the SQL Saturdays to advocate for Red Gate, but a half of my go to-ers is really just volunteer effort. For me, taking time out of my life help improve my career because I can talk about different things. For me, it’s a volunteer effort. Try to teach people something, try to go and speak and help somebody else if you get better at SQL Server or their career, or something else.

I tend to do a lot. I’ve been successful. I’ve got a little bit lucky in my career. What I try to get people to do is think about pacing. Certainly there are times of the year where I don’t travel, my family comes first, other things with my kids come first. I have to put work and other things on a little bit of a pause there. Or I have to shift work around to make time for them.

That’s why I encourage everybody else. You’ve got to build your career and it is important, but its got to fit around the rest of your life. You’ve got to remember you have hobbies, you have a family, you have parents or kids or something else. I try to keep in some kind of balance there.

Carlos: You bring up an interesting point there with the volunteering and you mentioned that you do quite a bit of that. How do you think volunteering helps a person build their brand?

Steve: There is a number of things there. But one thing I come back to from a career perspective is that I’ve managed lots of people, in big groups, small groups in different areas of my career. I never want to micromanage somebody. I never want to have them be told to do every little thing. If I ask them to set up a server, I don’t expect to ask them to also set up backups and set up maintenance plans and go ask somebody what security they need, and those other things.I kind of expect them to do a little bit more than I ask them to do. Volunteering is a great way to show that you’re willing to do more than you are asked to do, especially if you volunteer at work. If you volunteer to teach somebody something or to build a utility or do something that helps another group. It’s a great way to A) make your job easier or make somebody else’s job easier, but it shows that you’re willing to do a little bit extra at work.

That’s a valuable skill because so many people are happy to just go do the bare minimum at work. If that’s what you want to do or maybe that’s the place you are in your life, that’s fine. Certainly people get married and divorced, somebody is sick, they have times in their life where they need to just do the bare minimum. If you do that for your entire career, if you do that for decades, you’re just an average person and you’re not necessarily a great employee for me as a manager.

Volunteering is one of those ways to show that you do more. The other thing is volunteering outside of work on top of helping your mental health, because I think that it’s important to give back and help society someway at some point in your life, you also build these skills in terms of just getting things done when you often don’t have good supervision to get requirements or good direction in a volunteer effort.

A lot of times volunteers just say, “I need some stuff done, would you please do it,” and you have to figure it out. Those are great skills and stories that aren’t really appropriate to talk about most of the time, but in terms of your career, those are good places to talk about, “Hey, I am learning these skills or I have done this thing elsewhere.”

Carlos: I think from the volunteering perspective, another benefit is the ability to be around other like-minded people. People who are working towards that goal. They’re trying to break away from the pack, if you will. By doing that, rubbing shoulders with them, getting engaged in the activities that they like, you’ll be able to build your network outside of the domain, outside of your work environment and you never know what kind of…again networking opportunities might arise from that.

Steve: Exactly. There’s a lot of technical opportunities to volunteer, there’s gift camps around the U.S. where you code for charities, you can spend a day or two doing that. Lots of organizations, from churches to non-profits and charities, they need technical help a lot of times and they can’t really pay for it very much. If you’re willing to volunteer an hour a week, two hours a month or something like that can be a great help them and also build some skills for you.

Carlos: Sure. Now, you don’t want to be pessimistic. But I think you feel like you should put a word of warning. Because occasionally, particularly volunteering at work is probably a good example. Every once in a while, the flaming bag of poo is going to come your way and here you’ve just raised your hands. “I’ll take it!” And yes, that will happen, you’ll have to work through that. Every assignment that you take won’t be the CEO…chatting with the CEO or even taking him out to lunch, that kind of thing.

Steve: Right.

Carlos: But again working through that, showing that you are willing to put in that dedication, that hours. When things then do become important, your manager has something that’s critical to their path, they’re more likely to pick you for the team because they know that you’re willing to put in the effort. If that is successful, the rewards are… big upside.

Steve: It could be a big upside, absolutely. It could be a downside too, the other thing is if you volunteer to do something at work and you’re doing this and all of a sudden it becomes more important, you may get stuck with two jobs.[laughter]

Carlos: Sure.

Steve: Yeah, I always say that whenever you’re going to volunteer like that, especially if you’re doing something at work, make sure your manager or somebody knows about it. That they’ve kind of given it a blessing and an approval, even if its tacit. At least they have agreed that you’re going to do something else and they understand where your time is going and what you’re doing.

Carlos: So then that feedback then becomes important. To let them know, “Hey, this task that I’ve been assigned or I’ve volunteered to take, whatever. This is the status, this is where I am at, I may need some help or whatever.”

Steve: Exactly. And then always make sure you kind of document, keep track of this stuff. Like I said, a lot of your volunteer efforts aren’t necessarily going to be appropriate for you to, you know, talk about or blog about something. But there are things you want to keep track of for you or for the next interview that you have. They are good stories and they’re appropriate in those places to talk about that.

Carlos: Sure, exactly. But I think overall consistency then is the key. To consistently kind of be out there, making yourself available. Again, doesn’t have to be 24/7 but doing something on a regular basis.I think about like the MacDonald’s, that’s a big brand. But you’re going to walk into a MacDonald’s in Kansas, in California, or in the Congo. You’re going to know the layout and you’re going to know you’re going to get a big Mac and it’s going to be pretty much the same. I think that’s what folks are looking for from a brand. It’s some consistency and if you’re willing to put in that effort, then you can built that brand. Again, hopefully the opportunities will come your way.

Steve: Right. It’s like I said, it doesn’t have to be a ton of time, maybe you go on Twitter once a week or LinkedIn once a week and you post something or you respond to something. You provide that consistency that Carlos is talking about, just on a regular basis.Again, ultimately at times stop and look at you profile, then pull up your particular profile. Like I go to Twitter and look at Way Out West, I just see kind of what I posted there and how that looks. Or I go to LinkedIn and just kind of look at my status activity because when somebody goes to look at my career, that’s what they look at. Not all the stuff that I’ve done a few minutes here and there. They kind of see it as one group, so I want to show that.

Carlos: Well, thanks Steve. I do appreciate the conversation. I think there has been some valuable information shared.

Steve: My pleasure.

Carlos: Before we let you go, just a couple of standard questions that add a little bit of value to those listeners. One of the things I would like you to talk about is some of the favorite SQL tools. This can be a paid tool or a free tool, whatever. What’s your favorite tool and why do you use it?

Steve: What’s my favorite tool? Right now, in terms of what I do which is a lot of kind of ad hoc just variety of touching things SQL Server, has to be SQL prompt. I worked for Red Gate software, and they make SQL prompt. But it’s one of those tools that’s absolutely incredible for me. Because a lot of times when I’m trying to find out what a parameter is or I’m not sure what the next way to write code is, that comes up very often, pops it up and it’s really handy.I’ve been using this since before Red Gate actually purchased the tool and so it’s amazing to me. It’s quite noticeable when I’m on an instance that doesn’t have prompt installed in magic studio. I don’t love the Microsoft Intellisense. I haven’t tried in 2016, but certainly in previous versions, it was less helpful. No Intellisence is difficult for me at times because I’m expecting things to be here. That’s a tool I use a lot.

Carlos: So we’ve been talking a little bit about branding and I guess I’m wondering if there is a favorite story or experience that you’ve had around branding or the data field that has helped you or can capture why it is that you enjoy what you do.

Steve: Why do I enjoy branding? There are several stories. I certainly have no shortage of stories where somebody has gotten a job through their brand on social media or somewhere else. But one of the things that really helped me early on in my career figure out where the branding matters is my wife. She spent 20 years in high technology, I think she worked for six or seven companies and eight or nice different jobs in those times.But in all that time, she really only had to send out groups of resumes once. She got one job. She is very personable, she’s been good technically at her job. But the efforts that she’s made to always get to know other people, network well, talk about her experiences with the managers, has paid off. In all that time in her career in the 20 years, she was constantly being called by people to say, “Hey. I have a job for you or send me your resume because I have a position that I would like to get you hired for.”

And that worked out great for her and even although she left technology four years ago and started her own business, self-employed, she’s been called every year by people that know her that say, “Hey, would you like to come work for us again?” To me, that’s an example of an amazing brand, maybe an extreme example. But it worked out really well for her and I’ve seen plenty of other people have small levels of success just with some networking and a little bit of social media and then blogging or being good at their job.

Carlos: OK, we have one more question. But before we do that, Steve, we would like to take a second for our listeners to hear about another way they can learn about SQL Server.[commercial break]

Carlos: OK, Steve. For our last question, if you could have one super hero power, what would it be and why would you want it?[laughter]

Steve: One super hero power, I think…I don’t know. I need something to heal my knees and joints right now because I’m getting older.[laughter]

Steve: I’m almost 50 and I’m struggling. So maybe healing, maybe healing is my power right now as I’m almost 50. Actually, I pulled a hamstring yesterday playing baseball, so I’m limping around today. Healing, if I could be a super healer, I think that would be what I want.

Carlos: Well, very good. Thanks again for being on the show, Steve. We do appreciate it. As always compañeros, we’ll see you on SQL trail.

Podcast Episode 05: Principle of Least Privilege

Robert Verell and I chat about good security practices and the principle of least privilege.  We discuss Robert’s rule to never give anyone db_owner, other groups and Robert’s home grown group to give instead of db_owner.

Show Notes

Robert Verell on Twitter
sqlcowbell.com
What to use instead of db_owner

About Robert Verrel

Robert is a SQL Server Professional working in the Nashville, TN area where he is active in the PASS community.  He is originally from Mississippi and is a big Mississippi State fan.

Transcription: Principle of Least Privilege

Carlos L Chacon: Welcome to the SQL Data Partners Podcast. My name is Carlos L Chacon, your host. This is episode five.

Today, we’re talking about roles and responsibilities in the “Principle of Least Privilege,” with my guest Robert Verell. Super excited to have Robert on today. He’s been able to do some pretty interesting things in his environment. I’m glad that we’re going to be able to share that with you today.

As always, you can check out our show notes at sqldatapartners.com/podcast for today’s information, and for previous podcasts episodes. We are on iTunes, and if you like today’s episode, I invite you to give us some feedback. Rate our program, and let us know how we’re doing. If there’s something you’d like to hear about, we’d like to get that information as well.

If you feel so inclined, we invite you to subscribe to our channel so that you’re always getting the latest information, the latest podcast that we’re putting out. With that, let’s get in to it and welcome to the show.

SQL Data Partners

[music]

Carlos: Compañeros! Thanks for tuning in today.I would like to introduce our guest, Robert Verell. Robert’s a DBA for Cigna-HealthSpring, where he has worked as a senior database administrator for seven or eight years now. He’s a Microsoft certified IT professional. He hails from Nashville, Tennessee. Robert, welcome to the program.

Robert Verell: Hi, Carlos. Thank you for having me.

Carlos: So Robert, tell us what you’re doing these days and where people can connect with you.

Robert: Well, I’m speaking at SQL Saturdays. That’s always a great time. Also I’m pretty active on Twitter. This year I’ve launched my blog and you can go to that on sqlcowbell.com. That’s also my handle, if you will, on Twitter. It’s @sqlcowbell. It’s S-Q-L cowbell. So I’m real easy to find.Usually if you send me some kind of message or anything like that, I’m fairly quick to respond.

What I’m currently working on is I’m working for Cigna-HealthSpring. On the Cigna side, we’re developing an internal cloud. They’re doing database as a service on that end.

There’s a lot of challenges to that. There’s no elevated rights for anyone, so we had to figure out how to develop certain ways of doing a request. Since you don’t have elevated rights at the server level, you’re not able to create logins. So how do you do that?

You have an interface, obviously, that someone can go to and say, “I would like to have this user set up as a login in my database and give them these rights.” Basically, they pass in some sort of [inaudible 03:13] directory name, and that gets sent over to a procedure that I’ve written, and it’s set all that up for them.

Carlos: That’s one of the reasons that we wanted to have you on the show today, Robert, is that principle of least privilege. How simple server roles can help us attain that.I know in the past, you’ve given a presentation about these roles and I know that in your current position you’re working a lot with this. I thought you’d be perfect to come in and chat with us about some of the nuances or difficulties that we might have. Also, because you’ve been able to be successful, how folks can actually get over the hump and implement this.

When we talk about the principle of least privilege, I guess we’re talking about giving users only the rights that we need.

Robert: That’s correct, and Brian Kelly, if you’re familiar with him, he had a blog post back, several months ago that I read. It really hit home with me. As far as giving privileges to people on a database, if you follow the principle of least privilege, you should go all the way down to the column level. If someone does not need to be able to see certain columns in a table, then they should not have rights to it.That means going all the way down and getting granular and going that deep with it if you want to strictly follow the principle of least privilege. Now, I’m not telling anybody to go out and follow that to the letter, because I can’t even imagine trying to administrate that. [laughs]

Carlos: Yeah, that’s a…Lots of overhead there.

Robert: There is a good bit of overhead there, yes. At the same time, you want to make sure that you have a secure environment. You want to make sure that people have rights to things that they need, but not things that they don’t need. In case of any kind of malicious breach or something along those lines.If someone has access to sensitive data that they really shouldn’t or don’t really need to have access to, and their credentials were compromised. Now you’re talking about your organization having to send out apologies to millions of people, and losing money and a stock drop, and things like that. You just don’t want that on your conscience or on your permanent record, as it were.

Carlos: Sure. And I think, while there may be folks kind of gunning for that, the column-level security, ultimately what we’re talking about today is much, much higher than that. Providing at least some initial environment where you can begin to — I don’t want to say, “lock things down.” Only give users what they need to make, your environment a little bit more secure.As we’ll talk about a little bit later, potentially avoid an opportunity where you may be unable to provide services that you thought you were going to be able to provide.

Robert: That’s correct. Absolutely. If…

Carlos: So…I apologize. Let’s go ahead and jump in to some of the justification that users might request or things that our database administrators are getting requested with. Why they might default to those elevated privileges. Because that being the administrator role or in the case of the database and SQL Server database, the db_owner role. Right?One of the ones that kind of jumps out to me is creating databases. I think we’ve all been in that environment where we have a third party application that wants to install something, and it needs to create a database.

Robert: Sure. There’s a common misconception that in order to be able to create databases, that you need sysadmin rights. That’s a fallacy.If you give someone the db_creator role at the server level, then they will be able to create databases as they need to.

Carlos: Right. That’s a very good example of saying, “Well, here’s what they need to do. There is a level of functionality a little more granular that will give them that opportunity to do that.

Robert: That’s correct. Yes.

Carlos: I think another one that you point out is create logins.

Robert: Sure. In order to create logins, you don’t have to have the sysadmin rights in order to create a login on a server. If you give someone the security admin server role, then they’ll be able to do that as well.With the security admin role at the server level, there is a little bit of a loophole here and Microsoft admits this as well. It’s that if you have someone who is in the security admin role, they’re able to give someone sysadmin rights. So you could potentially set someone up with security admin and then they could turn around and just give themselves sysadmin rights.

[laughter]

Robert: From that perspective, I’m not going to say give that widespread, because you really shouldn’t. But if you do have to give someone that, make sure you audit. There’s something along those lines. Make sure you’re looking over that or have some sort of policy based management or alert set up that makes sure that that’s monitored. To where you can know if someone is making server side changes on roles or database roles.

Carlos: Sure. That kind of goes into then keeping up with your environment, so that you’re aware of what’s happened and changes that are going on in the system.

Robert: Sure.

Carlos: One of your ideas was that we should not be giving anybody db_owner ever.

Robert: Ever.

Carlos: [laughs]

Robert: Ever, ever. I will take that to the bank. I will take anybody. Any challengers. Any and all on that. [laughs] The reason why is…The biggest thing with db_owner is you have explicit and implicit permissions.When you give someone a database role, such as db_datareader, that implies that they will be able to read any table or view. Basically run a select statement against any object in the database.

With db_owner, the implied permissions are astronomical. They’re beyond anything that anybody really has to have if they’re doing development work. The biggest implicit permission that comes with db_owner is the alter database permission.

This brings in a whole list of things that you can do that from an administrator perspective, you don’t want anybody else working on. It gives someone with the db_owner permission the ability to add data files.

Let’s say you have all of your data files are nice and neat. You have a dedicated drive for them. But the developer comes in and they run out of space for whatever reason while they’re running something at nine o’clock at night. And they decide, “Well, we need more space.”

“I know that there’s space on the C drive, so I’ll just put a new data file out on the C drive and we’ll just let it grow forever. We’ll let it grow as long as it will go.” Eventually, that will of course fill up and then you’ll have a C drive that’s full. You may have a server that goes down. [laughs]From production to dev, that’s never a good thing, because that still halts development work even if it is a development server. Another thing that the alter database permission gives is the ability to change things like the recovery model.

Let’s say in a non-prod, but higher environment, such as a UAT. Or maybe you have an integrated testing environment where you’re wanting to test things very much like production. You have transaction log backups. You want that kind of similarity to production.

If they’re able to change that recovery model or maybe you have it in simple. OK?

Carlos: Right.

Robert: And you’re not taking transaction log backups. They could to in and change that recovery model from simple to full. Now all of a sudden, two hours later, now you’ve got a full transaction log on one of your testing databases because you’re not taking transaction log backups. [laughs]

Carlos: That’s right, and the application grinds to a halt.

Robert: Sure. On the flip side of that, if they switch from full to simple, now you’re trying to take transaction log backups. Those jobs are going to fail. You’re going to lose any kind of restore chain that you potentially have, because you got to change from simple back into full. And then take a full backup before you can start taking transaction log backups.

Carlos: Yeah. This was the big one for me. I thought that idea of breaking the backup chain…That’s that example of, so it’s database administrators, right? We always think of job number one as being able to make the data available. The source of that or the core is in those backups.If a change is made there or even if a full backup were to be taken and it breaks up our backup chain. We then put ourselves in jeopardy of being unable to provide that service to our users that they’re expecting. Things can get complicated very, fairly quickly.

Robert: Absolutely. The last one, and this was always the biggest kicker for me. I know you said “the restore chain” is big for you, but the alter database permission allows a user to take a database and take it offline.The biggest thing isn’t just that. It’s that you can take it offline and then you’re not able to bring it back online, because you have to be in the sysadmin role to be able to do that.

Carlos: There you go.[laughter]

Carlos: So they’re clicking around in the GUI and oops!

Robert: Yeah.

Carlos: And there you go. Just between you and me, Robert, have you ever had anyone do that?

Robert: Yes, I have.[laughter]

Carlos: Ooh! Well, there you go. I guess I consider myself fortunate now that that has not happened to me.We’ve just discussed some of the reasons why we don’t want to be giving the db_owner role to our users. We have this concept of role or kind of dividing and conquering some of the permissions that are available. We’ve already talked about the db_creator role and what that means.

We’ve also discussed the security admin as well.

Those are probably the two big ones or at least that I’ve more used. Any of the other roles that you want to talk about? The default SQL Server roles that you use on a regular basis?

Robert: At the server level, there’s really only one. I’m of the opinion that a lot of the server roles are…I don’t want to call them “useless.” I can see where there would be certain times when you’d want to give that role versus giving someone sysadmin rights.But I believe they’re really specific to maybe some special environment of some sort. Like disk_admin, which allows you to manage disk space and manage data files and log files. That’s something that you would assume a DBA would do, not someone who’s going to…You would assume that your DBAs are going to have sysadmin rights.

Carlos: Sure. Yeah, potentially if maybe your [inaudible 15:31] person, maybe they didn’t have a DBA and I don’t know. The disk people wanted to get in there.

Robert: Sure. And they wanted to manage their own disk space for whatever purposes. That would be a good example, sure.

Carlos: OK. Ultimately, we talk a little bit about rolling our own and other applications that use these. Even in some of the more familiar ones, at least in my mind, within SQL Server include the role in MSDB. Right? Like database mail user role? If you create a mail profile and you want that user to be able to send mail, you grant them rights to the database mail user role. Then they can send email all day long.

Robert: Sure. Absolutely. There’s a lot of roles in MSDB. I’ll be perfectly honest. I don’t know what all of them do! [laughs]From an implicit standpoint…I don’t know what every single one of them does, but I can say that I know that one that I use pretty regularly is the operator role, the SQL agent operator role as well as the SQL agent reader role.

The reader role is a real big one, because it allows users to be able to view job history. A lot of times, developers will have automated processes. They want to see run times and things like that. And compare. That’s good. We want our developers doing that. As an administrator, you want them looking at those kind of numbers.

If you give them “view job history,” they’ll be able to view all that stuff.

The operator role is good if you have jobs that you’re wanting people to be able to execute as much as you want them to be able to. [laughs] That was kind of long winded on how I said that. Basically, if you have a team, and all you want them to do is to be able to execute jobs, then you can give them that role. And they’ll be able to execute any job on the entire server.

The reader role will allow you to do that as well, but you have to specifically own the job in order to do that.

The operator role also allows you to be able to create jobs. But if you do not have them set up with any other kind of permissions in any databases or anything like that, then they won’t be able to run any kind of TSQL. If you don’t have any proxies set up for them, then they won’t be able to run SSIS packages or anything like that.

Carlos: Compañeros, one of the things we’re trying to do is to get some information out there. I think what Robert just said was huge. Giving users the reader role to your jobs is one that I see in a lot of different places, particularly your SSIS packages and other things that might be there. There is a way to give them that privilege without giving them the keys to the kingdom.I think that that’s something will be of value to a lot of folks.

Robert: Sure. That’s good.

Carlos: It’s a right there kind of within our grasp. We just need to execute and grant that role. Another one I was going to talk about was the RS.exe role, which is both in MSDB and the Master DB. That’s for, obviously, reporting services. So for those who’re going to be creating subscriptions or publishing reports, we make sure that that role is available to them so that they can have that privilege.Most of the time I think that comes through the application when you’re adding them into recording services. But I have seen from time to time, mostly with my migrations, it doesn’t quite get set. It’s just another example of specifying roles that a user would get.

Robert: Yes, I agree, that’s actually a great example, yes.

Carlos: We’ve painted the picture, we shouldn’t be giving folks DB owner role. We’ve talked about why. Some of the other things, but now they’re saying, “Wait a second, I don’t want to be bothered every time a user needs to create a database, but maybe I don’t want to give them that DB creator role or there’s other things like they want to do traces.”Another one that haven’t talked about is viewing dynamic management views. So that if they want to be doing a little tuning or performance and you don’t want that person to be coming back to you all the time requesting these little one-off permissions.

Robert: Sure, understandable. I can certainly respect the need to want to see that kind of data. Because, as stated earlier, we want our developers to be able to go in and view those kinds of things. So that they can tune their queries and we have a good, stable, fast running, environment.When developers and DBAs get along then your end users are very happy as well and your organization is happy as a whole. As far as the DMBs…

Carlos: I think we can also do the view database state or view server state, if they wanted to give them the whole server levels.

Robert: That’s right, yes.

Carlos: Would be another way to do that.

Robert: Yes, there’s many ways to do that, that’s correct. [laughs]

Carlos: Another part that I wanted to get to was your development and putting together of these roles or some of these permissions into a script or into a role that you’ve called DB developer.

Robert: That’s correct. I’m really big on this role, it’s something that I use in my organization and our shared development environments that we have. It gives developers the ability to develop in a shared environment without having the DB owner role.There’s no risk from my perspective about having any of the things that we talked about earlier. We’re saying, “Well we don’t want to mess with any kind of restore chains” or, “We don’t want someone accidentally dropping a database” or, “We don’t want someone changing a symmetric key that we don’t want changed.” [laughs] Just as some examples.

It gives you the ability to do all those things that you want your developers to work on, which includes stuff like being able to script out objects so you can see the code behind things. There’s not a role that gives you that except for the DB owner role.

My DB developer has that permission in it, where you can still do those things.

Carlos: Robert’s been very generous with us compañeros, and he’s going to make that script available for us on his site. We’ll have the link in the podcast notes, we hope that you’ll check that out and start playing with that.Another way that we can help manage some of these permissions, and this is more from an administration perspective and I think we’ve been headed in this direction for a long, long time. That is in the using groups for permissions.

Robert: Yes. I strongly recommend using groups. It’s recommended by Microsoft to do so. Basically the way that my environment is set up is, we have groups set up for anything that we think needs to have read only access. If someone needs that access we simply take their user and we put them into that directory group.Instead of going into the server and creating a login and creating a database user and then putting them into a database role. The reason why is whenever they change roles or if they leave the organization, it’s very easy to remove them out of those permissions.

If we’re wanting to remove them by using AD groups, all we do is take them out of the active directory group, that’s very easy to do. Whereas if we create a SQL login for them, then we have to login into the server, we have to delete the user and hope they don’t have any objects in the schema.

Then we have to delete the login. [laughs] There’s a decent amount more work that goes behind that.

Carlos: That’s a great point there. We can be assured that they’re not going to own any of those objects and it’s much easier to put them in or take them out. We even use that for our DBAs, creating that group and then adding them.That way, we know who has elevated privileges on a server, based on those groups.

Robert: That’s correct. That’s also another place where principle of least privilege can really come in. Let’s just take a really easy example. Let’s say that you have a server that was manually restarted. You know for a fact that on the Windows side that in order to be able to restart a server, you have to have administrator rights.You go into the server and you say, “OK, who has admin rights?” That basically narrows your list down of, “OK, well one of these people had to have been the person who did it.”

You can apply the same thing at the SQL server level. If a table is dropped, you can say, “Well I know that these are the people in this database that have the permission to drop these tables. It has to be somebody in this list.”

Carlos: Right. Making auditing a little bit easier there.

Robert: Yes.

Carlos: I think that is going to wrap up our discussion on privilege of least permission. Is there anything else that you think we should hit on before we change gears?

Robert: No I think that’s good.

Carlos: OK. Robert thanks for this discussion about roles and permissions, I think it’s been valuable. I definitely think everyone should be checking out your DB developer role. I think it will be very, very valuable. As I’m always trying to create value for folks listening, I’d like to share another way that they can learn about SQL server.[music]

Carlos: Hello there compañeros, I want to tell you about a unique training opportunity that is unlike anything you’ve encountered. As a listener of this podcast you’re entitled to a special offer. SQL Cruise is a premier training experience set in the most unlikely of places, a cruise ship.Tim and Amy Ford have created a wonderful culture on SQL Cruise. With five or six technical leads from various industry sectors, you and roughly 20 other students will talk shop in classes while at sea. While you’re in port, you can enjoy the wonderful world we live in either by yourself or with the group.

The beauty of SQL Cruise is the unique relationships you will form with other SQL cruisers. Worried about leaving your spouse? Bring them along. I did and she had a great time with the group. In fact, I have been on SQL Cruise twice and I’m looking to go a third time.

You may be wondering if this is serious training and I can assure you it is as good as any conference you will attend on land. It never crossed my mind that I could be talking about SQL server with several Microsoft MVPs on the beaches of St Johns.

I know you must have other questions so I suggest you head over to SQLCruise.com and check out the site. Once you are satisfied and you want to sign up, when you get to the question, would you be so kind to share the name of who referred you and what source material led you to us?

You enter SQL Data Partners and Tim will ensure you get $100 off the training registration. This is a great offer and I hope this $100 savings will entice you to check out the site. I did and went on the Caribbean cruise and had a great experience and I know you will too.

Maybe I’ll see you on board. So head on over to SQLCruise.com and use the code SQL Data Partners to save $100.

As we’re wrapping up here Robert, we have a couple last questions we’d like to have you share a little bit more about yourself. What’s your favorite SQL tool? It can be a paid tool, free tool, but what tool do you like and why do you use it?

Robert: That’s a really tough question. There’s a lot of tools that I use that I like. SSMS is my favorite tool.[laughter]

Robert: I’m kidding, I’ve got a better tool than that. If I had to give a really good tool to use, I would say probably Spotlight by Dell. That is probably the one that I really enjoy. It give a lot of really good data and the visualization of it, to me, is what really, really does it for me.I love other products by other organizations out there and I actually use them. I’m fortunate in my environment to have a lot of toys, as it were. In my opinion, Spotlight is something that I’m into every single day. I’m looking at stuff every day in there and evaluating things in there.

Carlos: OK. The Dell tool, that’s paid. It is interesting actually, I went to a session by Adam Machanic once and he talked about monitoring. To me, Adam Machanic is one of the people writing that knows so much about SQL server.

Robert: He’s forgotten more in the last day then I’ve learned in the last year.[laughter]

Carlos: That’s right. One of those people. He made the comment, he’s like, “Oh well, I would put a monitoring solution in all of my environments.” Interesting that, while yes, you could homegrown some of your own, that ability to have a product that is stable, is consistent along all of your environments and then be able to give you some of that history. I thought that was an interesting comment.We’d like to hear about an instance or experience that you’ve had that helps you remember why it is that you enjoy being a database administrator.

Robert: That’s really tough, do I have to limit it to one?[laughter]

Carlos: We are looking for just that one.

Robert: OK. If I had to give stories, I could mention SQL Saturdays, those are always great. Another one that I’ve done is, I’ve done a couple data center migrations and those are fantastic. You learn a lot during those, but I would say, probably one of the best DBA stories I had is something that I actually made a blogpost about.It was where I basically implemented compression earlier this year. Simply by compressing a partition in a single table, a nightly load increased by probably 20%. I was simply amazed by that. That was incredible for me, that’s the one thing that sticks out to me.

The reason why it was so big is because it was something that I had wanted to do for a long time. I had partially implemented it back six months ago, or something like that. Then two or three months ago when I got around to finishing it up and getting it completely done, the day that I got it completed, they came through. It ended up being one partition in one table that I compressed that you really did a lot of good work from it.

It was just a really good feeling to be able to implement something from an administrative side. Developers all the way down to end users saw a great benefit from it. They felt that it was a really good thing and they wanted to know where my magic wand was.

[laughter]

Robert: When you do things like that, it really does make you feel good and it makes you feel very proud. That’s the best I got.

Carlos: Sure, very good, very good. Any time we can add value there to the business, polish our credentials a little bit if you will. It makes us feel good. Very good. Before we get to the last question, want to remind folks if they want to connect with you via Twitter, they can do so at SQLCowbell. You have your new blog at sqlcowbell.com.For our last question Robert, if you could have one superhero power, what would it be, and why would you want it?

Robert: I’m going to go with…That’s a really tough question.[laughter]

Robert: I’m going to go with the ability to see the future.

Carlos: OK. There you go, divination, whatever that…

Robert: Yes, that’s a great word for it, yes.

Carlos: Very good, very good. Well Robert, thanks again for being on the show. I had a great time. I think the folks who are listening will be able to learn a lot.

Robert: That’s great, and Carlos, thank you for having me. It’s been a pleasure. As always, I enjoy talking with you. I’d love to be back on if you ever wanted to have me. I’m an open book. If you reach out to me on Twitter, I am more than happy to answer any questions. I’ve even answered questions to people who have written in Japanese kanji before.[laughter]

Carlos: Nice, man of many talents.

Robert: Yes.

Carlos: Thanks again Robert. We’ll see if we can have you on again. Compañeros, thanks for tuning in. It’s been another great show. We’ll see you on the SQL trip.[background music]SQL Data Partners.

Podcast Episode 04: Advanced Backup Techniques with Tim Radney

1400We finish up our conversation with MVP Tim Radney on backup options with discussions on encryption and Azure.  Tim also shares his favorite tools and his super hero power of choice.

Show Notes

Erik Véliz, the creator of SQL Server Booster is looking for folks to help with his latest update.  Check out sqlserverbooster.com for more info.  Download the new BETA version directly.

Tim’s Insider Video on encryption can be downloaded

  • In WMV format here.
  • In MOV format here.

You can get the demo code here.
The video on backing up to Azure can be downloaded

  • In WMV format here.
  • In MOV format here.
Transcription: Advanced Backup Techniques

Carlos Chacon: Hello, and welcome to SQL Data Partners podcast. I’m your host, Carlos Chacon. Today were going to finish up our conversation with Tim Radney, a Microsoft MVP and SQL skills consultant. We’re going to be talking about backup and recovery specifically some advanced features, particularly around encryption and backup to Azure.

Before we begin that, I do want to throw out something. For those who maybe in smaller environments, who don’t have enterprise versions or can’t quite do encryptions yet, that is the SQL server booster by my friend Eric Velez. Eric is actually from Guatemala, and has put together a free tool called SQL server booster.

This is an application for DBAs, developers, or people that do management around Microsoft SQL server and that need to make backups automatically. Compress those and send them to different places, through FTP, maybe through Amazon, Azure, or even Dropbox, or through your network.

It just so happens Eric is looking for BETA testers for his newest version of the product. If you’re interested, you can take a peek at sqldatapartners.com/podcast for this episode, which is episode five. We will make sure you have the link to do that. This is going to be particularly interesting for those who are with the express editions.

Maybe you have a couple of those you’ve been playing around with, you don’t have yet even a full standard edition version, and need some help with assistance around those maintenance tasks. You might want to check those out.

At the end of the podcast, we will have some information on how you can win Tim’s book on backup and recovery strategies. Also, we will give you the information on how you can get some free Perl site, free training from the SQL skills folk over at Perl site. That information will be at the end of the show.

I cannot forget, we are now on iTunes. I didn’t realize quite the level of effort it will take to get on iTunes. We missed that, if you will, on opening week last week. Now we are there on iTunes, so if you seek for us SQL Data Partners podcast, you will find that. We will make sure, again, that you have the links. You can send those out. Again, welcome to the show.

[music]

Carlos: Encryption, backup encryption, you want to talk about backup encryption?

Tim Radney: Sure, I was really glad to see backup encryption being added to SQL server 2014. Previously, we’d have to use third party tools. Some of those tools are still good for centralizing and managing your backup and things but with encryption being part of 2014, really helped add to in backing up the URL which was introduced in 2012 service pack and accumulated update.We gained the ability to backup URL, meaning windows server storage or Azure storage. Being able to encrypt natively our backups really eases the pain and concern about backing up across the wire up to Azure.

Working with banks, working with financial institutions, healthcare environments, we backup our databases. We are all good, we know that we need to have them somewhere else other than our SQL server. They need to be on a network share, a backup device somewhere. As soon as we place those backup files somewhere else, we don’t have that control anymore.

Sys-engineers, sysadmins, other folks that have access to those volumes could easily grab a BAK file, install SQL server express, SQL server developer edition. Restore the database, and they have access to all the goodies.

Having this encryption is very simple. We’ve had a SQL skills data video a few weeks ago, that includes the stepping through the process. You just need to create a server certificate, a master key, backup those two keys, then you can start backing up using compression. You can specify your algorithm from AES-256 and others.

It is very straight forward. You backup, you choose your encryption algorithm, you choose the certificate that you’re using it to encrypt it with, and it’s done. To restore to that server, is just like any other restorer. To restore to another server, you will need to have a master key, and then you will have to restore that certificate.

Restoring that certificate, you have to have the key you used to encrypt it. All that is documented in our video. You can restore those encrypted databases to another instance. It is really straight forward, it is super easy, just a couple little gadgets with the master key and the encryption cert. Having that encryption cert also backed up in stored, and keeping up the password that you used to encrypt it.

If you have those things in place, you can now encrypt with 2014 locally. You can encrypt and ship that off to your Azure. When it is up in Azure, that is really a nice thing if you’re a medium to small size shop, you don’t have a dedicated DR site, to get your backups offsite. To satisfy that concern and worry that if something happened to your data server, or someone stole your hardware or things like that, you have data you can recover from.

If you leverage Azure DR site, now you have your backups there. You can spin out a virtual machine with SQL server on it, that volume restore your databases. It makes for really good DR posture.

Carlos: One thing about those encryption keys, make sure you back them up, you don’t want to lose those. [laughs] Another advantage, which was available, I think it started 2012, I think there were some enhancements in 2014, and that is backing up to Microsoft Azure storage.You referenced that a little bit. This is one of the areas for those who are a little bit timid to take that jump into the cloud. Microsoft has definitely made a push into the cloud, they want you to be there.

For some folks it may be a little overwhelming. I think making your backup’s too, as your storage, fairly straightforward, fairly economical, and fairly straightforward.

Tim: Yeah, the storage is cheap, the bandwidth you pretty much have it. I really advocate for restoring the production database in Azure, with the idea of restoring from Azure back to your local data center if you have a problem. You’re still limited with your bandwidth but…

Carlos: Explain it a bit more.

Tim: Inexpensive off-site storage to meet regulatory and audit requirements, it is a simple, easy, inexpensive solution. Also like I have mentioned, if you want Azure as your potential DR site, you can spin off these VMs with VPUs low memory. Get you storage there, again storage is the cheapest portion of it. You can have store process in place, where it is taking your backups up there, basically like a poor man’s log shipping.You can have these databases ready to go. If you need them, and you’re setting up your app servers, and web servers and things as well, then you can turn up and choose instead of the one CPU six GB RAM machine, you’ve got eight CPU nine-something GB RAM, host as a server. You start paying for it when you increase the CPU in memory.

No real sense into paying the money for a high end server, just to be doing log shipping, or restore validations and things. Just turn it up when you need it, and when you get to your site backup, fell back over then dial it back down. I know several corporations that are leveraging that. It is an inexpensive insurance policy, rather than paying for this big iron sitting somewhere that’s not being used.

Azure is a really great option.

Carlos: Yeah, and you mentioned actually having that DR if you will, if it’s ready to rock and roll. I was referring to something even as simple as having your backups there, particularly if you’re in a pinch where you may not be able to be keeping quite as many as you’d like. There are ways to get around that, just like we talked about earlier.We’re going with differentials and making those backups a little bit smaller. Should you want to keep certain number of your full backups around, restoring an Azure can make a lot of sense.

Tim: Archive data up there, we’ve seen with SQL server 2016, the little consumer preview that came out with stretch databases. You can store archive information in Azure. It’s really starting to take off. Like you said, keeping older databases there, getting them off prim, in the off chance that you may need it, very viable option.I would still rather keep, if it’s dated, I think I would need for a production restore. I’m running tight on space, I’d be looking at really inexpensive onsite UT3 AES data, or something. Even a supposed spinning 5,400 RPM disc should still be faster than my connection up to the Internet. Pulling back gigs and gigs of data. It’s a really good safety net for sure.

Carlos: We have a couple questions we want to ask you. This is going to wrap up our back up and recovery section. As a reminder, Tim’s book is called “SQL Server 2014 Backup and Recovery Techniques.” It’s available out there on Amazon. We are going to give away five copies of the book. If you’d like your shot at winning the book, go to sqldatapartners.com/podcast.Under the information from today’s session, you can register. We will be announcing those winners on August 31st of 2015. Again, that is sqldatapartners.com/podcast, and you can register under today’s program information.

Tim, what’s your favorite SQL tool? This could be a paid tool or a free tool. What tool do you like most and why do you enjoy using it?

Tim: I would probably have to go with two of them. First is the SQL central plan explorer.

Carlos: That is a good one.

Tim: Just for diving into execution plans, from a production DVA, tuning, troubleshooting, that’s my go to must-have. The other is when I’m digging in routing a lot of T-SQL, is SQL prompt from Red-Gate. That tool saves me so much time from having to type, and formatting SQL, especially when I’m doing webcast or use a group presentation of things, and I’m writing new scripts.I could just use the SQL prompt feature to format my codes, so it looks like I’m really good at just sticking to standards, best practices, when I really just write really sloppy code.

Carlos: [laughs] It can make you look good. There you go. I think that the plan explorer, SQL entry plan explorer comes in two varieties. There is actually a free version available. Lets you look at one execution plan at a time. I don’t know that I can name all the features. I know one of them is if you want to open multiple execution plans, you need to go pro, and there is a small fee for that.

Tim: We joke about the free version, the regular plan explorer. It’s so free, they don’t even ask you for your email address.

Carlos: [laughs]

Tim: You go to SQL sentry, click on “download the free tools,” It is straight up download. There is no login, no registration. There’s just click the link, and you’ve got it.

Carlos: There you go. That is a good one. The other one is the SQL prompt from Red-Gate, that is a paid tool variety. Correct?

Tim: Yeah, it is. It is one of the tools that I have always joke about. If I wasn’t a friend of Red-Gate, and get it through that program, and didn’t work for a company that would buy it, I would buy it myself. It’s that good of a tool.

Carlos: It is a good one.

Tim: It is relatively inexpensive, a couple of hundred bucks. The product median increase from having that is absolutely well worth it.

Carlos: I’ve been toying around with that same vein with the apex SQL tool, the apex SQL refactor. They have another tool very similar to the SQL prompt in that same vein as tools.Ultimately, as the folks listening to this podcast now, I didn’t go to school to be a DBA, wasn’t my intention. Lo and behold, here I am. What is your favorite DBA story? What is it that you enjoy about being a DBA? What keeps you here?

Tim: The SQL community is huge. Having the friends in the industry, it’s really tight knit, in your family. That definitely helps, keep you in the business and doing the data stuff rather than changing careers. For me, I just like fixing problems. I like making SQL Server run faster. I like the ability to educate others.The fact that I’m not really aware of any school that you can go to and come out and be a DBA. You can go get your computer science degree, you can get your information systems degree, you can get your engineering degree, but there’s not really a school for DBAs.

Most that are doing what we do are those accidental DBAs. They were either a sys-engineer, a sysadmin, an application developer that was told, “Oh, by the way, you now own the SQL Server instance.”

Carlos: Congratulations.

Tim: You’ve been voluntold. Very few actually volunteer for the job, we get stuck with it. Being able to dedicate my time just to DBA and being able to help clients all over the world, and stepping in and saying, “Here’s things that we have found that you can do improve your instance,” because they’re running a default install. That’s just so much fun.Now we’re on an every two year cycle with a new version of SQL Server. It’s just getting better and better and new features and technologies to work with. It’s just an incredible thing. If I was to have to sit down and write code for an application, and that’s all I’m doing, I think I would get really bored with it.

Carlos: Interesting that one that you mentioned, the community, you’re right. The SQL Server community does have a great environment, a great core group of people, very open, and that diversity in the database field. I agree with you there. The two-year cycle, I guess, is a nice thing because new technology is coming out. A little scary, too, right?[laughter]

Tim: Absolutely. As soon as you feel like, “I’m finally starting to learn 2014,” “Hey, guess what? Here’s 2016.”

Carlos: That’s right.

Tim: A whole bunch of new stuff. Just when I have clients getting on 2012, now it’s like, “You need to be looking for 2014.” “Well, we’ll wait for 2016.” It’s extremely challenging. I miss the days where SQL Server 2000 lasted for…it’s still out and running now 15 years later. It was the work horse, the tried and true.2005 came out five years later. Then 2008 three years later. It felt pretty good with that three-year cycle. Two years is a bit challenging.

Carlos: They keep us on our toes at Microsoft.

Tim: Absolutely. I don’t know if they’ll follow a Windows 10, where it’s the last version. We’ll have 2016, and then we’ll just have massive service packs to make us feel better. Who knows?

Carlos: It’ll be interesting to see what that projection is. Last question. If you could have one superhero power, what would it be and why would you want it?

Tim: Superhero power…I think flying would be cool.

Carlos: [laughs] You chose mine, too. Are you a pilot? Did I see that?

Tim: I’m not, but if I could fly, then I wouldn’t have to get on an airplane. I could clean my gutters much more easily.

Carlos: All the uses, right? The honey-do list could get done much more quickly.

Tim: Just be Superman for real. Have the laser beams, see through walls, be able to fly, superhuman strength.

Carlos: We only gave you one superhero power. [laughs]

Tim: Out of those, the flying, I can buy a tractor for the superhuman strength.

Carlos: Very good. Thanks again, everyone, for tuning into the “SQL Data Partner” podcast. Tim, thanks for coming and joining us. It was great fun. I do appreciate it.

Tim: Thanks for having me. This was a lot of fun.

Carlos: That’ll do it for our show today. Thanks again for tuning in. Again, if you’re interested in winning a copy of Tim’s book, you can do so at sqldatapartners.com/podcast. At the bottom there, there is a registration for that book. The raffle will be on August 31st of 2015.Also, wanted to remind you of the offer from the SQL Skills folks for a free 30-day trial of SQL Skills content on Pluralsight. What you need to do to get that is you can email Paul Randal at paul @ sqlskills.com with the subject line “User Group Pluralsight Code,” and you will get the free 30-day trial. No strings attached, to all of the content from the SQL Skills folks.

There is about 150 hours of training on there, and so we hope that you’ll find that useful.

[background music]

Carlos: Thanks for tuning in today, and I’ll see you on the SQL trail.

Podcast Episode 03: Backup and Recovery

1400I chat with MVP Tim Radney about Backup and Recovery options in SQL Server.  We chatted for quite awhile and I ended up making two episodes out of our conversation.  This episode focuses on why we backup and some core ideas.  Advanced features will be in episode 04.  You can register to win a copy of Tim’s book on Backup and Recovery by visiting sqldatapatners.com/podcast.  Also be sure to check out the very cool offer from SQL Skills at the end of the session.

Show Notes

Tim on Twitter
SQL Server 2014 Backup Recovery Book
SQL Cruise

Transcription: Backup and Restore

Carlos Chacon: This is the SQL Data Partners Podcast and my name is Carlos Chacon, your host. Today we wanted to talk about backups and restores. My guest Tim Radney and I had a great conversation about this. Our conversation went a little bit long so we’re going to break this conversation up into two episodes.

Today’s episode is going to focus mostly on the reasons for backups, the different types, and why we do them of course. Episode four is going to focus on some of the more advance concepts as far as encryption, compression, and backing up into Azure.

The folks at SQL Skills have made us a great offer which we will let you know at the end of the session, so we hope that you’ll check that out. We have a special offer going, we’re actually going to be raffling off the book that Tim wrote about backup and restore options in SQL Server. Again, all that information will be coming at the end of both episodes. We hope you check those out and again, welcome to the show.

Carlos: Tim is a consultant with SQL Skills and has been awarded the Microsoft MVP Award. He has written a few books and if you fancy a twitter chat with him you can find him at @tradney. He hails from Fortson Georgia where he serves as the past chapter leader of the Columbus SQL Servers users group. Tim welcome to the program.

Tim Radney: Thank you, Carlos.

Carlos: Thanks for being here. Ultimately today we wanted to talk a little bit about back-up and restore operations, primarily in SQL Server as this is going to be SQL Server focused. One of the reasons is wanted to have you on the show today, Tim, was because you have written not just one but actually a couple of books on back-up and recovery. Your most recent book is entitled SQL Server 2014 back-up and recovery techniques and it is available from Amazon.I’m also super stoked to announce on our podcast that we are going to give away 5 copies of the book. If you would like to register for you chance to win you can go to sqldatapartners.com/podcast and under the information for today’s session you can fill out the form with your email address with a chance to win. We will have the drawing on August 31st. Again, that URL is sqldatapartners.com/podcast and you can register under today’s program information for a copy of Tim’s latest book.

Today our discussion will focus on backups. One of the things that we need to keep in mind, we need to keep the end in mind. It would be incomprehensible for us to talk about backups without first talking about recovery.

Tim: That’s exactly right.

Carlos: While we do take backups, we take them so we can recover. There are the two little acronyms we’ll go over quickly about recovery. RTO and RPO. I’m curious Tim, when we talk about these acronyms, first let’s define them.RTO is your recovery time objective. How long you think you’re going to take to get back up. Then your RPO is your recovery point objective. What point are you going to be able to get to in the event of a disaster or what have you?

Tim: Right. I like to tell people to think about recovery point objective as how much data can you afford to lose? Your point in time, is that 5 minutes, 10 minutes, an hour, to put it in perspective. Unfortunately when we talk about recovery time objective and recovery point objective, everybody says immediately, zero down time, zero data loss.Fine, give me $10 million and we’ll start talking. That’s really hard to hit but you can usually land on something reasonable and build your recovery strategy from there.

Carlos: That’s an interesting point, that you will need to setup your backup recovery, so again, you can recover, which we’ve mentioned. You want to talk about all the things that will be needed for you to be able to get to those points. Lots of different features that are available to give you highly available data, but they don’t necessarily come free.Having those discussions and being able to understand what your business requirements are, and what they’re willing to pay for or potentially even change their business processes if they need to so that they can be able to recover or take care of some of that recovery if needed, I think is also in play. Any thoughts on going about with discussions of the business of how to set these up?

Tim: Absolutely. It all starts with determining what is the [inaudible 00:05:39] and recovery time objective? Knowing what the business requirements are, and what they need in the event of a disaster, or in the event of some “oops” type situation. Update statement without a [inaudible 00:05:53] clause. That could be considered a disaster.If you just trash the entire sales table, your business is down and that is a critical point. Depending on your HA technology, if you are doing an availability group or mirroring type situation, that update statement was ran against both nodes. What can you do at that point? What is a restore operation?

For disaster recovery, if your data center were to go dark or become a smoking hole in the ground, how quickly do you need to be up in a secondary location? What is your appetite for having that secondary location and the technologies there and how are we going to get the data there, type thing.

Based upon those business requirements, will dictate what your backup strategy should be, because your backup strategy mimics your recovery strategy. It all comes back to what does the business require…I say require, not want. What is the regulatory requirement for that?

Carlos: Hopefully, part of our discussion is we want to be able to provide the ability to give back. They may not be necessarily happy with the time it takes to get there, but we have always wanted to be able to provide a way to get back. This is Lesson 101.It’s funny, actually his name is escaping me, but the fellow started code school, which is now part of pluralsight. as well, he tells us a story. He was a developer and still is. He tells a story about a script that he was going to run on the database for a company he was working for. He knew it was going to take a little while. It was an update to all of the contact table for this application.

The organization was mp3.com at the time. They had several million contacts in there. He didn’t have time to do it during the day, so he thought, “OK, I’ll just let the script run at the end of the day and I will check it when I get home.” He didn’t check it that Friday evening. Saturday evening rolls around, he logs in and says, “Hmm, I don’t see my changes. My script says that it ran, but I don’t see the changes that I thought would be in there. Let me run it again.”

He runs it again. Again forgets to check back in on it. He comes in on Monday morning and they have kind of this all hands meeting. The manager says, “So, someone ran a script this weekend that updated every single email address in the contacts table on Friday. The DBAs were in here all day Saturday restoring the database.” They had some issues with the restore process. It wasn’t quite there so they lost a little bit of data. Then, somebody ran it again on Saturday night.

[laughs]

Tim: Oh Wow!!

Carlos: Can somebody tell me who did that? He was like, “Please not me, please not me, please not me.” He goes back to look at his code and sure enough, no [inaudible 00:09:21] and so [laughs] that was an example of always having a fall back plan there.

Tim: That’s right. We always like to say it’s not a matter of…if it’s going to happen to you, it’s a matter of when you’re going to do that. When you are going to drop. You think you are in [inaudible 00:09:42] when you are in production and we try to segregate access and rights and things. You get one of those drive by or walk by your cube distractions and you turn around and you execute and you are on the wrong screen. Stuff happens.Even in real world, in the ETL process, you get a bad file. The file is structured correctly, so all your checks pass. All your little SSIS, little widgets turn green and it ingests this data and it’s really bad data. It’s just mesh.

[crosstalk]

Tim: If you don’t have an easy way of identifying that this was the data that was new, if it was a merge into a whole bunch of different dates and you don’t have an insert date or something you can trigger off of, how do you get back? You have to restore back to a point in time. So having the ability to do that is crucial.

Carlos: I have to tell a story on myself. I was working with some healthcare data in a scenario where they wanted me to help reclaim the data. All the females become Mollys, all the males become Mathews. Change the address information, for example, so that they cannot present this in their demo’s when they are looking at other clients.Just like you said, I was developing this in test , everything was fine. Somehow, my connections switched. To this day I have no idea how that happened and I ended up going against production. Ran the data manipulation and all of a sudden they’re in the data base. All the women were Mollys and all the men were Matthews. So yeah, talk about embarrassing.

Luckily, one tip I will give there is that once I had identified what I had done, I immediately found out how many instances, which luckily in this case just one, it was affected and then I immediately took action by notifying the team. The worst thing you could do is try to cover that up somehow or wait for somebody else to notice. That would not be good.

Tim: That’s a CLM, a Career Limiting Move, trying to cover up. Data professionals were supposed to have certain levels of integrity and honesty and things and being able to raise your hand and say “It was me. This is what I did. It was an honest mistake. Let’s get this fixed”.Once people like myself and you and others had that moment is that when we’re so much more alert and aware of what we’re doing in production. That’s like coming to age type moment. If you’re going to hire a junior DBA and they’ve never have that moment, guess what, they’re going to have it with you.

Carlos: You want to be prepared.

Tim: That’s right.

Carlos: That takes us into I guess the different steps that we can take to be prepared. Let’s talk a little bit about some of the recovery models. I’m counting seven options that we’ve got. We’ve got full, partial, differential file group, file logs and copy only. Let’s go through this. Question, how often are you normally taking full back ups of the databases.

Tim: I typically do weekly full and daily differential.

Carlos: That’s out of the gate you’ll do that?

Tim: Yes.

Carlos: Interesting. I guess I tend to be a little bit pessimistic. I have been advantaged to have lots of space. Normally in the beginning I’m taking full back ups every night. When do you decide to do something else? I think some of that may be a matter of style, size of the database, because it plays in there.

Tim: Yes. Size definitely plays into it. Also a lot of my clients are 24/7 type shops and backing up cross the wire to a network type device doing the same full back up every night, you’re backing up so much data that has not changed. You can decrease the amount of your back up size by doing differentials during the week and then in that we’re doing weekly full daily differential log backups at some smaller intervals anywhere from 5-15 minutes.Doing that, you drastically cut down your back up time, cut down the size of them. Your restore may take slightly longer since you do have to restore a full and then a differential, but most in cases these differentials are just gigabytes in size, so you’re talking minutes. But then the time during the week the overhead, the IO from having to do your constant your full back ups.

If I’m dealing with a client and their databases, their largest one is your 10, 15 or 20 gig, then OK, a nightly full would be OK. A lot of my clients, we’re dealing with hundreds of gigs to terabytes databases and doing a nightly full of a multi terabytes as databases is just not efficient.

Carlos: Right. You mentioned, I’m breaking that up, so ultimately the size of the database. Normally the time that it takes to do that backup, when I looked to change from doing fulls, if I am generally doing one file, then I’ll try to do a full back up to multiple files.Then if I can’t fit it within my window of time that I want those backups to take place, then that’s when I start working at the differentials. One of the pieces about that differential you mentioned is that the differentials will take back ups of everything that’s happened up to the last four.

Tim: Yeah. The differential does not reset the differential bitmap, only a full back up. It’s not an incremental, it’s not everything that has changed since the last differential. It’s everything that has changed since the last full. Over the course of the week as data continually changes, your differential would increase in size until you take that next and you schedule full backup.

Carlos: We mentioned the log backups. Interesting that you mentioned smaller increments. With full differentials we talk about doing that, so once daily generally, right? Now we get into our logs and you mentioned 5 to 15 minutes.It’s interesting, I run through a lot of places where they will set a log back up, they tend to do it even in hours, there’s a time I’ve seen like in four hours. Again, some of this goes back into the recovery models that we talked about. What I want to say is I want to err on the side of caution and want to take my log backups more frequently than every couple of hours

Tim: More frequent log backups give you a smaller granularity of your data loss to decreasing the potential for data loss. It’s also maintaining the log more efficiently. The more frequent that you do the log backups the more frequent the transaction log is truncated.You get the re-use of the VLS. The smaller that log you could potentially be if you are doing it full hours maintaining full hours worth of your logging activity. If you’re doing it every 15 minutes and you have transactions that don’t waste span longer than 15 minutes then the log should be relatively smaller. There’s additional benefits rather than just your recovery time or recovery point objective in having more frequent log backups.

Carlos: Sure. My experience has been that I don’t see if any disc space by increasing my log time. If I do four backups an hour every 15 minutes or one every hour, the log sizes are basically the same, right?

Tim: Yes, within reason. If you take a log backup every four hours, then over eight hours you’ve used X number of megabytes or gigabytes versus eight hours worth of log backups every 15 minutes. It’s still roughly the same amount of data churn. The more frequent the backups, you still have a little bit of header information and things like that.Bytes worth of redundant or additional data, but for the most part, the logging activities should be, within reason, about the same.

Carlos: With our disc space today, the bytes are insignificant. The lesson there is if you’re unsure about how often to write log backups, go with more frequently.

Tim: Right. If you ask the customer “How much data can you afford to lose?” they’re going to say “None.” Unless you’ve negotiated, and it’s some special case with the system where they can go and reload files, because the database is really just loaded from some ETL process, and they can redo that work, then there’s really no reason to have very wide, long durations in between log backups.From your perspective, if you’ve got a standard, your standard is every 15 minutes and they’re telling you every hour would be OK, why change it. Be the hero, and do more frequent backups.

Carlos: That’s right. I agree there. The last one is the copy only. This one, the copy-only is ultimately a full backup where we don’t want to break that chain.

Tim: That’s typically where you’ve taken more frequently. A copy-only with a differential is still just a differential backup, because is still isn’t going to reset the differential bitmap. Where the copy-only is crucial is when you’re doing fulls with differentials. If you were to make a full backup without a copy-only, every differential after that full belong to that full.That one full backup you just made is its base, so if you go to restore your full from Sunday that somebody made a full on Tuesday, and then you go to apply the differential from Thursday against the full from Sunday, it’s not going to work. If you’d made that full on Tuesday with copy-only, it’s not going to reset the bitmaps.

It’s as if that full never really happened. A good practice to get into if you’re in an organization that does differentials, and you’re making a one-off backup that is not part of your recovery plan, or your normal sets of backups that you are going to use to restore, always use copy-only.

Carlos: Yes. Is that fair? You talk about your backup strategy. I think I see that more if you’re using a third party tool to do your backups. I think if you’re using SQL server through maintenance plans or a job and you are storing this copy-only backup in the same place, you may be OK. Hut it’s usually if you’re using a VERITAS or Symantec or something to do those backups. It doesn’t know about that backup, and that’s why we use a copy only. Is that a fair assessment?

Tim: Yeah. I think where you’re going there is folks that are doing VM snap shots or something going on if it’s a virtual machine where it’s also taking backups. I’ve seen some of those systems use copy-only, so it doesn’t interrupt the DBA’s process. If you’re using native SQL server backups where database maintenance plan to do yours, that some of these third parties will use with copy-only so that they don’t disrupt your recovery strategy.

Carlos: OK. Those are the backup types. Your databases can have them. Any special consideration for backing up the system databases? We’ve got Master, MSDB, Model, and tempdb .

Tim: Yes. For Master, Model, MSDB…MSDB, a lot of folks, they don’t think about the system databases. They think they can restore or install SQL server, restore the user databases, and they’re good to go. If you’re not backing up MSDB, you just lost all your DTS or SSIS packages, if you store those there. I don’t know how many people memorize or take screen shots or have everything fully documented for every single jobs that’s running as well as this complex SSIS packages.

Carlos: Right. All this security is associated with them.

Tim: Right. I’ve had people say, “Well, we have those in source control.” Fantastic. Good for you. Are you sure the version that’s running on your server was the last one checked in, things like that. Play it safe. Backup MSDB. Master is another one. That’s where all your users and permissions and things are. If you were to restore your databases to a new clean instance, your applications aren’t going to be able to connect, because the users aren’t there.The last thing you want to be doing is trying to scrounge and figure out what AD groups had rights, and what were the levels of permissions and the local SQL accounts. Having Master backed up is a good thing, but you can also script out all your users and permissions so that you don’t really have to worry about restoring Master.

In order to restore Master, you have to have the exact same revision of SQL server. If you’re running 2008 [inaudible 00:25:13] “10.50.”…and these four digits, you have to be at the same level to restore Master. I always encourage folks, still have it backed up, but also script out your users and permissions so that you’re not having to fight and get to the same patch level to restore Master just to get those logins.

Model…SQL server can’t start up without Model. You tend to leave my favorite interview question is “How often should you be backing up TempDB?”

Carlos: [laughs] Your book threw me for a little curve there. There’s a little section heading on backing up TempDB, and I thought, “What–what–what could be there?” [laughs]

Tim: Right. The thing is, you can’t, even though it’s an option. You can go and try to back it up. It’s going to throw an error. That’s always a fun one. In interview questions, talk about how critical TempDB is and some settings around it. Say, “So, since it’s such an incredible, useful database, how often should you be backing that up?” “Oh, every 15 minutes we replicate it, and we put it in an availability group.”

Carlos: [sarcastically] That would be interesting to see. Along with that Master database…is it being the brain of that, you mentioned scripting those out. I think that if you’re in a fairly static environment, that may work for you. I think most places, however, the documentation around the instances and any changes that have happened since the last script, it’s just not there, it’s not a strong infrastructure.Yeah, go ahead and take that opportunity to back it up. It can be a little cumbersome to get that back, but always better to be able to go back and know you’ve got it than not.

Tim: Absolutely. There’s things where you can repair Master and things like that but sometimes, emergency repair options, you’re going to end up trashing. It’s going to rebuild Master. You’re still going to lose those logins. The organizations I’ve worked at in the past, large regional bank, all of our instances, every single night we would script out the logins and permissions.One person might have changed a local SQL account password during the day, but out of the one or two that might have done that from the hundreds that were on the server. OK, I can triage that. I don’t want to be triaging a thousand people

Carlos: Good point.

Tim: A SQL agent job, output it, save the results of the sp_help_revlogin procedure to a file, store it in a same location that you store your databases. That’s part of your recovery plan. Auditors, examiners, they love that stuff.

Carlos: There you go. You made an interesting observation there. The sp_help_revlogin. Just to take a second, that store procedure is ultimately available through Microsoft support. What that enables you to do is script out with a hashed encrypted password, all the information that you need about a user, so that when you re-created a second instance, for example, it will be just like it was on the first instance.All of that database information and security will be inherited, if you will. You don’t have to worry about orphans users and things like that, which can be a pain. I use it more often in moving environments, upgrading or things like that, but it could be helpful in your recovery. You mentioned you use that in your nightly processes or in your recovery processes?

Tim: Yeah. We’d have a scheduled job that would run, that would output that to a text file, store it in a location that we would have our databases, which was all encrypted and protected and things. Yes, nightly process. If we had to stand up a server in our DR sight from the backup device that was replicated to our DR, we would have instances that were but we wouldn’t have to worry about restoring Master.We’d just restore…run the script. We had the process that would run would first drop all logins and then run the script to add all the logins back just in case from in between the R tests and things that passwords have changed. The user already exists, so it wouldn’t be updating the passwords. We’d just drop logins, add the ones back, restore user databases, restore MSDB. Restoring MSDB is like any other database. There’s no tricks or catches.

Carlos: Yes. It’s very straight forward.

Tim: Restore the user databases and MSDB. We’re up in DR with minimal DBA interference. Actually, we had restore procedures where E admin, that could have access to the server…could run the Power shell script that would do all this stuff for us. It was almost 100 percent automated. It wasn’t too much to it.

Carlos: Yeah, very good. I think that you want to get there over time to be able to do that through…starting off with maintenance plans, go there first. Then, look at scripts. You even mentioned the Power shell scripts. OK, I think we should mention here, ultimately this is all about a chain or data chain.We want to be able to get all the pieces or sections of all the transactions that happen against our database…we want to be able to collect all of those and keep them together to be able to restore back to that point where we want. We use these different methods in order to help ensure we have all of the pieces or the links in the chain together.

Tim: Yeah. The [inaudible 00:31:47] .

Carlos: OK, that’s going to do it for us today. We are going to check the offer from SQL skills and then wrap up. Of course, next week, next Wednesday, episode four will be available where we’ll finish up the conversation with Tim about Backups. Thanks again for tuning in.The SQL Skills team has been very generous in offering a code to each one of you for 30 days of free training videos from their team. What you need to do is to email Paul Randall at [email protected] with the subject line, “User Group Pluralsight Code,” is what you need to put in the subject line. “User Group Pluralsight Code” to get a free trial. You get 30 days. You’ll have access to all of the SQL Skills content that is on Pluralsight.

Again, you can mention in your email that Tim sent him your way, and he’ll be happy to get that information out there. Now, Tim, you were mentioning that there’s how many hours of video are available?

Tim: I want to say it’s like 170 to 180 hours. There’s close to 40 courses, lots and lots of good content.

Carlos: Wow! Yeah, I can imagine that that is lots of deep dive stuff, particularly if the SQL Skills folks are known for their deep dive training. Do you have any courses on Pluralsight? I failed to check.

Tim: I’m working on one now on consolidation best practices and techniques, so hoping to have that wrapped up in the next 30 to 60 days.

Carlos: OK. Very good. I know those can take a long time.

Tim: It’s amazing how much time goes into putting together your slide deck and then doing all the recording. If you thought user group and SQL Saturday sessions, past summit sessions were intense, the Pluralsight, because you want to have it perfect and just right, it’s a labor of love.[music]

Podcast Episode 02: Extended Events

Argentina SQL Saturday

1400I was in Argentina for the SQLSaturday event earlier this year and made friends with Javier Villegas a 20 year veteran in the DBA world.  His session that day touched on extended events and while it is a bit of an advanced topic, I thought we could sit down and discuss it.  I also include a few sounds I recorded from my trip.

Show Notes

Javier on Twitter
Javier’s Blog
2008 GUI for  Extended Events
Books on Line: Extended Events
SMS toolpack

Transcription: Extended Events

Carlos Chacon: Hello there, compañeros, and welcome to episode two of the “SQL Data Partners” podcast. I am Carlos Chacon and I want to welcome you to the show. We’re going to hit some highlights on Extended Events. Now, today’s show has been a long time in the making. Some of you may know that I spent some time in Argentina as a missionary in the late ’90s for my church.

When Mariano Kovo and the Argentina team said they were going to host the first “SQL Saturday” in the country this last May, I had to be a part of it. Almost 20 years after I’d been there the first time, I went down and participated in the SQL Saturday. It was a great event. While I was down there, I made a new friend in Javier Villegas, who has been a DBA for 20 years and he agreed to come talk with me, so I’m super appreciative of his time.

Before we get started, there are a few sounds I want to share with you just really quickly from Argentina. As always, I hope you enjoy the episode. If you like what you hear, please rate our program and give us a review. If you want to hear something on our show, you can always hit me up at [email protected] or on Twitter @carloslchacon.

Children: SQL Data Partners.

[music]

Carlos: Javier, welcome.

Javier Villegas: Thank you. How are you doing?

Carlos: I am doing great and I appreciate you asking. It’s been great to be here in Buenos Aires. Now you’ve been here for some time and I know that you’ve been a DBA for the past 15 almost 20 years now.

Javier: Almost 20.

Carlos: Let’s see, who are you working for now?

Javier: Right now, I’m working for Mediterranean Shipping Company, the container company. We ship containers all around the world.

Carlos: Buenos Aires has a large port. Very important port to a lot of that shipping. You mentioned earlier, you’ve been a DBA since 6.5, right, and very involved in the SQL Server world. Today you gave a session on monitoring and one of the things was “Extended Events.” That’s actually the focus of our podcast today is Extended Events.Help me understand, what are Extended Events and what are we using them for?

Javier: Extended Events is a lightweight mechanism to get feedback on events and information from the SQL instance plus the operating system and the whole environment. It’s so easy to get the vital signs of our environment with Extended Event.

Carlos: You mentioned the events and some of that information. How is that different than the counters at PerfMon? Why would I choose Extended Events over PerfMon?

Javier: There are kind of different things. If you want to compare Extended Events with the tool, you have to take profiler because it’s deeper into SQL and it allows you to get the whole activity that you have in SQL. The good thing is that you can correlate some of the counter and the standard you get through Extended Event with the OS events. Probably you can match the information that you get through Extended Event with performer monitor and the counters.

Carlos: Would it be fair to say that the benefits of the Extended Events is that I can see both the query or the action inside of SQL Server, with some of the corresponding performance monitors like CPU or Disk or Memory?

Javier: Among other things. With Extended Event, you can get deadlock information. You can get SQL errors, you can track for long running statement or queries, while I mention errors. Whatever you can do with profiling or whatever you want to trace audit information that log-in log-out audit failed, et cetera, you can get it through Extended Event.

Carlos: You’ve mentioned the SQL Server profiler, why would I choose to use Extended Events when I have a profiler? I know how to use that. I open it up, I use my template, I click go and away I go.

Javier: One of the uses of profiling is that when you are on a difficult situation and a performer problem, you need to start doing something, and most DBAs the first thing they do is they start profiler. If you don’t know well how to use it or for some mistake you don’t set the fielders in the right way, you are going to be introducing additional problems to the one that you’re trying to solve.

Carlos: That idea of the watcher effect. While we are trying to monitor we’re actually causing more load on the system.

Javier: The benefit of the Extended Event is that it’s very lightweight mechanism. It’s running in the background and you can consume the output quite easily without affecting the performance.

Carlos: We talked about using Profiler, just to go back for a moment. For those of you who are still using Profiler, we hope that you’re using it in a server-side trace. If you’re just clicking “run” after you see the template, that watcher effect will be compounded. There’s a mechanism called server-side tracing, which may not be quite as lightweight as Extended Event, but definitely a lighter footprint than just running from the plain template.We’ve collected some of this information, and I know even for myself getting into Extended Events, there’s been a little bit of a learning curve. Take us through some of the concepts that we might need to know in order to become more familiar with an Extended Event or how to set up that capture of the Extended Events.

Javier: First of all, you have to define your Extended Event session. Within the definition, you have to specify what package or what kind of events you want to track. You have to specify which information you want to retrieve, like in Profiler when you select the columns that you want to get.Finally, the predicate. If you want to specify a particular filter, for example, if you’re capturing SQL errors, and you want to filter some error numbers, you have to specify it through this predicate. Finally, you have to define the target. Where you are going to store the Extended Event.

Carlos: In order to set that up, I have to define a section. I define the events that I want to capture in the session, I put some filters on that to only give me those, under certain conditions that I want information back, and then where I have to save the output from that. I know some part of the learning curve was that was only available via TSQL.

Javier: Yes.

Carlos: Is there now GUI interfaces to allow me to do Extended Events?

Javier: The first version which was back in SQL 2008, it was a hundred percent TSQL, no user interface at all. I believe there were some third party flaggings but…

Carlos: I think Jonathan Kehayias and Company actually put together a little GUI interface.

Javier: Which was actually good, but then back in SQL 2012, and now with SQL 2014, it is introduced within management studio. You have under management Extended Event, you can create the session, define your filters, the output, et cetera, through this user interface.Then you can use the same mechanism, through management studio, to consume the output. You can watch the data live as it is happening, and also you can go back and see the history and you can do your analysis with them.

Carlos: Are there standard templates you can use in the GUI?

Javier: You have your own user-defined template.

Carlos: Normally you use your own user-defined template?

Javier: Yes.

Carlos: I believe my experience there was I was using a certain template and it wasn’t the default template, like it wasn’t Profiler, but there is a general system performance template.

Javier: Correct.

Carlos: You have to go in and choose that one.

Javier: Yes. Choose it and do your own customization.

Carlos: Is it fair to say that once I set up these events, I’ve set up my WHERE clause or my filters if you will, for those, that once I start collecting that data, how often do I collect that and is this something that you’d look at every day? Under certain scenarios, or when are you most using this?

Javier: In my case, I use it to track. Two of the examples that I have is deadlocks. I have an Extended Events session just for deadlocks, so it’s running 24 hours a day. In addition to that session I have a SQLAction alert which get fired through the deadlock event, which execute a SQL shop, which executes a use of procedure by which read the information from the Extended Event, naturally, from the file.All the information is stored into XML format, so what these procedures does create a user friendly output and send it to the DBA through email.

Carlos: So that agent portion and then getting that email, that’s not part of Extended Events?

Javier: That is not part of Extended Events.[crosstalk]

Carlos: …if you have to customize that…

Javier: Correct, yes.

Carlos: You could open the XML in the GUI window.[crosstalk]

Javier: Yes, it’s a standard XML so you can open it and analyze what’s going on. In this case it deadlocks, you can see the deadlock victim, the killer, the results that’s involved within deadlock, et cetera. So you have all the information you need to troubleshoot that particular event in one output.The other case that I have is a user defined Extended Events session to track SQL errors. In my case, when we upgrade the application that we have in our company, it’s where I take most of…When I use this Extended Event session the most, why? If there is any SQL error that is happening right away after the upgrade, I can easily catch it, go through the proper marginal developer to take a connection.

Again, the Extended Events session can already open all the errors that are happening live. In parallel, I check that output and if I can see an error that is a hard one, or an important one, I take it and go to the proper one.

Carlos: I know sometimes chaining that together, particularly if you work very good on your filters, chaining the different events to a certain user experience, for example, was somewhat difficult. Is the filtering in Extended Events better than Profiling?

Javier: It’s different. Profiler is a quite mature tool. The good thing is that you have a lot of help. Whatever you were doing through Profiler, whatever you are checking to select, you may have a little help telling you what you are doing.In Profiler, let’s say that, if you are very, very, very good at Profiler you will find these filters quite easy to work with. Otherwise, you may have to read a little bit, but when you get used to work with the Extended Event, probably you will never use Profiler again.

Carlos: I was referring more to, once I have my output and let’s say I was just doing a system profile and it was generating lots of different users. Maybe there were even multiple databases that were called during that time. I’m only really interested in one database, or maybe even a specific user. Is it easier to filter that information?

Javier: Extremely easy. It’s just modifying the work tools. Like a simple select statement like you say were, “Log in, name, equal your user.” It’s quite easy.

Carlos: We’ve gone through that process, there is a little bit of a GUI. How would you recommend — for someone who’s not currently using Extended Events — they begin?

Javier: First of all, as I always recommend the books online. Books online is where you’re going to get all the information you need starting from the very basics, through complex cases, and through cases that you can even use in your production environment, probably with some little tuning, or assessment for your own environment. Books online is the best source for information.

Carlos: Do you have any blogs that you follow or folks in the community that have talked about Extended Events that you have found helpful?

Javier: Yes, there are some. I have my own blog. It’s JavierVillegas-DBA.com. You’re going to find some information in there, plus some examples of Extended Events session, the one that we were talking today here at the SQL Saturday.

Carlos: Thanks. I think that’s been a nice overview information. Obviously with any tool, we need to get in there, start playing with it, and then once we see some examples, then it will be easier to make the logical conclusions to the next step.We do appreciate that information, and so now we’re to some of the favorite things and then we’ll get to your DBA warrior story here in just a minute. Tell us, what is your favorite SQL tool? This tool can be either paid or for free, but tell us a little bit about it, whether it costs money, and how you use it.

Javier: First of all, my favorite tool overall is SQL Server Management Studio.[laughter]

Carlos: Which we all know and love.

Javier: Yeah. There is a plugin which is called SMS tool pack, which is a fave. Flagging, which makes the lines a little bit easier to the DBA because you can specify connection callers for your different servers. You can have shortcuts to your script, some pre-defined templates, something that you can use as an addition to the plan analyzer to get the deeper information. It’s a nice tool.

Carlos: It’s an administration, techniques, plus, OK.

Javier: It’s an administration, plus developer. It’s something that’s trying to fill those small holes that Management Studio has, and it’s a great tool.

Carlos: As a DBA, what’s your favorite thing to do in your role as a DBA?

Javier: My favorite things at DBA are performance tuning and disaster recovery. In terms of performance tuning, I love when users, developers, managers come to me saying I have this particular problem. This statement, this procedure, this application, that is not running as I expect. So I need to make it better.I love those challenges to get that piece of code and to make it better. To run with less resources and what everybody wants, running faster.

Carlos: You get to be the hero.[laughter]

Carlos: Javier made the database faster!

Javier: I really love that. The whole DBA administrations thing is right.

Carlos: I actually took two classes in college — DBA related classes — and both classes I hated and I swore I would never be a DBA. I was going to be a network engineer, that’s what I wanted to do and a funny thing happened and here I am as a DBA. I’ve enjoyed it, too.

Javier: Let me add something to that. In my early [inaudible 19:58] days, I started with the Microsoft certification process back in Windows NT 4 or NT 3.551 something and at the time you have the basic courses plus elective one.[crosstalk]

Javier: One was Exchanged and the other one was, I believe, Active Directory and there was SQL. I believe that I choose SQL because the other ones were full or something like that. It was like five days and at the second day, I say, “I will never work as a DBA.”[laughter]

Carlos: Never say never.

Javier: Never say never. Now after 20 years… [laughs] I remember that day.

Carlos: Yeah, I think that was my experience as well and hopefully through things like this podcast and some of the videos we have a great SQL server community that it will help lessen the blow, if you will, the learning curve in order to be a DBA because it is a very neat thing.The last piece that is your database administrator warrior story. This is something that has happened to you that…you get to be the hero. Something that was very difficult. I’d be interested to hear your story.

Javier: As I mentioned before, I love the high availability and the disaster recovery techniques. My company, we have many agencies with AlwaysOn and also that is mirroring a few years back.We had a couple of agencies, which suffer major earthquakes like in Chile and New Zealand. The good thing is that the disaster recovery solution configured and implemented there allowed us to have an operation quite good. We didn’t lose any piece of data because we have a remote data center in where we were hosting the replica, the secondary.

Carlos: Both of those earthquakes were very, very large, lots of devastation. I can only imagine right on top of that is to lose that data, lose that information. We’re not talking about people’s jobs that are impacted. They get their lives back in order, their homes back in order and then they have to get back to work and put all those pieces back together again.Being able to have that high availability available so that once they are ready to come back, it’s there, they can hop right in and just continue working. I can imagine that that’s pretty neat, that that was able to work.

Thank you for sharing that story.

Javier: Sure.

Carlos: Again, thanks for being here on the podcast, it’s been great talking with you.Before I let you go, I wanted to give you another way that you can learn about SQL server.

Hello there, compañeros, I want to tell you about a unique SQL Server training opportunity that is unlike anything you’ve encountered and as a listener of this podcast, you’re entitled to a special offer. SQL Cruise is a premier training experience set in the most unlikely of places, a cruise ship. Tim and Amy Ford have created a wonderful culture on SQL Cruise.

With five or six technical leads from various industry sectors, you and roughly 20 other students will talk shop in classes while at sea and while you are in port, you can enjoy the wonderful world we live in, either by yourself or with the group, the beauty of SQL Cruise is the unique relationships you will form with other SQL cruisers.

Worried about leaving your spouse? Bring them along. I did and she had a great time with the group. In fact, I have been on SQL Cruise twice and I’m looking to go a third time. You may be wondering if this is serious training and I can assure you it is as good as any conference you will attend on land.

It never crossed my mind that I could be talking about SQL Server with several Microsoft MVPs on the beaches of St Johns. I know you must have other questions so I suggest you head over to SQLCruise.com and check out the site.

Once you are satisfied and you want to sign up, when you get to the question, would you be so kind to share the name of who referred you and what source material lead you to us. You enter SQL Data Partners and Tim will ensure you get $100 off the training registration.

This is a great offer and I hope this $100 savings will entice you to check out the site. I did and went on the Caribbean cruise and had a great experience and I know you will too. Maybe I’ll see you on board. So head on over to SQLCruise.com and use the code SQL Data Partners to save $100.

[music]

Children: SQL Data Partners.