This week, Brent, Erik, Tara and Richie discuss severity 16 error warning, best way to move databases from SQL 2008 R2 to SQL Server 2014, adding Analysis services to a cluster, taking to higher management, deadlocks in a transaction log shipping setup, syncing date between an on-prem and Azure SQL DB server, log backups, renaming SQL Server VMs, Docker, SP_Blitz question, and more.
If you prefer to listen to the audio:
Enjoy the Podcast?
Office Hours – 5-30-18
Can I ignore severity 16 errors?
Brent Ozar: Alright, let’s see here. Steve asks, “I set up the alerts on my SQL Server per y’all’s suggestions, but why do all the references I can find say to ignore the warning severity 16 occurred on server name?” I want to say sev16 is one of the ones that you can – it usually indicates a T-SQL problem, so people say you shouldn’t worry about T-SQL problems. I still want to know. I still want to know if those kinds of errors happen. But if you have the kind of crappy application that throws sev16 errors all the time, I can understand why people would want to mute it.
Erik Darling: Yeah, you know, if you have so many errors coming out of your application that you’re getting error fatigue or alert fatigue, I would just limit it to the really high severity stuff like 19 through 25 so I know when stuff is really hitting the fan and not necessarily not every little peep and squeak the SQL Server makes.
Tara Kizer: It looks like our script does do 16 to 25, so maybe 19 to 25 is a better choice.
Brent Ozar: And I always wanted to know 16, but you’re not the first person to say that too.
Should I upgrade in place or build a new server?
Brent Ozar: Augusto asks, “Hi, if you have to choose between in-place upgrades or migrations and you’re moving between SQL 2008 R2 to SQL Server 2014 and you have a tight maintenance window and a very large database, what would you do?”
Erik Darling: Log shipping.
Brent Ozar: From the old cluster to the new one?
Erik Darling: Yeah, man.
Brent Ozar: And why would you do that?
Erik Darling: That’s my preferred way to fly just because it’s so dead simple to set up. Like, with mirroring, you know, you could add a lot of overhead to your system if you do synchronous. You might only be on Standard Edition, only be able to do synchronous. So if you’re on Enterprise, you can do async and then flip it to sync at the last minute and then flip over, but I would rather go with log shipping just because it’s so dead simple. It doesn’t really add any overhead because you’re most likely doing log backups anyway. And then when you go do cutover, it’s pretty easy to cut things off at that last log backup, bring things live on the new server and then let people, you know, mess around in there for a little bit. If anything goes terribly wrong, you can still point them back to the old server and have something to go back to. You don’t have that if you do an in-place upgrade. If you do an in-place upgrade, like you know, you get things upgraded, like people get when they start working and something goes terribly wrong, you don’t have a backup plan. You are just miserably stuck with whatever awful thing you did to your server.
Brent Ozar: I could see management saying you could need to do in in-place upgrade if it’s a very large database, depending on what they mean by very large, just because it’s less storage. And I’d be like, no man, that’s plan B.
Erik Darling: Like, how much of your maintenance window are you willing to dedicate to setting up a new server and then in the middle of the night moving things over to that to reassemble what used to work? I don’t want to do that.
Tara Kizer: I will never do an in-place upgrade in production ever, period, end of story. And if management is saying I need to, sorry, not going to do it. I just have to put my foot down. For production, it’s just asking for problems.
Brent Ozar: I like saying no. it sounds weird to say no to management, but at some point, you go, you’re paying me for my advice. You hired me for my expertise. I wouldn’t come to work wearing an asbestos suit; why would I do an in-place upgrade? There’s some things you don’t want to do.
Erik Darling: Why just paint over lead paint?
Brent Ozar: That’s right. That’s what kids are for. You give the kids a scraper and put them to work.
Erik Darling: And if they’re teething, it’s even easier; they just latch right on.
Brent Ozar: Which brings the question, Richie, during your home renovations, why aren’t you making this part of a home-schooling project where you just teach the kids construction? Of course, I guess you have to deal with the aftermath.
Richie Rump: Part of the same reason I don’t do a ton of work offshore; because you can’t control the quality.
Brent Ozar: You can control the quality with your kids it’s just continuously low quality.
Richie Rump: It’s difficult to control the quality; how’s that?
Brent Ozar: Samantha follows up with – she says, “Why aren’t you going to SQL Server 2016? Just bypass 2014. If you get 2016, you also get query store.” For that matter, why not go 2017? 2017 you start to get adaptive query plans, you can run the whole thing on Linux, Docker containers.
Erik Darling: Let your developers start using R and Python live in the database.
Richie Rump: So many bad ideas; it’s amazing.
Tara Kizer: None of my 2016 or 2017 clients that I’ve had so far, and I’ve had two 2017 clients so far, none of them have had query store enabled. It’s very disappointing.
Brent Ozar: It’s so weird. We have it in sp_Blitz, hey, you should put in query store. Nobody puts it in. it’s really weird.
Erik Darling: I wrote a whole stored procedure for it hoping that, like, this is the future, and I’ve used it twice. Every client that I’ve had has been on 2016 – well not every, but, like, a lot of them have had some variation on the story. But they’re like, yeah I tried to turn it on then my CPU shot off the radar for a half hour and I finally had to turn it off. I’m like, okay, well welcome to V1.
Brent Ozar: Weird.
Can I add Analysis Services to an existing cluster?
Brent Ozar: Justin asks, “Once you already have a clustered SQL Server, is there any way to add analysis services to the cluster?”
Tara Kizer: Even if there is, why would you? I don’t want those extra products on my SQL Server. The database engine is what I’m going to have on my cluster. I’m going to have supporting applications, such as my antivirus, you know, net backup or whatever it is, that supports SQL Server instance. But SSIS, SSAS, SSRS, they all get to go on their own boxes somewhere else. They do not get to pollute the SQL Server instance at all. I segregate everything. Nothing goes on the SQL Server boxes.
Brent Ozar: Preach. And too, if you think you’re getting high availability by clustering analysis services, you’re not really getting it. It doesn’t really do health checks on there. So if you go to Books Online they talk about how you can cluster it; it just doesn’t really have any health checking kind of things. What you’re better off, if you want high availability, is you build a couple of separate VMs or a couple of separate physical boxes and you put them behind a load balancer.
Should I switch to log backups every 15 minutes?
Brent Ozar: Next up, Steven says, “My company has a two-hour data loss policy.” Like us, we have a two drink minimum, so I kind of agree with that. He says, “Any suggestions of how to approach convincing them to go to 15-minute transaction log backups?” Oh, that’s an interesting question.
Tara Kizer: What, who are you trying to convince? I set up the job schedules and make sure that I at least hit their goals or better and 15 minutes is better than two hour anyway. Who is responsible for the job schedule here? My managers wouldn’t even have access to look; I would just be telling them frequently.
Brent Ozar: And y’all think she’s joking, but that’s how we roll here inside the company too as well. I don’t have access to do most of the stuff on our production SQL Servers. Richie owns this stuff. When one of us wants access to something, we put in a ticket to Richie. I have no business poking around in there, you know. It’s a bad idea.
Erik Darling: It’s a business’s job to tell you how much data they want to lose. It’s your job to figure out the technical setup that you need to meet those goals. So they shouldn’t be in there saying, no you have to take log backups every two hours. It doesn’t work like that – cart before the horse; whatever that saying is.
Brent Ozar: And I like doing log backups more frequently only because if one fails or two fail you still have enough time there to meet your two hour. If you’re only doing it exactly every two hours and you’re only supposed to lose two hours of data, the first job that fails, you’re screwed. You lost it.
Why are my restores being deadlocked?
Brent Ozar: Oscar asks, “I’ve got a shipped transaction log set up in one of our SQL Server databases and it works just fine, but every now and then it gets deadlocked in the restore job when it’s trying to restore the log backup. Is there any way to prevent deadlocks in a transaction log shipping setup?” I bet you’re getting deadlocks in MSDB. I bet you’re trying to insert update or delete record in MSDB and I bet that’s where you’re getting log shipped. And if you want, you can use sp_BlitzLock – sp_BlitzLock is a really slick stored procedure that Erik wrote to go get you the deadlock graphs and show what you’re being deadlocked between. And it’s probably something like somebody’s manual scripting that’s going and doing things inside the MSDB backup and restore tables. The one I’ve seen it happen a lot is where someone’s cleaning up history continuously. Like every five minutes, they’re trying to delete old records in MSDB. Just do that daily.
Erik Darling: Or if your MSDB history has just never been pruned and is gigantic and the god-awful thing that goes and looks for where to put in rows, where to delete stuff from, is just getting all lost on its own. I actually blogged a little while back where I put up some of the worst T-SQL that I’ve ever seen in a stored procedure and I wanted people to look at it. And it’s from sp_deletebackuphistory, or whatever that thing is called, and it’s all table variables and poorly written T-SQL with no indexes on anything. It’s like hell on earth whenever anyone wants to try and delete…
Tara Kizer: Like hell on earth, but it works starting with SQL Server 2005. You should have seen the crap-show back in SQL Server 2000. We all had to roll our own code back then and my manager back then realized that if he put an index on one of the tables, MSDB did that fix built in. I’m like, I don’t want to put indexes on tables in MSDB, so we all rolled our own back then. And in SQL Server 2005 or greater, they at least fixed that performance issue. I understand it’s not as fast as it could be, but SQL Server 2000 and earlier was horrible.
How should I keep data in sync with Azure SQL DB?
Brent Ozar: Daniel says he’s inherited an environment where they’re doing a custom database sync between on-premises SQL Server and then Azure SQL DB, which gets manipulated somehow in the production database. “What would you do in order to keep data…
Erik Darling: Excuse me…
Brent Ozar: “How would you recommend syncing data…” Richie… “How would you recommend syncing data between an on-premises SQL Server and Azure SQL DB?”
Tara Kizer: What are they trying to sync? Is that for a failover on-system or is it so you can run reports out there? So I maybe would use transactional replication out there, but maybe use another feature if it’s just for failover purposes. It just depends on what you’re trying to do.
Brent Ozar: Daniel says reporting, he’s doing reporting up in Azure SQL DB. Oh, so then I like Tara’s idea of transactional replication.
Tara Kizer: Well it does work well when it’s working and you don’t have to troubleshoot it and you can tolerate whatever latency that’s there when large transactions happen. It gets a bit of latency.
Where should I do log backups in an AG?
Brent Ozar: Sri says, “I’ve got a multi-site Always On Availability Group. What’s the best way to do log backups?”
Tara Kizer: It says log backups on all the servers. Do your log backups on the primary, that’s it. You certainly can move it to the secondary replica, but it’s going to be out of date, you know. So I do all my backups on the primary replicas. I don’t want my backups to be possibly not up to date.
Erik Darling: I really never understood the point of offloading backups with the AG. I just never got it…
Tara Kizer: What problem are they experiencing that they have to offload this simple task?
Erik Darling: Like, can you even have a good AG environment where backups screw you that hard?
Tara Kizer: I know. What does your I/O look like if that’s your problem?
Erik Darling: Like network disk, like, what is it that’s going on that’s making your server barf that hard taking a backup? It’s odd to me. Odd problem to solve…
Brent Ozar: I’m going to offload readable queries; I’m not going to offload backups because you’re going to pay the same licensing fee no matter what you’re offloading. Just go get rid of the locking problems; move those off.
Should I rename a SQL Server VM?
Brent Ozar: Next up, Guillermo asks, “What should I take into account when I rename a SQL Server VM?”
Erik Darling: Which part? Like just the VM or the SQL Server or…
Brent Ozar: I bet – there’s a Microsoft Knowledge Base article on what you have to do and it has to do with renaming – sp_rename, changing the server’s rename.
Tara Kizer: And it used to be that you had to do extra steps, but it’s much simpler these days.
Erik Darling: Well, I asked because something – I used to always goof-up whenever I made a new VM. Like, I would give the VM a new name in, like Hyper-V or whatever and I would be, like, this is my VM. And then I would like install Windows and go to connect to the VM name that I gave it in the virtualization platform and it’d be like, no that doesn’t exist. And I always forget that I have to change the computer name too to like match the VM name so I would connect right.
Tara Kizer: And there was one operating system – this does not have to do with virtualization, but one operating system where the name needed to be in upper case, and if you had it in lower case, it caused some kind of weird issue because we encountered that and it was horrific. And I forget what operating system it was, but it was a big deal. And this was on a clustered system.
Brent Ozar: And I wouldn’t change names on anything in a cluster either. Forget that. Guillermo says he wants to change the DNS computer name, all of it. I think we would take the same approach here that we would talk about with in-place upgrades. I’d rather build a new SQL Server with the name that you want and everything correct from the get-go and log ship or DR over or database mirror over to it. But this Knowledge Base article from Microsoft is actually fantastic. Books Online has gotten way better over the last several years and you can even be a contributor. You see how there’s these contributors things up here. You can go over and click edit on the right-hand side and you can go in and just edit these. They’re stored in GitHub, but you don’t even have to get fancy with creating a GitHub account or anything like that.
If you scroll all the way down when you’re logged in – I guess you do have to create an account, but you don’t have to go and fork the repo or anything. There’s an edit the page thing down at the bottom where you can just edit the page right there inside your web browser. You don’t have to do a whole lot of the more complex GitHub stuff.
Richie Rump: That’s why it’s gotten better.
Erik Darling: I’ll tell you one thing that bit me real bad with VM setup too is I had an old AG one that was all 2014. And when 2016 came along, I wanted to replace it with that. So it’s deleted all my old 2014 VMs, spun up 2016 VMs with the same name and then expected them to just work out of the box. But then I had to delete a bunch of DNS records, like other crap because it was looking at the wrong IPs, nothing was connecting. It was just a nightmare. So make a new one – please, just make a new one. Name it what you want, think about the children; awful.
Should I use Docker?
Brent Ozar: next up, Steven says, “I’ve heard a lot lately about Docker. What are your stances for Docker for DBAs in 2018? Is this something that DBAs need to worry about? Is this something that developers use?”
Erik Darling: Just stay away from the ones with pleated fronts. Pleated fronts are just miserable. I did it… I did it…
Brent Ozar: What you can’t see, ladies and gentlemen, is that he’s wearing plaid pants.
Richie Rump: Shorts – we know he’s not wearing any pants.
Brent Ozar: And socks and sandals… Now, have any of y’all played around with Docker?
Richie Rump: No. Docker is fun for the dev, but the question is, what problem will you be solving suing Docker? Because Docker is a way to install something very quickly. I believe it runs in its own container and does all this other kind of nifty stuff for the developer or I need to get some code out running. But for a database, what problem are you trying to solve here? So there’s a lot of questions that I would ask – why would I want something in a container? Am I moving it somewhere? Am I installing a lot of these in all these different servers all over the place? If this is going on physical hardware or if it’s going on a VM, just the ease of installation and configuration settings is what you’re getting. And if you’re a DBA, that’s part of your bread and butter, so even I, as a developer, haven’t really seen, for me in what I do, a really big need to be moving stuff to Docker. Serverless does that all for me.
Erik Darling: DBAs are like the most insecure people. Like every new technology that comes along, they’re like is this going to take the DBA job? I read a blog post not too long ago; DBAs worry about R taking their job. Like, what is R going to do that takes a DBA job? Like, what facet of R makes you nervous about your job? Like, no, no one’s going to, you know, look at statistical analysis of your log backups and decide you’re a terrible DBA and fire you. I just don’t understand. Everything that comes out, like, this is going to kill me. I don’t know what’s going to happen.
Brent Ozar: Artificial intelligence…
Erik Darling: Machine learning, the cloud, like everything, people are like the DBA is dead, done, buried, forget about it. And I’m like, you know, five, ten years later there’s still this big stinking pile of us that show up in Seattle every year, so I don’t know.
Richie Rump: You would think after 30 years of hearing your job’s going to be obsolete in the next five years that it would just kind of go over people’s heads, but no, everyone still freaks out about it. It just doesn’t make sense.
Erik Darling: you know, the [Mane Tack] repairman is still hanging out doing stuff.
Richie Rump: He’s still getting paid, I don’t understand…
Erik Darling: Residuals at least.
Why isn’t SQL Server recommended in virtualization?
Brent Ozar: Mark says, “Why is it not recommended to have SQL Server running on a VM?” Whoever’s telling you that, you need to go have a talk with them. He follows up with, “Are there best practices when running SQL Server on a VM?” yeah…
Erik Darling: VMware publishes them. VMware has a whole architecting VMware with SQL Server guide that I would say is a pretty good place to start for advice.
Brent Ozar: SQL Server VMware best practices – the one thing that I would warn you about is in some of these, they’ll talk about, for example, single core SQL Servers by default. So just read some of it with a grain of salt.
Erik Darling: That’s not in this one though. This one is actually pretty sane about how to configure stuff around numa and other things. I’m fairly behind this guide. Like, not 1000%, but…
Brent Ozar: See if it says anything about cores per – oh good, cores per socket, oh that’s great. That’s a good topic to get into. So that’s nice. We’ll throw that back up at the very top. So that is architecting SQL Server on VM vSphere and I got there from Googling that right there.
The First Responder Kit didn’t update itself
Brent Ozar: Marci says, “I installed the latest sp_Blitz scripts on my dev and DR boxes, but then when I ran the install all script on my prod box, it didn’t update the stored procedures.” I bet you ran it in the wrong database.
Erik Darling: Yep.
Brent Ozar: This happens to me every week to the point where Erik had to write a trigger for user databases to make sure that when we try to install them in the wrong database, they’d just get rolled back. A long time ago in a galaxy far, far away, we put a use script up at the top, like use master, so that it would enforce that it would go only in the master database. And then we have users who are like, no I want to put it in any database and I want to just be able to run the script. So we took the use part out…
Erik Darling: Now we have the extra fun part of figuring out which database they got installed in, then go delete all those. Context is everything, folks. Context is everything.
Brent Ozar: Alright, well that’s all the questions we have this week. Thanks, everybody for hanging out with us this week at Office Hours and we will see y’all next week. Adios, everybody.
Erik Darling: Goodbye.
Wanna attend the next Office Hours podcast and ask your own questions? Register here.