This week, Brent, Erik, and Richie discuss the new Extended Events Profiler, using SQL audit vs a trigger to log what people do, log shipping, migrating from on-prem to cloud, best practice for a database owner, SQL Server performance improvement, the difference between simple, bulk-logged, and full recovery models and when to use them, tools for transferring databases from one server to another, VSS Snapshot backups, encryption and more.
No headlines this week – sorry about that! We’re hustlin’ to build you some new cool stuff for our Summit pre-con. We think you’re gonna like this a lot!
Enjoy the Podcast?
Office Hours – 10-11-17
Brent Ozar: Grahame says, “Does management studio 17.3’s new extended events profiler – does it cause the same performance issues as the old profiler?”
Erik Darling: I just installed it this morning and I haven’t tried it yet, so I don’t know.
Brent Ozar: Yeah, looks – and it’s based off of extended events now, so it should have a lower overhead…
Erik Darling: Should.
Brent Ozar: Just know that when you use extended events, there are fields that you can grab, just like you could with profiler, that will have a huge overhead. The more of those that you pick – things like execution plans – the more overhead you can have.
Erik Darling: Or even SQL text is another one that can cause a bunch of overhead; from just sort of messing around with it too. So for some things, there’s the integer field and then the text equivalent of the field, like say for database ID versus database name. And there’s even probably less overhead for just filtering on the database ID than filtering on the database name, because database name isn’t part of every single action or event, so you have to like add it sometimes and then filter on it, so it gets weird. I’m actually poking at it now… what are you doing?
Brent Ozar: And I’d heard, somebody on Twitter said, if you just close SSMS like you close the profiler piece of it, that the trace keeps running and it was rowing a file on his drive. So I don’t know if that makes a difference on how you stop the trace or what’s up with that.
Erik Darling: When you right click on it, you can stop or launch the session. So if you just close the window, I imagine the session keeps running until you right click and hit stop. Just like with a regular extended event, you have to stop it for it to stop.
Brent Ozar: Makes sense… [crosstalk]
Brent Ozar: Let’s see, Emily says – which is my sister’s name – “Apologies I’m late…” That’s alright, we were late getting to your question too. “What are the pros and cons of using a SQL audit, so the built-in audit functionality, versus a trigger, in order to log what people do?” Richie, have you ever had to build anything that logs everything that people do in a database?
Richie Rump: No, because that’s usually really slow, so I’ve…
Brent Ozar: But isn’t everything you build really slow though?
Richie Rump: Yes, that’s only because you touch it and magic happens…
Brent Ozar: What happens if I do this right here, Richie?
Richie Rump: Don’t touch that.
Erik Darling: It’s always the end user that’s the problem. Everything Richie builds is fine until someone has to use it, and then…
Richie Rump: That’s correct. [crosstalk]
Brent Ozar: I can testify to that. It’s mostly operator error. Erik, have you ever had to do this?
Erik Darling: Build it, no. Support it, yes. And, you know, relativity, the audit table, they were just massive triggers that every time something happened, everything would go and get logged, and there were some really bad column choices in there. There’s like an XML column, and then like an nverchar max version of the XML column. There’s some really goofy stuff in there. So I’ve had to support it and like deal with queries that hit it that were just unfortunate, but as far as actually design it, no, thankfully.
Brent Ozar: Yeah, the thing with SQL Server’s built-in auditing is that it’s kind of like a construction kit. Really, just like a trigger, you can go build things with a construction kit, but it’s up to you to build things like reporting. What I usually end up saying is, is it just because we want to do it, or is this something that legally we’re required to do? Like the auditors are standing out there with a gun to our heads going, “You better capture everything that anyone sees.” In that case, the third party appliances are a much better answer.
Brent Ozar: Let’s see, Wes says, “Let’s isolate that quote out of context, Richie said to Brent, only because you touch it; magic happens when you touch it.” That should go down in fame. Two years ago, three years ago, I was teaching a PASS Summit pre-con, and we started at the beginning of the day and I’m like, “Look, just so you know, I understand if you’re sitting in those hard seats all day, you know, and you’re sitting there for six hours your butt gets hard.” You know, something about hard things in your butt and after six hours. And as the words are coming out of my math, everyone is turning to me and it’s like, I’m just going to stop talking now.
Brent Ozar: Wes says, “I remember but probably not, I was at a place where…” Oh, he said at his last job he was at a place where he had to learn ProgressDB and he was like, nope, I’m out. That’s funny, because we had a question on Twitter from somebody this week who was asking me and Richie how much work is it to learn a NoSQL database.
Richie Rump: Hold on to your butts, big guy.
Brent Ozar: I can kind of feel you, because I was a SQL Server DBA for the longest time and I had the opportunity to learn Oracle, if I wanted to learn it; like there were a couple of Oracle DBAs in the same shop. And I liked them, they were really cool guys, but I was like, “Nah, you know what, I’m really kind of good right here. I kind of know what I know here and I’m kind of fine.” It’s not that I don’t like to love – like to learn, I just didn’t want to learn Oracle at that time.
Richie Rump: He also doesn’t like to love.
Brent Ozar: I don’t like to love. I love to hate.
Erik Darling: Learning and loving, off the map.
Brent Ozar: And I’m not learning to love, and I’m not loving to learn. That’s totally not true; I adore learning and I’m going to be all over Postgres here shortly. Not because I think it’s a better database than SQL Server, we’re just using it for some of the projects that we have in-house.
Richie Rump: But he’s …
Erik Darling: do you have any hobbyhorse prod things that you’re learning, or are you all just work focused on learning?
Brent Ozar: Oh, so I love learning about what dotcoms are doing, just architecturally what other platforms are out there? Where are people storing their data? Why do they make those choices? What are the pros and cons of those architectures? Like I could learn about a new database every day for a year, and I would just love doing that. Managing it from day to day and being on call for it? No, I’m not really interested in that. But learning about them, I’m like yeah this is really cool. The compromise is that they make – so you’re starting to learn C, right? Or you were dabbling in it? Yeah, C++…
Erik Darling: You can see from the bookmark that I even made a little bit of progress.
Brent Ozar: That’s awesome. Not progress in the database but just progress. See that actually calls to me, only because I would love to learn to read MySQL, Postgres, like the source code behind those databases. That, to me, is a good time.
Erik Darling: Nope, it’s all C in and C out; that’s all it is. [crosstalk]
Richie Rump: The only C I want to see is this C right here, that’s it. No language of C…
Brent Ozar: But you want to see a W next to that.
Richie Rump: The W is right here.
Brent Ozar: Joseph says, “101010,” in the questions.
Brent Ozar: Thomas says, “If I log ship to another SQL Server to test before migrating, can I use those databases on the secondary to set up an availability group before dropping the log shipping; like ship from one place to one place to the other?” The thing with log shipping is, whenever you start restoring on the other end, if you want to do something with those databases, like test them, you’re generally going to break out of log shipping, because you’re going to want to write to those. You’re going to want to do inserts, updates, and deletes. So generally, log shipping isn’t as useful for migration testing. It’s great for migration; just not as great for the testing part.
Erik Darling: You can put them in standby and just let some queries fly at them, but they won’t be writable.
Brent Ozar: Khalid says, “I have one more question, please…” Absolutely, just in the side of your monitor, you’re going to see a slot for $20 bills, just slide it right inside – oh, your monitor may not have that. Well if your monitor doesn’t have that, you can ask questions for free; just keep asking them over in the questions tab.
Brent Ozar: J.H. says, “Our app team is looking to migrate their app servers up into the Amazon web services cloud, and they want to continue…” They want their apps up in Amazon and they want their SQL Server on premises. “Is there anything that I should do on the database administrator side?” Run for your life. So what would be the problems you guys would expect to see with the apps in the cloud and the SQL Server on premises?
Erik Darling: Richie’s favorite cloud word, permissions, and security, would be the first – those would be the first two things that come to mind.
Richie Rump: Yeah, connections. So the app may not be ready to handle drop connections. So talking to the cloud, if you don’t have automatic retries in your code, you’re going to need that, especially when you talking to a database, and maybe wrapping transactions around things, because the connection could drop at any time. –
Brent Ozar: Latency is going to suck pretty bad too. If you have an app that does one-row insert and then waits to get it back, another row insert, waits to get it back, that is a recipe for slow latency.
Richie rump: Yeah, and where’s the app living if the app is leaving in your own kind of collocation facility, now that has got to jump, go through the internet, go through all the hoops to get into the cloud, and then the own network in the cloud, and then it’s got to go all the way back. So that’s why you have a lot of cloud apps there in the cloud. They don’t have this separation between my network and your network and oh we’re going to have it in the cloud, because that whole latency thing is…
Brent Ozar: I’d be curious to hear more about why you’re putting the app up there and keeping the SQL Server database down on premises too, just out of curiosity.
Erik Darling: That might even be temporary. Get the app up there and then move the database up during another window?
Brent Ozar: In which case, stuff like log shipping and availability groups may make it easier for you to get that up their faster. I would move [crosstalk]…
Erik Darling: That’s not replication.
Brent Ozar: Khalid says, “What’s the best practice on who should be the owner of the database?” He says, “It defaults to the person who created it, but what’s like a best practice for the database owner?”
Erik Darling: Usually SA. I’ve never seen anything terrible happen to a database from a user owning it. They will have, you know, elevated permissions or privileges on it, but it’s not like with an agent job or something, where if the Windows account gets disabled then the database will shut down.
Brent Ozar: We alert you for it in sp_Blitz too, like that a database is owned by an end user. And we’ve got more details on that link. If any of you haven’t run it before, sp_Blitz is our totally free health check utility that goes and gives you a quick report, like an Excel spreadsheet type analysis of the problems with your SQL Server.
Brent Ozar: DB… Oh, DBA – funny how they spelled their name there – says, “In my first DBA job I inherited servers set up by a non-DBA.” I think that’s kind of how it always happens too. “We have a mess of over permissions and nonspecific accounts. Can you talk a little bit about the accounts that are used for proxy accounts versus credential accounts? How many proxies do normal shops use? Is the command shell proxy credential normally ever needed?” No…
Erik Darling: You should have read that one first.
Brent Ozar: No… We don’t deal much with security, but I don’t touch proxy accounts at all; like I’ve never used a proxy account. What I would do is – so here’s the problem. If you want to tighten down security permissions, if you want to start taking things away from people, you need to expect that apps are going to break. Things that are currently working today are going to start breaking. So what I would do, instead of tightening down on servers that already exist, is go build yourself – this is going to sound like a consultant answer – go build yourself the next SQL Server. Go build the SQL 2016 or 2017 that you want to move towards, with only the permissions needed inside there. And as people start to migrate their applications and test on the new box, it can be really tightened down. And when people say, I want SA permissions on this or I want to use a different proxy account or I need passwords to some proxy account, you can start asking why, and that’s where the documentation comes from. Until then, you just say no, you can’t have that. That’s why they joke that DBA stands for don’t bother asking.
Brent Ozar: Aaron says, “We’re upgrading our SQL Server from 2008 R2 to 2017…” Leaps and bounds there. “We currently run our ERP app on the same box as SQL Server. If we were to separate those two boxes, can we expect to see a noticeable performance improvement? We typically have 20 concurrent users in the system?”
Erik Darling: That’s a very broad question.
Brent Ozar: If someone says, I want to find a performance improvement in the server, where do you start?
Erik Darling: Memory, always memory. Memory is like my first thing.
Brent Ozar: What did you say, I forgot?
Richie Rump: Jeez.
Erik Darling: Even Richie cringed on that one, man, that’s rough. That’s a bad reception if Richie…
Brent Ozar: If you had a limited amount of RAM in the VM or whatever before and it was all on this same box and now you’re splitting it out, maybe now the app gets more memory. But maybe you’re changing the box too, maybe you’re changing how much horsepower is in the box. I would look at what are your primary wait stats today, like what wait types are your biggest ones? And it just so happens, watch our blog for this week, we have a post on how to do a performance check.
Erik Darling: Another thing that I see sometimes when people say that they have an app- running alongside it is it’s not just an app running. People are like terminaling in to use the app on the server, so there’s like the overhead or RDP or however else they get in on there to do it. Sometimes the app is like your browser interface, so when you have like 15 people in with IE open just clanking away on stuff, you’re like, stop.
Richie Rump: Yeah, I’d like to ask a question, and the question is, what’s your performance problem on the machine right now? So if you know what your performance issues are, then you could probably say having two different boxes will improve performance.
Erik Darling: Or you can say we’re going to move this to such a gigantic box that no one will trample on each other and everything will be fine.
Richie Rump: Or maybe you don’t have any performance issues and it’s already super fats and moving over to another box with a lot of hardware is not going to do anything?
Erik Darling: A lot of ifs in there.
Brent Ozar: There’s also licensing too. Since SQL Server is licensed by the CPU core, sometimes you can get bang for the buck just by peeling out to lots more cores, taking the apps and putting it somewhere else, and then SQL Server gets its own cores. That may not even affect performance altogether. You might be able to shrink the amount of CPU cores that you have licensed for SQL Server. Aaron follows up with, “The app is the normal bottleneck, and it tends to be CPU limited.” Perfect, in that case, set yourself up other VMs for the app server, give them as much CPU power as you want because you don’t have to pay SQL Server licensing for those cores. That will be good.
Erik Darling: When I was dealing with relativity stuff, it’s very carefully spelled out that you have your SQL Servers over here doing stuff, you have your app servers over here, you have worker servers over here that do things; everything is very separated out. Like some VMs will pull double duty, like with what they do, but they have just one application on them doing it, you know, whether if it’s DTI indexing or doing whatever agent-y stuff has to be done in the background to make sure files are in the right places. But really, it’s a good way to look at architecture for an app, because it gives you some precedence to say no, we need to separate these things out into different places.
Brent Ozar: Perfect.
Brent Ozar: Chris asks, “What’s the difference between simple recovery model, bulk-logged and full recovery model, and when would I use each one?”
Erik Darling: Wow, you just want to like start up here and travel down…
Brent Ozar: In the beginning, there was a database…
Erik Darling: So, the thing that’s not the difference, aside from some circumstances, is what gets logged. A lot of people think that is they use simple recovery, it logs less. Or if I use bulk recover, it logs less. Really, only when you get minimal logging on insert is that true, and for certain other things like index rebuilds can that be true. But for every other regular transaction, so like every other update, delete, all that other stuff, inserts that aren’t minimally logged, the same amount of stuff gets logged. What changes is who’s responsible for transaction log backups and also what HADR text can run on different recovery models. So in simple, its SQL Server will truncate the log and do stuff that it needs to; in bulk and full that’s on you. And bulk you can log ship, full you can log ship, full you can do mirroring, AGs, failover clusters, it doesn’t matter a lick. I don’t know, what are some other good ones?
Brent Ozar: Usually it all comes down to how much data you’re willing to lose; that’s the big thing I start with.
Erik Darling: When would you use them? RPO and RTO – that’s business though.
Brent Ozar: Yeah, long-running transactions would be the other thing that scares me. If some yo-yo does a BEGINTRAN and locks his workstation, goes home for the week after inserting one record… Hello, full hard drive.
Richie Rump: Why are you looking at me when you say that, Brent?
Brent Ozar: My own webcams are right there, so I’m also looking at myself…
Richie Rump: Oh sure, uh-huh.
Brent Ozar: Carlos asks, “Any tools or suggestions to transfer a bunch of databases from one SQL Server to another? No migration, just transfer them.” Oh, just like copying – sp_DatabaseRestore. So if you download our first responder kit, Tara wrote this cool stored procedure that just restores all of the database backups in a folder. It just pulls the most recent full, the most recent diff and all the logs since. So in our first responder kit, look for sp_DatabaseRestore, it works beautifully with Ola Hallengren’s maintenance scripts as well.
Brent Ozar: Grahame says, “Do you always recommend running…” I don’t always recommend, but when I do, it usually involves Dos Equis. “Do you recommend always running update statistics after someone drops and creates a temp table? I’ve noticed performance improvements, but should I always do this?
Erik Darling: Well, if you want to know more – I don’t think we’ll go into the full thing here, but a fellow named Paul White, who if you don’t know the name by now, you should jump off a cliff, has a couple of cool articles. One is called, Temp Tables and Stored Procedures, or Temporary Tables and Stored Procedures – Paul is a gentleman, so he spells the whole thing out. He’s not a lazybones like me. And then there’s another one called Temporary Table Caching Explained, and those two can give you a thorough and rigorous mound of information about how temp tables and stored procedures, and often real life, work too. You should ignore my bonehead posts about ghosts of temp tables where I create them and then stored procedures fail because of them.
Richie Rump: Before you read it, just get a cup of coffee. Now make sure you have a quiet place for a couple of hours…
Erik Darling: Yeah, get a cup of coffee, get a divorce, get a cabin in the woods, print the stuff out, go up there, you know.
Brent Ozar: It’s true.
Brent Ozar: Khalid says, “I’m migrating some old databases from 2008 to 2016. In my databases in 2008 there’s some text in text and image fields. I heard these aren’t supported in newer versions. Can I just set the compatibility mode back?” I think text in text and image are still in 2017, aren’t they?
Erik Darling: They are deprecated, but they are still in there. So you don’t even have to set compatibility back, you just have to deal with crappy old data typed.
Brent Ozar: Let’s see, next up Michael says, “I worked with an ERP app where the vendor recommended installing their app on the SQL Server. I refuse to do it.” [crosstalk] I hate it when vendors do that. No one’s around to give you the little gold star for being a good database administrator.
Brent Ozar: Ben says, “Do you have an opinion…” Odds are yes; probably not valid but we have an opinion.”Do you have an opinion on VSS snapshot backups being used for SQL database daily backups? We have app owners and management only wanting to do daily backups.” As long as they’re okay with losing a day’s worth of data, they’re actually magical; they’re fantastic. Just make sure that you’re getting a product that actually integrates with SQL Server, you should see it as a real backup in your backup history. If you don’t then it’s doing a dirty crash consistent snapshot, and your database backups may come back corrupt.
Erik Darling: So the way Brent said, as long as everyone’s cool with the fact that you can lose a day’s worth of data, going back to when that VSS snap is, then proceed. One thing you’re going to want to keep a really close eye on though is your error log. Because when legitimate snaps get taken, you’re going to see two messages happen for each database, one where I/O is frozen – because it quiesces drive activity serially each data file at a time. And as it quiesces them, it starts taking the snapshot, and then when it’s done, it thaws. So you need to be really careful that there’s not a big chunk of time between the freeze and the thaw, because that means that your drive is essentially frozen in time for the entire duration of that. and there have been so many times, you know, me, Tara, even Brent has worked with clients and seen this in the error log where data gets frozen for 30 seconds a minute every time these VSS snaps get takes, either because it takes so long to quiesce dive activity or because it takes so long to copy the snapshot somewhere. It can have a real impact on performance. For you, taking it once a day, there’s a pretty good chance that even if it does that, it will be during some maintenance window; it will be at like midnight when nothing else is going on. But we’ve seen people who are taking like 15 minutes to have some of this stuff for like five, ten, 20 seconds. You’re like, well you just added like 20 seconds onto every query, no wonder it’s slow.
Brent Ozar: Looks like a big long blocking chain when you’re looking in sp_whoisactive.
Richie Rump: So this is how long I’ve been around and how scarred I’ve become. Every time I hear VSS is visual workload store safe, and I’m like no, don’t put it in there, it won’t come out; it’s corrupted.
Brent Ozar: That was my first experience with source control, both as a developer and then as a DBA. I built these VB script things to continuously, like every hour, patrol my databases and check them into visual store safe. And I was so proud of myself until I started losing data in corruption, and then I was not quite as proud of myself.
Richie Rump: Yeah, that wasn’t my first introduction, but when – it was like, we’ve got to backup like all the damn time, because it’s going to get corrupted, that’s what it is.
Brent Ozar: I love how one person, let’s see here, the question is from Grahame. He says, “Looking at premium assurance SQL 2008 R2, I see all the licenses need to have PA purchased.” And as he’s asking the question, as I’m reading the question from him in GoToWebinar, he goes on to Stack Exchange and asks the exact same question. So Grahame, now I know what you’re account is, Grahame, you’re apparently ThunderCougarFalcon. That’s an awesome, ThunderCougarFalcon, how cool is that? That’s fantastic.
Erik Darling: It’s like one of those password generators where it’s like three random words and then some numbers.
Brent Ozar: Correctcoursebatteriestable.
Erik Darling: I bet that’s his password too, we should all try it.
Brent Ozar: Next question in here, let’s see, Joe asks, “Are you aware of a way to create something like a virtual SQL Server for developers? My developers want to develop against SQL Server, but I don’t want to give them a whole full blown SQL Server. What should I use? Like a preconfigured VM?”
Erik Darling: Oh, that’s a good question.
Brent Ozar: Richie’s looking like he swallowed a cat.
Richie Rump: I just install the developer edition in my machine. That’s like – when I get a machine, the first thing I do is install developer edition. But I’m probably a little bit different than most of your developers because I hang in the middle of development and database land. So I’m like this weird oddball guy. There are ways that you can actually get a VM of that and if somebody else talks for a minute then I’ll look it up.
Brent Ozar: I would say also, this is what – so developer edition, first off, is free. There are no strings around it, totally free, you can install it on their machine. If you don’t want to patch it, this is one of the cool things that Microsoft came up with in 2017, Docker support. So you could spin up a Docker VM as often as you wanted. I actually think it’s easier for most Windows shops to just install SQL Server developer edition on the developer’s machine. But if you’re a Linux friendly shop or a Mac-friendly shop, that’s where spinning up a Docker VM can make a difference. [crosstalk]
Richie Rump: You could even go to the Docker store or package manager or whatever and download MS SQL and it will just be there. So Docker was the thing that I was thinking of, but I haven’t jumped into the Docker pool yet, I’m still old school; I run 46s with VX2. [crosstalk]
Erik Darling: If you’re on any sort of modern-ish version of SQL too, it actually just came to 2012 SP4, and you don’t want to have a full-blown database on every developer machine, you can use DBCC CLONEDATABASE to have a statistics only copy of your database available for people to do development against. Just don’t rebuild indexes or update stats, because everything will go… But, as long as they don’t do anything goofy like that, the DBCC CLONEDATABASE is a pretty cool command. It’s just a stats only copy of your database. You can do that on 2012 SP4, 2014 SP1, that was where it dropped, 2016 and 2017. So all the good places that you would hopefully be doing your development against, that’s available.
Brent Ozar: And kind of following up, related, Thomas says, “What’s the best way to give devs a comparable database to prod without giving them prod data?” This is a really easy way to do it, just keep in mind it has no data. And if you want any config tables, for example, you may have to script those and include those as well.
Richie Rump: That’s like one of the hardest problems we have as developers, to create a good test data set so that we could actually do things that prod is eventually going to do. And we don’t want prod data, there’s no way that I think any sane developer would ever want prod data. And if they do want prod data, it’s because they want to make sure that it’s running at the same size, breadth, and speed that prod would get into it. So that’s why getting a stats only copy would be good, but if I’m actually writing T-SQL and testing it and doing all that, it doesn’t help me much because there’s no data here.
Brent Ozar: One quick one, Chris says, “I get the following error when running sp_Blitz on a 2008 R2 box. I have an arithmetic overflow error.” Make sure, first off, that you’re on the latest version of sp_Blitz because it’s possible that that’s an older one. We’ve fixed several of those bugs. It may also be that the thing has been up for, say, two or three years and wait stats have gotten really high. I thought we’d fixed that bug, but just in case, I would go look in the wait stats question. Or just try – this is going to be awful to say – I bet Erik has a better answer than me.
Erik Darling: I know I do.
Brent Ozar: Go ahead.
Erik Darling: So just – if you want to try running it with debug equals one, you can figure out which step that happens on. Then, if you want – you can figure out if it’s the query that’s doing it or if we’re doing something wrong. And if we’re doing something wrong, you can tell us about it on GitHub. Just make sure you have whatever check ID is running when the arithmetic overflow happens, and we can do a better job investigating.
Richie Rump: What if you put debug equals 42?
Erik Darling: I think it’s a bit, so you’ll probably get an arithmetic overflow error there. Actually no, it’s a tiny int, because you can do debug equals two if you want to print out the dynamic SQL that runs as well as the check IDs.
Brent Ozar: Then J.H. asks, “When we’re trying to encrypt all out network connections, what should I look out for?” Go his this post by Aaron Bertrand, SQL Server Support for TLS 1.2. There are about another dozen questions that we didn’t get a chance to get to. Don’t hang around waiting for us for another week, head on over to DBA.StackExchange.com, and you can post questions about SQL Server. We answer all the time. It happens to be a different VM than I work in. I work in this one but if I had my real one up, it would show my reputation and all that kind of thing. But Erik and I sit around all day answering questions on Stack, not in here webcast.
Richie Rump: That’s right, they do.
Brent Ozar: So thanks everybody for hanging out with us this week, and we will see you all next week on Office Hours. Adios.