Listener Eduardo Cervantes wanted to get our take on what developers should do when they get admin access to a database. We take on this challenge and I give 5 points you might consider if you are a developer with admin access to the SQL Server. As Uncle Ben in Spiderman quotes, “With great power comes great responsibility.” We hope you use yours wisely.
“The question you should be asking yourself is, why was this not already enabled? What is the downside to implementing this course of action?”
“Who owns the code? We all do. The same could apply to the database. Documentation then comes into play.”
“Rolling back is very hard when you don’t know the original state. ”
“You may think you understand the consequence, but then if there’s unintended consequences, give yourself a way to get back.”
“Patching, security, backups, boring. Perhaps, but they need to be taken care of and they do become important.”
“Just because you have admin access doesn’t mean that everybody else should have admin access.”
Listen to Learn
02:09 Compañero Shout-Outs
03:39 Tips & Tricks
06:42 SQL Server in the News
09:48 Intro to the topic
10:12 We are going to assume there is no DBA present.
10:32 This could apply to both production and development
10:50 Do you know the consequence of your action? You have some control of the behavior of the system—just make sure you understand the consequences.
12:28 Who is the owner of the system? Does that person now what you are doing? Shared ownership-new concepts in programming.
14:19 Rollback is hard when you don’t know the original state. Always give yourself a way to get back. Containers. 😊
16:26 Don’t forget the small stuff. (patches, security, backups, etc) Just because YOU have admin access doesn’t mean everyone should.
18:28 Install Database Health Monitor
19:33 Closing Thoughts
**Sound effects have been removed from the recording**
*Untranscribed Introductory Portion*
Carlos: Compañeros! Welcome to another edition of the SQL Data Partners Podcast. I am Carlos L Chacon, your host, and this is Episode 123. We would like to excuse Steve Stedman. He’s not with us this episode, so that’s unfortunate. But the good news is, he’ll be back next episode. We’re looking forward to having him. We’re doing something a little different this episode. Of course, if you’re a long-time podcast listener, you won’t notice, however, we are trying to incorporate some video into this episode. So, if you’re joining us via YouTube, welcome. And of course, for you long-time compañeros, welcome back to the program. It’s good to be back with you.
Today’s topic is five things developers should know when they get admin access to a SQL server. For a lot of you developers out there, you don’t have DBAs, we know that there are more and more of you listening to the program. So, actually in 2018, we are trying to gear more of our content to you so we can help bridge that gap and help you be better prepared as you’re trying to tackle some of these administrative tasks while you’re developing. This topic was suggested by a listener, Eduardo Cervantes, so we thank Eduardo for suggesting this topic and sending it our way. So, before we get into that, we do have a couple of compañero shout-outs. We want to give a shout-out to Aaron Hayes. Aaron Hayes is ready for the SQL Trail event . Hooray! Aaron joined us last year, in 2017 and looking forward to another great event. I know we are still trying to work some of these details out. We haven’t announced the 2018 event just yet, but hopefully that will be coming fairly soon. We’ve been working with some sponsors and looking to do some things with some labs, and so that will be exciting once that finally gets announced. Vivek Patel reaching out on LinkedIn. Hey, Vivek! Nathan Hills chimed in on Episode 114 on Getting Started with Consulting. He thought that was kind of interesting, so thanks for that feedback, Nathan. And Chriss Voss, shouting out, sharing some enthusiasm for having developers on the show. I know we made fun of Bert a little bit in Episode 120 and Chriss said “hey, glad to have developers on the program, ‘cause there’s hope for the rest of us!” So Chriss, thanks for that, and we are trying to do, like I mentioned, a bit more content for you developers. So welcome, and it’s great to have you as compañeros. And then Davy, all the way from Langholm, Scotland, chimed in, “really enjoyed the podcast, really well structured, surprised how much I know and horrified there is so much that I don’t know.” So, Davy, yes, join the rest of us in not knowing a whole lot and we look forward to having you around for future episodes.
So, we did promise you compañeros that in 2018 we would include a Tips and Tricks segment. This obviously was user-suggested, and we’ve been soliciting some ideas, and I have to admit, I’m a little concerned. Those ideas aren’t coming in quite as rapidly as we would like. So, we’re going to go ahead and start sharing some of the ones that we put together. And I think that maybe you’ll be surprised at potentially how simple they are, and this idea of, again, just sharing the way that we work. And so, for this Tips and Trick, and again, I’m going to be using the power of video here. So, let’s say that we wanted to pull some data from a database, or we wanted to copy some data from the internet, and it’s not formed well. In this example that I’m using, I’m actually just doing SELECT * from sysdatabases and I have this, and let’s say I want to put it in a report or something, I want to format this for whatever reason. In this case, I’ve actually put the output to text so that I can get that format, so that it lines up correctly, cause if I do it from grid and copy there, you know, the headers get all messed up from the body. So, I run that SELECT query, I copy the results, and then I want to do something with that, so I’m going to paste that. In this case, I’m pasting it into SQL Server Management Studio, but this will actually also work in Word and the other Office products. And so, what I see here is I have a lot of white space in between some of the columns. Or let’s just say there’s a column that I didn’t want to include in my report. So, for example, I know that by policy, is auto-shrink on, is not something that I’m ever going to have to take care of, but I don’t want to have to go through each line and delete that data from the line. So, one of the things I can do is I click where I want to start, and I’m going to hold down the ALT key. If I hold down the ALT key, then I can click and drag, and what that will allow me to do is to get blocks of data, or blocks of columns, if you will, and I can select that and remove it and all of the rows will line up nicely. And so this is particularly useful, again, more for white space, where there’s white space I just want to take out, but it’s in multiple rows. So, I would click there, hold down the ALT key and I can do that and again the size doesn’t really matter. I find that to be really helpful when I want to some formatting and so I hope that you’ll find that useful if you’re not already using it. And so that is the podcast Tip and Trick for this week.
And now, time for a little SQL Server in the news. So perhaps not news. It was announced, I know at PASS, I think they introduced it at PASS Summit last year in October, and this is the SQL Operations Studio. What is news, is that they have started, very similar to the SQL Server Management Studio, is that they have started to introduce monthly updates to this program. For those of you who are not familiar with the SQL Operations Studio, the SQL Operations Studio is the new tool and it is ultimately a visual interface to be able to connect to SQL Server in a visual way but that runs on LINUX and Windows. For those of us who have been using SQL Server Management Studio for some time, you’re going to find some of the features lacking, so don’t get too concerned here. I think there are some very interesting developments that are happening, that are coming, and talking with the product team, again, when I was out in Seattle, there are some things coming, but it’s just like everything, it will be slow coming. So, kicking the tires a little bit on the SQL Operations Studio, again, very similar to SQL Server Management Studio, I can connect to a server, I can see some of my databases, I can write queries, select the database that I want to run the query against. There we go, I can change the database and run it against that database. So again, all very simple, if you will. If you’ve been using Visual Studio to do some SQL Server queries, that’s probably a little bit more familiar in the sense of, you’re specific to writing those queries. A lot of the administrative-type tasks, yeah, not baked in, and I’m not sure that they’ll come. SQL Server Management Studios probably is still going to be the place for you to do that, but there are some different things that they’re trying to do here with the Operations Studio. One of which I know, and again, talking with the product team, that they’re going to allow us to do is to create these reports, if you will, and then tag them onto the dashboard. So here they have a couple, backup status and search databases, and you can actually go in here and run the query and that’s what this is, and it will show you the data behind the query. What at least this knuckle-dragging Neanderthal hasn’t been able to figure out just yet is how to take a query that I’ve written and then put it into the dashboard. I believe that’s going to be coming. Maybe it’s already there and again, I just don’t know how to do that. I found it difficult. I couldn’t immediately find the documentation to do that. But I know that it is coming. So again, it’ll be exciting to see what happens with the Operations Studio. Not super feature-rich, but particularly if you’re in an environment where you’ve started using LINUX but want that visual way to be able to connect to the database and start doing something with it, then again, you have that ability with SQL Operations Studio.
So, with that, we’ll go ahead and get into the episode. The URL for the show notes for today’s episode will be sqldatapartners.com/access. Yes, like the database, because we’re talking about developers getting access to the SQL Server environment, or you can go to sqldatapartners.com/123. For this conversation, the idea is what developers should know as they get admin access. So, we’re assuming that there is no DBA present, and there’s kind of a shared responsibility model. There’s not maybe a group responsible, and so you or the other developers are taking on that responsibility. And this, obviously, would apply to both production and development environments. I’m not necessarily going to get into specifics as to is one different than the other. In my mind, these initial steps are going to apply to both scenarios, and then we’ll go from there. So, the top 5 steps are one, do you know the consequence of your action. We like to make changes, you might run into a problem and you read on the internet, oh, you should do X thing. You should change this parameter or you should use this function or you should enable this trace flag. While the suggestion is that that will help you solve your problem, the question you should be asking yourself is “why was this not already enabled? what is the downside to implementing this course of action?” You know, there’s a reason they don’t turn certain things on by default. Now, more and more, some of that is just because they don’t want to break the old stuff, so if you’re developing new things, there are lots of, you know, those best practices evolve over time. I get that, but at the same time, when you want to start implementing something, you need to be able to understand what the bad stuff is, what the negative is. So, do you understand what that consequence and what the trade-off is? What maybe you might be subjecting yourself to, now that you have taken this action, that you wouldn’t have otherwise? And you could potentially, you know, could you solve that a different way, based on that knowledge? So that’s the first thing to take in mind because when developers get that access, we tend to “oh, it’s exciting”, it’s like “hey, I can finally do what I need to do.” But we want to take a moment, pause, think about what we’re doing.
The next thing is to identify the owner of a system. I’ve been the DBA for many organizations. They’re going to come to me as the owner. So, a lot of DBAs think of themselves as the gatekeeper, but you won’t have that without a DBA and if you’re developers taking care of this, then who then is the owner? So, from a developing concept, there’s pair programming and there’s a shared ownership idea. These concepts have been around in programming and so it’s almost similar to say well, who owns the code? Well, we all do, so the same could apply to the database. Now having said that, documentation then comes into play. Source Control does a great job for the store procedures, the views, even the table structure. All of that you can get into Source Control and then you can okay, well, here’s what’s changed and whatnot, who changed it, things like that. What’s very difficult, or what Source Control doesn’t give you are system setting type processes. So, who changed the trace flag, or who allowed this action in the database. So, from that perspective, you’re going to have to find a way to document what those changes are, who’s making them, and then, are we all okay with making this change? You know, with code it’s easy to “okay, let me work on it, let me commit it and then people can take a peek at it and give feedback.” Again, with those settings it’s a little bit harder and so then coming up with a “here’s how I’m going to manage this” is an important thought process to go through. Cause rolling back is very, very hard when you don’t know the original state. How do we get back to the way we were?
Which brings me then to step number three: always give yourself a way to get back. Obviously, backups are a good way, if you’re changing parameters or system settings, writing that down, what was it, what did I change it to, those kinds of things? That’s no-brainer-type stuff. But we know that database changes, sometimes, they can be problematic, particularly as your database starts getting large. Like oh, do I really want to take a backup, can I just save off a table? Yeah, maybe. Maybe you can. Me personally, I think this is one of those areas where containers are going to start playing a larger role. And obviously if you’re using containers in your development environment, the implementation of containers into the database is going to be a no-brainer. It’s going to give you that ability to be able to spin it up, okay, let me try to make this change implement, right, I’m implementing something here, you could think of that as a code push as well. Is it behaving the way that I think it’s behaving? Okay, yes it is, now let me apply the same change to the database that I want to change. It’s very easy to make that leap because I have a way to test that very, very easily. So I’m interested to hear how containers play a larger role in the database environment, and I think that specific scenario, so in the dev scenario, and being able to get a container of your production environment very, very quickly, without having to do all that restore, allocate all that space. It makes a lot of sense and I think that’s a great way to go, if that’s something that you can pull off. Obviously, the folks over at WinDocs will be very happy to help you out. We are looking to partner with them and work with them and help more organizations take advantage of those containers. And so that all goes back to giving yourself a way to get back, just in case. Cause you never know, you may think that you understand the consequence, but if there’s unintended consequences, give yourself a way to get back.
The fourth thing is don’t forget the small stuff. So, patching, security, backups, boring. Perhaps, but they need to be taken care of and they do become important. Just recently, just this last week, we’re getting word about the CPU bug, where under certain circumstances, someone gets access and they can actually get access to the memory layer for the CPUs and see in clear text all of the things that we’re trying to encrypt because it’s what’s the CPU sees. And so again, these are very real problems. You’re patching your software, for example, the things that you develop, you know they’re going to have bugs. Well, okay, the database is no exception and you have to think of a way, “okay, how am I going to keep up with this stuff?” Or again, who is going to take ownership of that or how have I looked at solving this problem? There are lots of third party applications out there from a backups perspective. So again, just understanding what it is that you’re getting from that point in time, how long am I going to keep those backups, those kinds of things are things to think about. And then of course, just because you have admin access doesn’t mean that everybody else should have admin access. Security still plays a role there, particularly from the application perspective. You have a web app, you don’t want to be giving that user admin rights because you’re just inviting the ability to be compromised, frankly, because you have bad security in place.
So, the four have been: do you know the consequences of the action, identifying the owner of the system, and if you have a shared ownership model, detailing who’s going to be making those changes and how they’re going to be documented, always give yourself a way to get back and then don’t forget the small stuff. And the last suggestion I have is to install Database Health Monitor. In my mind it’s a no-brainer. It’s a visual interface, it’s going to give you that ability to be able to get better insights into the database without having to look at all of the logs or know all of the queries. There are lots of reports that are baked in that are going to give you some feedback. The biggest benefit to the Database Health Monitor is the wait stats history. Now you’re going to have to install a small database to keep some of that history, but now when you go back and you’re like “wow, at 10am this morning I was having problems”, you’ll actually be able to have the history to be able to go back in there and start digging around. What was going on? Again, lots of different ways to home-grow that yourself, and if that’s your option, go for it, but for an easy out-of-the box way that’s free, Database Health Monitor, I think, is the way to go there.
So, compañeros, what do you think? Do you agree with my list? I’m very interested in hearing from you. I’d be interested to get your thoughts and feedback. And you’ll note that I didn’t give specific, “hey, you should do this”. There are some best practices out there, but I thought that identifying some of these first steps would really be more important. Again, even using the Database Health Monitor as an example, that would help you understand what the best practices are and you can start implementing that as you see fit. So, you can leave comments in the show notes or reach out to us on social media. We are always interested in hearing from you. That is going to do it for today’s episode. We have quite a bit of music. Our music for SQL Server in the news is Mansardian, used under Creative Commons, as is all of the music that we used today. We hope you’ll reach out to us on social media. A lot of people are connecting with us on LinkedIn and we invite you to reach out to us on LinkedIn. I am @CarlosLChacon and compañeros, I’ll see you on the SQL Trail.