In this episode of SQL Data Partners, I chat with Amy Herold about making the move from development to database administration. Amy’s presentations at SQL Saturdays include Deadlock, Block & Two Smoking Barrels: Breaking Down Blocking and Deadlocks and Making the Leap from Developer to DBA. Amy started as a C#/.NET developer and moved into the database world as an “accidental” DBA.

Amy shares her tips from the trenches, including:

• The transition from row-based thinking to set-based thinking
• How to manage developer access in production
• The database performance activities new DBAs need to master
• The importance of getting code reviews outside the developer team
• The tool Amy relies on as a DBA, and the one event you shouldn’t miss
• Why Amy believes that changing jobs frequently can be good for your career

About Amy Herold
Amy Herold is a SQL Server DBA, instructor, and PASS speaker. She began her career as a .NET developer but eventually moved to SQL Server Database Administration, where she is currently pursuing her MCSE in SQL Server. Amy is a regular attendee of SQL Server User Group meetings in North Texas as well as SQL Saturday events across the US.

Show Notes

Resources
Amy on Twitter
Amy’s Blog
SQL Sentry monitoring
SQL Cruise
Making the Leap from Developer to DBA, 24 Hours of Pass
Carlos L Chacon: This is “SQL Data Partners” podcast. I am Carlos L Chacon your host and this is episode 33. Compañeros, welcome to the show again. Thanks for joining us on the SQL Trail.

This podcast is dedicated to SQL Server related topics designed to help you become more familiar with features around SQL Server and how you might use these, or ideas and how you might apply them in your environments.Today we are talking about making the change from the developer role to the DBA role, and how those roles or responsibilities differ between the two positions.

My guest is Amy Herold, a database consultant with Fortified Data. Today’s episode is brought to you by SQL Cruise. Learn. Network. Relax. Grow. Just add water.  Check out sqlcruise.com for more information. Compañeros, always good to have you, welcome to the show.

Amy, thanks for being here today.

Amy Herold: Thanks for having me.

Carlos: One of the things we wanted to talk about was the roles and responsibilities, the differences between the DBA and the role of the Developer. Now, as you were coming up or began your career in IT, you started on the developer side, is that right?

Amy: Yes. I stared out as a C# and ASP.net developer. I only came into SQL Server when I landed a particular position, and they asked me if I could write SQL Server Query and sure, yeah. I hadn’t written one before, but it’s code, I can write code so ”Why not, I can do this.”

Carlos: There you go.

Amy: And just pretty much dove in head first. That’s my approach to many things.[laughter]

Carlos: There you go, roll your sleeves up and get to work.

Amy: Exactly.

Carlos: I think it’s a familiar track for a lot of Developers, in that they’re on application, they’re dealing with the back-end, and then all of a sudden that back-end becomes a little more a real deal and more attention needs to get paid to it.One of the first things that you talk about in some of your discussions or your presentations about this topic is the idea of data integrity or availability.

Amy: Yeah. One of the things that I talk about in my sessions is data integrity and availability. Someone told me once that as a DBA, ”You are the keeper of the Gold,” and the first answer when somebody says, ”I want to see the Gold,” your first answers always ”No.”

Carlos: [laughs] Interesting.

Amy: They ask again. Second answer is always “No.” When they come back a third time with a more refined response and they say, “I want to see this piece of gold, I just want to look at it.” “OK, you can look at the gold. You can’t modify the gold — you can’t touch the gold, in any way you can just look at the gold.”

Carlos: Yeah. So, it’s interesting, Ron Deskins who’s one of our compatriots here reach-on Virginia with our user groupie, he talks about the differences right between the developer and the DBA. Ultimate organizations bring in a developer, because they want to change something.As you just described, as a database administrators, a lot of times they want us to keep it the same. [laughs] As though there is kind of these two competing theories or ideas there. Sometimes that can lead to a little bit of budding of heads.

Amy: Yeah. I have budded heads a numerous times. Such as in over permissions or what having though, and it’s your whole world and I get where they are coming from. As a DBA, my responsibility is to make sure that data is there and available for the business.If I grant out too much in the way of permissions to production and a developer takes celebrities. That can prove problematic and quickly becomes an issue. The next thing I know I am stuck having a nice late night restoring a database. No fun is had by anyone.

Carlos: Now surely in only giving the read access you are talking about just to production environments, that wouldn’t necessarily apply to other environments. Would it?

Amy: Oh it just depends. It depends on the company, it depends on the situations. I cleaned house in the last place I was at. I’d have a very long day and I’d run into several developer created issues, and towards the end of the day there was an issue in production.Some data had apparently been modified at some point. I also couldn’t get a backup from that time period. That was the other problem that company had. They don’t keep their backups very long.

So, I couldn’t get a backup around that time period, I was upset by that. But anyway, the data had been updated. I had my suspicions on how it possibly have gotten updated. Suspicions are nothing, I have no concrete proof, so I just went in and cleaned house, both in production and pre-prod.

I’d had enough problems that day in our pre-prod stack that I was done, [laughs] and nobody’s breaking replication anymore.

Carlos: Yeah, there you go. I think that’s a good point. As you mentioned, that responsibility for availability does lie with the database group. I think the idea of formalizing some processes around how that data would get modified or changed is definitely in the wheelhouse up there.

Amy: Yes, absolutely. The only person making any changes in production should be the DBA, in my opinion. No developer should have access to do anything like that. At the very most, they should only have read access in production, even if that could be for a limited amount of time. It depends on the production environment.Is your environment heavily transactional? Is somebody getting in there and running all kinds of query, reason running amok? How is that going impact your production environment?

Carlos: Or sensitive data, things like that.

Amy: True. Yes, or a sensitive data PII. If that’s viewable, then yeah, access definitely needs to be limited.

Carlos: You had mentioned backups there, and I think part of this process…A, number one availability, making sure you have a backup. This is where I think developers can provide a lot of value or can get up to speed very, very quickly when it comes to the database administration tasks, and that is taking your knowledge of coding, applying to best practices, and then using that in some of your administration tasks.

Amy: Oh yeah, definitely, absolutely. Scripting out tasks and making things. As a developer you already have that coding background. You have a strong foundation for being a DBA and being someone that can script out things and automate things, and essentially make your life easier as a DBA.

Carlos: Right. I think, as the role of the DBA continues to change, nobody wants a DBA that just does backups and restores anymore, right?

Amy: No, no.

Carlos: More and more, you’re going to be asked to do more and more things, so being able to very quickly get up to speed and just insure that they’re running, that they’re working, and then being able to move on to other things.

Amy: Exactly. Yes, exactly.

Carlos: The one area that we had discussed — that developers might need a little change in thinking — and that is, when it comes to set-based theory in their scripts or in their logic.

Amy: Yes. It’s definitely a change for a lot of developers who may primarily be doing, say, C# programming or something along the lines where they’re not used to doing set-based development. They may turn around and write something — write a cursor, and do something within a cursor that, if they gave it some more thought, they would find that this could be set-based.I had one situation where I had a process that was running. Not only running overnight. It was part of a job. There were scripts that were called from a job and would run overnight for hours and hours and hours, but that it would also…The same code was called from the website, and it was causing websites timeout in certain cases.

I started looking at that. There were cursors and cursors, could have been even another level in of cursors.

Carlos: Yikes.[laughter]

Amy: Yeah. First I revised that to…I put in while loops instead. That got a little better, but not great. From there I started looking at the code some more and removing the looping altogether, making the entire thing set-based.

There were a couple sections where I wound up writing correlated sub-queries that ran really fast and basically brought that whole thing together. I think probably the last thing I wrote for that.

The process ran. Obviously the website was timing out, so it was running it over a minute. I got it down to, I want to say, 10 seconds. Maybe it was less than that. The developer who wrote it was like, “How’d you do that?” and came back at me. He was very proud of his work, too. He touted it in a meeting.

Carlos: Sure.

Amy: Was like, “I wrote that.” I’m like, “That…

Carlos: It’s kind of complicated to think about it in terms of rows, I think, generally, is what developers tend to kind of lean towards that.

Amy: Yeah, they do. It’s not necessarily that way. I was working on some code recently that was part of a cursor. There was a store proc and it was pulling this massive dataset and then passing the rows from that dataset one at a time to another store proc which had a cursor in it. Oh, it was ugly.I was refactoring that and it got me thinking about some different things. I wrote some things in ways that I hadn’t thought of before.

Carlos: I think it might be important to notice and think that change is usually thinking about columns instead of thinking about rows.

Amy: Yeah.

Carlos: That, to me, has been the best concept or idea when it comes to set-based theory.

Amy: Yes, I hadn’t thought about it like that, but, yeah, that’s definitely true. You’re only pulling back the data that you need in most cases. Depending on the data that you’re pulling back, how can you group that? How can you pull that together to get the same results in a set-based query versus running it one at a time from a blue broad cursor?I feel like many times it’s a matter of thinking outside the box and thinking in terms of doing it set-based, just to get there.

Carlos: One of the next components that the DBA is in charge of that’s not necessarily in the developer’s wheelhouse. That is the underlying architecture of the system. Sometimes, if we don’t necessarily think about the capacity planning aspects of an application, how big is this thing going to get? How many users is it going to have?

Amy: Yeah, and that’s something as a DBA you have to think about. You have to ask those questions ahead of time, be it an internally developed application or a third-party application that the company’s purchasing. Can they give you good numbers on what’s going to be needed six months from now, a year from now?Also, as far as capacity planning, getting a read on your databases and your growth and things like that, and tracking that information over time. End of the year, when someone comes to you and says, “We need to find out how much space we’re going to need for the next year,” you at least have some basis to give them, “OK, we group this much over this year, now, so we need at least this much space next year, maybe a little more.”

Carlos: Right, exactly. In another episode, talking with Erin Stellato, she talked about the backup sizes being an indicator of that. That’s an easy way to tell the growth of your environment.

Amy: Yeah, I hadn’t thought about that way. Yeah, definitely, if you track your backup sizes. I guess if you’re keeping…Yeah, that’s one way. I would say you’d definitely have to track that just depending on how long you keep your backups.

Carlos: Exactly, right. Even looking at the history, and I guess if you’re not trimming that up. I think we talked a little bit about baselines, recording some of that data, and it just that — taking a…[dropped audio]

Carlos: …a quarterly basis or what have you, and getting, “OK, what are some of the stats for my environment right now?”With that capacity planning, and we talked a little bit about some sizing, it would include CPUs, things like that. Then we also kind of get into the requirements around performance, we had talked a little bit about just even changing that from row-based to set-based theory, but there are some other performance requirements for the DBA must adhere to.

Amy: Performance is always important in an enterprise environment. You have indexes. You have index fragmentation to deal with as a DBA and taking care of that. Statistics, making sure those are up to date. You may have poor performance if your stats are out of date. It’s not just code.My primary focus when looking at performance, in many cases I’ll look at the code first. Most of the time, it’s bad and needs revision, or can be redone in some way, making it better.

Then there is the indexes to consider. Maybe you’ve made a change. It could be a small change, and the index that exists already may not be able to handle that. You may need to modify that index.

Yeah, performance is a big topic. Also, on performance, it’s not just code. It’s not just indexes and stats. You have hardware to consider, CPU memory to consider. There’s just all different aspects to performance. That’s the very large topic. I think most DBAs don’t go past the indexes and the stats, and that is even if they get to coding.

I’ve talked to many DBAs in interviews. I asked them how to troubleshoot a long-running store procedure after they mention looking at index and stats. It’s like, “Well, what about the code?” and they said they’d send it back to the developer. It’s not always the case.

There’s one interview I said it got hit by a bus.

[laughter]

Carlos: Yeah, what are you going to do now? Right?

Amy: Yeah, what are you going to do now? You got to fix it. He’s not here, so it’s up to you.

Carlos: Potentially, a good happy medium there is, yes. As a DBA you may not have time to sit there and refactor, that’s their procedure, but if you can at least point the developer in the direction to say, “Look here. Here’s the little section of a code that is the worst performer,” or “taking too many reads,” things like that.At least you’ve then given them something to work with rather than, “Here, go do this again.”

Amy: Yeah, and that’s definitely something to look at. There’s that episode on “Saturday Night Live,” where they have the IT guy and he comes in and says, “Oh, just let me fix it.”[laughter]

Amy: A lot of times that’s just…that’s me. I’m like…[laughter]

Carlos: There you go. That is the consultant coming out of you there, then.

Amy: [laughs] Yeah, and that’s just kind of how I operated. That’s my train of thought. I’m like, “Move, I’ll fix it.” [laughs] Yeah, you’re right. It’s good to send things back to the developer because they need to expand their skillset as well.They need to learn and they need to be able to do things differently if necessary, and if you can give them more information and educate them. Then that’s going to help you with your job as well as make them a better developer.

Carlos: As the developer moves into that DBA position, another area that they might come in contact with is the reporting or the data warehousing groups or functions. I guess groups if you’re large enough to have a group. What are some of the interactions you’ve had with the BI on reporting space?

Amy: I haven’t had a lot of BI interactions. I’m just thinking of my lab position. We had a data warehouse team, and their primary responsibility was the development of processes to pull everything into the data warehouse. I was involved with them in the aspect of deploying their processes and getting those out.In that particular situation, I should have had more oversight into some of the stuff they did, but that wasn’t the case. It just wasn’t how things were. We also, in that case, had a parallel data warehouse, or an APS appliance, analytic platform system.

That was an interesting thing to be able to get to play with and work with, and find out how different that is from just a regular SQL Server environment. As far as BID warehousing, for a DBAs perspective, occasionally you have to generate reports, so you might be doing things that are BI related, depending on what your task was.

It could be just a report on server stats or just something simple, but something that other people are going to view as…

[dropped audio]

Amy: …group, so you have to make sure that they can understand it, they can take in the data. Also, you’ll have to make it probably look nice.

Carlos: I thought it was interesting that you used the word oversight. That was kind of my thinking. I saw your BI in data warehousing components there. You want to provide some oversight. You want to provide maybe some architecture and things.In some point you’re probably going to want to draw the line and just communicate with your management team that you may need some additional resources, or at least some additional training to get into that space because it really is a different animal.

Amy: It is. It really is. We had a number of people come in, in that case, that were leading the data warehouse team. When I say I probably should have had more oversight, those folks on that team were not submitting code for review. They did not do code reviews, or they had code reviews just within their team.Like, “Nope, that’s not a code review.” It needs to be reviewed by someone from the DBA team.

Carlos: True.

Amy: There was that. That data warehouse, it was interesting in the fact that we did have the APS appliance, but there were certain little nuances to it and everything. I thought things could have been done a lot better.

Carlos: Sure, and it’s interesting that you bring up the different roles. We’ve talked about integrating with developers, the data warehouse team, even users. I think that is the other component there, is the communication involved in the DBA role by necessity just has to increase because you’re dealing with so many different people and processes that affect the whole team.Right? We are here talking about data warehousing.

Amy: Yeah. There’s the data warehousing. We had the reporting team. Then we had, also, other developers working on the app side who were doing T-SQL code. Our DBA group was dealing with various teams just throughout IT to accomplish everything that we needed to accomplish on a daily basis.

Carlos: Yeah, and some of the decisions you’re going to make are going to affect those teams, so data refresh policy, how systems interact versus like, “We’re going to use link service here. I have to use an ETL process here. We’re going to use database govies here.” Right?

Amy: Right.

Carlos: That’s the way people can then interact and use that data as well.

Amy: Yeah. It depends on your environment. It depends on the situation. It depends on who’s wanting access to something and where they’re wanting access to. You mentioned link servers. I had one report writer want to do an update, or do something across a link server, and the permission didn’t exist for it already.Oh, he was trying to do a delete instead of…

No, he was trying to do a truncate instead of a delete. [laughs] I’m like, “Just do the delete.” The report writer actually reached out to a member of the business, asking if it was OK to do a delete instead. I’m like, “They don’t care. They want it done. They don’t care how you do it.” [laughs]

Carlos: Yeah, make it happen.

Amy: I kind of [laughs] segued there, but yeah it just came to my mind. I remembered that happening at work.[laughter]

Amy: We don’t care. They just want it done.[laughter]

Carlos: For all you developers out that that want to make the jump for the DBA position, one of the things you need to do is go and check out sqlcruise.com. Amy, are you a former cruiser, by chance?

Amy: I wish. I would love to go. Oh gosh. I was talking to some people about it last weekend, and I was seeing all the pictures coming through on Twitter. I’m like, “That looks fun.”

Carlos: Yes, Tim and Amy Ford have put together a most unique training experience and, of course, they want you on board. Listen to the podcast, get $100 off the cost of admission. You can get those details at sqldatapartners.com/sqlcruise. Learn. Network. Relax. Grow. Just add water.For more information about events in 2016, you can check out sqlcruise.com.

Amy, let’s get into the SQL family questions. Thanks again for this conversation, I should say before we move forward. I think it’s been good. Those who are more interested in taking over those database responsibilities can have a couple of things, potentially, on their list to look at for this year.

When we talk about how people work, one the things I always like to ask is what their favorite tool is. This could be a free tool, paid tool. What tool do you like and what does it do for you?

Amy: Currently, in my current position, we are using SQL Sentry monitoring. That’s a tool I like. It’s definitely better than one of the other monitoring tools out there, paid monitoring tools that I’ve used in the past. It seems to be a little more robust than previous one I’ve used.I will say one tool that I’ve used before that, since I really like to get into SQL code and look at things, I would SQL Compare. I even wrote a power shell. Using the command line version of SQL Compare, I wrote a power shell script that would go out, for developers’ deployments. I would generate the deployment scripts, the roll back scripts, a report on the differences, and dump all that out.

That was trying to maintain control over the code that was being deployed. I eventually was overridden because the developer said, “Trust our code,” and I was saying, “No, no.”

[laughter]

Amy: No, we don’t want to, but I was overridden. I still wrote the script and SQL Compare is, I think, a really neat tool.

Carlos: It is. It is a very cool tool. You just inherited one million dollars. What are you going to do with it?

Amy: Let’s see, aside from thinking of Austin Powers and “One million dollars.”[laughter]

Amy: I guess I’d pay off my house and…

Carlos: There you go, a smart decision.

Amy: …very likely invest the rest.

Carlos: Not even a little bit of splurging? Not even a SQL cruise, Amy?

Amy: OK, I would probably go on some cruise. I would. I would pay off my house, go on to a cruise, and then anything left over I would invest.

Carlos: Then invest the rest.

Amy: Got it. That’ll take a long term here.

Carlos: Very good, very good. Yes, good capacity planning there.

Amy: Yes.

Carlos: You mentioned the previous position that you had. You’re now in consulting.

Amy: Yes.

Carlos: What’s one of the best pieces of career advice you received over your career?

Amy: I think, aside from the, like I said before. Someone once told me that you are the protector of the gold. Gosh, I just thought of another thing. One piece of career advice someone told of that I still stay in contact with, they said that, “You learn more by not staying in the same place.”If you want to take on a bunch of contracts — three-month, six-month contracts — you’re going to gain more experience moving around more frequently than you will if you stay in the same place. I think in many cases that’s very true. I also think you are your own, you can be your own worst enemy. You can limit yourself in ways that you don’t even realize.

At the last place I was at, we hired a DBA who’d been in the same place for 12 years. When I told him he would do good to script things out instead of using the gooey, and pointing and clicking…He’d been a DBA for 12 years and he couldn’t write the simplest queries. That was kind of sad.

He limited himself by staying in the same place, but he’d also limited himself by not taking the initiative to do more and do things differently.

Carlos: Sure. Kind of re-invent himself and advance his career there.

Amy: Yeah. He made the choice to not do those things. I just didn’t see how you could possibly get through being a DBA for 12 years and not do a lot of those things.

Carlos: Everyone has their own unique experience I guess on the SQL trail.

Amy: I guess. [laughs]

Carlos: Our last question for you today, Amy. If you could have one super hero power, what would it be and why would you want it?

Amy: I guess invisibility.

Carlos: There you go. [laughs]

Amy: It’s even better than being the fly on the wall. [laughs] My mom always said that you can always learn a lot more just by being quiet sometimes.

Carlos: There is that.

Amy: You’re visible, you’re quiet, you can learn a whole lot.[laughter]

Carlos: That’s a whole lot of dirty laundry going to come your way there, Amy.[laughter]

Amy: I have some dirty laundry, but you can learn. Sometimes you learn a lot more just by taking things in and listening, and not saying anything.

Carlos: There you go. That is good advice. Listening. That’s why you have two ears, they say. Right?

Amy: Exactly. That’s why you have those two things that God gave you, and [laughs] you need to use them. That and the one in between. [laughs]

Carlos: Amy, thanks so much for being here. I do appreciate it.

Amy: Thanks for having me.

Carlos: Compañeros, thanks again for being here. If you have something that you’d like to…that you think…hmm, let me say that again. Compañeros, if you have something that you’d like to add to our list that the developer should know as they transition to the DBA, you can leave it on the page for today at sqldatapartners.com/podcast where you’ll see today’s episode.You can link there and leave your comments on the page. Of course, if there’s something that you want us to talk about on a future episode, you can reach out to me on Twitter, @CarlosLChacon, or shoot me an email at [email protected].

We do appreciate you being here again. If you like today’s episode, we invite you to leave a comment on iTunes or Stitcher so that other people can find the episodes and enjoy them as well. We’ll see you on the SQL Trail.