This week, Brent, Erik, and Richie discuss database corruption, multi-instance clusters, career advice, whether you should transition from contract work to full time, VMware vMotion, reducing failover time with AGs, query tuning, 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 – 10-25-17

 

Should we use 64K NTFS allocation units?

Brent Ozar: We might as well get started. We’ve got a few technical questions coming in here. “Should we still allocate disks for 64K NTFS allocation units when SQL Server data and log files when using VMware and EMC XtremIO?”

Erik Darling: Gosh, I just don’t care.

Brent Ozar: So every … their own best practices documentation, and EMC XtremIO has their own. I want to say, but don’t quote me, that it still says 64K, but it may also say things like 4K because different SAN vendors do things at a different size internally; as opposed to your old school hard drives that used to do everything in certain sector sizes.

Erik Darling: Most SAN vendors want you to use the smallest block size possible so they get the higher IOPS ratings – they can cheat on the test. Like, “Look how many IOPS we do…”

Brent Ozar: Wes Crocket laughs out loud when he says, “Technical webcast.”

Erik Darling: Screw you, man. How’s that new job going because of all the questions we answered, Wes?

Brent Ozar: That’s right.

 

How do I change SSAS startup parameters?

Brent Ozar: Wes says, “Actual question – SSAS has a startup parameter pointing to G:, how can I change that startup parameter? G no longer exists.” Okay, a quick show of hands for everyone in the room, how many of us manage SQL Server Analysis Services? There’s a lot of you. Do any of you know the answer to that question?

Erik Darling: There’s an XML file, probably…

Brent Ozar: We got nothing? … In SSAS, you can go right click on the properties – I should make Victoria come around to this side. Victoria says, “You can right click on SSAS, go into properties and you can set it there.” Plus, it’s a Microsoft product, so isn’t that always the safe answer for anything? She’s getting ready to open up her laptop and she’s going to see…

Erik Darling: Just right click on everything.

Brent Ozar: Oh, Wes says, “analysis services won’t start at all unless I remap a G drive and move the config file to it.” Well that’s a onetime thing. You can use the SUBST command, fake a G drive, like point it to another drive letter; that will at least get you started. Then you could – and I’m totally going off of what this nice lady said on the other side of the screen here – then you can right click…

Erik Darling: As soon as she nods, we just keep talking.

Brent Ozar: I see a menu here, so she right clicked and went into properties and it looks like that’s where it comes from. [inaudible]… This is the way she’s always accessed it.

Erik Darling: You know what I would do? I would check maybe under configuration manager; I would see if there’s a startup option in there. I assume SSAS and configuration manager has an entry, because I’ve seen it in there. So I would right click on that and see if you could change it in configuration manager. That way, you don’t have to start it up.

 

I have corruption in MSDB. What now?

Brent Ozar: Lee says, “I have corruption in my MSDB database and my last best backup was five days ago. What do you think about fixing it?”

Erik Darling: No. As soon as corruption starts showing up in system databases, I want to run screaming. What I would do first is see if you can rescue it. Because sometimes what happens, and I’ve seen this a with temp tables where sometimes people get screwed up because of those – so what I would do is check the corrupt pages DMV, and I would see how many errors you have and when the last one was. Because if it’s an older error and you don’t see the errors piling up, you don’t see them keeping happening, it might be just a temp table or something that disappeared, and you can not care about it anymore. But if it’s like a real deal system view or something that’s continuously giving problems, I’d be a little dicey about staying on that server. I’d probably want to start looking towards something new, because generally, corruption doesn’t just stick around in one database.

Brent Ozar: Whatever drive that database is on, other databases can start becoming corrupt. It’s almost like – Eddie Murphy joked (NSFW) about back in his comedy videos like, whenever someone hears, in a horror movie, a voice saying, “Get out,” the smart people just go running right out of the house. It’s the dumb people that get the flashlight and go, “I better go look.” No, get out.

Erik Darling: Where did that come from? The basement? Alright…

Richie Rump: Brent, what have you done for me lately?

Brent Ozar: Ice cream…

 

 

Can I have an active/passive 3 node cluster…

Brent Ozar: Peter says, “Hi, I’m a first-time questioner, long time listener…” Welcome to the show. “Here’s my question about 2008 R2 on Windows Server 2008. Can I have an active passive three node cluster that uses shared storage for a database held on two instances?” Alright, so if I don’t say this, Allan Hirt is going to kill me; Technically that’s a multi-instance cluster. So technically it means you have a two instance three node cluster. I totally understand what you’re saying, it’s right, it’s just that people get freaky about that language.

Erik Darling: It’s like AOAG, everyone just throws stuff at you.

Brent Ozar: Or just Always On. My Always On is broken…

Erik Darling: Always On.

Brent Ozar: So yeah, you can totally do that. The gotcha is that it requires Enterprise Edition if you want any instance to be able to failover to any one of three nodes. If you kind of duct tape it together and any one instance is only on two of the nodes – so like one instance is on node A and B, the other instance is on node B and C – you can do that with SQL Server Standard Edition. Just you don’t usually want to do that; once you start getting fancy, you go Enterprise. Peter says, “I understand the active active passive only needs two SQL licenses.” Yes, that is true, as long as you’re covered under software assurance.

 

Should I learn data science?

Brent Ozar: Grahame says, “There seems to be an explosion… That’s true with pretty much anywhere where we’re at. “In data engineer and data science jobs, so I’ve begun focusing on learning SSAS and R, those are interesting to me. What are your thoughts on the evolving nature of the data profession?” So it sounds like you’re saying that your job is doomed, what are you going to do about that?

Erik Darling: Nothing, I’m going to hang out and wait until that actually comes to pass, because like every year that I’ve been a DBA or I’ve worked with SQL Server, I’ve heard that my job is over and shortly going to be extinct or a fossil and I’m going to be holed up in a data center somewhere freezing my butt off and waiting for a server to go down. [crosstalk] So far that hasn’t happened. So you know – you said about the weekly links…

Brent Ozar: Yes.

Erik Darling: Yes, Microsoft has been proclaiming the death of the DBA since, what, SQL Server 7? Before it even had a year; In fact, it was still just a sad number.

Brent Ozar: If you subscribe to our Monday links – so this week I had a kind of funny batch group of links, including the performance tuning guide for SQL Server 7 and the manual for SQL Server 7, where it says, and I quote, “The database has become largely self-tuning.” It gives you all kinds of advice on how you don’t have to worry about performance tuning anymore. It also says that the index tuning wizard, the thing that’s dead now, “Does a better job of indexing than humans do.” Yeah, if we outlasted the index tuning wizard, we are going to outlast the next self-help thing.

The other thing I would say about that is, if someone’s telling you that the DBA role is dead and you should go do something else, maybe watch what they’re doing in case the person that you mentioned, they’re actually talking about DBA topics at PASS, not about data science. So that’s kind of funny how that works out.

Erik Darling: But, you know, if that’s what interests you then go for it. I mean, don’t stick around being a DBA if you don’t want to do it anymore. If you’re into SS whatever S and R then go crazy.

Brent Ozar: And there’s money in it.

Erik Darling: Yeah, totally. Just, you know, get your Ph.D., and a few years from now you’ll be a massively successful data scientist.

Brent Ozar: You’re competing with everyone who comes fresh out of college who has this Ph.D. in math, Ph.D. in computer science, and they make $20 an hour because they’re desperate for ramen to pay the rent. That’s how we got Richie, for example.

Richie Rump: Yeah, well it wasn’t just that. Well, there was a ramen without the flavor packets, so…

Erik Darling: I snorted all the flavor packets, so…

Richie Rump: Well the real question that I have is what constitutes and explosion? Obviously, there wasn’t a lot of data scientist jobs, not all of a sudden there’s an explosion, what does that mean? And how many data scientists do you really need for a company? And how do they integrate with one another? And frankly, the big problem with data isn’t the data scientists, it’s the data itself and morphing all that data so that it can be actually processed by a data scientist. So is there going to be a new job now where that’s going to actually transform all this data into a readable format for the data scientist because they don’t want to pay all these data scientists this huge amount of money so they can actually do their data science type stuff?

I mean, I don’t know, but it’s still so young and it’s so early right now. If that floats your boat then go off and do it and have fun at it, but if you’re just trying to chase a dollar sign, that typically doesn’t work out well for anyone. Just ask the Silverlight guys and see how that turned out.

Erik Darling: Ouch.

Bren Ozar: Grahame follows up with, “That was supposed to be a softball question, my bad.” Well no, it’s just that we love that particular softball because it comes up a lot. There’s a lot of people who are like, “The database is dead, there’s not going to be any careers left.” I’m like, “They said the same thing when XML came out.” “No one needs databases, we’ll put it all in flat files.” “Okay, get back to me on that.”

Erik Darling: We did a sold-out pre-con on the database administrator not being dead.

Brent Ozar: How many seats did we sell?

Erik Darling: 360… No, 361, it was me. I had to pay to get in there.

 

Heard anything about DDBoost?

Brent Ozar: Let’s see, Daniel says, “My vice president…” I assume he means Mister Pence… “Wants us to do another proof of concept with DD Boost…”  I want to say that’s data domains. “Have you ever seen it and do you have any horror stories?” Have you ever seen anybody use it?

Erik Darling: I’ve heard SAN guys talk a lot about it, and SAN guys seem to love it. I’ve never actually heard a DBA talk about it and love it.

Brent Ozar: If you go to the bottom of our blog post about it, there was a comment from somebody who was like, “I’m going to go in and run reports against it to see how it goes.” I’m like, “God bless you; you’re wonderful.” And he actually came back and he’s like, “Restore speed tends to suck.” Like okay, I wish they would come out with some numbers showing whether it’s better or worse, and when they don’t come out with numbers, hat usually means it’s worse.

 

Can you do an FCI in an AG?

Brent Ozar: Robert says, “Hi all, have you seen a hybrid environment…”  I think he means like Commodore 64 and Amigas. “Where there are failover clustered instances with shared storage and Always On availability groups with locally attached storage?”

Erik Darling Hell yeah, yeah there’s been like 24 people managing those. There’s like numbered team jerseys, you’ve got to…

Brent Ozar: It’s complex.

Erik Darling: You have to be one heck of an engineer to intermingle and interoperate all of those technologies.

Brent Ozar: When I used to talk about AGs a few years ago in 2012 when this stuff first came out, allrecipes.com had a public example of that and Discover Channel had a public example of that as well. So that was way back in 2012 when they first came out, but all those people had like three, four, five people in their DBA teams, and that was their only cluster.

 

The B-side of Brent Ozar Unlimited

Brent Ozar: Peter says, “Richie looks like a bee.” Yeah, he’s our B team…

Richie Rump: Watch out for my stinger, boiii…

Erik Darling: Our little B-side.

Brent Ozar: That’s human resources, paging human resources…

Richie Rump: Again?

Erik Darling: She’s out getting tacos with my wife right now.

Brent Ozar: Human Resources is Erika She has the worst mouth of any of us. She swears, curses me under the table…

 

Is vMotion okay for database servers?

Brent Ozar: Amdal says, “Hello, what are your thoughts on VMware VMotion in database servers? What are your recommendations for this? I recommended the team to set resources and change them from shared to dedicated; do you like VMotion?” Do we have anything against VMotion?

Erik Darling: I have nothing against VMotion. I guess my only hang up about VMotion in general is that if you have a SQL Server – which I assume is why you’re here – on a virtual machine, VMotion actually won’t be aware if the SQL Server fails. So if the SQL Server goes down, VMotion won’t make a peep about it. VMotion won’t protect you from SQL Server going down. The other thing that sucks is, if you VMotion a VM with a downed SQL Server, it will come up on wherever you VMotion it to still down. It doesn’t actually restart anything, it transfers the VM over in the exact same state that it was in when VMotion started. So it doesn’t protect you from downed SQL Servers; other than that, pretty cool, when you pay for VMware double enterprise full set of teeth licenses.

Brent Ozar: And be aware too that if you VMotion like a running database mirror or an availability group, if it’s down for too long during the VMotion, you can cause a mirroring failover or an availability group failover.

Erik Darling: And if it’s less than graceful, you might even cause yourself some corruption.

Brent Ozar: So weird that I forget about that.

 

Followup on the Analysis Services config file

Brent Ozar: Nesta says, “For your analysis services question, the config file for SQL Server 2012 is located in C program files…” Well, it’s probably going to be wherever you installed it. “Microsoft SQL Server lack MSSS…” You know what, I’m just going to paste that into the questions window and say here you go, rather than looking like an idiot trying to read that out loud. So if you’re looking for that location, it’s in the answers window. It’s also in the config file…

Erik Darling: Well the configuration manager.

Brent Ozar: Oh configuration manager, no kidding.

Erik Darling: You can just right click your butt off.

Brent Ozar: Now I know how analysis services work.

Erik Darling: Now you’re a data scientist.

Brent Ozar: I’m a data scientist, I can dump this crappy job.

Brent Ozar: Oh my god, Daniel says, “The data scientists at my company have databases named C:usersuserdesktopthelastfinalcopy.MDF.

Richie Rump: Yep, and you guys think I’m lying about this but I’ve seen it. I’ve seen these data scientists work. They’re amazing, but how they get their data is crazy insane; it’s insane.

Brent Ozar: And there’s like no chain of custody throughout half the time. Where’s this field come from? “Yes.”

Erik Darling: It was in a flat file somewhere.

Brent Ozar: Daniel says, “I can provide screenshots.” If you’re willing to show they publically, like if your company’s okay showing them publically, it would be really hilarious to send them into us, but just make sure first that your company is okay with that because we wouldn’t want to get you fired. You seem like a nice guy, but not that nice; you’re here on the webcast.

 

How can I reduce failover time with AGs?

Brent Ozar: Leah says, “I would like to failover in production for patching using synchronous AGs. In my testing, I see five seconds on failing over and our application fails to connect. How do I reduce failover time with AGs?”

Erik Darling: That’s a good one. I can think of a few places off the top of my head. My first question is how many databases are you failing over? Because on failover, those databases do have to start up on the other end, so that would be a question there. [crosstalk]…

Brent Ozar: So if you have a large number of virtual log files, it takes a long time for crash recovery or whatever recovery to happen on startup.

Erik Darling: Or if you have really big VLFs, then SQL – so it’s like you don’t want to have too many and they can’t be too big, it’s very Goldie Locks in the zone where VLFs are happy. So I would check, you know, number of databases failing over and number of VLFs in there, which I do believe … So run sp_Blitz and see how your VLFs are doing.

Brent Ozar: There’s also, you can work on tuning network failover time, like the DNS IP config, release and renew.

 

Should I be an employee or a contractor?

Brent Ozar: Peter says, “I am the single DBA in my company…” Alright, if you’re asking how to like find a date or whatever, Match.com, PlentyOfFish.com? I got a laugh from Richie. … I like that. And then if you’re willing to go overseas it’s GetUTCDate.

Richie Rump: Well, I mean if FarmersOnly.com is a thing, I guess DBA Get Dates could be a thing too.

Brent Ozar: Someone who understands that you’re going to be on call pretty much every night, you’ll be foul and uncompassionate and smell like a data center. He says, “I’m the single DBA in my company. When transactions in the app fail, everything comes to me. What I’ve got there is a contractor, they never had a DBA with their platform. I’ve tuned the crap out of it and boosted it so much, they’ve now asked me to go fulltime.” Okay, what’s your question? Oh, should you go fulltime? Do you want health insurance is the big one; health insurance is usually expensive and hard to get. Vacation time? Like to have someone else take care of your vacation. Richie, you’ve been both sides, you’ve done contracting and fulltime. What are the things that would make you decide one over the other?

Richie Rump: Stability is really the big thing from a family perspective. So at any given time, when your contract runs out, they can kind of say see you later, and if you don’t have enough money in the emergency fund or you’re actively looking for that next gig, you’re going to be on the bench for a little bit while you look for it. So if you’re cool with that and you’ve got money in the bank and you’ve got enough for three months or whatever, go for it. But I didn’t like going out looking for the next gig; that was the worst for me. So at that point, my wife said, “You know, you’re not even looking at this, it’s been two months, you’re not even looking. Go get a real job so that you can actually pay for this house, you fool.”

That was the actual conversation, I’m like, “Yeah you’re probably right, maybe I should be looking for a fulltime job seeing as I don’t like going out there looking for work.”

Brent Ozar: I would also say too, if you turn down – if they ask you to go fulltime and you turn it down, be aware that they may start looking for somebody else that they’re going to bring in as fulltime and terminate your contract because you might be more expensive on a contract basis. On the flip side, if you’re doing an amazing job and you’ve built up a really good rapport, they way I would spin it with them is I would say, “Look, you don’t really want to do full time with me because I’ll be bored half the time. How about we just start tailing off some of these contracting hours so that it’s not so expensive for you?” Then that way, you can go off and start getting your next client, if you’re insistent that you want to go contracting. Richie, you should talk a little bit more about the emergency fund thing because we’ve talked about that several times recently. So what’s the concept of an emergency fund and what’s realistic that a contractor consultant should have for a parachute?

Richie Rump: Right, so the way I figured it out, our emergency fund is what can we live on bare minimum, just meeting all the bills and enough that we could live on, what is that number? So we came up with that monthly number, whatever that was, and then we just multiplied it by six because – I would have been fine with three months of a padding, but my wife said, “No, no, no, no, no, no I don’t feel good with three months, I want six months.” So we saved up and we lived essentially off that base number and then just pocketed everything else, put it into the savings account until we got to that six months number of where she started feeling really good about me being on my own and going out and doing all that fun stuff.

What happened with me, how I actually went independent was I got laid off but I got five months’ severance. So that actually helped out a lot, that actually helped me get the next gig; it gave me a buffer to find my first contract and actually to put off a bunch for the emergency fund so that she kind of felt good about me being on my own. I didn’t do it for six months, I did it for almost four years. And it’s one of those things, if you have a frank conversation with your spouse, they’ll get it. It’s like, “Well what number would make you feel comfortable? How much do we actually need to that if I’m without work for X amount of time, you know you’re going to be okay?” If you’re single, just have a talk to yourself, you know, sit in front of the mirror and say, “Hey, how you doing, you’re good-looking, you doing alright? Yeah, I’m doing all right. What number are you comfortable with? How long’s good for you?”

Brent Ozar: Yeah, Peter follows up and he says, “My statement was about the death of the DBA role.” Yeah, I don’t know anybody who’s getting laid off as a DBA from companies going, “You know what, those databases just take care of themselves now. We hardly need any help at all. You move on up the road.” Even when they go to the cloud, even when they go to Azure SQLDB or Amazon RDS – we had one client who went into Amazon RDS and hired a DBA because they’re like we have so many needs now that are outside, above and beyond what the cloud provider does for us; because you still need index tuning, you still need query tuning. And when you’re paying by the hour for a server and for performance, suddenly it makes a DBA even more required because you can drop your bill right away by doing performance tuning.

Erik Darling: Or when you’re paying by whatever the heck DTU is. I’m still not sure on that one.

 

Listeners chime in

Brent Ozar: Let’s see, Dee BA, I still love their name, Dee BA says, “For the contractor, make sure you ask enough questions ahead of time as well. On salary they can often overwork you without the overtime pay.” That is so totally true. They may be going, “You’re a contractor, you’re too expensive, let’s hire you fulltime so we can work you 80 hours a week and not get in trouble for it.”

Brent Ozar: Michael says, “I also did stupid things like pay my mortgage into the future for six months. That helped a lot when I ended up working for nothing.” Sure, I made my fantasy football bets for like a year in advance, that way I knew… That’s totally not true.

Erik Darling: I just got life insurance, so if things ever get rocky for a while, I’ll just slip and fall somewhere. Everyone wins.

Richie Rump: Well you know you have to die for the money to get paid, you know that, right?

Erik Darling: Oh that’s the plan, don’t worry, I’m cool.

Richie Rump: It really should be called death insurance. I don’t know why they call it life insurance.

Erik Darling: Well because everyone else gets to live a good life.

 

Can sp_WhoIsActive prove that the database isn’t the problem?

Brent Ozar: Nestor says, “We have a third party app blaming the SQL database for slow performance. I see queries sp_whoisactive come and go in less than two seconds. Is this enough to prove that SQL Server is not the bottleneck? No.

Erik Darling: I’d want to look at overall wait stats, I think, on that server.

Brent Ozar: And how would you see wait stats?

Erik Darling:  I would use sp_BlitzFirst, available in our first responder kit for free.

Brent Ozar: sp_BlitzFirst, which we will be teaching the students here how to use tomorrow. That’s one of the things we show in this class.

 

I’m using SQL Server 2000…

Brent Ozar: Gordon says, “Given that some of my company’s clients still use 2008, 2005 and 2000, I don’t think the DBA is going to become irrelevant any time soon.”

Erik Darling: So let me ask you a question. What’s the timeline on you getting on a version of SQL Server made, say, in this decade?

Brent Ozar: And you know you have no support. Like if anything goes wrong, you are screwed. I know it’s not yours, I know you didn’t choose to run those versions. This is where I start to say things that are more my management, say, and you put this in writing, “Just so that everyone knows, if any of these servers die, I have no support capabilities with Microsoft.” And when they come to ask me to make a query go faster, I’ll flat out say, “Oh man, these are the tools I have on 2008 and forward, but I don’t have those tools on 2000, 2005.” “What can you do?” “Nothing…”

Erik Darling: Run DTA.

Brent Ozar: That’s right, I forgot; go back to 7 because it’s self-tuning.

Richie Rump: On a good note, some of those databases can legally drive now, so that’s good.

Brent Ozar: Unfortunately they’re intoxicated.

Erik Darling: Buy scratch tickets, get drunk, join the army, all sorts of fun things.

 

Is the Expert SSIS Training sold out?

Brent Ozar: Last question we’ll take, Wes says, “Is the expert SSIS training sold out?” All our training classes are on hold now until November the first. We’re going to announce everything back out on November the first, including our new lineup of classes for next year. So right now we’re building up anticipation via emails and talking about the kinds of stuff that we’ll release. So check back on November first at 9am Eastern time, we’ll open up all the sales again; and they’ll be open at Black Friday prices as well. So thanks everybody for hanging out with us this week at Office Hours and we will see you all next week. Adios everybody… Actually we won’t see you next week because we’ll be at PASS. We’re not running an Office Hours during PASS, so we’ll see you in two weeks, or we’ll see you at PASS…

Erik Darling: Unless Tara and Richie do it on their own?

Brent Ozar: No I gave them the week off. Adios, everybody.

Erik Darling: Slackers.