Skills Every Junior DBA Must Know

and it's 11:00 a.m. I guess we could go ahead and start if you're ready okay here we go the broadcast is now starting all attendees are in listen-only mode good morning or afternoon or whatever it is where you're at welcome to the DBA fundamentals virtual chapter meeting for July today we have kinder little who are presenting a session called three skills every junior DBA must know I'm going to go over a few other things first my name is Steve Cantrell on the chapter leader for the DBA fundamentals group and one thing that's important this week is this next Sunday on the 17th at midnight Pacific Standard Time the cost of pass for this fall goes up $400 and if you're going to go I would definitely suggest do it now use our code it will be an extra hundred fifty dollars off there's other virtual chapters I thought that's got all of it they have codes to definitely make use of that the advantage we have and I have that down there wrong on the screen if you let me back up if we use if you use our code and you can make a print screening of this we're going to put you in a pool of people that use our code and the winner will get $500 Amazon gift card so be sure and if you're going to do it do it this week it's going to save you 550 well at least save you 400 hours you can still use our discount code later but I definitely do it now our sponsor our as what this is our returning sponsor sequel century they are going to be with us for the next year and they helped pay for giveaways and other things they're going to be giving away different things and have different contests in the future we haven't got the details out but if you want to do a print screen of this page to the little camera up at the top and single century is a great location for resources and obviously things to buy but in the meantime they have a lot of free resources you can download a copy of plant explore which is a great way to look at it execution plans there's plenty of ebooks demos they have query tuning advice at answers equal performance comm check them out they're great sequel sentry comm-pass includes a lot of virtual chapters pretty well most every topic is taken and dissected already and a lot of these chapters have foreign languages that cover a good portion of the world so there's plenty of options out there so go to sequel pass org forge /vc and join a bunch of these chapters just to get free information all the time here's our chapter sessions coming up next actually next week the sequel server performance Microsoft Tiger team is going to be doing a session on performance monitoring and baselining that will include things about 2016 in August Glenn Berry is going to be doing a session on high availability and disaster recovery 101 he's great you should really definitely come listen to him our down under Chapter I will be doing a session in August also securing sequel server recommended pass practices a John Martin and in September we have two sessions the downunder chapter will be doing understanding backups but Sean McCown and managing very large databases will be here in the US on the 13th our sessions are recorded and you can get our recordings at this site or at our YouTube site so if you want to do a snap of that you got that that way you can get to our recordings anytime okay - kyndra little is a teacher author and Microsoft Certified Master in single service she loves helping people write fast code troubleshoot problems and design reliable database systems and she's very good at that she has a new blog I don't know how long you had it kinder but it's a dear sequel gosh what was it dear sequel DBA dear sequel doctor or something I had it written down sorry not prepared good today anyway I'll let Kendra talk about that a little bit more let me pass it over her and let her get started thank you so much Steve thanks a bunch alright let's see if I can get my screen shared here alright show my screen and let's get the slide deck up there all right it looks like audio is working hi folks let's test my little remote here yep my remote works alright we're good alright welcome to three skills every junior DBA must know I am Kendra little and I am a consultant speaker teacher and blogger I've played all these roles in the past these days mostly I am writing at little Kendra calm and doing a weekly podcast called dear sequel DBA but I started out as a junior DBA and actually as a junior DBA my job title wasn't even DBA right I had like you know it's systems analyst or different job titles and I worked with sequel server and with databases but actually back when I started it was it it took me a bunch of work in progress even to get to the point where I had like a DBA title all right cool and folks who are here I see that the Q&A panel is working a Dane says he sees my dog mr. little is working hard that's totally true I'm lucky enough to have an assistant these days so if you have questions and you here live please go ahead and put them in the Q&A panel I'll try to get to them as I go or circle back at the end this presentation today is for folks who are trying to get started or getting started as a database administrator maybe you don't have that DBA Junior DBA job title yourself yet either maybe you do but what this talk is to help you with is the big problem well I think the hardest thing when you're getting started is just oh there's so much I could learn there's just literally an overwhelming amount of things you can learn as a database administrator even if you're only working with one platform sequel server how do you pick what to learn to master being a database administrator and and that's what the three things that I'm going to talk about today are what in my experience will really pay off for you to focus on and be really critical to your success as a database administrator so all about it's not about everything you can do it's what you should do because what I learned I was lucky enough to have a really awesome group of senior DBAs when I first started who I worked with who could help teach me a lot of of what we did at the job and how to administer sequel server but there were a lot of things that I was kind of slow to figure out and this is the secret behind that for all the critical skills for things you want to learn to be a DBA there's a secret that I learned behind it and really as long as you kind of understand the the thing that people don't always think to just tell you you'll know oh these are the things I need to focus on for each of the really important things that I'm going to talk about today I screwed it up at least once I'm only going to tell you about once for each one today but I'm going to share like okay here's here's when I really messed up with this and what I learned from it and what I would do differently the very first thing I would say that is important to focus on as a junior DBA is designing great backup strategies is super critical even when it doesn't seem like it would be and so when I was that junior DBA who didn't even have the DBA job title right I was trying to get to that my job was to set up development environments I didn't even have production access I was bad Junior and these dev environments had restores and very large databases and we just put everything into the simple recovery model after we restored it to the dev environment because we didn't have space to do all these backups of all the dev environment it was many terabytes across all these environments this kind of you know the things I was taught when I first started was all about his complicated process to set up the environments there was replication there was removing personally identifying data I had a lot to learn I also had a lot of automation that I needed to learn to take care of muted looting like working with all these C sharp scripts and lots of stuff the environment was a dev environment and it wasn't a performance tuning environment either so the environment was super slow and I was starting to hang the job I was starting to get really good and then one day it was actually Friday night I was trying to kind of you know be really good at my job and these it took a long time to stage these environments and get data flowing through them there was a lot of dependencies so I was trying to get some stuff going on a Friday night running some of these automated scripts and I accidentally dropped a database in the dev environment and it was a database that kind of logically lay sort of in the middle of the environment it was downstream of some things and then upstream of other things and did I mention that we put everything in this simple recovery model and we didn't do back UPS of this environment so absolutely drop this database and then start it immediately you know on Friday night looking can I get it back I get it back how can I undo that thing that happened in an instant well I couldn't figure out how to get it back and it was the weekend and this isn't a high priority issue so I kind of worried all weekend long I went into work Monday morning and I talked to the people on my team you know the team that managed this environment I'm like sue is there anything I'm missing can we rebuild it can we get it back and we couldn't get it back with the right data without resetting up the entire environment because there's all this processing and did I mention sequel server replication so I went into the stand up meeting for the dev team and I am basically like so your environment that's supposed to be finished by tomorrow here's what I messed up in it and we figure it out okay what can we do here because if I was to fix the error I had done the environment would it be ready until the next week this team couldn't start their sprint without the use of this environment right they have very specific features they were working on they could use most of the environment after the mistake I've made but they couldn't do really end to end testing throughout it but they decided that that was better than having to delay their whole sprint by best case a week right because setting up these environments was actually like if I got new backups because the old backups were gone find a new ones they might not restore properly I might have to get another set from the data center is a mess right basically I learned about recovery time objective in this experience what I hadn't thought about was this isn't a production environment this isn't the source of valuable data but if I mess it up where if anyone messes up how long does it take to get us to get it back and what is the cost for not being able to get it back whatever sequel server databases you are working with whether or not I mean especially if their production databases but even if they're not this is actually super important to think about is how much does it cost if it's offline and how long can I get it back because in my situation this was a team of like seven developers their salaries for a week is a a notable amount of money and if they can't work like we're relighting dollar bills on fire and I had no concept of that before this happened so making sure that whatever databases I'm working with if I lose them how long how fast can I get them back and then in addition if we lose data from it what is the impact to the business now in the development environment I was working with what I did after this incident is I wrote down these two things okay how long can it be offline you know how long can the whole environment be offline or critical parts of it and then what happens if we lose data in it and for each environment I got it signed off and in my case because it was development nobody cared if we lost data from it essentially as long as we had one snapshot of the environment and could bring it back in 24 hours it was fine and they didn't care if we lost the data because any updates to the code they did they had checked into source code anyway so they're like yeah yeah we can redeploy our code changes any time but just make sure it's up and we actually had a sequence we had to back up the databases in to be able to restore them properly to so you want to get these signed off for every environment and don't make the mistake I did the mistake I did was nobody has ever done backups of this environment before it must be right for it to view this way it was 100% my responsibility that I hadn't said hey how about if we figure out a way to take a snapshot of this environment instantly after we get it set up the data cleaned so that we can bring it back and if we don't have the resources to do that how can I talk to to explain I think it's a good idea that we should have this so that these teams can keep working right it really was my responsibility because as as the person who's the DBA I'm responsible for present preventing downtime and data loss even if it's not obvious and especially if your manager isn't a DBA your manager will just assume that you know everything about backups and avoiding data loss and avoiding downtime they won't necessarily know how complicated it is and how much training that you might need to to learn that so what I would do differently going back is to say okay to avoid you know get this signed off how much down times okay how much data loss is okay use that to configure the recovery models and backup frequencies simple recovery model is not going to be okay in a lot of environments like we were using in this environment I would also test the restores and make sure I can need those numbers and whenever you can't guarantee that you're going to be able to meet those numbers you need to say okay here's what we need to be able to get there and bring that up to your management and say I think we're at risk of losing data or I think we're at risk of more downtime than is allowed here's what I've looked at to figure out how to mitigate that can you help me it's okay to ask for help be really careful about misinformation there's a lot of misinformation out there where people think oh well I don't need backups because I've got database mirroring or an availability group these technologies all have loopholes in them where maybe your secondary server isn't up-to-date and you could lose data if the primary goes down you also if data loss is a like if it's not okay to lose data you typically need the ability to restore the data to a specific point in time outside of production like Oh somebody made a mistake at 3:00 p.m. on Tuesday we need to get back to the point right before this important table was accidentally dropped to be able to get the data out of it that means that you need to have really granular log backups depending on how many minutes have data that you can lose and especially if you're talking about the system possibly failing you need those backups so if you have any of the signs on the screen if you have log backups running every 15 minutes that it's possible that your system has a recovery point objective of 16 minutes right or we can lose 17 minutes of data or we can lose 20 minutes of data in which case maybe a log backup every 15 minutes would be ideal but that's actually pretty rare it's very rare that I talk to people and say how much data can you lose and they say 20 minutes it's usually like one minute or five minutes or like an hour like it's kind of rare for it to be in the middle but at some point somebody on the internet decided that like 15 minutes was a good number and everybody did it that way and nobody questioned it so that's one of the signs of like maybe nobody thought about this if you have jobs that change the recovery model at night that will cause problems with restoring transaction logs and if you have this gap where like log backups only happened during the day this is another one of those things where it's like on these systems and questions a long time ago maybe nobody access the system at night because there wasn't as much people working at night people working remotely people working from home but these days it is very rare for a database to not have a potential to be used at night that's another big warning sign there at the end of this presentation I've got a training plan for you I've got a link to an online resource that says here's how you learn about these topics because there is a lot to learn about recovery models types of backups in testing restores I've got links to free online resources that will work you through this because skill number one if you don't really have this down this is really where you want to start and this is going to be incredibly useful in your career not only for protecting your company's data now but for every job interview you ever do as a database administrator there is a high likelihood they're going to ask you questions about backups and restores because it really is super core and critical to the job of being a database administrator but no one will ever train you on it unless you ask right you gotta say hey this is complicated stuff I need some time to learn this can you help me out here the second skill so we talked about about backups um and uh yeah so some folks are commenting on yeah having changes in recovery model at night um so we got it we got a question about the 15-minute backup thing I'm glad I scroll to this let me go back from Tyler uh so you know how I are saying that the log backups every 15 minutes is an issue so Tyler question is could this smaller and I think you may mean recovery point objectives the smaller data loss requirement the accomplished using a tail of the transaction log while keeping the backups at 15 minutes so this is fantastic question Tyler the issue and the risk is what if you lose the disk with the transaction log on it so if I can only lose five minutes of data and I'm doing transaction log backups every 15 minutes if that draw it under the transaction log gets corrupted and I I know that storage administrators say that nothing can go wrong with the sand but you know I everything can fail then you can't do a tail of the log backup and you're only as good as the last log backup that and you don't know what happened since then so that's why the log backup frequency is so critical is you can lose the log backup Drive and even if you have like an availability group before how do I know before the log backup tried totally went gone how do I know and I would have to check was it synchronizing data successfully to the secondaries could they have been out of sync you know all but doing log backups more frequently does not add a lot of overhead because the log backups all gets smaller so the great question though because I don't think that's obvious at all so still number two the three critical skills for junior DBA improving sequel server configuration when I kind of got into actually actually I don't think I even had a real DBA title at this I think I was like Systems Specialist three or something like that but my job was really I was hired to be a DBA and I started handling performance issues and I was working with the team of developers we're having performance issues on this one sequel server and it turns out it really hated itself it this was sequel server 2005 I believe so we're a while back not I mean this was when 2005 looks like latest and greatest and the app we were running it had to have analysis services and sequel server installed in this same Windows install performance would get really bad and what we found was in the sequel server error log it had this message the process is getting paged out essentially memory pressure was being put on the sequel server and it was getting squeezed it was not able to kind of shrink its memory fast enough and it was having to write the windows page file things were getting really slow and the developer said if you grant the sequel server the lock pages in memory right and set the max server memory for the sequel server to about sweating it was 45% of the memmer in the box because in Alice the services actually did a lot on these things then the sequel server when it gets memory pressure will say I'm not give it up talk to the hand and analysis services will have to back off and sure enough performance did improve in this case this was a 64-bit it wasn't like that way back so it fixed the problem I was like okay I'm going to make this part of my standard configuration and I'm going to start doing this on all sequel server so later on and a totally different environment totally different I was at the same company but I was working on a totally different out one night we had a sequel server crash during critical processing it just took a hard dive and it we it somebody's paid showers I'm going to click it back on get the sequel server back up you know get it running again but it was a caused a delay we weren't able to meet service level agreements for the data that was being delivered after processing and we had to look into what the root cause of the incident was and the root cause of the crash was that we had granted blocked pages in memory to this sequel server account this was not this was only is with a dedicated sequel server all that ran on it was sequel server and lock pages in memory in this case there was another there's a job that was running that ran an old version of Robo coffee exe and it had a memory leak and so memory available memory started getting low but the sequel server would it back off because the lock pages in memory setting and windows got squeezed windows didn't have enough memory and it just went down really hard and we were able to get this from messages in the logs and things like that and in this case what we looked at was well do we really need this setting on this sequel server it should should we tell it not to back off because it shouldn't really need to back off memory it's the only thing the box is being used for if it comes under memory pressure performance is going to be impacted if it has less memory but we'd rather have that than a crash as long as we're monitoring for when available memory gets low this is this is hard there are so many things you can configure for sequel server and I particularly bring up this setting lock page is a memory on purpose because you will read very passionate arguments for you should always turn it on and you should not always turn it on because you can have bad things happen either way there is no for some things there is not always an obvious right setting for every sequel server you have to make a judgment call it's hard to master sequel server config and this is the approach to take for it first just document the way everything is in your environment before you change anything capture all the settings that you've got with scripts and I've got links to some free scripts that will help you do this catalog so for the important production sequel servers make sure you know what how everything is before you go changing things because if things get worse then you're like oh well I'm not sure how it was before that's really bad take care of the backups first and then prioritize other changes there are some free scripts that will help you prioritize settings and configuration by risk and importance go slowly executing changes the secret that I learned was you are responsible for how the sequel server is configured even if you just inherited the settings or even if the settings that you're using work fine in another place but not fine here it's always the DBA who owns the responsibility for that some things are going to go wrong right just got to be really careful and Method all methodological with it and work your way in a prioritized way always knowing the before state and the interstate my way of thinking as I progressed as a DBA change from originally I wanted to have one sequel server configuration for all sequel servers when I was a junior DBA I just thought that's the way it should be I recommend having a couple different sequel server configurations try to be as standard as you can but there are going to be some settings that you're not going to make the same everywhere so in my case from my belief about this lock pages in memory setting is when I know that the sequel server has to share with another application I will use a lot of pages memory but only if I know that that other application when there's memory culture is going to back off its memory use if I am NOT certain if the other application will yield its memory I will not enable log pages in memory and if the sequel server is dedicated my preference is not to use lock pages in memory but to monitor the available memory counter there's other things like what is the best setting for Mac's degree of parallelism that you're going to find may work better differently on different sequel servers when performance is important and you're going to want to have slightly different configurations across sequel servers so it's fun to kind of have a default config for we don't really you know know exactly what's going to be running on the server we have an initial setup but do allow for customization in your configuration processes all right so we got a question about when is paging bad for your environment this is a little bit this everything's confusing um it's just Windows paging file will proactively be like hey I'm gonna flush stuff to the patient file just in case you need to use it right so that's why it's like um seeing that Windows is growing out the page file just think it is not necessarily a bad thing because it when we'll be like oh well I'll do this just in case unless you told me not to the messages I was talking about earlier the ones where it's really bad in the sequel server error log there is a message that it'll be like I have paged out and performance is impacted so when sequel server is not able to use memory and it has to go read from that paging file on disk usually on your C Drive unfortunately just usually people don't change it and it's the C drives usually really not very fast storage that's the wind things frying to a halt and the sequel server when it does that will put a message in the error log just like hey this is bad news I'm incredibly slow so but just because you see that the paging file grow it is not the same thing which is not always obvious the third critical skill for junior DBAs the first one was backups and recovery not losing data promises to be too long offline the second was learning critical sequel server configuration and changing it slowly skill tree is really related to both prior skills be smart about how you plan changing your configuration and this also goes for backups as well as a senior DBA I thought I was pretty good at changes right like I was very lucky the company where I was at that little bitty junior DBA had a awesome production change process and they taught me all about change requests and change management and what to do when things went wrong so I thought I was pretty good at this and you know whenever you think you're really good something that's when you've got kind of a check headed for you right to remind you that you're not perfect there was a problem with one of the databases that I was working with and the database was almost entirely redacted there was a bunch of application caching that kept a lot of the Rays from going to the database but there was still a very high amount of database reads against this thing it was mostly metadata and it was only updated periodically like once a day type thing of like people would figure out okay it's very complicated business logic it was actually stored in XML and I mean the procedures that updated this thing more like look at them and it was just and it would have dead locks when they would try to get the metadata to update the the business logic so complicated that there be dead locks between all these readers and the one thing that's trying to update this and this is controlled by the internal team so they can guarantee that there's only ever one thing updating this I want so I looked at this and I was like I have a great idea for fixing this without any code changes there is a database setting for isolation level that we can change to read committed snapshot isolation and when we do that at the database level and at this point I mostly had just book knowledge about this I've used it a little bit before in an environment but I didn't know that much but basically no more a lot more now especially after this with read committed snapshot isolation whenever sequel server makes a change it keeps a version history in tech TV and it does this without making you change your code so that readers will just read the version consistent with when each of their statements started and use those versions int empty B and I chatted with the dev team and they said oh yeah this this this setting is valid that it's perfectly fine for people to read those versions in temp TV um it's great so readers won't block writers writers won't block readers and this should really reduce the likelihood of deadlocks because the readers and writers aren't fighting with each other anymore so we did what I thought was a good change process we tested it in our pre-production environment and the script succeeded I had a script to make the database turn on read committed snapshot which is an altered database command and I had a script to turn back to turn the setting off in case it went bad now the scripts put the database into single user mode changed the setting and then put the database into multi-user mode because to change this specific database setting sequel server requires that you be the only active user in the database at that time this is not true for all alter database settings just this one which is kind of a bummer the big problem was the environment that I tested this in was idle like we could run it one person could use the app but it wasn't like production so when we when I ran this drip in production it was approved to be run during the day because nighttime was actually a really busy time for this environment and you know I started at the approved time and I started the script and a read error it kind of ran for a little bit and then a read error appeared on the screen and it was a deadlock message and so I tried to rerun it and it said you can't run your script because the database is in single user mode and you are not that single user so I'm out here and there's a bunch of different service accounts from all these things that read the database and one thing had single user mode and everything else is lighting up red and I start to hear noise in the office things are going badly and I had no idea what to do so what had happened this is actually very hard to reproduce but I have seen it happen again it's just I don't have a script that will reliably just do it that statement the alter database statement that put the database into single user mode it sort of succeeded that there was a weird deadlock and I did not win the single user which I don't think should be able to happen but I swear it happened to me it was a very bad day and so my changes complained undo the change because I'm not the single user there are other databases on this instance if I just shut down the instance and bring it up in single user mode whole instance I will break a lot more than is even broken right now so the outage time was not as minimal as planned right there was a little bit of outage that had been planned for the moment when it was single user but yeah this was longer than that I actually didn't have the idea to fix this I didn't I was like you know this deer in headlights um I chatted with some friends privately who were DBAs it was like hypothetically database was in single user mode and you weren't user and you couldn't restart the instance what would you do and Jeremiah pashka thank God for Jeremiah peshwa he said ah disable the logins right because I didn't have a kill script to just kill Evan and anyway with the kill scripts things would just come back so disabling the logins getting for that database getting a I was able to get it back I was able to roll the change completely forward and the good news is the change actually works like the deadlock furball the bad news was my change had some major problems so my the secret that I really learned is that you know that you're never safe from something going wrong like you do here this is part of change management is trying to think about where things could go wrong in your change that you haven't noticed before and just knowing that you're never going to think of everything but the more changes you do the better you'll get it looking at your change plan and saying okay well what if it does break in the middle or can I avoid putting the database into single user mode change management is your sort of superpower for the moment that you do your change request is the time for you to look at it and say oh is there a better way to do that because the truth is you don't have to put the database in single user mode to use read mitad snapshot isolation you can actually on the database statement that changes the isolation level do you alter database set transaction and isolation level and you can put a width rollback of median on the end of that statement you actually don't have to do what I did at all I just hadn't looked it up because I hadn't thought about something going wrong where it did so now when I'm planning changes and I'm thinking about this third thing I what is the worst thing that could happen in this change I try to think about not only what is the the thing I'm trying to do doesn't work but what if something goes wrong in the middle of me applying the change is that possible right and I I didn't have a kind of view of that before this incident now I'm much better okay I'm trying to think about what can I go wrong in the middle so basic change management and in the training plan how to link to a basic change management template in case your company doesn't have it I would started off as a DBA even if it doesn't exist in your environment because for each change you want to basically always touch her what are we doing why are we doing it how do I get it done how do I undo it and if there isn't a way to undo it what is the risk of that right if I can't take it back what does that mean and what are my alternatives and then what are the worst things that could happen we actually used to have this question on our change management form what is the worst thing that could happen when you have this do not write down an asteroid could hit the earth that is not a question that changes or that is not a comment that change a pervert like to see so Julian says this single user issue happened to me before almost went insane trying to figure out how to get in I think I actually did go insane Julia liked Doakes it was a moment of madness right just this feeling if this can't be happening am i awake this is terrible that was a there's an adrenaline rush during that day so put it on muted or your learning plan and by the way when things do go terribly wrong you will get if you haven't had this before if you have had before you know you get like this feeling as your body injects adrenaline into you and you're really up for a while and then as soon as it wears off you are just exhausted which is why when things go wrong and you can't fix them for like days you know because I've had these systems where we have a problem and it takes days to fix it man is hard you don't wait to be in that situation so I'm going to go through the learning plan don't feel like you have to screenshot everything you can if you want but I have a link to these all at the end where they're clickable and they're online so the three big secrets designing great backup strategies whenever you see backup also think restore even if your job description says nothing about backups even if the backups are handled by an infrastructure team if people look at you as the DBA and there is data loss or downtime the senior level executives are all going to come to you this is an unfortunate truth that I've seen proved many many times over and it's really hard when you were kind of told someone else is taking care of that you have to be politically a little bit careful in that situation and you have to say hey I would love to test you know we have a requirement that we don't lose this much theta let's run a test and can I help can I help out and prove that we can meet that requirement and you've got to kind of try to initiate a project that way if explicitly it's in someone else's job description because the problem is they will really come to you configuration and change management are the next two so you can't learn them all at once you want to start with the backups and documenting the config and propose changes one by one as you say okay I think we should improve this here all the links I've got today are for free stuff for backups you really have to know recovery models first and Microsoft has really good documentation online about what are the recovery models and water backups once you know those concepts which are fairly quick to learn there is a stairway to transaction log management written by Tony Davis and Gail Shaw on sequel server central which is awesome if you've never used the stairways before it's a progression of training that walks you from a simple article to a more complicated article and they even include code for you to be able to test and demo stuff in an environment so level 5 in the stairway lets you work with sample scripts to actually get your hands on restores and sort of understand different exercises it is excellent I really recommend it you do have to give them an email address in your name to see stuff on the site very very cool and thank you to Tony and Gail for writing that for recovery point objective and recovery time objective the cool thing about these is that they are not sequel server concepts they are just business concepts so I would actually go to the general Wikipedia stuff just for when you're talking about these it doesn't really matter if the chief technical officer knows anything about sequel server to understand this this is a concept that you can use just the general language about that applies to all the systems they are responsible for for documenting your server configuration Glenn Barry who's speaking for the chapter soon I love this tie and so Glenn is a great speaker and he also provides really great detailed scripts they are customized for different versions of sequel server and he gives you the scripts to capture your configuration and an Excel spreadsheet to paste the results in so that you can in a really detailed way capture and save off here are the settings for this that are relevant for this version of sequel server because they add and take away features as time goes on the scripts are thought you could run everything at once run them one by one because you're going to be pasted you want to make sure to read and understand what you're running for you're on it and you're going to be pasting individual results into individual spreadsheets anyway for once you get started prioritizing what do I want to change Broncos are unlimited offers a free script called SP blitz that gives you a more prioritized list of here's the biggest risks that you want to look at first and it tells you it has links to help explain what might go wrong with them this is now an open source script if you want to download like the current public recommended tested version you have to register I think you can get the development version of it on get just registering with get so check out that URL for information oh I go back into the Ford engine for learning change management grant Fritchie wrote a fantastic article on source control so if you're at the point where you're like actually an our dev environment the code isn't all tracked into source so we have to do backups because we you know could lose developer code if we lose the dev environment you wanted to start encouraging folks to not have that be the case and to check to be using branching and source control and check all their changes in their grants article is a good start on development and source control and then for database DBA changes I have a change request template because with developer code it's often a little more complicated than just use a change request if they aren't using source control just a little bit of a special and if some folks well this is a funny issue because some people are like how good developers ever be like that and then some people are like our developers are totally like that so I understand kind of the different situations that people can be here don't don't feel bad if it's you just try to encourage folks to fix it all of these links are at little Kendra calm slash jr. it'll redirect you to an article it has this whole training plan laid out as a progression with clickable links for you to use and oh great so Andy has eight comments on change management include peer review ask the duck and details for phone-a-friend I love this so with change management getting other people to review your changes is insanely helpful and because the approver of the change usually sometimes they're a good reviewer but usually they're not a specialist in your area usually they're sore ribs overlook the whole system and the change approver is often thinking more about risk than about is there a better way to do this if you are not the only d-day if you have other DB days ask them all to review it but at least get one if you are the only DBA this gets a little sticky because you're like well because of confidentiality reasons I can't always ask someone outside my company to review my change right I'm not always allowed to expose that info what do I do in this case you really want to try to identify okay well if I win the lottery who is going to end up taking care of this until they hire another DBA that person is the person who you want to ask to be your change reviewer and you may have to step them through the change and that really it's going to be just a conversation where they ask you questions we need to do the same thing for them with their changes but it's way better than nothing because you are getting someone who's at least kind of asking why you need to do that what does that do you know are too are there other you're at least having the conversation so even if you're the only DBA a change review is is super helpful a comment from Jana I wish there was this feature where I can set what user will be able to use a database when in single user mode I hate single user mode and you know after my experience I completely agree it's it's uh it doesn't that I really use anymore that day all right so I'm I'm clicking through comment than Larry comments that my dog must be a senior DBA he's not interested um he also it says no to the UPS driver a lot ah great question from Andrew I am working as a sequel developer and my company's database is fairly small and simple how do I get the environment to practice the skills you mention pour the backup and restore um like I meant in that in that stairway series they do give you just some sample commands that you can run against a developer edition instance that you can do there I would say actually as long as you have one production sequel server you can actually develop all of the skills I mentioned make sure that you have a place to restore all those databases using developer edition which is now free that's just for testing and learning and make it as much like production as possible but all of those sequel server I mean there's a ton of complex sequel server settings that even for a single instance so configuring parallelism making sure that you've got all the right database settings all and as I keep talking about backups I I could keep myself busy for a couple years sort of perfecting a single sequel server instance but always make sure you've got that test instance to UM research on before production right ah okay question about third party apps can we trust an application aware backup feature of a backup software from a third-party developer and there are a few out there there are some that use and specialize in virtualization there are some that are just that tools so well and these can be kind of a sticky situation because if you've got this third-party backup tool in the environment that the company's already invested in people want to use it right so in which I understand so I would really take the approach of focusing on getting people to say ok how much data can we lose and how long can we be offline so don't still start by talking about how you're doing the backups start by talking about how much data loss and downtime is acceptable and get that all signed off on then say if these are already set up can we need these reflect I need I want to do a test to see if we can really meet these requirements if I can restore within this amount of time my experience with these third-party tools is that usually when you do this comparison in these environments you fall way too short because these third-party tools have been set up to backup massive amounts of resources and they're actually taking like snapshots every hour or less and you don't have enough resources to get more granularity with them so that's why I found this method to be successful is you just sort of go in and say it's a bad tool which may or may not be true like it may actually be true then it's the person who advocated for the tool you end up in the sort of battle with them so I kind of go around it just what are the requirements we need to meet and can we meet it with his talks I've actually found that the way they're configured usually does not in fact I have never found them configured so they could meet the actual requirements but could just be my my experience that's why I didn't say any names tools when I read your questions maybe the tool that I wasn't thinking about this person right uh uh for a complete beginner and folks who are here if you have an opinion on some of these it's totally in you chime in and I could I may be able to see it if we have time this is a great question what is the rough time frame you would expect to learn these things and become a senior DBA I've been doing this for two to three years now but mostly development so the the path to senior DBA how long does it take from beginning like well I know how long it took me um but is that normal ah so and this brings up the question of what is it a senior DBA as well senior DBA for most companies also has a fairly strong leadership and process component there's a technical component but there is also how well do you define processes and sort of influence other teams as well as your management and how well can you kind of lead possibly contractors possibly other junior DBAs as well as do you sort of understand all the stuff as well and for me with my career progression you know I like I got some of the technical stuff before I got the leadership stuff so I would say like depending on if you're already really good at some of these you and you only need to move the dial on the technical knowledge and experience it's going to be a shorter path um if you're sort of starting from an introductory level on all of them I would say the best case is five years - from you beginner to senior DDA not that it can't be done faster but just that you're still going to be maturing your your operations experience and your ability to draw on previous experience to kind of avoid things and handle things when they go wrong so you can get the title faster than five years but I think it's kind of operationally there isn't just a lot of being in there you need to be now if you've been doing something like DevOps you may have acquired a lot of those incident points and production points already because the more that people are combining development and production support the more people are getting sort of this operational knowledge and process knowledge without actually being a DBA so I think it's getting muddier but if other people have a different timeline I would be very interested to kind of now Michael says how often do you test restoring your backups um my personal preference is to for it a database where it's important my personal preference is to always be restoring my backups and this is one of the reasons that I like log shipping is that you can always be restoring your transaction log backups which is proving that those transaction log backups work now you're doing log shipping your logs to make secondary isn't proving that your full backups restore those so I actually have had environments where we had multiple ways we had high availability we had log shipping and we periodically would also go in and restore the full backups it it I would ask that as a question to the business owner the related question is how deep do you store your backups and how do you need to like if you have a business requirement to keep three years of data available like we will be able to restore back to three years ago to your clients you need to periodically be testing restoring old backups that may have already been tested before and have just so it really depends on what are your agreements with your clients as well as do you actually have something like log shipping that's actually testing restoring them all the time so but as much as I can get away with it disability my answer to that I have had backups that won't restore because the media they were written to is I'm corrupted and things like that Oh at least asked what if it's totally fine that it's off topically so what brand is the dog bed something was actually a gift it is an organist brands dog bed and as you can see it is somewhat larger than the inhabitants of it he likes it it's a little bit of overkill think I can sit on it with them Gerald says I am a current Windows server administrator and I am trying to become a DBA do you have any advice for me on getting training and experience um I think it was Brent it was our who said sit as close the DBAs as possible I just if you have DBAs and your company just sort of show up at their desk you know just soft hand like doughnuts Scotch whisky whatever and that attracts them but see if you can be their backup and I would talk to them about gaining experience and just I actually learned a lot about Windows server administration just from sitting as close to the Windows server admins as possible I didn't I didn't want that job like I learned it up to be like oh and that looks really hard it's about that but and they said the same thing about my job to be honest but um so then use the local people as much as you can also just talk to your boss about a training plan and you are as a server admin you are in a really great related area and all of the stuff that you know is going to be useful so that's the really good news but say like just talk secures my career goals I would actually like to move more in this direction and ask for can I go to this conference can I do this online training go ask for whatever training you can get I would really focus on with your experience I would focus on the backups and the restores and the infrastructure level stuff first just because that's really where you're going to shine I wouldn't try to learn all of T sequel right away you're eventually going to have to learn enough to be dangerous but I would leverage where you're starting from in your case because it is so related and look at master backups and restores work on the configuration and change management and then after all everything we covered here I would say okay well how about storage and high availability those tend to work really well with the infrastructure level knowledge that you've got so Steve hope we actually hit our time limit we can go longer if you want to all right I've got ten more minutes if I understand if folks need to go but I can save for two more minutes but then I have an appointment after this so cool a few more questions oh okay how do i isolate a performance issue and figure out if it's sequel server related or infrastructure related I had a hard time convincing my VM and storage administrator that there's problems at the VMware storage level so I actually did a dear sequel dv8 podcast on my performance triage methodology like how do I look it up what steps do I take to figure out what the cause of a performance issue so if you search tender little performance DBA you'll get to that episode and there is um you can watch it or there's also a Notes version in the blog post that you can read through like so you can do either one essentially the first thing that I always look at a sequel server weight stats and what's running when it's slow and the sequel servers weights that help you see what is it waiting on so it can help you show is it locking and blocking which would it be a VM or an infrastructure level but then okay is it waiting on still reads from storage which would indicate that it might be a memory allocation or storage feet issue so it helps you kind of dig into that data and you can say we've we'll you know what you've rolled out and what you've looked at but it is a little more complex than that it's a 30-minute summary so I would check that out but wait stats are where I usually where I usually start after making sure it's on you know what it can I access it of course right there's some initial ah Ronnie says how do you know when you're no longer at the junior DBA level are there specific signs well to some of us like there's a what is called impostor syndrome right there is a tendency for many of us to always kind of feel like other people really know what's going on and we don't know enough yet so there is a way in which some folks will constantly underestimate their skills and then there's I mean we've all nut there's people who suffer from the opposite thing right to where they're like no I'm super advanced and then they accidentally fall into a major pothole there's a problem with sort of overestimating and under estimating so how do you how do you assess when you're no longer a junior DBA um so typically just in terms of job role junior DB a job role is usually following processes and responding to incidents and doing basic triage so the the typical role of the junior DBA is I am NOT deciding what Hardware to buy or how the sequel server should be configured or what kind of store I am following an established process and doing things as I have been before then they're sort of normal level DBA and the nor you know not senior yet but just sort of mid-level DBA that is usually speaking those processes of it we have a way of doing something but I am going to strip modify them and shape them and I'm going to try to automate stuff as much as possible I think between junior and mid-level is usually more of just a you know I've mastered these concepts about backup and restore I understand all of the items I can can figure and is familiar with the pros and cons of different settings you know that moving up to senior of defining processes and defining um sort of budget level stuff and you know what what our long-term plan should be an architect a high availability although sometimes it's more development focused or more info focused that sort of the third level um I have a post where I kind of mapped out what's the difference between a regular and senior DBA where I think I I'm asked these things out so and I think it's linked from that training plan I have at the junior DBAs alright ah so Harry has a comment and he says the breadth of what you are allowed to do is very important man Harry it really is and the problem is that the breath can be overwhelming as well um so one of the things I ran into you as a DBA was just people would constantly bring I never had a lack of things to do except at one job I didn't like things to but usually at different places the problem was that I had so much to do on different projects and different initiatives and different little things that broke that I actually had to learn to ask for time to learn the basics because you know people would just bring me all this stuff to react to and to help with and I had to actually be like I need to carve out some time in my schedule and I worked with my manager to you this I need to carve out some time in my schedule so that I can understand the difference between log shipping and mirroring and how close I need to work in our test environments that I really understand how clustering works and I'm comfortable with that I had to to work to get time to do that because sometimes sometimes you're not allowed to do enough but sometimes you are overwhelmed with so much that you have the opposite problem and when you kind of go to your manager I mean what I explained was I know here's what I really want to learn and why I want to learn it here's the time I think I need every week to do it and the training that I think I want and here's how I think it would help our team usually what my manager wanted for me was some kind of documentation of my progress and either doing a demo for other team members at the end or a write-up of what I learned or recommendations for some changes at the end just so that we could kind of get something out of it that he could share with his boss or her boss it the one I'm thinking of what to him but so that you know we could show others of hey we're spending these time to improve our skills and help the team as well but we have breath is a real problem like both ways all right so I love that there are more questions but I do have to go you guys for attending and fake the chapter for hosting this um please attend more sessions it looks like there's some really great ones coming up and for anyone going to pass I hope to see you at the House summit conference in Seattle Thank You Kendra this this was great and if I send you two questions that are that you might have missed which you maybe answer them in your blog so that would left okay I'll send that and thank you so much I wish I'd have had something like this as a plan when I first got started I'm actually go back and look at some of the stuff anyway so it's great ideas I'm sort of a in-between I'm sort of a regular DBA trying to get to a senior levels so it's a it's a fun road I really I think is a great profession so I think it's a fun path okay thank you and everybody remember the deadline on the code on saving money to sequel pass vc-1 5gb q6 you can go to our website and sign up there and our session next week is an extra session with the sequel server Tiger team sequel server performs monitoring baselining thank you bye

Channel: DBAFundamentals

Views: 65,163

Rating: 4.8373985 out of 5

Keywords: sql, kendra little, junior dba, senior dba, backups, rto, rpo, server configuration, dba, microsoft, microsoft certified master, microsoft sql server

Id: YXMg50mGo34

Length: 70min 21sec (4221 seconds)

Published: Tue Jul 12 2016