This week, Brent, Erik, and Richie discuss Availability Groups, versions of sp_BlitzIndex™, SQL Server installation issue, backing up SSRS reports, SQL Server on Linux, failovers, detaching/attaching databases, extended events, career progression of a DBA, and more.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast 2017-8-30

 

Can old compat mode databases be in an AG?

Brent Ozar: Larry says, “For a coworker…” Yeah, sure Larry, “Can a database on SQL Server 2012 Service Pack 3 running in 2008 R2 compatibility mode participate in an availability group?” I think so…

Erik Darling: I would say yes.

Brent Ozar: It shouldn’t matter.

Erik Darling: I have a more important question though; what’s stopping you from just trying it?

Brent Ozar: Do it live, that’s what we always say at Brent Ozar Unlimited.

Erik Darling: Well not in prod but, you know – because if you’re embracing availability groups then clearly you’re embracing the pre-prod staging environments; so you can test everything against an availability group. If you want to be a responsible availability group owner you can’t not have that staging environment.

Brent Ozar: And you can do it with one database with 1GB; just create a 1GB database and see what happens.

Erik Darling: Less than 1GB even, could be 512MB.

Brent Ozar: Larry says, “Yes, I swear a coworker. I would have tested it myself.” Good answer, Larry, we appreciate that; you pass.

Erik Darling: Thanks, Larry.

 

I’m having this odd SSL error…

Brent Ozar: Let’s see here, next up J.H says, “A developer is getting the following error… No one else is having problems, only him. ODBC encryption not supported on the client SSL provider. Client and server cannot communicate because they do not possess a common algorithm.”

Erik Darling: Where are they – from management studio?

Brent Ozar: Management studio and it looks like also via ODBC.

Erik Darling: You know, I ran into something kind of weird like that once, setting up mirroring, and I blogged about it. It was a problem with mirroring, like when I did mirroring with a GUI it set up two different encryption algorithms, for some reason. And then that was a big reason why mirroring wouldn’t work, but man, I got nothing on why management studio would do it. I would just make sure that you’re on the most recent version of management studio and see if that fixes it and breaks everything else.

Richie Rump: Uninstall, reinstall?

Erik Darling: Restart it.

 

What changed in sp_BlitzIndex since 2014?

Brent Ozar: Peter says, “I’m working on a server that’s got two versions of sp_BlitzIndex. One is old, from 2014, and one is current, 2017. The two versions report a lot of different stuff. Like the old one reports much more stuff. Why would an older version of sp_BlitzIndex report more stuff?”

Erik Darling: I think it’s a default mode. So with the new sp_BlitzIndex, you want to be running in mode four if you want the Full Monty of errors and warnings. Mode zero is kind of like a higher prioritized list of stuff. Mode four is like the deep dive end of stuff. Mode one, two and three are still the same though, where it’s like the aggregate stuff, the current definitions and then just missing indexes. But zero and four – I think four was new, right? Or four was…

Brent Ozar: I think it was new because I think before we just reported every tiny detail and people were freaking out… “Oh my god, I have a missing index on a table with four rows.”

Erik Darling: “What’s happening?” I don’t know, nothing… [crosstalk]

 

Can’t wait to see you at Summit 2017

Brent Ozar: James says, “I can’t wait to see you guys at the PASS Summit 2017, your pre-con.” Man, Erik has been working hard on his slide decks and I’m like way behind. I blocked out, I think, next week. Next week I’m working on my pre-con decks.

Erik Darling: You’ve already blacked out for next week?

Brent Ozar: I’m drinking so hard, I’m blacking out for next week. Richie, are you bitter that you’re not going to Summit?

Richie Rump: I am. It’s like the first time in four or five years that I’m not going. It’s kind of weird.

Brent Ozar: Halloween, you’ll enjoy it with your kids. That’ll be fun.

Richie Rump: I’m totally happy I’ll be home, but it’s one of those bittersweet things. It’s like it’s good to be home but you kind of want to be around everyone else and doing all the everyone else things.

Brent Ozar: It’s just that we figured out that you love your family more than you love us. We see how it is.

Richie Rump: Yes, and I’m still married, which is amazing. That’s a good thing…

Brent Ozar: To your wife, not your job, yes, that’s the important part.

 

I’m getting this odd setup error…

Brent Ozar: Kevin says, “I’m trying to install SQL Server for both standalone and clusters and it’s failing. I’m waiting on the database engine recover handle. Any and all help would be appreciated.” I bet you’ve got a bad download. I bet you’ve got a bad ISO file; because I’ve seen stuff like this happen when I had a corrupted ISO file that I downloaded or a corrupted CAB. So I would just try downloading a fresh install to see if that gets you.

 

How do I back up a folder of SSRS reports?

Brent Ozar: Richard says, “I have a number of reports on our reporting server. I need to back them up within the entire folder. Is there a way to backup my SSRS reports without doing them one at a time?”

Erik Darling: I have no idea.

Brent Ozar: Me neither. Who would we ask? Doug Lane – is it SQLtheatre.com? I think it’s SQLtheatre.com. I’ve got a web browser, let’s go use my web browser.

Richie Rump: DougLane.com…

Brent Ozar: Let’s look and see – and you know, once we start pulling up things, we’re going to pull entirely the wrong website up here…

Erik Darling: I think it’s SQLhater.com…

Brent Ozar: SQLtheater.com. And so from there, you can contact Doug, or not, maybe it shows on the blog…

Richie Rump: Twitter, Stack Overflow.

Brent Ozar: Twitter is good. Stack Overflow is a good way to contact as well.

Erik Darling: I would go with Twitter. He seems active on Twitter.

Brent Ozar: He certainly does; he’s very active on Twitter. If you’re scared of joining Twitter because you go, “Oh no one’s going to want to see what I had for breakfast or listen to my opinions…” That’s true, no one does, but all you do when you use it is go ask the questions you want to ask and go from there.

 

Can I see a transaction save point while debugging?

Brent Ozar: Speaking of which, Tracy Sells says she, “Tried the SQLhelp hash-tag on this for Twitter a couple of weeks ago and didn’t get any responses.”  So now she’s going to try us. “Is it possible to see the current transaction save point or name of a save point during a debugging session? I have a .NET app looping through code and it’s at a debug statement. Could I look at the SQL Server side, where a transaction is and what its save point is at?” She is a he. Tracy’s a guy. Sorry about that, Tracy. I bet that happens all the time. It happens all the time with me. [crosstalk]…

Richie Rump: Nice.

Brent Ozar: Moments like that, our transcriptionist is trying to rewind that going, “Wait, what did Richie just say?”

Richie Rump: I’ve been doing that for 20 years with that song, by the way.

Brent Ozar:  I don’t think any of us have an idea on that.

Erik Darling: Well, Richie might be able to answer the debugging side, but seeing where a transaction is – I mean, you’re reading the transaction log?

Richie Rump: Are we debugging the stored procedure or are we debugging the .NET?

Erik Darling: I’m guessing they’re doing something with BEGINTRAN and they’re naming their transactions. So it’s like BEGINTRAN TRAN1, do some stuff, roll back some stuff.

Richie Rump: Are we talking about nested transactions?

Erik Darling: I hope not, there’s no such thing.

Richie Rump: That’s usually a bad thing.

Brent Ozar: Oh man, Tracy says, “Yes we’re doing rolling back of save points.” Holy smokes, a transaction with save points. I have never used those in my life. I don’t know anyone who has. So ask on DBA.StackExchange.com and know that you’re in a rare world of hardly anybody using these things.

Erik Darling: That’s a Better Call Saul question; Solomon Rutzky who wrote the c# tools. He will have an answer for you because he knows everything I don’t want to know.

 

Will Linux’s dynamic threading make SQL Server faster?

Brent Ozar: Larry says, “I’m wanting to know your opinion of SQL Server on Linux. The reason that I’m running it is that Linux supports dynamic threading. A scheduler that has idle threads can dynamically reallocate those threads to another scheduler. This dramatically increases the performance of SQL Server on the same hardware over Windows.”

Erik Darling: Where did you paste that from?

Brent Ozar: Scroll down…

Erik Darling: It sounds like it came from the Linux brochure.

Brent Ozar: yeah, I’m going to call BS on that.

Richie Rump: Yeah.

Brent Ozar: Now, it’s not that it’s not possibly true. It could totally could possibly be true; but never in the entire history of my long illustrious career have ever gone, “I really need to move a task to a different core in order to go fast.” So…

Richie Rump: Or, “CPU is my problem” right?

Brent Ozar: It’s so rare. I mean, there are wait types where CPU is your problem, but usually, it’s due to single threaded functions; something causing your query to go single threaded. At that point, it doesn’t matter which scheduler you move it to; you’re screwed. And if you’re worried about contention of lots of queries that are all single threaded that end up on the same core, how about you tune your queries?

Larry says, “My source is deep within MS.” [crosstalk] So our answer – at least my answer, and anybody else you can talk to is, I’ve never seen that as a problem that I needed to solve; so deep of a problem that I would rather change operating systems than tune the code. So there you go.

Richie Rump: I agree, wow. If that’s the case then great, but I’ve never seen that problem and I’ve had databases that were 60TB and stuff like that, and CPU has never been a really huge issue. It’s always been memory…

Erik Darling: Or lack thereof.

Richie Rump: Or lack thereof, yeah.

 

Continued about older sp_BlitzIndex versions

Brent Ozar: Peter has a follow up on his earlier sp_BlitzIndex question. He says, “The new version will not allow mode four when specifying the table name.” You don’t have to whenever you specify a table name; it already is, kind of, a mode four. If you’re seeing missing indexes in an older version, they may not meet the basic cost thresholds we use these days. Like it may only have a very tiny benefit and the server’s been up for a year.

Erik Darling: From what I can remember, setting the mode was never valid when you set a table schema, or whatever, name. That was always something that was not allowed, or that had no effect. I think we were just more verbose about it not working in newer versions.

 

Why would CHECKDB cause a cluster to go down?

Brent Ozar: Robert says, “Hey guys, have you ever seen or heard a CHECKDB…” Yeah, I’ve held my ear to a server a few times and heard it carefully… “Cause a node in a two node cluster to go down and cause a failover when you’re doing CHECKDB – we think it’s a firmware upgrade that resource databases on C…” And he thinks it’s a CHECKDB on the master database that’s causing the failover. Wow…

Erik Darling: I’ve never had it on master. I’ve had it on a regular user database, which was fun. If you want the short story, it was – we had a server with 512GB of RAM and with max memory set to 485GB. The node would crash and restart on CHECKDB on a 4.5TB database. With max memory set to 475GB, so down 10GB, it would go just fine, bizarre. But with master, no; unless you have a 4.5TB master database.

Brent Ozar: Or unless you have corruption in the master database. That’s the only thing I can think of. It caused a severe enough server error that it would cause the server to go unresponsive; but I’m reaching in order to pull that up. And you would think it would happen on either node too.

Erik Darling: Also, it would have to happen on either node. Also, that error would bubble up somewhere, it wouldn’t just be like, “We don’t know.”

Brent Ozar: Yeah, check your error log; that should show it.

 

I have this really detailed AG question…

Brent Ozar: Grahame says, “Our server team has set up a Windows 2016 failover cluster for a network document share. I want to set up an availability group using the same cluster, but this is very concerning, in the current failover cluster, I see multiple supplicates of folders…” This is kind of more detail, I think, than we can answer in a quick Q and A here. I think you’re going to want to show a lot more details; try posting this one over at Stack Exchange. I also wouldn’t post this at DBA.StackExchange; I would post this on…

Erik Darling: Server Fault?

Brent Ozar: Yeah, Server Fault, ServerFault.com.

 

What version of SSDT do I need if…

Brent Ozar: M.M. says, “We are rolling to SQL 2014…” I don’t know if that means he’s like driving along in his 5-0 listening to a 2014 CD. “What determines the version of SQL Server data tools that the developers need? Does it depend on what version of visual studio they use? I think some of our developers are still using visual studio 2008.” I have no idea. I’ve never used DT in my life. For that one, I would post that on SQL Help, because it’s short enough that you could probably get by with that. There was a session at Lightning Talk at the PASS Summit last year about which versions you needed to use. And the presenter had this whole grid of stuff up on the screen, and I remember just looking at that going, “God help the poor guy who has to determine that licensing.” [crosstalk] Even worse than regular licensing.

 

What’s the difference between read-only and any connection allowed?

Brent Ozar: Courtney says, “In always on availability groups, what’s the difference in behavior when I say that a readable secondary allows queries, or that read only intent is on?” Well, what this means is, if someone connects in directly via listener and didn’t specify read only intent – like if they tried to connect directly to the database using the server name, the secondary replica name, if they didn’t specify read only intent they’re not allowed in the database. I have a lot of third party crappy apps that don’t have application intent in the connection string. Somebody maybe uses like some kind of reporting app and I want to just let them connect to the database, I know they’re only doing SELECTs, but they don’t put the read only intent in, they wouldn’t be allowed to connect.

 

Should I restore master when I move servers?

Brent Ozar: Let’s see, next up, Ronnie Jones asks – I try to only use the first names, I forgot there, sorry Ronnie…

Erik Darling: That’s such a good name.

Brent Ozar: that is a good – it’s like a country star’s name. Ronnie Jones next up with I Lost My Trailer In The Flood… Ronnie says, “We’re moving our SQL 2012 instance from one physical box to another. The new server will assume the same name as the old server. I have a whole bunch of linked server objects; do I need to move master across or should I just script everything out and then reload the new master?”

Erik Darling: I would rather script everything out. I’m not a fan of having things tag along like that because at least then you get to do some house cleaning, you know. Do I really need this, do I actually need this? This linked server hasn’t been around since 2002, why is this name …

Brent Ozar: Gotcha.

 

Yes, 2008 compat mode databases work in an AG

Brent Ozar: Larry follows up on his earlier question. He says he, “Confirmed by testing it’s a SQL Server 2012 database in 2008 compat. mode can participate in an availability group.” Awesome Larry, that’s one question down. Now we had a few more in the queue, if you can test those and let us know…

Erik Darling: That reminds me of a question that came up in the comments in the blog post earlier this week where someone asked if a database in 2012 compatibility mode could participate in direct seeding on a 2016 database. Yeah, there’s no good reason for that not to work because the database compatibility mode doesn’t really impact the higher server functions like that.

Brent Ozar: Larry says, “Pass them on.”

 

Could corruption in the resource DB cause a bluescreen?

Brent Ozar: Robert follows up, “This cluster is showing corruption in the MS SQL system resource database. Can that in itself cause the node to completely blue screen? If we run CHECKDB on master it actually causes the node to crash after its launch.”

Erik Darling: I think – I want to say Paul Randall has something about how when you run CHECKDB on master, it reaches out to MS SQL…

Brent Ozar: Automatically when you run CHECKDB on master it immediately follows by CHECKDB on the resource database. Can that cause a blue screen? Yes. So fun trivia, this is also one of the reasons that Azure SQL DB won’t just let you hand over a database and they’ll restore it and run it from there. They have no checking built into SQL Server to make sure that a database won’t cause a system to crash whenever it’s gone and restored. So this was never a problem in on premises because you’re the one who restores your own databases. So if you choose to restore something corrupt, you’re kind of on your own. Whereas up in the cloud, if you restore a database on a shared instance and you bone everybody, that wouldn’t be good for their availability.

Erik Darling: That reminds me of when I drop off laundry. I know what’s in the bag, they don’t know what’s in the bag. It could be anything in that bag. You see other bags in there and you don’t know what was in those bags. I wouldn’t want any of my laundry getting washed with what was in any of those other bags. [crosstalk]…

Brent Ozar: Oh, that makes sense. So like – so if somebody has weed in there or something that the drug dogs are going to come running into your place and you’re like, “It’s laundry.”

Richie Rump: What’s in that bag?

Brent Ozar: Nice Se7en reference. I like that; that was good.

 

More about SSDT versions

Brent Ozar: Kelly says, “For SQL Server data tools, I think you can download the most recent community edition of visual studio, the most recent SSDT and then specify the target version of SQL.” We’ll take your word for that, Kelly.

Richie Rump: I believe that is true.

Brent Ozar: Here comes Mr. Roboto, chiming in as well.

 

Do database properties change on restore?

Brent Ozar: Next up, Nestor says, “When you detach and attach a database, do some properties get reset? I recently performed a detach-attach and DB chaining was lost. Is this normal?” That one and Trustorthy. I know Trustworthy doesn’t come across with a restore. I don’t think there’s any others though.

Erik Darling: I used to have to attach – well, detach and attach databases fairly often, moving them from one drive to another, but I can’t think of anything – I can’t think of any weird settings that might have got lost. I know that, like, when I change things like auto shrink and auto close, that was just gone after a detach and attach. Thanks, sp_Blitz.

Brent Ozar: God, that’s horrible.

 

Is the DBA job dead?

Brent Ozar: let’s see, and then next up, Garland says, “One blogger has been blogging about moving towards data science because DBAs are getting automated away. What are your thoughts on career progressions past DBA? Is this just more hype about DBAs being phased out? “

Erik Darling: How many times has the DBA job been dead? I remember when NoSQL came out, the DBA was dead. When SQL 2005, because it was self-tuning, the DBA was dead. SQL Server 2000, there were no such promises; everyone back then was still pretty sure that the DBA was alive and kicking in some weird source code sort of way. Maybe sitting in a box attached to a bunch of tubes…

Brent Ozar: And there’s this weird race of automation. So on the one hand, Microsoft is racing to automate the parts of database administration that suck, which is great, I’m all for it. Managed backups was an example of that. Then on the other end, you have other teams in Microsoft racing to deploy new features; column store indexes, in memory OLTP, R, Python. These new features don’t even have instrumentation, let alone best practices, let alone any kind of automation to leverage them; so I kind of look at it as this moving sweeper. There’s always a part of Microsoft that’s sweeping up the crappy stuff off the street. After all, we don’t have to do a lot of performance tuning anymore on databases that are like 5GB or 10GB; that’s kind of taken care of now. Your skills will always be required at the higher end of the spectrum.

Erik Darling: I’ll give a good example of something that I heard a long time ago: extended events were going to kill the DBA because anybody was going to be able to find any problem in SQL Server. And to this day, extended events are still one of the most unusable features to most people in SQL Server. It’s a mess of XML and the GUI’s weird and takes forever. There’s just not a good set of, like, if you have this problem, use this extended event. Like, you have to go searching to find stuff, you might use one event or another. I still get confused about what extended event does what sometimes because the names and descriptions are like one word off sometimes. It’s like, I don’t know, I thought I had the right one, I’m sorry.

Brent Ozar: Richie, how many times does your … you’ve heard over the years, developers will be dead, everything will be so easy that managers will be able to…

Erik Darling: Self-writing code.

Richie Rump: Oh, you mean like RAD?

Brent Ozar: Yeah.

Richie Rump: Yeah, I mean, that’s – it seems like every time we get a new technology jump or new technology, some things speed up but other things slow down, right. And, you know, I jump from one technology to another and it seems to take the same amount of time for me to do, maybe a little bit more stuff, but it’s just never the panacea that it’s promised to be. The whole node thing, like we can just write JavaScript everywhere. And everyone realized that maybe that’s not such a good idea because we’re writing JavaScript everywhere.

Brent Ozar: XML was a great example. Like, we don’t need database anymore, the developers can just throw XML in some kind of property bag and they’ll be able to define their own schemas. No one will need data modelers ever again. And yeah, that didn’t pan out so well.

Richie Rump: Also, like, XML, JSON…

Erik Darling: Who? The question that no one really answers when they talk about that stuff happening is, what happens when the automation breaks? So we were talking about it earlier, you know, direct seeding in SQL Server 2016 comes along. You no longer have to worry about setting up a process that will join your databases and sync them across an availability group. But what happens when something goes wrong with direct seeding? A long time ago when it came out and I was excited about it and blogging about it and stuff would go wrong, it still required me to go in there and do a bunch of stuff to fix it. Like when it broke, it broke hard finding the error messages and figuring out what they meant and restarting things and kicking things back of. I had to go do that. That wasn’t automated then and I don’t think it’s automated now.

Brent Ozar: I have to show a screenshot too that I just utterly die about. In the Books Online section talking about automatic tuning, because now in Azure, you get some ability to do some automatic tuning, here’s a comment that’s on that post. “Hey, using under automatic index management, it says new index will be retained only if performance of the queries is worse.” Oh yeah, sorry about that. We’ll have to go in and fix that. At the end of the day – that’s like, of all the typos they could have, that’s the worst possible one. At the end of the day, it’s still human beings on the other side and this stuff will break at the same time you’re trying to push the game forward.

Richie Rump: I mean, if you go back to the actual question, is the DBA moving to data science and how much correlation is there between doing DBA work and doing data science work? I don’t think there’s much. I don’t think there’s much, at all. I mean, data science is a lot of math and statistics and you’re actually writing some sort of code and query to get all the data and form the data in the right spots. And then you’re actually running all these algorithms up against it and you have to understand what’s a positive, what’s a false positive.

And then you’re seeing all this data and you’re extrapolating information from it, giving it to an executive where he’s going to make a business decision on it. Well, if you’ve done your job correctly then that’s great, but if you’ve done it poorly, now you’ve given the business executive information to make his business decision which may tank the entire business because you didn’t understand this algorithm or you did things incorrectly.

I’ve actually worked with data scientists, and they’re Ph.D. type level people and I was like, this is really super interesting stuff. I just don’t want to stake my career on what you’re doing because you went to school for 12 years to understand all the math behind all this stuff. And I want to know, if I’m going to give this information and say that it’s right, I want to know that it’s right. And for me to know that it’s right, I have to understand all that algorithm and all that math and all that stuff. And I’m like, I’m interested in it, but I’m not going to go move my career that way.

Erik Darling: And if anyone’s ever seen a DMV query, we all know that DBAs hate math.

Richie Rump: As a matter of fact, I’ve been looking at DMV queries all week.

Brent Ozar: I will say too, so you asked what’s the career progression of a DBA. This one person chose to go off and do data science – you can. This is one of the things I love about database administration. You’re in the center of the business. You can go in any direction you choose. If you don’t like database administration and you want to go do something else – for example, that blogger, they might be just done with database administration and they may choose to go off and do something else. The world is your oyster as a DBA. You could become a developer, and architect, a consultant, data scientist, almost anything. The one thing that I’d be careful with data science is, if you don’t have a real college degree and you’re going up against people who do, just know that that’s going to be a real tough market. There’s a long difference, as any of you who’ve every hired a DBA know, there’s a long difference between a Microsoft certification and something like a professional degree when it comes time to take them seriously.

There was a brief rush during the dotcom rush in the early 2000s where anyone with an MCSE could get an amazing job at great rates. That’s true today in data science. If you can spell data science, you can probably get a pretty good job. I don’t know that that’s going to be true a few years from now.

Alright, well thanks everybody for hanging out with us today at Office Hours and we will see you all next week on Office Hours. Adios.