This week, Brent, Richie, and Erik discuss estimating compression savings, disabling triggers, suggestions to first-time Pass Summit attendees, tools for database version control, transitioning from SQL 2000 to 2016, troubleshooting failovers, and how to convince your manager to upgrade to newer version of SQL.

FYI: our regular transcriptionist is out for a couple of weeks, so we used an online service this week, and it’s a hot mess. I think it’s safe to say the robots won’t be taking over our jobs anytime soon. I did a fast round of cleanup, but it’s still a hot mess.

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 – 2016-10-12

 

How should I upgrade 310 terabytes of SQL Server 2000 data?

Brent Ozar: First one is kind of a doozy. We have 310 terabytes of databases on 30 instances and unfortunately some of these are antiques. 10 of them are SQL Server 2000 and 20 of them are SQL Server 2005.

Richie: What SQL?

Brent Ozar: Big ones too.

Richie: Golly.

Brent Ozar: Usually these are the kinds of … when you get big databases, hundreds of terabytes worth of stuff, you want to get those off old versions as fast as you can. These databases need to be migrated to SQL 2012. They’re critical in terms of business operations and we’re trying to get to less licenses and minimum downtime. I advise to go with side by side migration. Would you guys suggest the best approach? In place or side by side? Wow! Say, Eric, someone handed you 300 terabytes worth of data on SQL 2000 and 2005, how would you approach that?

Erik Darling: Run screaming into the night.

No. The first thing I think is weird about that is that they’re only moving to 2012.

Brent Ozar: Oh, that’s a great point.

Erik Darling: Why not one of the newer newer versions?

Brent Ozar: You know why … It’s kind of hidden in that question too. If they’re so conservative that they’re only wanting to go to 2000 or … They’re running on 2000, 2005 today, they probably are like, “I don’t know about that new fangled 2014!”

Erik Darling: Ah, that’s interesting. Well, I would not do an in-place upgrade because the chances are that that hardware is old as dirt, possibly older. I think this is going to sound weird because I almost never vote for this, but I think this might be a good case to address part of the question about licensing.

This might be a good case for virtualization where you could license enterprise at the host level and set up as many host as you want and sort of have them vary in the hardware department as to how much they actually use.

At this point, any newer hardware is going to be an improvement.

Brent Ozar: Yes.

Erik Darling: There’s no way that the virtualization layer is going to be an issue.

Brent Ozar: A noticeable problem.

Erik Darling: Yeah. Anything is going to better than what you’re on now.

Depending on your licensing, if you’re on standard that falls apart a little bit because you have to license standard guests, per guest not at the host level, but if you’re on enterprise licensing, that’s what I would do.

As far as moving the stuff over, that depends on what kind of storage it’s on. If it’s on the SAN, you can do some SAN magic. If it’s not, then you probably have to log ship or something to move it on over because 2000, depending on what service package, 2005 you’re on, you’re not going to have a lot of other options.

Brent Ozar: Hmm. Yeah, I like all those thoughts. I love the virtualization angle. That’s really sharp.

Let’s see, next …

Erik Darling: I didn’t say Azure because I’m not that cool.

Brent Ozar: You should just put the whole thing on a … yeah.

Erik Darling: Well, I was just like, “How are you going to get 310 gigs to the cloud, Eric?” You’re not. Terabytes, right? Terabytes.

 

Will changing my primary key to datetime be faster?

Brent Ozar: Yeah. Let’s see. Next one. Would changing your primary key from GUID to datetime see a performance benefit?

Erik Darling: No.

Brent Ozar: Wow! So, I could imagine a case where it would. Imagine that you were, and I’m really reaching here, Imagine that you only did selects with a filter on the where by time. That’s like the only edge case. I would stare back and go, what’s the performance problem you’re trying to solve? Drill deeper into things like wait stats to figure out whether or not. But changing the primary key, that gives me heart burn right there. Which, Erik, or actually Richie, if you were picking primary keys for a table, what do you go by there?

Richie: I like identities. It’s typically what my default it. But if you’re doing identities, you also want to take a look at your natural keys and place them as alternate keys as well, where applicable. Not every table is going to have a natural key in it. But you definitely want to say “Hey, does this have a natural key or not?” And if it does, create that as an alternate key. When you do that, you minimize the duplicates you could get in the table. And by the way, if changing your primary key is changing your performance anyway, it’s your clustered index you should be worried about anyway, not your primary keys.

 

Any suggestions for a first time PASS Summit attendee?

Brent Ozar: Got an interesting one. Any suggestions for a first time PASS summit attendee?

Erik Darling: That was my question, I’m sorry. (laughter)

Brent Ozar: Is this your first time going to PASS?

Erik Darling: Yeah.

Brent Ozar: Oh, it is your first time.

Erik Darling: No one else has ever sent for me. Surprisingly.

Brent Ozar: Oh man. Let’s see here. I just got done writing our weekly newsletter for next Monday and it’s full of tips. So, my big ones are stay hydrated, go drink water after every session. It’s very easy to slip into “I’m just going to drink coffee and Coke”. And then, next thing you know, you feel like hell by day 2. So, drink water after every session. If you find someone that you like talking to, don’t be afraid to skip sessions and keep talking to them. If you’re bored, go hang out in the Sheraton lobby. It doesn’t matter if you’re staying there or not. There are coffee bars and lobby bars and just couch areas. Wander around through there like you’re looking for someone, because that’s exactly what you’re doing, and when you see someone you recognize, you just go stand near them. It is not creepy at all, that’s what all of us do when we’re down there. You’re going to see so many people that you recognize. It was like the first year and I’m like, “Oh my God, Linchi Shea, I know that guy!”.

Richie: We played Pandemic last year in the Sheraton lobby.

Brent Ozar: Wow. Gathered a crowd, I would assume, too.

Richie: Well, not as you would think because there were other people there doing their thing. But we had people poking over like, “What are you guys doing?”

Brent Ozar: I wonder if we should set up a game night. A Pandemic game night in the lobby.

Richie: That’d be cool.

Brent Ozar: Or Cards Against Humanity. I have the SQL Against Humanity game set from Dev Nambi. It’s quite enjoyable.

 

Should sleeping SPIDs hold onto memory?

Brent Ozar: Let’s see. Fellow human asks, should sleeping spids hold onto memory? We were having memory pressure on our server, and I showed 8 or 10 connections sleeping with around 2 gigs of memory usage each. We restarted the app service with the sleeping connections and 18 gigs freed up. Is that a bug, or is that supposed to happen for some reason? I’m on SQL 2008 SP3. What are reasons why sleeping spid would be using memory? I’ve got temp tables is the one that I’m thinking. If you create temp tables, that’ll do it because they’ll get cached in RAM, just like a regular database pages would.

Erik Darling: My concern with sleeping spids is more that someone boffed connection pooling. Like they didn’t set a time out or a disconnect if you haven’t done anything in X amount of time.

Brent Ozar: And if they’re using RAM, I’m just guessing, temp DB objects. They probably have other goofs in their code too.

Erik Darling: Mm-hmm (affirmative). Like someone actually did begin TRAN and then didn’t commit to the thing. That would be at least part of the reason why the temp tables would be up and running.

Brent Ozar: Before you kill a query, if you’re looking at a list of sessions and like sp_WhoIsActive, is there anything that you look at before you kill them?

Erik Darling: No, I just haul off and start shooting things.

Richie: Good answer. Good answer.

Brent Ozar: Well, that’s why you hire us for consulting, ladies and gentlemen. We do it live.

Erik Darling: Well, I mean, if I can I like to use the fancy get outer command and stuff for sp_WhoIsActive to see if he has done any transactions. There’s the get trans info where you can see if it’s done anything with the transaction log. You can see if it’s written anything. If you kill it and have to roll back, who knows what you’re rolling back. The important thing to look at is if that query has made modifications or simply just done some selecting using temp tables.

Brent Ozar: Nate says we tested a pretty darn legacy app environment that was on SQL 2005, and we upgraded to 2016 and it worked fine. Listen, Nate, you’re lucky. Not everyone is as lucky as you are. I do appreciate that though.

 

Can you estimate backup compression savings without taking a backup?

Brent Ozar: Next one. Is there a way to estimate backup compression savings without actually creating a backup? Am I the only one who’s played around with this?

Erik Darling: So, there’s a stored procedure for table compression. I don’t think there is for backup compression without taking a backup. I’m trying to think if you did the backup to null, if that gives you the compressed size or not. I think it does. So, if you do a backup to file and you put in for the file name N-U-L, just nul, not 2 L’s just one L. That actually takes a backup and doesn’t write it. The problem with that is that it is a real backup, so it will mess up your differential bitmap. Make sure you’re only doing a copy only backup.

 

Is Locked Pages in Memory still a good idea?

Brent Ozar: Next question, locked pages and memory, is it still a good idea for SQL Servers where the OS is 2012 or plus? I just read an article where Brent is against setting it with newer servers.

Brent Ozar: Now I’m gonna go answer this, so here’s the deal. The two people in the community who are kind of pro and con with this, Jonathan Kehayias is very pro linked pages and memory. If you search for Jonathan Kehayias, and it’s spelled just like it sounds, Jonathan Kehayias locked pages and memory. He has a blog post. It’s very lengthy, and it explains both his point of view and my point of view. Jonathan’s brilliant, I love the guy. Then go down and read the first set of comments. Read the first four or five comments and one of the comments that Jonathan writes is under certain circumstances if the OS can’t free up memory fast enough, you will experience a Windows crash due to memory pressure. So, I’m just not pro setting blue screen of death settings. I just rather avoid that. Go find out what’s really causing the SQL server to swap to disk and go fix that.

 

Can I bypass triggers?

Brent Ozar: Let’s see, fellow human says, I got this vendor trigger and I need to update a column, but I don’t want the trigger to fire. Do I have any options beside rewriting the trigger? Ooh, that’s kind of cool.

Erik Darling: Can you disable triggers?

Richie: I think you can.

Brent Ozar: I think you can just disable them.

Erik Darling: Yeah, I know that there are some … No, that wouldn’t work. Never mind.

I know with bulk insert I think you can ignore triggers and constraints, but I can’t think of a way to do that with an update. Yeah, I would just disable the trigger.

Richie: Disable trigger. Oddly enough, that’s the SQL for it. Disable trigger.

Brent Ozar: There you go.

 

Do you have any thoughts on version control?

Nate says, this may be a bit odd-ball, which is our favorite kind of question, but do you guys, particularly Richie or Jorriss, either one, have any thoughts on database version control? Have there been any tools you found that work well for that? What is the SQL environment need to look like to be ready for that?

Erik Darling: Oof, man, that’s such a hard thing to do. Red Gate has a tool that some people really like, some people don’t like the cost, that does a pretty good job of doing source controlling in your database stuff.

Also, I have used a DB projects with some success as well. You can hook that up to whatever source control that you have handy. Preferably not VSS. I think between the two of them, whichever road you go, they’re pretty good solutions.

I like DB projects if I was going greenfield. But, test it in your environment. And how much funding you have for these kind of things.

Brent Ozar: Angie says maybe we should be playing Cards Against Humanity in the lobby.

Richie: And why not! We’re all adults.

Brent Ozar: The funny thing is it probably isn’t even going to be the most offensive thing that happens inside the Sheraton lobby.

Let’s see next one. Have you noticed that some SQL counters no longer work? For example, under SQL server resource pool stats, CPU usage target percent is always zero. I don’t think any of us have noticed that because we don’t look at Resource Governor that closely. But, that’s the great kind of thing that you put under connect.microsoft.com. Connect.microsoft.com is where you go file bugs. That could certainly help you out a lot there.

Angie says there’s probably going to be at least one kid at the Sheraton. Angie, you’re pretty young. You might even qualify as a kid.

Richie: She gets a free sundae.

Brent Ozar: Adam Machanic, the first time I found his age, now this is a while ago now, but I’m like, you’re how old? And you’re already this smart? I think he was like 32 when I met him. And I was like, this should be against the law. Jonathan Kehayias as well. Oh my God, the guy looks like he’s 14. And he’s a rocket surgeon. Amazing.

 

How do you troubleshoot Always On AG failovers?

Next question. Testing Always On Availability Groups. First off, you’re off to a great start because she spelled it correctly with the spacing and everything. You’re practically a guru on that. I’m having issues connecting to my databases after failover because the users have lost their link. Have you come across this before and how have you handled this?

Eric, have you had to play around with troubleshooting AG failovers?

Erik Darling: I’ve never had outside users losing their link, no.

Richie: No?

Erik Darling: The problem is typically … it depends, are you failing over inside the same data center or off to another data center if there’s another IP address involved. Troubleshooting multi-subnet failover can be a little bit tricky. We actually cover this in our senior DBA class. And it’s a little bit beyond what I can answer in a Q and A, but search for the term multi-subnet failover equals true. This is the big one. And it’s enabled by default in the current versions of dot net. I want to say it’s 4-6-2. I can never quite remember.

Richie: Sounds good.

 

Why should I bother upgrading from SQL Server 7?

Brent Ozar: Let’s see here. Next up. There’s a question that I feel dirty even saying out loud. My manager is running a database in SQL server 7 and somehow hasn’t failed horribly and if he says it ain’t broke, why fix it? What is the best way to go about convincing him to upgrade to a newer version of SQL?

Erik Darling: Break it.

Richie: I love it!

Brent Ozar: Yeah sure, right?

Erik Darling: I mean, that’s my big question. Okay, it’s not broken now, but when it does break, how are you going to get support on that?

Brent Ozar: That’s 3 full versions out. 2000 isn’t supported. 2005 isn’t supported. That’s a hell of a runway.

Erik Darling: Yeah. I mean, you would have to first upgrade it to another unsupported version before you could get it to a supported version. You would have to put it on 2005 and then 2008, I think? Right?

Brent Ozar: Mm-hmm (affirmative)

Richie Rump: Going back to my old project management days, as much as I try to block that out, you could create a risk mitigation document. Say, hey, these are the risks that involved: data corruption. You could say high, medium, or low what is the likelihood for this to happen. And then, what’s the mitigation? How are we going to fix it? And how long is that going to take?

You could just estimate all these things. Just say these are the things that could go wrong and this is how long it’s going to take to fix. Right? This is the probability of this happening. Just say hey, by the way, I made a risk mitigation plan, why don’t you go ahead and sign that just in case something happens with this database we all know what we’re going to do. And you approved that it will take 3 or 4 weeks that the systems be down and you’re okay with that.

He’ll probably take a look at that and say, “Oh, that’s unacceptable!”

Well, you know, if you spend a little money now, these risks go away.

Brent Ozar: And you don’t have to email his manager. Don’t try to jump up the chain or anything. It’s not going to make you any friends.

Erik Darling: … your boss.

Richie: To the president. What I’ve run into in the past is that people don’t want to necessarily get on a new version of SQL. They don’t necessarily want to have to test the app or the code to move up. But they are okay with virtualizing that server.

So, you could just P2V that older server. Put it on newer hardware. Get at least some of the risk mitigated. But, not have to go through the full breadth of testing the application and the code. Get it something a little bit safer than where it is and just let it keep running on where it is.

Chances are, if it’s been on SQL 7 that long, no one really cares about it.

 

How quickly should we adopt new versions of SQL Server?

Brent Ozar: On a related question, does anyone have a standardized timeline for adoption of new SQL server versions? How long does it take? 6 months or a year?

You know what I would do? I would go to spotlightessentials.com. Spotlightessentials.com is Dell Software’s free monitoring product. But they have this really cool collective IQ, or collective knowledge, page that lists statistics about all the versions of SQL server out there. It’ll tell you which versions are the most popular.

It’s going to be kind of shocking how slow adoption really is. Because adoption is expensive!

Erik Darling: There’s usually new hardware.

Brent Ozar: Actually, as a follow-up to that. Another fellow human says first good service pack is out, but not no more.

Erik Darling: What does good service pack mean?

Brent Ozar: And on top of that, how long are you guys going to have to wait for that service pack because Microsoft is moving away from the service pack servicing model. You’re going to get a lot of cumulative updates, you’re not going to get those service packs too often. So, what’s the first big cumulative update?

Erik Darling: The real question is will SSMS when you connect to the updated database.

Brent Ozar: SSMS will never work.

Richie: But 2012 SP1 and 2014 SP1 had some horrific bugs.

Erik Darling: Mm-hmm (affirmative).

 

When I’m tuning a complex query, where do I start?

Brent Ozar: Let’s see. JH asks When I’m performance tuning a complex query, what are the first things that I should look for? Do the high cost portions in the plan, something like 80% or 90% items, is that somewhere that I should start? Or not necessarily so.

Erik Darling: It’s a good starting place. I don’t think it’s a bad starting place at all. It may not be where you end up.

If you’re troubleshooting a high-cost query and you’ve done some finagling and all of your costs are still on an index seek, I don’t really know where you’d go from there. Maybe the highest cost is a good place to start, may not be where you end up though.

 

Mumbo jumbo mixed up terms

Brent Ozar: Next question. I’m trying to create a copy of a 2012 SQL server that has always on replication …

You have to be really careful when you say things like this because replication has several different meanings. I can’t quite tell by the question if you mean transactional replication or just always on replication.

… I’m trying to restore a copy of the production to a point in time using transactional log backups on the dev server but I keep getting LSM errors.

Good God! What are you trying to do?

Erik Darling: You should have read this question first.

Brent Ozar: You can’t merge backups from two different servers like production and development even if they started from the same place. Once they fork off, you’re screwed. So sad how that works.

Erik Darling: Mm-hmm (affirmative). You are forked.

Brent Ozar: You are forked.

 

Any upgrade issues from 2008 to 2016?

JH says, is there anything to be aware of or consider when I’m upgrading from SQL 2008 to 2016?

No, not at all. Next question.

Erik Darling: No breaking changes whatsoever.

Brent Ozar: There’s like a really good technical upgrade guide if you search for SQL server technical upgrade guide. I think it’s still for 2014. I don’t think they have the 2016 one out yet. It’s like 300 pages of stuff that you want to consider.

Erik Darling: One thing that I really like checking, Microsoft lists breaking changes between versions. So, if you want to search SQL server breaking changes, you get a guide of stuff that breaks between versions.

I don’t think it skips version. So, you couldn’t do a 2008 to a 2016 comparison, but you can at least get breaking changes between some versions which might be helpful because you could figure some stuff out real quick.

 

What about upgrading from 2000 to 2016?

Brent Ozar: Related question, are there any resources or guidelines to upgrade from SQL server 2000 to 2016.

Erik Darling: Ha-ha. Ha-ha-ha-ha-ha-ha (mockingly)

Yeah, no because 2000 has been out of support for so long. This is one of the reasons that Microsoft always supports so many versions. They gave you guidelines during current versions. What you’re probably going to have to do is look for old documentation from like 2008, for when people were moving between 2000 and 2008, and make that jump first. And then later, make the jump from 2008 to 2016.

Also, note that the vendor is out of business and there’s no support anymore.

Brent Ozar: This is … (Sighs)

Erik Darling: You know what I’d do? I would just spin up a dead server. I think you’re going to have to hit a version between 2000 and 2016. So you can at least skip 2 versions ahead. I would just battle it out, get it on 2016 and see what happens.

Brent Ozar: 2016 will let you restore databases from 2005, which is kind of cool. So, it allows huge chunks. I just don’t think you can go from 2000 to 2016.

Richie: I mean, that was like before Nickelback! Like, Oh my gosh. Seriously.

Brent Ozar: Wow. That’s almost Janet Jackson kind of. When she was good.

Richie: It’s like Nipple-gate, right? I mean, geez.

 

How should I react to this corruption issue?

Brent Ozar: This one I’m going to have to take a deep breath in order to read it. And I’ve read it, and it’s awesome, and I just have to share it. AG cluster, SQL server 2014 enterprise, experienced storage failure on the first node. So the databases became suspect on my primary node, then started going to fail over onto the second node. I had to do a manual failover with data loss. So, my databases are online on my secondary node. Check DB was run and returned no error.

I’m assuming that you mean that you ran check DB on the first node. Believe me, when you have storage failure, you want to be running check DB like it’s a free party and everybody is invited.

What is the risk of data corruption of the recovered databases? Is the AG failover with data loss safe when I have storage failure?

Man, when you have storage failure, ain’t nothing safe!

Richie: Nothing’s safe at all.

Brent Ozar: Just go run check DB. I would do with extended logical checks as well. It’s going to take a little extra time, but that also catches other things. Normally, even just physical only will catch them.

Erik Darling: Yeah, if it was a bad storage [inaudible] and you got corruption because of that, physical only would throw a whole mess of errors. You’re going to have some problems.

Brent Ozar: Mm-hmm (affirmative). And you’ll know quick.

Richie: And I would also say too, whenever you do that failover, before you fail back to something that had extreme storage problems, load test the bejeezus out of it. Really load test it for a while. Do several repeated database restores and check DBs just to make sure continuous write is working.

Erik Darling: I might even tear that AG down and start over again. That’s how terrified I am of storage failures. We’re gonna do something fresh here. That’s not good. [inaudible]

Richie: This is what we’re talking about this is one of those reasons where I pray that if I ever need another job after this, I’m working with something in the cloud. RDS or azure database where I don’t have to worry about this stuff. I can just say, nope. We’re going to make some indexes today.

Erik Darling: Man, that’s awful.

Brent Ozar: It seems so cool when you read the brochures. There’s all these new features that come out on the SQL servers. Like, Ah, that looks so awesome. But then when you have to manage it afterwards, it’s like, you know indexes and query tuning are really pretty awesome.

Erik Darling: Mm-hmm (affirmative). Mm-hmm (affirmative).

 

What’s the best way to keep logins in sync?

Brent Ozar: Let’s see here. What is the best way to keep logins in sync? SQL logins in sync between two servers, like live and test?

Erik Darling: You know, I’ve never had to automate that. I know Robert Davis has a thing that helps you strip things out. Let me think of the key words to search that.

This is like Robert Davis script logins, right?

Brent Ozar: Totally. Yup. That’ll do it.

Richie: That’ll work.

Erik Darling: So I would go there and just look for a way to weaponize that into something that checks between servers. You might have to do something [inaudible] with link servers to do it, but if you wanted to automate it, yeah.

Richie: I mean, we talked about source control. That’s something you could source control. Especially from test, right? Test server, you probably don’t want all your production people on it. So, strip out your test users and whoever needs to access it and go from there.

Erik Darling: Well, that brings us to something else. Do you really want to be doing that? Because you might have given devs some advanced permissions on that dev server that you don’t want them inheriting on the prod server.

Especially because they might be forgetful about where SSMS is connected. Something whacky.

Richie: I’ve never dropped users table.

Erik Darling: I have never overwritten a production database. That anyone could ever prove!

 

Buzzword bingo: “SSIS Task”

Brent Ozar: Someone else pipes in, SSIS task?

I like that. If we were playing keyboard bingo, you totally would have gotten a box for that one. But if you’re an SSIS person, that’s the kind of thing that makes sense.

The one thing I would want to say is, it’s kind of like Power Shell. Just because you know the hammer really well, doesn’t mean everyone else on the team does. So, just try to write things that everyone is going to be able to re-use.

Erik Darling: I love to hate SSIS. Is that? [crosstalk]

Brent Ozar: I’m like, oooh, that looks kind of cool over there. I used to do that.

 

Is NetBackup okay for SQL Server backups?

We were recently told to migrate to net back up for using backups. So far, things look good. But do you have any horror stories about using net back up?

And in order to avoid trash talking any particular third party tool, I would say do you have any horror stories about just using any other third party tool for backups?

Erik Darling: Is net back … Is that like an overlay for native SQL backups? Or is that doing snapshots?

Richie: It’ll do both. It’ll either do snapshots or it’ll do full on transactional backups.

Erik Darling: Okay, so, as far as third party tools go, I have one that I really didn’t like that I won’t say the name of because we are recorded and transcribed these days. But there was one that I had really negative experience, well several very negative experiences with.

One of the problems that I had with it was that when it was taking backups, it would create and destroy these virtual devices which were just GUIDs. So, it would back up to a device and it would name that device a GUID and then it would destroy that when it was done. What bothered me as a DBA was that if this backup tool server went down, there was no way for me to strop out a restore on my own, outside of the backup tool. [inaudible] Because A, that backup device doesn’t exist, I don’t know how to map it, I don’t know how to do anything with it. If this backup tool goes down, I’m hosed.

In an ideal world, I want a little more control over how backups get taken.

Richie: Agreed.

 

How do I get a Brent Ozar Unlimited coffee mug?

Brent Ozar: Someone says, I’ve always wanted one of those Brent Ozar Unlimited mugs. I’m jealous.

Erik Darling: Yup.

Brent Ozar: This is an old school mug.

Richie: It is! Look at that!

Brent Ozar: That’s a contraband one.

Richie: That’s contraband!

Brent Ozar: From one of our illegal logo mods. Back when we didn’t understand how trademarks work. To be more specific, our graphic artists didn’t understand how trademark worked.

 

How can I reduce disk space requirements for Availability Groups?

Brent Ozar: Several other questions. We’ll take one more. We are going to go for … What is your starting point for space saving? If I’m using always on availability groups, and because each replica has to have its own copy of the database, is there any way to cut down on the amount of storage required, considering that I have production, development, staging … All these different environments. How do I cut my storage costs?

Erik Darling: Compression.

Brent Ozar: Oooh, specifically what kind of compression.

Erik Darling: It kind of depends what’s in your tables. Page compression offers really good compression across the whole spectrum of things. Row compression can sometimes buy you a little bit more. Again, depending on … I think that’s good if you have cut row. That’s good if you have chara columns or something like that. Where you’re actually not using all of the space. [inaudible]

It’s been a while since I read up on that.

Brent Ozar: It’s an enterprise edition feature, but who cares because you’re using always on availability groups.

Erik Darling: Right. So, that would be one way. The only thing is, you’re going to compress all this stuff and your data files are also going to be the same size. [crosstalk]

Richie: … at the storage level. So, if you’re using a third-party storage … I say third party storage. If you’re using shared storage like pure EMC, equallogic, those can start doing de-duplication at the storage layer as well.

But man, I’ll tell you what, I don’t know how big your databases are, but in our weekly links this past week, Intel is now shipping 2 Terabyte PCI express solid state drives. 2 Terabyte drives for $1,000. That means you’re going to put pairs of them in, but then you get 2 Terabytes of mirrored storage for 2,000 bucks? That’s a lot cheaper than a lot of development approaches that we’re going to do.

Like, having to change table design or stuff like that. Compression is easier, but …

Erik Darling: Or you could just switch everything over to column store. Get rid of your primary and foreign keys. Get rid of everything else. Just column store, column store, column store.

Richie: Hey, [Air-Mongos 00:30:00] good for that.

Brent Ozar: SQL server 2016 has the new compress command. Compress and decompress. So, what you could do is just have one XML column and you could just put everything in there and send it every time you want to access it.

And that is the end of our bad advice questions here for this week.

Richie: You know what else is cool?

Brent Ozar: What else is cool?

Richie: You can make a computed column with compress. That’s all I’m saying.

Brent Ozar: Wow! That’s kind of nifty.

Richie: Yeah.

Brent Ozar: I like it. (laughter) And there goes the next time we get access to a live server. Fellow humans, says you guys rock. That’s probably the best place for us to drop the mike and walk away. Thank you, ladies and gentlemen, for hanging out with us this week, and we will see you guys next week.

Erik Darling: Goodbye.