This week, Erik, Tara, Jessica, Doug, and Angie discuss queries, installing multiple instances of SQL server on a Windows VM, using DENY Database rules, migrating databases to Amazon RDS, availability groups, using filtered indexes, and more!
You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.
Office Hours Webcast – 2016-07-13
How should I convert a database from Access to SQL Server?
Jessica Connors: From Steven Mitchell, he says, “For converting MS Access 2010 to SQL 2012 would using SSMA tool or upsizing tool be preferred? Or some other method?” I have a feeling that we don’t know.
Tara Kizer: We don’t use Access.
Angie Rudduck: Sorry.
Jessica Connors: That’s important.
Doug Lane: Having used Access in the past, I would shy away from that because Access has a nasty habit of rewriting things in a way that just really makes no sense. It might be logically equivalent but you’re probably better off just importing. Like if you want to do the quick and dirty way, just do import data from Access data source. Go through all the tables, import the data that way. Because you’re going to end up with—and maybe you want this, but probably not—like keys coming through, constraints, weirdness. You’ll end up with fields that get converted into datatypes you don’t want. So you’re probably better off just importing that data and manually rigging up how you want those columns to come in.
Jessica Connors: All right, enough with Access.
When will Microsoft release an updated BPA?
Jessica Connors: Question from Henrico. He says, “When will MS release BPA for 2014?” Do we know?
Erik Darling: Ask MS.
Jessica Connors: Microsoft.
Doug Lane: I’m not even sure what BPA he’s referring to.
Tara Kizer: It’s the Best Practice Analyzer but it’s already out. I used it at my last job. Maybe the question is about 2016 since that was just released?
Erik Darling: Probably.
Jessica Connors: Do not know.
What is free virtual memory?
Jessica Connors: Wow, James really wants us to answer this question. He asked it twice, three times. “What is free virtual memory? What is free virtual memory? How to resolve the issue?”
Tara Kizer: What are you trying to solve? We need some context.
Jessica Connors: Yeah, anyone know what is free virtual memory? Is that a proper question?
Erik Darling: I guess it’s when you get virtual memory for free from the virtual memory store.
[Laughter]Erik Darling: It was on sale that day.
Angie Rudduck: Yeah.
Jessica Connors: All right, James, we need more context.
Should I enable Lock Pages in Memory on VMs?
Jessica Connors: Onward to Edward. He says, “I see conflicting posts on the internet for lock pages in memory versus VMs. Thoughts? Pros and Cons? I’m thinking of adjusting the minimum memory to one half of max.”
Tara Kizer: I always set lock pages in memory as just say standard across servers. I worked in large enterprises so we just had a document to fall on, lock pages in memory was always set on the dedicated database servers. As far as min memory to half of max, that’s also what are standard was at these large corporations.
Erik Darling: Yeah, I think for VMs more specifically though you have to be careful because if you’re not setting reservations at the host level, then I think stuff can get ripped away anyway.
Tara Kizer: Yeah, we never even worried about that on VMs but maybe that’s just… I don’t know.
Erik Darling: I mean, it’s a VM, you don’t care about performance anyway.
Doug Lane: Didn’t this come up as a question last week where Brent answered it too and he said I could go either way but I usually do it?
Tara Kizer: That’s right, yeah.
Doug Lane: Rings a bell.
Angie Rudduck: He said it was not the first thing to worry about or something like that.
Doug Lane: Yeah, yeah.
Angie Rudduck: If that’s the only thing you have to worry about, good job.
What’s the fastest way to copy data between servers?
Jessica Connors: Question from J.H. He said, “Would select * into be the fastest way of copying large data, or some type of bulk method, or something else from one server to another via linked server?” He says, “My tests look like select into is very fast.”
Erik Darling: It depends on what version you’re on. SQL Server 2014 and up select into can run parallel which is a nice added bonus for that. If you’re going to move a large amount of data, I would probably shy away from select into and probably use a method that sort of respects your transaction log a bit more and breaks it up into batches.
Tara Kizer: I would actually use SSIS for this since it can do it in bulk. It has a fast way of transferring data between servers.
Doug Lane: What do you guys think about minimal logging in that case?
Erik Darling: If it works, it works. It doesn’t always work. Don’t forget that TABLOCK.
Doug Lane: Right.
Tara Kizer: The problem with minimal logging is you’re reducing your recovery points though. So, yeah, you can minimally log things but you don’t have recovery points in that window.
Erik Darling: Yeah, so if it’s a one-time data move and you don’t care about anything during that window, then go for it. But otherwise, if your database is in full recovery mode, you’re not going to get much out of minimal logging.
What’s the best way to recover free space from data files?
Jessica Connors: I’m going to butcher this name—I don’t know how to say it. “What is the best way to regain the free space in data files after deleting a large amount of data?”
Tara Kizer: Well, I mean, it’s shrinkfile. So a one-time shrink is okay if you have to delete a lot of data and you want to reclaim that space. If you don’t need to reclaim that space, leave it there. If you have enough storage, just leave it there because when you shrink the file down you’re going to be causing some performance issues, fragmentation. One time shrink though, go ahead and do it. DBCC SHRINKFILE.
Erik Darling: The thing with shrinking data files, at least when I’ve done it is that if you only shrink it to the point where you have free space, like if you just sort of truncate it to where your data ends or a little bit over that, like a gig or 512 megs over that, you don’t really see the fragmentation problems because you’re just getting rid of empty space at the end of the file. So you kind of don’t see the same problems, at least I haven’t. I just shrunk down a bunch of databases on my availability group VM thing because I blew up Stack Overflow with some horribleness. But I shrunk it down to just sort of where the [while 00:05:48] things ended and didn’t see much fragmentation change. So maybe that’s a 2016 thing though. Be careful with that.
Tara Kizer: Maybe. I’ve always just shrunk it down so that there was 10 percent or 20 percent free space that way autogrows didn’t have to occur immediately after we’re done shrinking.
Erik Darling: That’s a good plan.
How can I run cross-server queries without linked servers?
Jessica Connors: Question from our friend Justin. He says, “Other than using a linked server, do any of you SQL super geniuses know of a way to run a query from instance A against instance B?”
Tara Kizer: You can use OPENQUERY but usually we want you to do this work in the application. Have the application query the two different data sources. OPENQUERY, you can use OPENQUERY, it’s just like a linked server.
Erik Darling: It’s not necessarily better, it’s just different.
Tara Kizer: Yeah. It does have a benefit of getting around an issue where with a linked server it can query the entire table even though you have a where clause on it. So if you have a ten million row table and your results set is only going to be ten rows, linked server can pull over those ten million first and then do the filtering. OPENQUERY can get around that issue.
Erik Darling: There’s a really good talk by Conor Cunningham, one of the bigwigs at Microsoft in the SQL Server department, on the SQLBits website. So the SQLBits conference, they put all their videos online. They did one a few years back on distributed queries where he talks a lot about the pros and cons of different methods of querying across servers and how it works behind the scenes. You should definitely check that out if you’re really interesting in querying data across servers.
Why shouldn’t I install multiple instances in a VM?
Jessica Connors: Question from Raul. He says, “Are there any downsides to installing more than one SQL Server instance on a Windows VM?”
Tara Kizer: How many do you have, Erik?
[Erik puts up 10 fingers]Tara Kizer: 10?
Erik Darling: More. I’d stick my foot up, I don’t want to gross anyone out.
Tara Kizer: On a test system, go for it. But on a production system, we don’t recommend it.
Erik Darling: Yeah, stacked instances in any environment, whether it’s physical or virtual is just bad news. If you’re already virtualized, why are you stacking instances? Spin up a new VM, it’s right there anyway.
Jessica Connors: I feel like we get that question a lot.
Angie Rudduck: Yep. That and shrinking, or replication, which there’s already one in there too.
Jessica Connors: Or, what are your favorite monitoring tools?
Erik Darling: I don’t mind answering that. That’s a nice question.
Jessica Connors: But it never changes. I mean, has it changed?
Doug Lane: Well, there all nice questions.
Jessica Connors: There’s no such thing as a dumb question.
Erik Darling: I just assume when someone asks that question it’s a vendor plant. “They always mention us! Do it again!”
[Laughter]Doug Lane: “You don’t believe me? Watch, I’ll ask.”
Erik Darling: It’s the same thing every time, it’s amazing.
When would you use the DENY database roles?
Jessica Connors: Question from Samuel. He says, “What would be a scenario where you would use the DENY database roles?”
Erik Darling: Developers.
Angie Rudduck: Yeah, I think he says two things. He says, “Why does DENY even exist if you give datareader isn’t it the same as denydatawriter?” But not necessarily true. I think the key, my understanding anyway, is that SQL is the most restrictive. So if there’s a denydatawriter, even if you have datawriter as well, you’re getting denied. So I think it’s kind of safeguard, but I don’t know.
Doug Lane: It’s an easy way of making exceptions security wise. So you say, “They should be able to write across all these databases, except I’m going to DENY this particular one.” So the DENY is very explicit whereas it can be used as sort of an exceptions tool to just kind of cut out, “All right, don’t give them this. Give them this, give them this. But they can do everything else that the other permission that I gave them allows them to do.”
Tara Kizer: We used the DENY at my last job for sensitive data. We had active directory groups. We had a lot of people in certain active directory groups and a lot of people in a different one. One specific group got the denial on certain columns in a table, like address information, emails, sensitive information. The other group was allowed to see it but if you were in both groups, you got the denial because DENY overrides the grant.
Angie Rudduck: I like that.
Doug Lane: I was going to say you’ll get a lot of questions on that if you ever take a Microsoft certification exam.
Tara Kizer: Oh really?
Doug Lane: Yeah.
How do I get data into Amazon RDS?
Jessica Connors: Question from Kimberly Lu. She’s migrating to Amazon RDS. She says, “My first experience migrating a database to Amazon RDS has been painful because I could not use backup/restore. I had to backup/restore to a local server, generate scripts, recreate users, import/export data using wizard. Do you have a less painful process?”
Tara Kizer: Nope. It’s RDS. If you’re using Amazon EC2 it would be easier since it’s a virtual machine, but yeah, RDS, it’s like Azure SQL, right? Limited features.
Erik Darling: Yep.
Angie Rudduck: Somebody else asked about going to the cloud and how to do it. I think that that’s the key. If you’re doing the VM versus the hosted database, if you go to the hosted database at all, you have to script all of your data into or out of. There’s no backup/restore to hosted databases. So if you’re not prepared for that, maybe don’t go to a hosted database.
Erik Darling: Yeah, the one sort of exception is going to Azure. Well, you can use it for RDS too I believe. There’s something called the SQL Database Migration Wizard. It’s available on CodePlex. It just does sort of a BCP out and in to Azure. I’m pretty sure you can use it for RDS as well but I haven’t tried. So it’s okay for that but it’s still slow if you’re migrating a lot of data because you are doing a BCP out, writing a file, blah, blah, blah. Not the fastest thing in the world.
Can I use different drive letters with Availability Groups?
Jessica Connors: Availability group question, question from Tim Smith. He says, “One of the benefits of availability groups is not having to have identical hardware. Does that include drive/drive letters? I tested and it looked like you could have a database on different drives on each node but a recent outage has me questioning that.”
Tara Kizer: I’m pretty sure you can. I’ve always had the same drives across because it’s just so much easier with restores but I’m pretty sure it acts just like mirroring where you can have different drives because you’re telling it what to do during the restore command. I’m not too sure what issue he’s referring to on the failover though. But I’ve always used the same drives. I’m pretty sure that it supports it across different drive letters.
Erik Darling: Yeah, anytime that I’ve had to build a cluster or anything else, I’ve always made sure that the drive—even like different servers using the same sort of hardware, I always have the drive letters be the same and do the same pathing for my database and log files, just so that at least it’s consistent across all my stuff and I know where to expect things to be. So you can do it, but it’s not really a best practice to have things be totally different across servers like that, especially when you are counting on a one-to-one copy when you get over there.
Tara Kizer: Yeah, we would need more info on what happened when you did the failover.
Angie Rudduck: He said, “On the restore of the database after failover the NDF file was gone.” I feel like there was a different problem there. Like maybe you had a problem with your drive and somebody actually deleted something. Maybe there wasn’t just a failover. Maybe that’s why you had a failover.
Tara Kizer: I wonder if the NDF came later after the availability group was set up? If another file was added and it didn’t get added to the secondaries? I don’t know. I haven’t tested that.
(Postscript: yes, this is where different drive letters will fail.)
Can you turn off auto-updating for SQL Server 2016?
Jessica Connors: Question from Scott Kelly. Isn’t that an astronaut? Scott Kelly? Didn’t he spend a year in space?
Erik Darling: Yeah.
Jessica Connors: Scott Kelly is here, guys.
Doug Lane: That’s Commander Scott Kelly to you.
Erik Darling: How’s your bone density?
Jessica Connors: Yeah, they did an interesting test on him and his twin. Let’s see here. “Is SQL 2016 auto updating not able to be turned off?”
Doug Lane: Oh, boy.
Angie Rudduck: Like Windows updates but SQL updates?
Erik Darling: You can turn SQL updates off. You can turn automatic updates off for anything. Have you had an update? Because I haven’t.
Doug Lane: So there’s the phone home thing that I think in—what is it—Developer and in Express editions you can’t turn it off. If I remember right. But I don’t know if that’s the same as the auto update. I think you can disable auto updates from SQL Server.
Erik Darling: Yeah, the Customer Experience Improvement Program.
Jessica Connors: Yeah. Scott Kelly wants us to know that he did not work for NASA.
Angie Rudduck: No, he did. He’s not the astronaut but he did work for NASA.
Doug Lane: How about that.
Jessica Connors: Oh, he did work for NASA. That’s awesome.
Tara Kizer: I went backpacking with a guy who currently works for NASA this past weekend. He’s a physicist or something like that. It was really cool.
Angie Rudduck: Did he do better at the hike?
Tara Kizer: He stayed back with the person that was having high-altitude issues, or altitude sickness issues, I should say. He actually had to carry her out on his back because it was so bad. Yeah, I’ll tell you guys later.
Jessica Connors: You did a fourteener, right? You made it to the top…?
Tara Kizer: I completed it. It was rough. It was really rough.
Jessica Connors: Good job, Tara.
Can you do columnstore indexes on top of GUIDs in SQL 2012?
Jessica Connors: From Joshua. He says, “Is there any workarounds that allow columnstore indexes with a G-U-I-D in SQL 2012?”
Tara Kizer: GUID.
Jessica Connors: I’m like, it’s not GUI.
Tara Kizer: I don’t know, Erik?
Erik Darling: What is the question? “Are there any workarounds to using a columnstore index with a GUID?”
Doug Lane: Do they not allow GUIDs in 2012? I don’t even know.
Tara Kizer: I don’t like GUIDs so I don’t use them.
Erik Darling: Yeah, columnstore in 2012 in general is kind of bogus. You couldn’t update it and there were a lot of limitations so probably not. I haven’t tried explicitly to use a GUID with it and I certainly haven’t tried to use a GUID with it in 2012 because that was just a no fun version of columnstore. It was just lackluster.
Do you recommend Converge Solutions?
Jessica Connors: Question from Julius. He says, “Do you folks recommend running SQL Server on Converge Solutions? Any experience, drawbacks, benefits, etc.?”
Tara Kizer: I don’t even know what that is. Do you guys know?
Doug Lane: I have never heard of it.
Jessica Connors: All right, never heard of it, Julius.
Doug Lane: Sorry.
Tara Kizer: Our answer is no.
[Laughter]Tara Kizer: I’ll have to look that up.
(Postscript: I’m guessing they weren’t referring to a brand name, and instead just general converged infrastructure, which is a pretty big/broad topic.)
Why is only one of my TempDB data files growing?
Jessica Connors: Ronny… oh boy, this looks long. Do you want to read it, Angie?
Angie Rudduck: Oh sure. So Ronny says, “I have multiple tempdb files and only one file is growing, actually the second file that was created. All other files are still reflecting the initial memory size.” He has trace flags 1118 and 1117 enabled—oh, no, just 1118, and wants to know that he also needs to add 1117. Does he need to delete the other files and start over before adding the additional trace flag?
Erik Darling: No. What you need to do is make sure that you grow all the other files to the same size as the second file. Elsewise, all of your queries will still be attracted to that larger data file and it will keep growing. So if you can, grow out your other files to match the size of the second file. If not, you’re going to have to deal with the kind of painful and possible corrupting experience of shrinking a tempdb data file.
Tara Kizer: They actually are saying it hasn’t been a problem—or, Paul Randal said corruption on shrinks has not been a problem in several years. They fixed it with 2012 I believe he said, or maybe even earlier. They just never made it public.
Erik Darling: Oh, okay, well, that’s good. I hope you’re using 2012 or later then, pal.
Tara Kizer: It might have been earlier. I can’t remember if it was an email or what it was.
Angie Rudduck: Yeah, if only they publicized when corruption is not happening anymore.
Erik Darling: They should just tell people that shrinking databases will cause corruptions so everyone will shut the heck up about it.
Doug Lane: So you also want to make sure that your file growths are set to zero, otherwise your tempdb files will keep growing. You don’t necessarily want that. It’s better to set it at the right size and then just kind of let it go with that because that may be why you ran into trouble in the first place is that you’ve got file growth on that one file and not on the others.
Tara Kizer: I actually quote/unquote disagree with that.
Doug Lane: Okay.
Tara Kizer: It’s not that that’s bad, it’s just it has to do with monitoring. So on the Brent Ozar website it says to grow your files all the way out to whatever that drive is for tempdb. If you do that, then your monitoring software is going to be alerting, the on-call DBA is about to get a disk space issue. Now of course, you could have an exception for the tempdb drives but I try to avoid exceptions. So instead, grow your files out to what they need to be and then keep autogrowth on. Have the file sizes be identical and you start out with the same exact autogrowth and they should grow proportionally based upon free space. Should not have an issue. If you do have an issue, I wonder what’s going on. You might have some kind of weird thing going on. I like to keep 20 percent free space to avoid alerts. Then when you do get an alert, grow out the mount point or drive.
Angie Rudduck: That’s for good IT teams that have alerts.
Tara Kizer: Yes.
Angie Rudduck: If you don’t have alerts, Doug has a good point. If you never allow them to grow, you never have your disk go over space, but your tempdb could go offline.
Tara Kizer: That’s the problem with that solution is that if you have a query, this wild query that runs, it’s going to fail if it needs more space in tempdb. Whereas if you had free space, well, if you have alerts in place, DBAs are going to start receiving alerts that we’re running out of space and you might be able to add the space before the query fails.
How can I move all my nonclustered indexes to another filegroup?
Jessica Connors: Speaking of running out of disk space, Gordon asks, “I’m running out of disk space for a one terabyte database with a single data file. So the plan is to move clustered indexes to a new file and a new filegroup. Given that everything is currently on the primary filegroup, would it be a good idea to move all the non-clustered indexes as well?”
Erik Darling: I would set up two different filegroups actually because if you’re doing this sort of work and you’re going to get some benefit out of it and you want to be able to restore the database as files or filegroups separately, you’re not going to want everything in the primary file group because you’re going to need that primary thing online first in order for the database to be accessible and restore other files and filegroups. So I would actually create one for clustered and one for non-clustered and move them off into one into each so that I can keep the primary part small and just have like the system databases and stuff in there. Be careful. Depending on what version you’re on because not all of the rebuilds are able to be online if you have max column types prior to 2012. So if you’re planning on a seamless move, mind your datatypes.
Are there any gotchas with SQL 2014 SP2?
Jessica Connors: Did SQL Server 2014 SP2 just come out? I thought they weren’t doing that anymore. You guys just told me they weren’t doing that anymore.
Tara Kizer: It will be rare.
Erik Darling: Starting in 2016.
Jessica Connors: You’re liars. Gotcha. “Any issues with this SP that y’all are aware of?”
Tara Kizer: Not yet.
Erik Darling: No, it’s a lot of fun.
Tara Kizer: It’s too new.
Angie Rudduck: It is too new.
Erik Darling: A lot of the stuff in Service Pack 2 was oddly catching SQL Server 2014 up to Service Pack 3 of 2012. It did add a couple of other things but most of it was just playing catch-up there with a lot of like the stuff that you can see execution plans and finding out about memory grants.
Tara Kizer: Oh, the memory grant issue. Memory grant columns are in Service Pack 2?
Erik Darling: They are and I’m going to be updating stuff.
Tara Kizer: Yeah, I’ve got a client that’s been waiting on those columns so I’ll let them know. Just real quick on the Service Pack 2 thing. When I said it’s too new to know about issues, just remember that service packs are fixing hundreds or thousands of bugs. So by not installing it, you’re more at risk of not installing it than you are of installing it. So service packs, hotfixes aren’t necessarily a bad thing and you don’t necessarily have to wait a while to see how other people are doing.
Erik Darling: If you’re worried about it, install it on a dev server. Let it hang out for a month and do your thing on it and then push it to prod. If you don’t have a dev server, then quit your job.
Are there any downsides to filtered indexes?
Jessica Connors: Question from Joe. He says, “Are there any particular downsides to using filtered indexes? I’ve recently been performance tuning some queries that have non-parameterized search arguments and they’re like magic.”
Tara Kizer: They are like magic. The only issue is if you have a query that has the opposite of what your filtering condition is, then it’s going to have to do a scan. But you know, you want your filtered indexes to be what your queries are usually going to do. Like is deleted equals zero for instance. If you have an is equals one, probably that’s going to be a maintenance job that runs at night to clean out data. It’s okay for those things to do a scan.
Doug Lane: The higher the cardinality on whatever it is you’re filtering on, the more effective that index will be. So if you’ve got data that’s split 50/50, it will be helpful. But if you’ve got data where you’re going to be looking for something where the filter condition there is down to say ten or fewer percent of the rows, then you’re in really good shape. Incidentally, I just put out a statistics course that does include filtered stats, which you get with filtered indexes.
Tara Kizer: All right.
Doug Lane: So you can check that out too.
Jessica Connors: I think about three months ago we just included access to our entire SQL training library for anyone that does the SQL Critical Care. If you want any of these guys to take a look and assess the health of a database, let me know. Reach out. For some reason, July and August are slower for us. I think it’s because summer.
Tara Kizer: Everyone is vacationing.
Angie Rudduck: Too much beer and barbecue.
Jessica Connors: Yeah, vacations. So if you are on this webinar and you reach out to me, Jessica@BrentOzar.com, we are supplying some special deals within the next two months for our SQL Critical Care product. So not only do you get the Critical Care but also the additional training, access to our SQL training library.
Angie Rudduck: Isn’t Brent come out with a new one? Not us three… but the other Brent?
Tara Kizer: Not these fabulous Brents.
[Laughter]Angie Rudduck: Isn’t he coming out with a new video, Doug? I just expect you to know.
Doug Lane: I think so. I know I’ve got another one coming.
Angie Rudduck: Oh, see. There we go.
Tara Kizer: He painted his wall green so he must be working on something soon.
Doug Lane: Oh.
Tara Kizer: Not you—Brent.
Doug Lane: Oh, Brent, yeah. I’m thinking, “Is my tint that bad?” More stuff on like execution plans and querying.
Angie Rudduck: Nice.
Jessica Connors: Cool beans. All right, guys, that is all the time we have for today. I’ll let you go. Adios.
Erik Darling: Later.
