After Your SQL Server Upgrade – Upgrading SQL Server Webinar Episode 4

This article is part of our SQL Server Upgrades Made Easy series:

• Why Upgrade?
• Before You Upgrade
• During Your Upgrade
• After Your Upgrade
• Recap Chat

Upgrading SQL Server – Week 4 “After You Upgrade SQL Server”

You can watch the previous 3 weeks on youtube.

This is the last video from the webinar portion of the SQL Server upgrade webinar series from the past four weeks. Here we chat about the things to consider AFTER your SQL Server upgrade.  Health checks. Monitoring. Keeping the lights on. Frankly, this webinar is a must whether you are doing a SQL Server upgrade or not! In a week – after our live hangout video goes live, we’ll make a single page that has all of the videos, download links, references to blog posts and other content you might like and if we come up with more free checklists or downloads to help you with your SQL Server upgrade projects, we’ll throw it there and let that page be a living page. Subscribe to the blog or come back in a week to check it out!

You can also sign up with the webinar ninja series to get the full cuts from Webinar Ninja series and watch replays there and see any Q&A each week including the little crosstalk before and after the “recorded” portion of the webinar starts. Might even see me “dancing” sadly!!

Subscribe to the youtube channel to be alerted of the live chat hangouts and to see the upcoming talks in our continuing webinar series.

Towards the end of March, I’ll start with a multi-part series on High Availability/Disaster Recovery options for SQL Server. If you have questions, you may not need consulting, just some help!  That’s presicely the point of these webinars. Trying to help get some content to you of things to think about when there just isn’t a training budget but you’d like level up a bit. Perhaps in the future we’ll go 200/300/400 level and consider freemium and premium content. For now, though, these webinars will be live, recorded, and free to watch either way.

I hope to see you in the Google Hangout with some of the Straight Path team and I discussing upgrades on the live chat thinga-ma-jiggy at this link.

Downloads from the SQL Server Upgrade Series

(We’ll have a couple more pieces of content still to come on the final recap post that links to all the videos, and all the downloads and plus we’ll work to get links from all the videos when we make that “Central Page” for the webinar series! The upgrade checklist is ready but we’ll put that into it’s own post today, 3/7/2019!)

• Download our free Self-Assessment health checklist – (this link is a direct download of the PDF).
• Our (Hot off the presses) SQL Server Installation and Configuration best practices checklist (this link is a direct download of the PDF)

Transcript

(I’ve used a transcript service for this and I reviewed some of it. It’s a work in progress and there may be some errors!)

Welcome back to the webinar series. Today’s the last actual webinar in the upgrade series, so we’re gonna talk today about things to consider after you upgrade SQL Server. Again, like I’ve said a couple times in the blog post, and like I mentioned ahead of time, this webinar really has little to do with the SQL Server upgrade, but it’s my chance to extol some of my virtues on you when it comes to best practices and configuration and desired state and all the stuff that we should be worrying about but sometimes don’t have time to do. So, like we talked about before, when you’re done with a SQL Server upgrade or when you’re doing a SQL Server upgrade, this may be something that a company might do once every five years, once every ten years as some people here would say, and if we’re doing it so infrequently, what better thing to do than to use this as an opportunity to start fresh.

Pretend it’s like New Year’s Day for your SQL Server. You’re gonna finally eat better and workout or whatever your resolution is, except for this is gonna be much more serious than New Year’s resolution because you’re actually gonna do it, so when you’re done, how do we know we have a best practice to set up on our SQL Server? How do we know our SQL Servers are set up for success? And, that’s what we’re going to talk about today.

If you’re new to the series, this is where we have been. We’ve done a lot of things. We had a big picture at the very beginning. That was more just talking about why, why, why, so why are we going to upgrade? What’s the point?

Then, we talked about what do you do before you upgrade? How do you plan it? How do you ask the right questions and get the right people involved? We talked about checklists, and that’s one download I’m still working on finalizing for you, so that’ll be in the recap. We do have one PDF we’ll talk about in a second, to download today. From last week, the SQL Server health and best practice checklist, we finally released that, so you could download that on a blog post that went up today.

Then today, we talked last week about the upgrade, so how do you actually upgrade? How do you actually go in there and do your upgrade, we talked about … Again, in 30 minutes, I’m not gonna tell you how to upgrade your SQL Server from start to finish, but we talked about the important concepts.

I think the HA/DR web series might be a little more involved. We can stretch it out a bit and maybe do a few more weeks in there and actually do some demos, and AGs and FCIs and even log shipping. But this, I don’t wanna get into demos because there are so many. I’ll post the link. Actually, I’ll post it right now. If you just go to my blog. Just go to my blog, Jose, and you’ll see the latest post is the recap from week three.

In there, there’s two links. There’s only two downloads right now. There’s the 20-ish page SQL Server Configuration Guide, and then there’s the much smaller PDF of the Do Your Own Health Check guide. That’s cool, but the actual SQL Server Configuration checklist is a recap of all of our best practices that we look through when we do a SQL Server upgrade. Checklist is probably a strong word for it. It’s really a document with bullet points, but I’m sure we can probably turn it into a checklist if people would like it that way.

Then, the last thing I’m working on is an actual upgrade checklist. Again, I proposed … I might just make an Excel spreadsheet and post that instead of trying to make it PDF and make it marketing friendly. That’s the hang up here, is trying to get into PDF, but I’ll figure out the right format, and I’ll put that, when I share I this video from this series next week, I’ll share that.

Next week, I’m not traveling as much, so I should actually have time to get the blog post out by probably Monday or Tuesday, like I hope, and get the transcript servers to run sooner. Anyway, that’ll be the last downloadable thing we have there, is the upgrade checklist, or the before and after and steps there.

Anyway, we’re here today, we’re going to talk about after. We’re going to talk about what do we consider when we’re all done upgrading, you’re not just done when your upgrade is done. And if you think you’re done when your upgrade is done, you’re lying to yourself. You should never be done with SQL Server. We’ll talk about that in a second.

Then next week, we’re going to do a live hangout. So, this will not be on WebinarNinja. You have to go to that link right there, that YouTube.com watch and that will take you to, I think, to my channel, the Straight Path, our channel I should say, the Straight Path channel. Once you’re there, there’s a little remind me or whatever icon, you click that icon and Google will somehow let you know, maybe give you an iCal to remind you for the live hangout. Next week, same time, we’ll have the whole Straight Path technical team, or a good portion of us anyway. I know Tara will be there. I know I’ll be there. I think Mike Clark will be there, I’m not sure. And maybe Jack and Joe, and maybe Bruce. So, we’ll have the whole technical team there to answer your questions.

If you have questions, we’ve gotten a couple, if you have more questions, whatever they are, if it’s about a Sequel upgrade or even about the health checks and the best practice stuff, bring the question with you live. Or if you’re too shy or you can’t be live, we’ll record it, just send me an email to mike@straightpathsequel.com and we will answer that question on that hangout. We’ll record it, and when this whole series is all done next week, when I do that hangout, I’ll take the video from that and all these other videos and blog posts, and we’ll make one big, sort of like a spoke or hub blog post that lists all the videos in order with more download links. And as I re-watch the videos and listen to my annoying voice, I say, “Oh, I should probably add that,” so I’ll add a lot more extra links that nobody asked about but that come to mind while I watch it. So, that last post will hopefully be a guide and maybe even have some checklist steps in there.

The goal again, is not to upgrade your SQL Servers in five 30-minute webinars. It’s really to get you thinking about the right things, and to help get you your questions answered and to put you on the right track.

So, thank you for coming back, by the way. A lot of you came back and I appreciate that. You stuck with us, so either you have nothing else to do or you appreciate the content.

Who am I? You all know by now. Most of you know who I am. I’m Mike Walsh. I run Straight Path Solutions. We’re a SQL Server consultancy. I’m not doing this as a SQL Server consultancy thing, I’m doing this really as an MVP thing. I’m just doing this as a person in the community trying to give back. Whether I keep my MVP or don’t, I don’t care, I just like sharing. Our company does a whole bunch of things. But one of the things we do a lot of, being a consultant company, is come alongside people and do upgrades.

As a result, I’ve been burned by a lot of upgrades, and I’ve taken over upgrades that they didn’t think about these things ahead of time. They didn’t go through a checklist. They didn’t do best practices. We’ve been there to catch people when they fall from upgrades not well thought out. I want to avoid that for you. I want you to avoid that.

Where are we? Again, there’s some recaps if you go to straightpathsequel.com/blog you’ll see pretty much all the latest posts there. There’s one availability group post in there. But, the other latest posts are all about upgrades. I have a couple more posts in the queue, in the draft mode, that will be about upgrades as well, and again, we’ll have the big recap post next week with a bunch more links. Really, the content for the rest of the next week or two on my blog will be about upgrades. I’m going to switch over and make it HA/DR stuff, because that will be the next webinar in March.

Again, the same idea. We’re not going to go in depth. We’re not going to be 500 level material. I want to start at the beginning and what should you think about before. Which approach, how do you do RPO and RTOs, and how do you balance budget, and can AGs do this? Can FCI do this? How do we mix and match, and how can we do it on a shoestring and what shouldn’t we do on a shoestring, and all these things. I want to talk about before, during and after. In fact, every single webinar we do, I really want to hammer this whole idea of things to think about and do before, things to think about and do during, things to think about and do after. I think if we simplify it to that, we can make simple. Right? That’s my hope.

The next series will be probably two weeks after the live hangout, so towards the end of March. That will be on, again on High Ability DR, so the content will start switching over the HA/DR at that point, I think.

By now, we should know all the things we knew last week, the week before. We should know why we want to upgrade. We should know what we need to think about. What questions we should be asking. You know, who, what, when, where, and why, from two weeks ago video. We should really be knowing where we want to go, how we want to get there, and really why we want to go.

Now, we should also know how because last week we talked about how. If you all remember one thing from last week, I don’t like in place upgrades, and really frankly, you shouldn’t either. It doesn’t save you time and the rollback can be bad. If you have no choice, you have to be careful and plan it out, and spend extra time making that rollback checklist in the go, no go criteria. But you should have a better idea of how you want to upgrade. How you’ll test it. Who’s in charge. That sounds so simple but so many times you can have conversations and somebody thinks that they just got assigned a task … or they think they assigned you a task but nobody knows because nobody’s just done that little … here’s the plan, here’s your role, here’s your role, he’s your role, and people who asked before, I forget who it was now, maybe Dale, you know, what if four weeks ago, you have all the hats? You’re the DBA, the SysAdmin, or again, well have the conversation with yourself again to understand what the right roles are and what the right pieces are.

Then, we’ll talk about the rollback. Talk about the steps, and that’s what we should know from the before, and during upgrades. That’s it. We’re upgraded. Congratulations. The webinar series is over, right? No. No. Remember we talked last week about this. Y’all remember what this is, right? You don’t have to all type in chat but it takes hard work to type a few keys, but we know what this is. This is the Show Time rotisserie grill. Actually, I think my in-laws have one and it actually works okay. You can cook Italian sausages in it, by the way. But I’m not [Roan Po-peel 00:10:07]. It’s really good if you don’t like greasy Italian sausages in spaghetti. But anyway, I really digress now.

This is a chicken making machine. It’s set it and forget it, is what he had all the people in the infomercial audience saying over and over. “Set it and forget it.” This is not the SQL Server rotisserie chicken machine. This slide right here, while I’ve the changed the SQL Server logo, the very first talk I ever gave on SQL Server, one of my slides was the rotisserie chicken machine with a SQL Server 2008 logo on it. Probably 11 years ago. I had this presentation I gave called, “So You Want to be a DBA? Where Do You Start?”

So many people, not you because you’re here at the webinar and you care about the Sequel community and you care about learning, but so many people think that SQL Server can be set and forgotten. Right? You install it really easy. There’s a few more clicks to have to do, but for the most part, it’s next, next, next, next, finish. Then you can go away, have some of your, no brand name, have some of your coffee, a little sugar. It’s Americano. But you can just walk away and come back and poof, you have a SQL Server. Then, you can take your installer from your client, for your ERP system, or whatever application you’re using to do critical business, and you can install that application and it will create it’s own database, for the most part, or maybe you have to create it. Then you can walk away and you have a SQL Server.

I can’t tell you how many health checks I do for clients, big and small clients, where it’s like, “Oh, you don’t take backups? Did you know you don’t take backups?” “You don’t do check DBs. You don’t do this.” I see it all the time. Not you. None of you do that. You’re watching this video, your here at this webinar. You’re not doing that because you care about best practices, but other people do.

SQL Server, and I’m not a slide guy so you can make fun of my animations all you want, but there you go. That’s the extent of my animations. Rotisserie chicken, SQL Server, no. A bouncing no, too. And a misshape, but SQL Server’s not set it and forget it. That’ the main point of this one webinar. We’re going to talk about just again, things to keep in mind.

This almost goes back to one of the before webinars where I told you that you have to do a health check first. If you were obedient and you did your health check before, you’re going to be okay, because you saw what was bad and you care, and you made a list, a checklist even, of what you want to do new and better on the new server and you fix it. Right? Didn’t you? I hope you did. I hope you’re going to.

If you didn’t do the health check and you’ve upgraded and you did a set it and forget it upgrade where you just do your upgrade and your done, well, it’s not too late. You can do a health check. In fact, I recommend you taking one of the free health tools out there from all the people in the Sequel community who worked hard to build them, and do a SQL Server health check once a year. You know, does DBA checks, [inaudible 00:13:21] I don’t mention here and in fact I actually, I’m going to make a note here.

Normally, our office manager comes to the meetings to take notes but she’s actually on a client call. Some of us have to do work. So I’m just going to make a note here in my notebook. Give me a second.

I’ll make sure I include some links to other tools. I mentioned Glen’s script. I mentioned Brent’s blitz scripts, but there’s so many other ones. And I’ll mention a few others. Try a different flavor. But, once a quarter, once a half year, once a year, do yourself a favor and give yourself a SQL Server health check.

Why, Mike? The configs don’t change. I know they don’t change but new databases got created. New users got created. New logins got created. All things have changed. Maybe not your max [dop 00:14:01] and your max memory. Hopefully that’s not changing as you go, but it could. You could have other people who are helpful DBAs in there, or vendors with the wrong level of access because you didn’t fix security, undoing your best practices.

So, after you upgrade, and if you never upgrade, and if you go to the cloud you never have to worry about upgrades, do health checks. Make sure your server’s good. Seriously, it saves a lot.

Dale is saying, great point Dale, he says, “Applications change and new store procedures get installed. They may not perform as expected,” and there are some client’s tools out there that will actually change your system configuration because the vendor thinks they know best and they need SA for the installer, and the installer will actually change some settings. It’ll turn XP command shell on, and then when you get audited in nine months, the auditor says, “Why is XP command shell on?” And you say, “It’s not. I turned it off.” “No, it’s on.” And you say, “I don’t know who did it,” and you turn it off and then you get a phone call from accounting saying that the system’s not working.

You can not just install and walk away. The DBA, whether that’s your official title or not, is the person at the company, again I’m being, this is hyperbole, sort of. But you as the DBA, I think it stands for Database Advocate, you are the one who cares about the data. In fact, I’ll say … and you can write me a letter if you like, you’re the only one who cares about the data in the company. That may not be true, but you should act like it’s true. Act like your the only one who cares about the data, and treat it well. That means do health check, that means when that bad application vendor tries to do something naughty, you get mad at them and you say something about it.

Some best practices evolve, right? If you were to get a SQL Server health check from me, 10 years ago, before … I think it was before Glen started writing his diagnostic scripts, maybe about the same time, but anyway, 10 years ago before I had a blitz or Glen’s scripts and I had my own …

I had a blitz subscription. I kinda had my own. I bet you there are things that I would tell you about today, that I didn’t check 10 years ago. There are things that I forget about today, or that I find today, that I didn’t even think of three years ago. So some best practices evolve and it’s okay to learn but the trick is you have to keep learning. Right? So towards the bottom in italics there, I say, “Always learn.”

Part of what you do after your upgrade is don’t just move on to other things and forget about your basic PBA skills. Go to classes, take webinars, come to the free HA/DR webinar. Maybe we’ll do one on SQL best practices and performance tuning and configuration. Learn about SARGable queries.

I know we should all know this but I keep finding the same problems over and over so somewhere, some place, people aren’t learning. And, so, if we learn and we do more, we’re good.

So I’m reading from the bottom up. But do the health check. Again, our PDF is a real basic step. So over the two downloads we have, the PDF. One of them is just a real small, “Don’t forget about these important things and people get burned by these things.” Those are the things we see most often getting burned by. The longer document, the one that came in today that we just kinda- hot off the presses today, that has a lot more details about how we configure SQL.

But the trick is be intentional. Intentionality is important, right? So here’s a list of things. Right? What’s your max memory set? Did you set it? Again, it’s never said. Do you have power saving enabled? That’s always set these days still. Is your Tempdb configured best practices?

The nice thing about 2016 and 2017, by the way, is the installer tries to manhandle you and force you to do Tempdb the right way. It even forces you to turn on, if you click the little box, you understand what the box says, perform volume maintenance. Which gives you instant file initialization. I have to say that slowly. But it turns that on for you.

Configure your MAXDOP. Configure your max memory. Do maintenance. Don’t just install SQL Server and run away from it. Do your maintenance on it. Do your indexes.

Now, there’s lots of schools of thought about when you should rebuild and reorganize your indexes and should you do it and forget about that. There’s a debate and I actually think both of the sides of the debate have merit. There’s no debate about should you update statistics. Yes, you should. And if you’re not doing it, you’re in trouble.

Are you doing a backup plan? Are you taking a nightly full, daily diffs, transactions every 15 minutes or hour? And if you are, is it corruption resistant? Actually, I’ll take a side step and talk about corruption with just some background here. I have a blog post. It’s really, really long about it and maybe I’ll put it into one of my links. But I can shorten the 4,000 word blog post. I think.

If you take a weekly CHECKDB, like most people do or a lot of people do, ’cause you don’t have the performance hit available to do more frequently and you don’t have the ability to do on offline server and all that. If you do a CHECKDB weekly and, so, let’s say we do Sunday.

Sunday morning, do my CHECKDB, and it’s great. So cool. We have no corruption last week. And, now, we do our CHECKDB next Sunday and there’s corruption. When did that corruption happen? Anybody know? Do you know when the corruption happened? You might know if a user got an error message that told them that, “Hey. You’re trying to talk to a corrupt page.”

What if you have a big database and nobody talks to that corrupt page except for CHECKDB when it runs? So some database page got corrupted and based on the error log and how you look at it, you may or you may not- In fact, I would say, based on the experience people call us up in a panic, you likely won’t know when that corruption happened.

So now you have a corruption that you don’t know when it started. What are you gonna do? What are your choices? You can run a CHECKDB repair allow data loss and that allow data loss is serious.

You don’t really get to control what data is lost and you can kinda see what data is lost, potentially, but you might not know what data was lost. So now you say, “Hey. The good news is we don’t have corruption anymore. The bad news is we’re missing some data. I don’t know what data we’re missing but it’s probably one of these tables. I think.” That’s not a really confident thing to have to be able to say.

Or you can restore the Monday backup and say, “Are you corrupt?” Restore the Tuesday backup. Are you corrupt? Restore the Wednesday. Corrupt? Thursday? Oh. Yup. We were corrupt on Thursday. So we’re gonna go back to Thursday, when we had no corruption, and anything that happened after 2:00 p.m. Thursday, we’ve lost the data. Sorry. Only three days a day. No big deal, right?

Or you could have a corruption with just a backup strategy. And all that is is keep all your logs. Keep every single transaction log backup for the week and you have to be doing a backup, your restore tests and verify your backups and actually do restore tests periodically to make sure you don’t have some long standing IO problem causing your backups to die.

But if you do this- if you do a regular basis keep your transaction logs until you have a good CHECKDB. So keep your fulls and diffs and, really, the most important is you keep the full right after your last good CHECKDB log right up until the point you know this week’s CHECKDB is good. And I’m paranoid so add a day or two. Storage is much cheaper than data loss.

And if you have corruption that you detect on Sunday, you have two choices now. You can try and figure out when it happened and try and restore around it and see if it’s just missing an- Is it an index problem and we can just rebuild the index and say, “Whew! Dodged a bullet.”

Or you can restore the last good full. So that’s way back to Sunday and then restore every single transaction log backup since Sunday to this Sunday and poof. Your corruption is gone.

Because, remember, the corruption exists in the data file. The MDFs. So your data file is corrupt. The log file is just a replay log of redo this transaction. And the transaction- SQL Server doesn’t have a corruption transaction. Some weird bug notwithstanding which is very rare. 99% of the time or more, it’s gonna be your hardware or your drivers at the operating system level going to the storage layer causing the problem. Not SQL Server causing the problem.

So, 99% or more of the time, the corruption will not be in your log file. Because, again, there’s no instruction in the log file that says, “RelOp do this. RelOp do that. Blah, blah. Corrupt data.” All it’s gonna do is replay your inserts, deletes and updates. And that’s all a transaction log backup is. It’s a replay of those items.

So what you’re able to do now is you’re able to institute a downtime for an hour, maybe two hours if it’s a big data base, on Sunday morning. Big whoop. You’re gonna save all the data. It’s okay. They’re still gonna parade you around like a hero and restore every single transaction log.

And that’s easy to script out. You can find millions of scripts on the internet. You can do with DBA tools on IO, the PowerShell set of scripts.

The point is you can restore the full and every log and because you had a corruption with just some backup strategy because you kept all those logs, you now have no problem. That’s a big deal and most people don’t do that.

And I work for a couple vendors as a- I don’t work for them but they contact us. So we’re kinda one of their go-to people for clients with corruption and other problems in environment and I hate the conversation with the vendor’s client when I say, “Hey, what’s the problem?” And say, “Well, we have this error message. It’s 823, 824, or cannot continue scan.” Or whatever the error message is. There’s so many of them. It’s corruption.

And I say, “Well, when’s your last backup and when did it start?” Sometimes I don’t know when it starts. When was your last CHECKDB? Well, we don’t do CHECKDB. And the corruption has potentially been there forever. For weeks or months. And some data is gonna have to get lost.

Or you can call Paul Randall and he can potentially play in the Hex editor and do some magic if you luck out. We shouldn’t use luck to manage our SQL Servers. That’s why we have to make sure that we don’t treat our SQL Servers like a rotisserie chicken machine and do set it and forget it.

So what’s the other thing you can do when you’re done? Document. I know. Raise your hand if you like documentation. Right? I like reading documentation. I gotta be honest. I’ve been doing IT for 20 years. Documentation is one of those things that I need to be better at still. And after 20 years, I don’t know, should I stop saying I should get better at it? I don’t know. I don’t like writing documentation.

I like fixing the problem. I like getting in there and solving the issues and slaying the dragon and then leave me alone. Pay my bill if you want and then leave me alone. But we can’t do that. We can’t just do our upgrade and then say, “Leave me alone.” We have to document.

So I know you don’t like it but it really, really does help. You’re not gonna be at your company forever. Now, if you are here and you’re with Straight Path, I hope you will be. I like the team we have right now. But you probably won’t be at your company forever. Somebody else is gonna take it over. Do you wanna be the person that’s talked about as, “Yeah. I remember when Mitch or whatever, some name that’s not in the chat room here, was here. Remember what he did? He messed this whole environment up, didn’t he? We have no documentation. What a loser.” You don’t want that.

You want people to say, “Be more like the DBA who just left. Look at this document. Look at this book that he gave us. The run book.” This is just my notebook. “But look at this run book that the DBA left of all these they’ve done.”

Make a run book. What are the jobs supposed to be there? What’s the desired configuration? How should it be? How did you configure it? Why did you make the MAXDOP eight instead of four? Why’d you make it four instead of eight? Why’d you make it one instead of four? Why did you set CTFP? Why did you do this? Just a simple document that describes what you did.

Take our PDF and talk about those things. Make your own version of that for your company. When we install SQL, this is our standard. Make standards. Make documents of your environment. Describe how you do the upgrade so that when you go to do this again in four years, it can be easier.

And if you have a problem and if you have a really big problem where you don’t have HA/DR. We’ll talk about in a few weeks in our webinar series. But you don’t have a good HA/DR plan and your system is down and you have to rebuild stuff. Well, your run book can help you.

And save the sign-off email. Cover your butt. Save the sign-off email from, again, we talked last week about during. Get a sign-off email. Get somebody, other than you, to say, “Yup. I concur. Our environment is really, really, really strong and it’s a good environment and the upgrade is successful and we’re happy.” Get that email and save that email. Get a folder in your inbox about the upgrade and keep it there and have something to go back to.

The around-to-it list. The around-to-it is someday we’ll do it when we get around to it. And I’ve never gotten around to it. So I don’t know what that means. But I always say, “I’ll do that when I get around to it.” Nobody’s ever given me around to it. I think it actually means, “I’m never gonna do it.”

When we do upgrades, we sometimes get a later list. Everybody has a different list. Some companies, the upgrade policy is: Let’s just upgrade and don’t change anything. We wanna keep it as streamlined as possible.

I was just talking to a customer yesterday. They’re about to do a tech refresh and the tech refreshes parameters are: This has to look exactly the same and we’re done. Development has no time to do any changes. We don’t have any time to change anything here or there. We can’t tweak things. We don’t have time to do full regressions. I had a long chat.

But, anyway, the idea is we don’t have any time to do a lot of changes so change as little as possible. Don’t change log-ins. Don’t try and make things better. Just upgrade them. And I say, “Well, let’s have a conversation about that.”

But sometimes that’s what your business will demand and you can say, “No. I’m not gonna do it.” And go like that and just stare at ’em but that may or may not work. And depending on how else the job goes for you and what’s good and what’s bad about the job and what the job market is, you might win the argument by turning your resume or you might lose the argument by just doing the upgrade.

So, sometimes, we have a later list. A lot of times it’s around security. Nobody wants to mess with log-ins. Nobody wants to delete that database that I don’t think anybody uses. But, man, somebody might be using it. So document your later list. Document the things that you didn’t do that you would love to do and make a pick list.

Yes, you have to keep the lights on. As a DBA, depending on the number of stores you have, you’re gonna spend five hours a week, maybe 10 hours a week, keeping the lights on. Respond to user request, doing the little log-in things, the periodic patching, restore tests that you have to do manually. Just all the little stuff that you have to do as a DBA. That happens.

And then you have the projects and you have a lot of meetings when you’re a full-time person. A lot of meetings. I have meetings too but at least I get paid to be in a meeting. I don’t. I pay myself by the year and not by the meeting. But you get less meetings as a consultant.

And then you have a little bit of time to do projects. So take all your later stuff and make a project list and then prioritize it. I would say one of the most important things for me is backup and recovery. If your backup and recovery-

A long time ago I had a manager named George. And I have a blog post that talks about this and I’ll make a note to put that blog post out there as well. So I’m a DBA where I start or whatever it’s called and that was one of my first blog posts in 2009.

But I had this manager named George. And maybe three or four years into my career. So I was a DBA and I knew this stuff but he sat down and said, “Hey, listen. I’ve been Oracle DBA my whole career and I wanna give you some tips. As a DBA, we should care about the itties of database administration.” The I-T-T-I-E-S. The itties of database administration.

I look at him in the office. What the heck are you talking about? I was like, “Are we going some place inappropriate? What do you mean?” And he says, “Recoverability. Security. Ability.” He said, “Performability.” The itties of database administration. And I have a whole blog post that talks about this.

There’s two that, I think, have to have a certain order. After these two, it can be any order that your company needs. If you can’t recover, that shouldn’t even be on an around-to-it list. That should not be on a later list. That needs to be on the I’m gonna do right now list or my job is in jeopardy. If you cannot recover your database, right now, leave this webinar right now and fix that problem.

After that, I think security is the next itty to worry about. Because security is really important. Because if you go and make all of these best practice changes and then somebody else, well-meaning or not, comes behind you and tries to fix these things for you. You weren’t asking them to help you.

We talk about with our kids. We say, “Listen. It’s good to help but if you aren’t sure, don’t just do something that you don’t think we want. Ask first. Don’t do this if you’re not told to do it.” We have to tell them, “Please help me tell you to help and don’t help in areas that you don’t understand yet.”

But your vendors will do that to you. Your other staff if they have access. I worked at one company and we had an off-shore team in Ireland and I came in as a new DBA and I secured the environment. I locked it down tight. I said, “We’re gonna do group-based and role-based.” We made a DBA group and I put the DBA group into the SA role. Everything was good. And then I went ahead and started fixing settings.

So recoverability first, then security, then after that, you can work on best practice config, performance, and all the other long-term stuff that you’ll do for your lifetime as a DBA. But you have to do those two things first.

So, anyway, I did the security thing and then I fixed MAXDOP, I fixed max memory. I did a bunch of settings changes ’cause it was a small environment that was- Actually, it was a busier environment. But, anyway, it was mismanaged and it was a lot of easy things to fix.

Well, I happened to look back at the server and said, “Hey, who changed MAXDOP?” And there’s only two people in the DBA group. And, anyway, what happened is one of the developers in Ireland called Help Desk and said, “Hello. Would you please add me to the DBA group?” And the Help Desk said, “S-

Would you please add me to the DBA group? And the help desk said, “Sure. No problem. Anybody else?” So, the Irish team was in the DBA group. Like, no, why did they do that? So I learned something. That you’re only as good as everybody else. It’s like the hiking rule, you’re only as fast as your slowest hiker. You’re only as secure as your least careful member of the team. So then we had to talk to the help desk and have a policy that, “No, you should make a group owner.” And the DBA team is a group owner, and nobody comes into our group until you get permission from the DBA team, so we identified another hole.

But anyway, fix your recoverability. Fix your security. Then you can start doing encryption at rest with TDE. You can make the auditors happy. You can start doing SSL encryption. By the way that’s another webinar series I’d like to do if you all think it makes sense. But I’d like to do a webinar maybe towards the end of April, May when we’re down the HA/DR one about security and encryption. I’m not a guru in there at all, I do a lot with clients that I’m – [inaudible 00:33:04]. But I’ll see if I can get Brian Kelly or somebody to join for a webinar if he’s willing or two. Because he’s like a security whiz and guru.

Do your restore tests, make sure you offline your backups that we talked about last week … if you don’t have an offline, not connected to the internet copy of your back up, it’s liable for somebody malicious to delete it. Then you can look for those unused databases, track them down, and delete them. So, you can have a later list, it’s okay, just make sure you don’t make it a never list, because sometimes a later list can be a never list.

Patching, yes! We have to patch. I’ve evolved – I told you, the best practices evolve. I’ve evolved my patching stance, in fact, it depends on the day it changes. Here’s the deal, everybody’s opinion is different, and I’m curious what you all think. If somebody has a really strong opinion go ahead and type it out there.

[inaudible 00:33:55] there will be no more service packs. Before then there was, and is, service packs. I’m a fan of applying a service pack after maybe one or two CUs.

And why? I used to be afraid of patches from Microsoft SQL Server team, ’cause there were some problems. The 2008 days, the 2005 days, and in 2000 days. But it got stronger, like 2012, there were a couple problems here and there but 2012 and 2014 were fairly strong. Then we started having a couple weird things … was it one of the 2016 SPs? It’s all a blur because I have so many versions. But one of the SPs that was recently, in the past five years or so, caused potentially corruption on doing an online index rebuild. I think it was 2016 SP1, but I actually forget.

I want to put another link, SQL Sentry has an amazing little website where they actually list all the builds, and they have red flags above the builds you should avoid and quickly patch after. Anyway, I wait now, maybe a CU, maybe a second CU before I patch a SQL server to a service pack. I try to keep all my service packs current, and up until 2019, I’ve always had the opinion of saying, “I’m not going to apply every CU that comes out. I’m gonna analyze it and it see if it effects you, or effects me.” Now that 2019 is moving to a no service pack kind of patching model I think I’ll probably be more apt to just kind of go over the latest CU. But I might be more apt to go with the second to latest CU. If CU ten is the current CU, I’m going to do CU nine, once CU ten comes out. I’m gonna look at CU ten and see, “Are there any fixes for CU nine?” If there aren’t, I’m just gonna put CU nine out, and then wait, and then just always be one behind.

That’s the patching that I am implementing, and I will probably push straight path toward doing with our clients, is always be just a little bit behind everybody else. Let somebody else get the nose bleed for you.

If anybody has a totally different opinion, type it out in the chat. There’s a bunch of you here, a few of you are chatting, type it out if you have a different opinion. If you don’t, that’s fine. We’ll talk about it next week too.

So, does your SQL server talk to you? Yes, it should. A good DBA, and I’m kind of joking, but a good DBA should get phantom buzzes on their hips. If they’re on vacation, they should think [inaudible 00:36:17] … I’m joking, you should actually not get phantom buzzes because you should have stopped all the noise. I’ll talk about that in the next slide.

Do you have database mail set up? Do you have agent operators? That’s just so basic. You don’t have to buy any tool for that, you can set up a database mail, set up an agent operator, and then set up alerts. So your SQL server can at least tell you when it’s really unwell, it might not tell you when it’s sort of feeling like it might be sick next Tuesday, but it will tell you when it’s barfing.

Then do you take regular baselines? You can do that for free with a tool like PAL, I’m gonna have a blog post about that, and PerfMon. Get monitoring tools, there’s free monitoring tools out there. There’s OppsManager, DBA Checks, Steve Steadman has a tool out there, I think Daniel Janek might have a tool … but there are a bunch of tools that are free. There’s that new one that people keep talking about which I’m actually drawing a blank on the name, but it’s on my … ’round to it list to download and play with. I forget what it’s called now, it’s making me mad. Anyway, once I’m done with this webinar it’ll come to me. It looks pretty cool actually. I’ll put it in the links when I have it, and I apologize to the makers of this tool. But it’s a community based tool for SQL Server monitoring.

There’s a lot that are free. There’s some great paid ones. I’m a huge fan of SQL Sentry, I don’t make a lie about that. I’m a partner of theirs, so we use that tool. All of our … DBA customers, so we have a lot of clients where we actually are their DBA team, or we’re their senior DBA team for their existing DBAs. Mentoring and coach them in DBAs. All those clients, we give them an included license of SQL Sentry because I just, Sentry-1 … I find them to be the best. I think Solaron’s DBAs’ not a bad second tool as well. So there’s some great paid ones that do a little bit more than the free ones, as well.

But monitoring tools are not expensive, they’re not as expensive as downtime. They’re not as expensive as you spending hours trying to find the worst query. Now Query Store helps for sure, but I still like the monitoring tools to help pinpoint where I’m gonna go. I’m still a big fan of just knowing how exactly how to go into the monitor that I prefer, get to the time, see the query, find the query, tell them why it’s a bad query, give them the plan, and just solve it. I do that with SQL Sentry, you can do that with Query Store, and sure you can do that with the free tools as well.

The trick is, I don’t care how you do it, make your SQL Server talk to you. That’s the trick! It can’t just have it talk to you, you have to actually listen to it. This is a slide that came to mind. If you know Seinfeld you know that scene. He had made a reservation for a rental car … but he shows up and they didn’t actually hold the reservation for him, so you know, he goes, “Take your reservation! You don’t just hold the reservation.” So, you know how to have your SQL Server talk to you, you just don’t know how to listen to is. Having it send you an alert is cool, but I’ve gone to many shops … when we do health checkups and we go, “Hey, you know you’re getting … you’ve had like 75 emails a week for a Severity 20 error?”

Yeah, yeah, we got a lot of noise from SQL, so we turned that down, we stopped that alert. “That’s a big deal, why did you stop that alert?” Because we get so many of them, so you can get alert fatigue. Alerts are cool, but ignoring them is common, so try and be uncommon and don’t ignore them. Now, part of that, is that you have to filter the noise, and that’s hard work. I know, I run a support company … we do DBAs as a service. We’re like a senior DBA team for people, and we have a lot of alerting tools and all of our customers. We get noise, we get noise that we’re trying to filter through and it’s hard, but if you put in the effort to get rid of the noise, you’ll find the meaningful alerts, and be able to fix a problem before your users call you.

Your goal as a DBA should be, “My SQL Server is going to run all the time, I’m gonna prevent the alert from happening.” Your second goal should be, “When something bad happens, I’m going to know about before my users tell me about it.” And I’ll tell them, “Hey, there’s an issue, we’re working on it.” That’s so cool! Your users will be like, “What? Did IT just send me an email? And tell me there’s a problem? They told me and that they’re working on it?” That’s amazing. Imagine the power company called you and – well, now they do with the text message but, anyway … you want to find the problems before the users do. Because then you have more chance to solve, and then the impact is less widespread.

New features. I can’t talk about the new features here. Every version has so many. In fact … my team [inaudible 00:40:52] blog posts, we have a lot. I’m trying to get a blog post together with an ensemble blog post with all the team to talk about their favorite new feature. There’s so many new features in SQL Server. There are so many people who could’ve used windowing functions for the past 15 years. There’s security features, there’s dynamic data masking, there’s a new feature, SSMS, for data masking of copies or clones. There’s so many features out there, but we don’t use them! So learn about them, and use them. Learn from the SQL community, get … past motto was, or I think it still is, “Connect, share, and learn.” I think that’s what is it, “Connect, share, and learn?” Sounds right. But connect and learn I know, I’m pretty sure it’s share.

But connect and learn, right? You belong to a community of people who want to give so much knowledge to you, take advantage of it! Go to the pass events, go to Group-I conferences, go to webinars like this, or watch the recordings later. Read blog posts, and just learn, and look at all these new features you’re not using because you’re on SQL Server 2008 or 2012, still. Stop doing that.

HA/DR, after the upgrade if you said somebody would do it, it’s time. It’s time to give yourself some high availability. You can do it without having to spend a million dollars. That’s what we’re going to do … I’m gonna have a blog post next week about the next webinar series, I wanna talk about high availability DR. I wanna talk about the matrix, if you will, of RPO and RTO. The business wants zero and zero, and you say sure, this is the number of dollar signs. I want to talk about that ratio, and talk about how to negotiate and how to plan and how to think about an HA/DR.

So, again, like the upgrade series you’re not going to leave that building a bunch of HA/DR solutions, but I hope to give you some talking points, and even more than just the upgrade. Frankly, we kind of said, “Hey, let’s do this.” I’m actually gonna do it, I’m gonna stop talking about it, ’cause at my ’round to problem I described earlier, I just wanna do it until waiting until I get around to it. I’ve been wanting to do this webinar series for two years, I just keep making excuses. I just gunned this one to do it, the HA/DR one I’m giving more thought to, I’ve been doing a lot of stuff behind the scenes. We’ll have a couple downloads for you for that as well, even ahead of time.

But I wanna talk through what you should think about when you do an HA/DR solution. So, it’s time now to do it. Even if it’s just using your VM HA/DR, even if it’s making a filler or a cluster, if it’s an availability group, even if it’s a basic availability group, log shipping is still a great approach [inaudible 00:43:17] to help you with that. Just lots of ways that we can tackle HA/DR now that we’re upgraded. Because if you went from SQL Server 2008 to SQL Server 2017, you’ve a world of options available to you that you didn’t have before. So, now is the time to do it.

Recap and homework. Remember, you’re not done after you upgrade. You’re never done, until you quit, and you decide to get out of SQL Server, and just go be a farmer full-time. Don’t do that, because farming full-time is interesting. We almost didn’t have the webinar today, a few of my cows jumped the fence and started running around the backyard. The backyard is still their pasture but it’s wintertime, and the electric fence is just three strand electric. In the wintertime, you need to get an electric fence to work, you have to be grounded, and touch the electricity and feel it. But if you’re walking on a bunch of dry snow you’re not grounded, so they don’t care about the fence necessarily. The up close fence is all woven wires so they can’t go through it, but apparently I just learned today they can get over it when the snow gets high enough. They can jump over it. I wish I saw that, ’cause that would have been a fun sight, and a horrifying sight.Anyway, a few cows … they’re all back now. But you can retire to something more peaceful than farming, you can retire and just go be a … somebody stacking umbrellas at a beach or something.

Until you get to that point where you stop doing the SQL thing and hang up your SQL tools, you’re not done. We have to be always evolving our knowledge, always be learning, always be thinking about that next thing. We have to be intentional. It’s so easy to get stuck into responding that we forget, so be intentional.

Again, next week, the other part of the homework is that we have a webinar that’s going to be live. It’ll 20 minutes, 30 minutes, 40 minutes, no more than 45 I bet, unless we have a ton of questions. I don’t think we will probably, but we already have a few in email. Send me an email with your questions, or just come to the live Q and A, and ask your questions. We’ll all be live on camera, you can just be in the chat window so you don’t have to worry about doing all your hair and makeup like yours truly did. Just show up there, and learn.

Again, we’ll have about a two week hiatus from the webinar series, and we’re going to go right into the next one. HA/DR, and I’m thinking that’s going to be a six or seven week webinar series. If I finally stop my idea-ing. That’s it! Anyway, thank you. That’s that.

So, I talked about the chat here, so again go to this link, and I’ll put this link onto my blog post. Actually, it already is my blog post I think, if not I’ll go in there and edit today’s blog post with this. Actually, can I put it into the chat … yeah, I can, just give me a second. I can’t, I’ll put it in the chat in a second. I’ll figure out how to do it. I’ll put this in the chat when I’m done with the link here. Apparently I can’t edit this while I’m delivering it, but I’ll put this link here so you can get to it, or just go straight passed SQL. If you just go to my blog, go to one of my videos, you’ll see the straight passed channel, there’s not many videos there, and you’ll see this live webinar.

So, thank you! Ask your questions, get them to me, and that’s it. Now I can say thank you. Thank you so much for coming to the webinar, I really appreciate it. My goal here is to help you and teach you. My goal here to give you knowledge. Mike just shared the video link, so thanks Mike.

So, thanks for coming, I hope this helped. Next week is the wrap up webinar and ask your questions.