Blog

This week, Brent, Angie, and Tara talk through your questions about monitoring tools, transactional replication, configuration management, source control software suggestions and much 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.

Office Hours Webcast – 2016-06-15

Why did some of my drives disappear?

Angie Rudduck: Bruno says he has an instance with several DBs and suddenly a couple of them became unavailable and the physical disks where the data log files disappeared. No Windows events. How can he audit what happened at SQL Server level?

Tara Kizer: It’s unlikely a SQL Server problem. I’ve encountered this many, many, many times. You’ve got to talk to your sysadmins or you’ve got to talk to your SAN admins, server admins, they’ve got to take a look. Something happened. It’s almost certainly not a SQL Server issue.

Angie Rudduck: Yeah, if your drives disappeared, it’s probably not SQL Server’s fault.

Brent Ozar: When the drives disappear, I don’t know that you would see a Windows event unless there’s some kind of error message that pops up from the SAN or whatever. I’m assuming it’s a SAN.

Tara Kizer: You would eventually see a SQL Server error once it finally has to write to disk. I mean, it’s going to be a little bit before that happens since SQL Server does everything in memory. So it’s not going to know about it for a while. But the checkpoint, any kind of writing to disk. It’s finally going to start throwing errors and those should be posted in the event log.

Brent Ozar: Backups.

Tara Kizer: Yeah. We’ve encountered weird bugs on like Cisco hardware that caused it and just various weird things. But it has happened numerous times, across many servers, many different hardware platforms, different SANs. It just happens.

Brent Ozar: I think it’s usually it’s just human error. I mean like Robert Davis, a fellow Microsoft Certified Master, just ran a blog post on how he’s like, “Even I screw up.” Drops the wrong one and all these database transaction logs disappear.
Angie Rudduck: Oh yeah, I’ve dropped two databases from prod before. Two separate occasions I have dropped a database from prod. Thankfully both were quick enough recovery. The second one turned out not really used, so that was okay.

Brent Ozar: It’s a matter of time. That’s how you become senior too, you have to have those experiences.

Angie Rudduck: I was just going to say I feel like something that, I’ve met people having only been a DBA for three years, I run into people who have been DBAs for ten years and I know things they don’t only because it’s things I’ve experienced that they never did because maybe they were a smaller shop and I worked in bigger places. Just all about what experience you had.

Brent Ozar: Yeah, everything that involves replication. Tara knows everything.

Angie Rudduck: Somebody already, “Since Tara is here, blah blah replication” question.

Brent Ozar: Here we go.

 

What’s the best SQL Server monitoring tool to use?

Angie Rudduck: Constantino—I butchered your name, I’m sorry—he has a long-winded, easy question. Basically they’re trying to look for a good monitoring tool for production servers. They’re looking specifically for health monitoring that can alert them when something happens or is going to happen. So don’t get Ignite, it’s not in your list, but don’t get Ignite. He’s looking for a full-rounded solution. They’ve tested a bunch: Spotlight, Foglight, Redgate, SQL Sentry, Idera. Do we have any favorites that we would point them to for health monitoring and for SQL alerting?

Tara Kizer: SQL Sentry provides both with the performance advisor and then the event manager tools I believe. Both of those together can give you everything you need. At previous jobs, we used SQL Sentry at my last job and previous jobs we used Spotlight. I wasn’t a big fan of Spotlight. It was great for looking at certain things. I did set up some availability group alerts but it wasn’t as comprehensive as I wanted. We also had Foglight which I think is now called Performance Analysis. Then we had SCOM, so Microsoft’s System Center Operations Manager with the SQL Server management pack. But SQL Sentry, their two big tools did replace SCOM and the performance analysis tool for us at the time. But it’s pretty expensive. SCOM plus another tool is not as expensive. But SCOM requires almost a fulltime monitoring person that knows how to use it. It’s very complicated.

Angie Rudduck: Yeah.

Brent Ozar: I’ve used all of those too. I’m fine with all of them. It comes down to personal preference.

Tara Kizer: Yeah.

Angie Rudduck: Did he mention Dell’s? That’s Spotlight, right? Dell is Spotlight.

Tara Kizer: Yeah, Spotlight and Foglight. Foglight is the name that we used to call them. I think it’s Performance Analysis, I think. People may still refer to it as Foglight.

Brent Ozar: They renamed it again.

Tara Kizer: Oh they did? They went back to Spotlight?

Brent Ozar: Yes.

Tara Kizer: Oh, I didn’t know that. They were probably sick of people calling it Foglight and they’re like well we should just call it that too.

Brent Ozar: A friend of mine calls them FogSpot. He’s like, “I don’t know which one it is. I’ll just call it FogSpot.”

Tara Kizer: Yeah, one of them.

 

What should I do about the replication error “undelivered commands”?

Angie Rudduck: All right. So we will move along. Let’s see—not that one—we will go to Nate, with the transactional replication. They have a setup where often they get stalled transactions from the “alert for undelivered commands job.” Any thoughts?

Tara Kizer: Stalled transactions. I’d probably need to see the full error. So undelivered, so it probably means that it’s sitting at the distributor and it hasn’t been sent to the subscriber. I would take a look at the throughput. Take a look at the distributor and the subscriber to see if there’s any kind of CPU issue, possibly it’s just a lot of data got pushed through. Yeah, I don’t know for undelivered commands. Usually it’s a bottleneck on the publisher with reading the transaction log. Maybe there’s just a lot of stuff in there, you’re not backing it up often enough so the amount of data that has to go through is bigger. Mirroring, availability groups, and—well those can add to replication latency because everything gets stored in the transaction log.
Angie Rudduck: All right. So I realized I missed this very small question from Greg, so I will give him some attention. He said he saw some tweets recently that stated you should have four cores per NUMA node. What do we think about that?

Brent Ozar: Somebody was pulling your leg. It’s not configurable. It just comes down to for Intel processors it’s the number of cores per processor. If you turn on hyperthreading, it’s going to suddenly magically double. There are differences under virtualization, unfortunately, it’s such a huge topic you can’t possibly say, “You should always have four cores.” It depends a lot on the host hardware that you’re using and whether or not that hardware is identical across all of the hosts in your cluster. But yeah, anybody who just says four, they’re over simplifying something. Or it might have been for just one particular guy’s setup, like if one guy had just one host design.

Angie Rudduck: Yay for answers where people are pulling your leg.

 

What’s the best way to create a SQL Server inventory?

Angie Rudduck: Okay. Samuel wants to know, “What is the best way to create a SQL Server CMDB/inventory without buying third party software?”

Tara Kizer: I don’t know what that is.

Brent Ozar: Configuration management. Idera just had a new tool. If you go to Idera.com and click on free tools, I want to say it’s Instance Check, it’s got something along the names of inventory in it or it’s not discovery, but it’s something along the lines of inventory. So go to Idera and click on free tools. The other thing to search for is Dell Discovery Wizard. Dell Discovery Wizard will go through and survey your network and discover SQL Servers and identify them for you. Put them into a database. Another tool that you can use is SQL Power Doc. SQL Power Doc is an open source PowerShell script from Kendal Van Dyke. If I had to pick one that I like, I used Dell Discovery Wizard a bunch of times. Idera’s looks pretty easy as well. SQL Power Doc, not very easy, but super powerful.

Angie Rudduck: Very good.

 

Should I use multiple Availability Groups or just one?

Angie Rudduck: Eli has a question about availability groups since Bret Ozar II isn’t here. They’re planning on upgrading from 2008 R2 to 2014 to take advantage of availability groups. They would like to know if there is a performance advantage to having databases spread across multiple AGs instead of one single AG. His example is having the primary on one AG but be on a different node than another primary to take advantage of the hardware.

Tara Kizer: Yeah, I mean, definitely. The first part of your question, there is no advantage to spreading them across multiple AGs unless you are putting the primary on a separate replica. But you know, you do have licensing implications in that case.

Angie Rudduck: Curse licensing. Always out to get you.

Brent Ozar: That was a smart question. I’m glad he said move the split around different primaries because I was like, “No, there is no advantage—Oh yeah.”

Tara Kizer: There is an advantage there.

Angie Rudduck: Tricky wording there.

 

Why am I getting an external component error when installing SQL Server?

Angie Rudduck: Kimberly, welcome back, I haven’t seen you in a bit. She is installing SQL Server 2008 R2 on Windows Server 2012 R2. This is compatible based on MS docs she checked. However, she’s getting the “external component has thrown an exception error.” What is she missing?

Tara Kizer: I wonder if there is a prerequisite that you need to install first. At least on older versions of SQL Server and Windows it was supported on newer versions of Windows but you had to install something first. I don’t remember what it was and I don’t know that that’s why you’re encountering this error. This is the type of thing that I’d probably open up a support case with Microsoft.

Brent Ozar: The other thing, go download that, whatever the ISO file or the [XE 00:09:32] that you got for the download, go download it again and just save it to another place and try it again. Because I’ve gotten so many funky setup errors just from a corrupted ISO file. Then when I go and get another ISO, like bloop, works perfectly. I’d also say anytime you get them, I’m kind of paranoid like this, but anytime that you get an error during setup, I’d rather wipe Windows and start again. I’m just paranoid. I want to build something that’s going to last forever. So if you’re having repeated errors on the same Windows box, hey, go download a new ISO and then try again on a fresh install of Windows.

Tara Kizer: You can go through the setup logs to see if there’s a better error because that’s probably a pretty generic error. The problem with the setup logs is it’s hard to find the errors. Scroll all the way to the bottom and then you might have to start scrolling back up to see the failure. Because even though it failed, it might have done a lot of extra work afterwards and all of that is going to be logged.

Brent Ozar: There’s like 50 million instances of the word error in the log.

Tara Kizer: Yeah, exactly, it’s awful.

Angie Rudduck: I do like the trick that I learned about filtering the Windows log, starting here during triage. I had no idea about that and then one day when I watched you do triage and you right clicked on the left side bar, I was like, “What? I only knew…” Because half of the time during triage I have to ask the client to move my head because it’s always floating right over the filter log on the right panel in Windows events, so that happens a lot. I’ve been trying to work around not asking them to move my head because it sounds weird to me.

 

How should we do source control with SQL Server?

Angie Rudduck: Since we’re talking about a lot of software, let’s ask another question from Scott. Do we have any suggestions on source control software? When Richie is not here of course.

Tara Kizer: Yeah, I was going to say, Brent and Richie love Git.

Brent Ozar: So there are two ways you can do this. One is you can do source control before you deploy your code, meaning you go make a stored procedure change, you check the code into GitHub or Subversion or TFS, whatever tool you want to use. That’s proactive. Then you go deploy the code after you’ve checked it in. Man, almost nobody does that. Plus too, you end up having DBAs who need to change indexes in production or need to make an emergency change. So the other way you can do it is reactive source code control which means this tool goes out and looks at your SQL Server every x number of hours and then goes grabs any changes and checks them into source control. So this gives you a postmortem log of everything that changed but not who did it and not the exact time that it changed. I am personally a fan of reactive source control as a DBA. I don’t really care as much about who did it but I want what was changed. I want a breadcrumb list of everything that changed on objects. So Redgate’s source control for SQL Server has that ability that they’ll just go through and patrol your SQL Server periodically and check in any changes. It’s just not the source control that your developers are used to. That proactive change control is really, really hard.

Tara Kizer: We did both proactive and reactive at my last job. We used Visual Studios Team Foundation Server. Anytime we did deployment of the application, that was always proactive. And of course, DBAs, you know, are having to make changes. The DBAs were supposed to go in and do a schema compare and then update TFS. That didn’t always happen. Other tasks were more important. So whoever that next person was that touched that database, when they did the schema compare to create the deployment scripts, they would see that ther are these other things that shouldn’t be in my deployment that they’ve already been deployed to production but weren’t in source control. Besides that though, because you could have databases you never touch again. So besides that, twice a year they went through and did a schema compare against all databases and got them up to date.

Brent Ozar: Scott asks, “I didn’t know about reactive source control. Who makes it?” It’s a technique, not a product. It’s just part of Redgate’s source control as well. I think I even still have a blog post on our blog about how you do it with a batch file. Like I wrote a batch file in VBScript to do it with Visual SourceSafe. I need to burn that in a fire.

Angie Rudduck: That sounds complicated to somebody who’s going to try and totally mess up. That was cool. I was about to ask Tara if you could do them together. So that’s cool that you have seen them both together because I was like I feel like one place we didn’t consider indexes, we didn’t let developers change indexes. So if a DBA throws them in and then doesn’t check it in, that would be great to have the reactive right there.

Tara Kizer: Yeah, as long as you have a schema compare option in the tool that you use. Or you can get another schema compare. Then you can see what the changes are between source control and your database.

Angie Rudduck: Very cool.

 

What’s the fastest way to modify a big table?

Angie Rudduck: J.H. wants to know, “What is the fastest and/or safest way of exporting a large table and then reimporting it and maintaining its primary key auto identity seed ID … SELECT * into temp table from large table or bulk copy out or something else?”

Brent Ozar: Okay, so I’m going to tell you the terms to google for: modifying a table online Michael J. Swart S-W-A-R-T. So because you said fastest, Michael has an interesting set of blog posts, it’s like a five-part blog post on how you go set up the new table, how you build something to keep the old and new table in sync and then you move data across in batches. So this way end users notice very minimal downtime and yet you’re able to keep the two in sync as you get larger. The only time I would go that route is if, “You cannot take any down time. We’re willing to let you put a whole lot of development work into it” and it’s more than like say 50 gigs in one table. If it’s less than 50 gigs in one table, I would probably just do a select with a tablock and move the data across that way.

Tara Kizer: Then you can use the identity insert option to handle the identities. That way you keep the values the same between the two tables. So SET IDENTITY_INSERT ON. You can only have one table at a time do this so make sure you set it off when you’re done.

 

How should I manage identity fields with replication?

Angie Rudduck: That’s a perfect follow into Paul’s question. He has existing replication where he wants to change the identity management of primary keys have identity 1 1. He wants to change the primary keys to identify 1 2 on the publisher and identity 0 2 on the subscriber. Is there a way to do this without recreating the tables?

Tara Kizer: You do have the DBCC command where you can change the seed but I don’t think that you can change the increment. Usually, in a scenario like this though what people do is they have the publisher, it’s inserting positive numbers and then on the subscriber inserting negative numbers. So you would have, you know, if it’s an integer, you could have two billion rows for the subscriber and two billion rows in the publisher. That usually satisfies most tables. Otherwise, go to bigint.

Brent Ozar: So there’s a DBCC command to reseed the identity. I cannot remember for the life of me what the syntax is but if you search for that.

Tara Kizer: Yeah, I think it’s [Inaudible 00:16:26] IDENT is the command.

Brent Ozar: Yeah, you just run that on the one where you want to change them.

Angie Rudduck: Good info.

 

Should I use checksum when taking backups?

Angie Rudduck: Samuel wants to know, “Is it best practice to always add checksum when taking backups?”

Brent Ozar: Did you do that when you were a DBA?

Angie Rudduck: I didn’t.

Brent Ozar: You too, both of you, yeah, yeah.

Angie Rudduck: I didn’t know it existed.

Brent Ozar: I don’t think most people do.

Tara Kizer: I knew it existed. Did we do it? Probably not. It does add overhead to the backups and we were—at least a lot of the critical systems we would always, not always, but we would have a backup restore system. So we were testing our backups regardless. So do you need checksum if you are going to be testing your backups?

Brent Ozar: Yeah, I learned about it after I got started consulting. I’m like, oh, that’s an interesting idea. I went on a little quest of “I’m going to get everybody to do checksum on their backups.” I put it in sp_Blitz as a warning, “Hey, you’re not doing checksums on your backups.” Universally, people were like, “What is that? Why would I want my backups to go slower?” So I took it out as a recommendation just because people don’t like their backups going slower.

Tara Kizer: Does Ola’s solution, does it do the checksum by default?

Brent Ozar: Not by default, yeah.

Angie Rudduck: I think it does.

Brent Ozar: Oh, does it?

Angie Rudduck: Because I’ve been playing around. Yesterday I was playing around, let me double check my savings here but I ran the scripts default and then took a look. So would have to double check, but it’s included as an option at the very least.

Brent Ozar: And doesn’t his do verify by default too out of the box?

Angie Rudduck: Yeah, maybe it does verify by default and not checksum by default. But the verify, I mean the one thing I don’t think people think of is like how it can impact because you might be, “Oh, my one gig backup is taking 20 minutes.” I don’t know. But it’s because it’s just doing the restore verify only command against the backup it just took. So it’s just saying, “Oh, is this still a valid backup?” And at the basic level, right? Correct me if I’m wrong, but it’s only saying, “Oh, yes, I can open this as a file. I don’t know its validity inside.” Just that it could reopen it as needed. So that’s just something to be considerate of, it’s not the saving grace. “Oh, I did verify only.”

Brent Ozar: Yeah, it could be all corrupt data in there. It could be 100 percent corrupt. The way you’re going to have to find out is to run CHECKDB.

 

Why don’t our SELECT queries show current data?

Angie Rudduck: All right.

Brent Ozar: We’ve got all kinds of questions coming in. It’s crazy.

Angie Rudduck: I know, they’re definitely falling in now. Okay, so Andrea says they have been having issues with data not showing up in reports for sometimes up to 15 minutes. They are an OLTP shop running 2012 Web. Is this possibly a thing with SQL or is it due to something else?

Tara Kizer: I think we would need more information as to how is the data getting into this database? Is it queueing? Is there a backlog in say a Tibco queue or something like that? Or, you talk about reporting, do you have a replicated system? Or in availability groups, readable secondary, maybe there’s a delay in getting the data to those. I don’t think we have enough information to answer it.

Angie Rudduck: Yeah, I agree.

Brent Ozar: It’s never normal to do an insert in SQL Server, commit your transaction, and then not have it be available for a select immediately.

 

Why am I getting tempdb-full errors when my tempdb is 4GB?

Angie Rudduck: Let’s see what David has to say. He’s getting this on a server with four by one gig temp data, eight gig temp log, insufficient space in tempdb to hold row versions. Need to shrink the version store to free up some space in tempdb.

Tara Kizer: That’s a pretty small tempdb. I’ve supported tempdbs that were half a terabyte in size just because we had business users running ridiculous queries. So, first of all, why is your tempdb so small? Are you shrinking it down? You probably need some more space. Version store, are you running read commited snapshot isolation level? So you need more space for tempdb.

Brent Ozar: And then how much space do you need? Generally, if somebody puts a gun to my head and just says go pick a number, I’m going to go with 25 percent of the size of all the databases combined. So if you have say 100 gigs on the database server, you probably need at least 25 gigs for tempdb.

Tara Kizer: A few jobs ago, we set up hundreds and hundreds of servers. So we just made a policy and tempdb we set at 70GBs. These were shared servers with lots of databases and we didn’t know what was going to occur on them. We would have alerts to warn us if data files or the log file was creeping up on, if they were going to fill up, so we could react to those. But 70 GBs for all of the tempdb data files and I believe 30GBs for the tempdb log file. That was just our default.

Brent Ozar: I don’t get out of bed for less than 70 gigs.

Angie Rudduck: Silly, silly.

 

If I don’t know monitoring tools, will that hold me back in job interviews?

Angie Rudduck: Ronny supports about 25 prod and dev databases as a DBA. He’s not in the corp Windows DBA group and does not have access to all the tools monitoring performance, etc. “All monitoring I have in place is based on scripts that run and report issues. Will the lack of experience working with the tools like that hurt my chances with pursuing a new DBA job? I know it really depends on what the hiring manager is looking for but is knowing tools like that an important skill to have?”

Tara Kizer: I don’t think it’s an important skill necessarily, I think it’s obviously going to depend company to company but if you don’t have any experience with monitoring tools, I think that that’s fine as long as your other experience, your actual SQL Server experience, is what they’re looking for. You can get up to speed on these tools, I wouldn’t say fairly quickly, but you can at least click around and figure things out and with some guidance get some pretty in-depth knowledge of these tools. For the most part, I don’t think that companies are paying for tools like this. So I think that it’s pretty rare that companies have these tools in place.

Angie Rudduck: Yeah, unless you’re going to a large DBA shop, I don’t feel like you’re probably going to have very many of these tools.

Brent Ozar: And you’d have to know all of them. I mean, you know, if you only knew one and then somebody doesn’t use that one, you’re screwed.

Angie Rudduck: It’s not the same thing as not knowing SQL Server versus MySQL versus Oracle. They all run pretty similarly and nobody expects you to know all of them or they’re only going to hire you if you know this one. Like if you only know Redgate, great, because they’re a Redgate shop. That’s usually not the case.

Brent Ozar: Yeah, when we do interviewing for consultants for example, so when we go and hire people, we will often give them a virtual machine and say, “Now you’re on, take remote control of this thing. Show me why it’s slow.” Or, “Troubleshoot why this query isn’t working.” If someone comes to me and says, “Well, I’m sorry, all I can do is use a tool,” like I only ever troubleshoot this with Dell or Idera or Redgate and I’m lost without a third party tool, you’re not going to do well as a consultant because we can’t rely on those tools either. When we parachute in, man, I have no idea what’s going to be happening on the other end. So it pays better to know the native ways to doing things.

 

Idle chit-chat about smoking weed and your friend sp_WhoIsActive

Angie Rudduck: I think we have probably time for one more question. Did you guys see anyone while I scroll back and look?

Brent Ozar: Greg says he remembers that the tweets about tempdb stuff was four tempdb files per NUMA node. They were smoking weed too. I don’t know who that was.

Angie Rudduck: They must be in Portland.

Brent Ozar: Yeah, Denver, something like that.

Angie Rudduck: Someone chimes in, Eli says, “The sp_WhoIsActive is your friend about the monitoring” to you Ronny. That is a good point, we love…

Tara Kizer: WhoIsActive and the Blitz stuff.

Brent Ozar: Yeah, free tools. Pro tip: If you’re going to apply for work at our company, you may want to try using our tools. Know that they’re out there. If you come in and use someone else’s tools, it will not be a good time for you.

Angie Rudduck: Yeah, I agree.

Brent Ozar: Not that you ever need to know how to use our tools to work here. We teach you those too after you get here. But, yeah.

 

Is PostgreSQL better than SQL Server?

Angie Rudduck: Yeah. I feel like there was one that I… There’s a couple that are like…

Tara Kizer: Wes asks the same question, I think he wants—they’re 20 minutes apart.

Angie Rudduck: He really wants me to read his question. Wes, I’m going to tell you my answer is SQL Server pays our bills. Wes wants to know what our thoughts are on Postgres versus Microsoft SQL Server. SQL Server.

Tara Kizer: We’re SQL Server professionals so our answer is going to be SQL Server. If you want me to support your system, I don’t do Postgres SQL so I can’t support it. I mean, I could probably learn it but I don’t really have any interest in learning it.

Brent Ozar: See, I don’t support it either. But I always try to learn about other stuff. There’s stuff that’s really cool about Postgres. Unlogged tables is a classic example. If you search for Postgres on our site, we’ve written a couple blogposts about different features in Postgres that we would want in SQL Server. But boy, at the same time, I kind of like parallelism. Man, Microsoft SQL Server has had parallelism for a long, long time. That’s kind of nice in today’s huge, multicore environments where 16 cores isn’t a big deal. 32 cores isn’t a big deal anymore. Parallelism is pretty freaking awesome. And they’re iterating super fast. So, yeah, I kind of like Microsoft SQL Server. If I was going to start a career from scratch, so like Microsoft is where it’s at in the enterprise environment and Postgres is where it’s at in the startup environment. Well, thanks everybody for hanging out with us today and we will see you guys next week.

This week, Brent, Angie, Erik, Tara, Jessica, and Richie discuss SSMS issues, security auditing, snapshot replication, SSIS Cache Connection Manager, AlwaysON Availability Groups, deadlocks, and Jessica’s trip to Mexico.

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.

Office Hours Webcast – 2016-06-01

Jessica Connors: All right, I guess we should be talking about SQL Server.

Erik Darling: Nah.

Brent Ozar: Oh no.

Erik Darling: Boring.

Jessica Connors: That one product.

Angie: Meh.

Erik Darling: Snoozefest.

Brent Ozar: Which is out new today. So ladies and gentlemen, if you’re watching this, SQL Server 2016 is out right now. You can go download it on MSDN or the partner site. There’s places where you can go it. Developer Edition is free so you can go download the latest version right now. As we speak, Management Studio is not out yet but will be coming any moment.

Jessica Connors: That was our first question too: Is 2016 out yet?

Brent Ozar: Dun dun dun.

Jessica Connors: Are you hearing any rumblings on problems with it?

[Laughter]

Brent Ozar: We all start laughing. There were a lot of problems with the community previews. For example, SQL Server Management Studio would crash every time I would close it. So I’m really curious. Usually you don’t see stuff quite this buggy as you get close to release. But at the same time, I’m like, well, no one ever goes live with it in production the day it comes out anyway. People are just going to get widespread experience in development, in dev environments, and QA, then they’ll go find bugs hopefully and fix them. Hopefully.

Angie Rudduck: Wait. So I shouldn’t install that in our production servers running everything?

Brent Ozar: Yeah, no. I would take a pass for a week or two. Just let things bake out just a little bit.

Jessica Connors: Let it just wait.

 

Jessica Connors: Let’s take a question from Dennis, SSMS question. Is there a way to have SSMS format the numbers in the output messages? Not data in the query, like the row count at the bottom?

Tara Kizer: What are you trying to solve here? Because this is a presentation layer issue. Management Studio, it’s just a tool for us to query data, why does the formatting of the output matter? If you have an application you’re developing in .NET, format your data there. The row count at the bottom. No, Management Studio, there isn’t a way to format it. You can change the font and things like that in the tools options but I’m not sure that that’s what you’re asking.

Richie Rump: Is there a way, Brent? Could we format that?

Erik Darling: One thing you can do is if you’re interested in just having commas in is you can cast it as money or convert it to money with a different culture and you can get commas put in. But other than that, I’m not really sure what you’re after so if you’re a little more specific.

Brent Ozar: Well and return it as results. Whatever you’re looking for, return it as results instead of looking at what comes out of SSMS. Then you can format it there as well.

Jessica Connors: Dennis hasn’t replied to us.

 

Jessica Connors: Let’s go to Ben. He says, “[inaudible 00:02:24 old] to SQL. Hearing rumors about going to the cloud, MS, or Amazon, specifically in terms of security. What are the gotchas and pain points? Security is not our forte.”

Brent Ozar: This is so totally different from on-premises because on-premises you don’t have any security risks at all. No one could possibly access your data. I’m sure it’s locked down tighter than the pope’s poop chute. I mean it is completely secure as all get out. Just me, I’m usually like… Erik says, “Pull my finger.” I would say usually it’s more secure because you don’t go wild and crazy with giving everybody sysadmin. So I just turn it back to people on-premises and go, “So let’s talk about your security. Let’s go take a look at what you got. Everybody is SA. You haven’t changed your password in three years? Yeah, I think you should get out of on-premises. On-premises is probably the worst thing for you.” Nate says, “The pope’s poop chute? Really?” Yes. This is what happens when you work for a small independent company. You can say things like “tighter than the pope’s poop chute.” Probably can’t say that but we’ll find out later.

[Laughter]

Angie Rudduck: You’ve already said it at least three times, we’re going to find out. You’re going to get an official letter from the pope.

Brent Ozar: The Vatican, yep.

Angie Rudduck: Yeah.

Brent Ozar: “The pope does not have a poop chute.”

[Laughter]

Erik Darling: Going for a world record, most references to the pope’s butt in one webcast.

Angie Rudduck: Stop it.

Brent Ozar: Dad always said that to me, so yeah, there we go. Someone else should probably tackle the next question.

Richie Rump: Yeah, somebody else talk now, please.

Jessica Connors: Brent, I’ll just put him on mute.

Erik Darling: Looser than Brent’s…

[Laughter]

Erik Darling: Wallet, wallet, wallet.

Angie Rudduck: Wallet on the company retreat.

Brent Ozar: There we go.

Jessica Connors: I’m glad it’s a short week.

 

Jessica Connors:  Question from J.H. “Would creating a server trigger and emailing our DBA team if someone makes changes to the server role safe? Hard triggers affect performance, but it’s rare in our case that we have server role changes but want to catch it if a network admin puts himself in the sysadmin role without letting us know.”

Tara Kizer: We had security auditing at my last job. I’m not too sure what was used. Well, I think the other DBA who set this all up, he just set up a job and queried for the information. Then the job would run every few minutes I believe and would send the DBA team an alert if anything changed.

Brent Ozar: Yeah, I like that. My first reaction was Extended Events.

Tara Kizer: We had really strict auditing that we had to put in place due to the credit card information. It was encrypted but we had to be very careful with everything.

[Erik and Brent speaking at same time]

Brent Ozar: Would you say you had tight security? How tight was security? Go ahead, Erik, I dare you.

Erik Darling: Oh, sorry. I was going to say that you can set up the event data. You got me all flustered now. You can set up event data XML. It’s pretty good for modification triggers like that. It’s not like, you know, if you put triggers on tables and you’re doing massive shifts of data or you know before and after stuff. It’s a pretty lightweight way to just log changes as they happen.

 

Jessica Connors: Let’s see here. Question from Terry. “Is there a way to set up databases in an AG without doing a backup and restore?”

Erik Darling: Not a good one.

Tara Kizer: No.

Brent Ozar: 2016 there is. 2016 we get direct seeding where we can seed directly from the primary, so starting today you can. But unfortunately, not before today.

 

Jessica Connors: All right, a security question. This is from Nate regarding security auditing. “Any suggestions on getting some basic setup that tracks and alerts for security changes and schema changes?”

Tara Kizer: I don’t know.

Brent Ozar: I don’t know either. Is there like an Extended Event or something you could hook into?

Tara Kizer: Probably. What we had set up for security would have just been queries, just to query for the information. Look at the system tables and views. For schema changes, I don’t know.

Angie Rudduck: I think somebody set up a simple, “Hey, there’s somebody new in this group” for a security group. I think it was PowerShell at my last place just to like all of a sudden somebody is in the DBA sysadmin group. How did you get there? It would fire off of one server in the domain but I don’t know anything about schemas.

Brent Ozar: Yeah, schemas are tricky because you can log DDL changes. The problem is if your trigger fails, then the change to the table can fail and that can be kind of ugly. You can also set up event notifications and dump stuff into a queue with Service Broker, but it is kind of challenging and kind of risky. If you want to learn more about it, search—god, I’ve got to type this woman’s name out—Maria Zakourdaev. So if you search for “event notifications and SQLblog,” that’s what you do: “SQLblog Maria.” SQLblog is all one word. Maria Zakourdaev, and I’m sure I’m butchering her name, from Israel has a post on how you go about setting up event notifications and how they break because they do break under some circumstances.

Erik Darling: Everyone mark it, not only with SQL Server 2016 release today but Brent recommended Service Broker.

[Laughter]

Brent Ozar: It’s a great solution.

Richie Rump: You didn’t see the disdain on my face when he said that? You didn’t see that at all?

 

Jessica Connors: Let’s talk about snapshot replication from Trish L. “I have…

Tara Kizer: I’ve got to go get my coffee.

Brent Ozar: I know, we’re all like, “I’m out of here.”

Jessica Connors: Maybe we could tackle this. “I have snapshot replication which is scheduled to run one time per day but recently I’ve started to see blocking done by the snapshot replication. Do I need to [Inaudible 00:07:53] the distribution agent as well because it is running automatically now?”

Tara Kizer: I’m not sure about that but the blocking, you’re going to encounter that because it has to lock the schema. That’s one of the last steps it does. So anytime you have to do a snapshot, whether it be snapshot replication or transactional replication, I assume with merge replication too. Anytime you have to do that initial snapshot or reinitialize a snapshot, it does block changes—data changes, schema changes, you’ll see a lot of blocking as it’s going through the last bits of the snapshot creation.

Brent Ozar: What would make you choose snapshot replication? Like what would be a scenario where you’d go—or have there been any scenarios where you go, “Hey, snapshot replication is the right thing for something I encountered.”

Tara Kizer: I’ve never used to it but if users are willing to accept that their data is a day old, let’s say. Any time that I’ve used transactional replication, they’ve wanted near real time data. They wanted zero latency. We couldn’t deliver that in replication. But yeah, snapshot replication, it just depends on what your user wants as far as the data goes.

Richie Rump: I’ve used it for reporting solutions.

[Richie and Erik speaking at the same time]

Jessica Connors: What?

Erik Darling: I was asking Tara if a different isolation level would help with that blocking.

Brent Ozar: Oh.

Tara Kizer: We were actually using RCSI so, yeah, it was definitely a schema lock. We definitely still had blocking.

Brent Ozar: Makes sense. It was probably worse without the schema, or without the snapshot or CSI, probably horrible.

Tara Kizer: It was very rare we had to do the snapshot but sometimes replication would be broken for whatever reason and we couldn’t figure it out and we’d just have to restart replication. Our database was large. It took like five to eight hours to do. Not the snapshot portion, the snapshot took like about 45 minutes I believe but there was a lot of blocking during that time.

Richie Rump: I like snapshot replication for reporting purposes, right? So again, you just dump the data over there and it’s okay that there’s a time delay for the reporting aspect and there’s your data.

Tara Kizer: I just wonder instead of snapshot replication if people should be, not backup and restore because that might take too long on larger databases, but a SAN snapshot, a daily SAN snapshot, because it’s just available right away. You don’t have to wait for anything.

Brent Ozar: No schema locks, it doesn’t matter what the volume of change data is, yeah.

 

Jessica Connors: While we’re on the hot topic of replication, there’s another one from Paul. “I am replicating a database using merge and had an issue where if the developers changed a procedure on the original database, the change would not be pushed to the replicated database. Replicate schema changes is set to true. Any guidance on the reason why the changes won’t replicate? I did a snapshot before initiating replication.”

Tara Kizer: So replicate schema changes has to do with the table changes, it does not have to do with stored procedure, views, functions, or anything like that. So if you do an alter table, add a column, that will get replicated if you have the replicate schema changes set to true but you would have to also have in a publication either your current publication or a different one to also replicate the stored procedures.

Brent Ozar: I wouldn’t do that in merge either. Like I would—if you’re going to change stored procedures, just keep them in source control and apply them to both servers.

Tara Kizer: Yeah.

 

Jessica Connors: Let’s move onto a question from Justin, SSIS Cache Connection Manager question. “I want to load several objects into cache, about one to five million records, but can’t figure out how to access that cache’s source of data. It’s quite a bit faster for us to load to a cache versus staging tables. Is this possible? If not, how would you store this?”

Brent Ozar: Have any of us used the caching stuff in SSIS? No, everybody is…

Tara Kizer: No, I’ve used SSIS a lot and have not used that.

Brent Ozar: The one guy I know who does is Andy Leonard. If you search for Andy Leonard SSIS, he’s written and talked about this. I know because it was in his book. I didn’t read the book, I just remember seeing the book. It was on my shelf at one time. It was a great paperweight. Smart guy, really friendly. Just go ask him the question, he’ll be able to give you that right away. Normally we’re all about, “Go put it on stack exchange.” Just go ask Andy. Just go “Andy Leonard SSIS” and he’s super friendly and will give you that answer right away.

Erik Darling: Tell him Brent sent you.

Brent Ozar: Tell him Brent sent you on this.

 

Jessica Connors: Question from Tim L. He says, “I’ve got an ancient Access expert here at my company. I’m having SA access. He has a lot of ODBC from multiple Access dbs into my 2008 R2 SQL Server. How do I find out what tables he updates? There’s nothing in terms of jobs or stored procedures that references his data pull and updates.”

Tara Kizer: You could do an Extended Event, run a trace, add a trigger.

Brent Ozar: It’s 2008 R2 though. I like the trigger.

Angie Rudduck: I like cutting his access.

Richie Rump: I love that, “ancient.”

Tara Kizer: Yeah, why does he need SA access?

Brent Ozar: Just go ask him. He’s ancient. He’ll be a nice guy. He’s mellow by now. If you run a trace, that’s going to be ugly, performance intensive. The trigger will be intensive.

Erik Darling: Well you can at least filter the trace down to table name.

Brent Ozar: Well but if he wants to know what tables he’s doing, it’s going to be every time…

Erik Darling: Oh, never mind.

Brent Ozar: Yeah.

Tara Kizer: He could filter by his login at least, if that’s what it’s going through at least to connect to SQL Server.

Brent Ozar: And don’t try to log his insert statements or updates deletes. Just put a record in a table the first time he does an update, delete, and then immediately turn off the trigger on that table, or the trace on that. But, yeah. That’s tough. Just go ask the guy. Go talk to the guy. It would be nice.

Erik Darling: Shoot him email.

Brent Ozar: Yeah, shoot him an email. Buy him a bottle of Bourbon.

Erik Darling: Yeah.

Brent Ozar: It’s a human being.

Richie Rump: Yeah, just don’t give away the wine. Right, Brent?

Brent Ozar: If you were going to give somebody wine, you should give them like Robert Mondavi.

[Laughter]

Brent Ozar: He’s Access. He’s not, you know. That’s not true. Cliff Lede, ladies and gentlemen. This webcast is brought to you by Cliff Lede wines.
Jessica Connors: Do any of us participate in SQL Cruise?

Brent Ozar: I cofounded that with Tim Ford. Tim and I cofounded it and when we split up the consulting company versus the training and cruise-type business, I wanted to let him go do his own thing there and not be on it because I felt like I would kind of shadow in on it and make the thing murky. It is a wonderful experience. I strongly recommend it to anyone who thinks about going. It’s fantastic for your professional development. It’s limited to just say 20 attendees and like 5 to 10 presenters, so the mix, the ratio of presenters and attendees is fabulous. You get to hang out with them. You get to have dinners, from all of this you get to know them really well. So it can be a rocket ship for your career and it helps you really build networking bonds with not just the presenters but the other attendees who are there. The downside is you get to hang out with the presenters in hot tubs so that may be a pro or a con depending on what your idea of a good time is there. So it’s not for everybody but it is truly fantastic.

Erik Darling: Grant Fritchey in a speedo, ladies and gentlemen.

[Laughter]

Jessica Connors: Do you still go on the cruise then? Are you done?

Brent Ozar: I don’t. I totally stopped doing that. I go off and do my own cruises. My next one is in Alaska in August I think, going on that one with my parents. But I haven’t done a technical cruise since. Most of the time what I like to do now is just go out on a cruise and not talk to anyone. I like to go out and sit and read books.

Erik Darling: You did Alaska before, right?

Brent Ozar: This is my fifth time I think, yeah. Absolutely love it. It’s gorgeous. I never was a snow kind of a guy but you get out there in the majestic snow and mountains and bears and all that, it’s beautiful.

Jessica Connors: Nice.

Angie Rudduck: Minus the jacket.

Brent Ozar: Yes.

 

Jessica Connors: Let’s talk to Graham Logan, he’s got some problems. He says, “SSMS crashes when expanding database objects in objects explorer. Database is about 1.2 terabytes and has about two million objects.”

Tara Kizer: Oh good lord.

Jessica Connors: But, he says, “[inaudible 00:15:43] design. It’s not mine. How to view all database objects without SSMS crashing.”

Tara Kizer: You just cannot use object explorer. You’re not going to be able to use object explorer. You can’t use the left pane in Management Studio. You’re going to have to write queries to see things. It’s very unfortunate but that’s a heck of a lot of objects in the database.

Brent Ozar: Before you expand the list, you have to right click on the tables thing and click filter. Then you can filter for specific strings but without filtering, it’s useless… I’d go information schema tables, information schema, yeah, all columns, all kinds of stuff.

 

Jessica Connors: Kyle Johnson has a new one. “We have a 4.2 terabyte database with a single data file. I’m working on a plan to migrate to multiple ones. Shrinking the database to a level with the data between files isn’t really practical with a six-hour window of no users. Have any other suggestions? Reindexing tables and specifying the file groups to move the table to two?” From Kyle Johnson.

Brent Ozar: Not a bunch of good options here.

Erik Darling: Brent is getting ready to tell you about Bob Pusateri.

Brent Ozar: I was. You are psychic. You are phenomenally psychic. Tell us more. I want to subscribe to your newsletter.

Erik Darling: Bob Pusateri, which I feel like this webcast has been obscene enough without me saying that, has a blog post about moving file groups, a lot of the gotchas, and you know, bad things that can happen to you. I will track down the link for it and send it to you but I would not do it justice just explaining what goes on it, because it’s scripts and everything, so.

Brent Ozar: Bob had a 25 terabyte data warehouse with thousands of files in it because the prior DBA thought it was a good idea to create a separate file group for every employee and then later came to regret that decision so he has a great set of scripts on how you go about moving stuff around and keeping them online wherever possible. So it’s really slick. So you do that prepping leading up to the six-hour window so that your six-hour window is only dealing with stuff that you can’t do offline, like moving the LOB data if I remember right.

 

Jessica Connors: Question from Claudio. “I’m trying to understand the differences between the new AlwaysOn Basic Availability Groups and the synchronous commit mode and the mirroring and high safety mode but they look identical except AlwaysOn seems more complicated to set up and manage. Are there any benefits to either solutions, features, performance, licenses, liability? Which one would you recommend to adopt?

Tara Kizer: Database mirroring is being deprecated so you’re going to want to move over to the AG basic availability groups. Get on it now. It’s the replacement for database mirroring.

Brent Ozar: The drawbacks, so you’ve managed both too. What would you say the strengths of AlwaysOn Availability Groups are over mirroring and vice versa? That’s not a trick question, I promise.

Tara Kizer: Mirroring you’re not failing over groups at a time. You’re failing over a database at a time. So availability groups let you failover in groups which is good when you have an application with multiple databases that it needs.

Brent Ozar: To be clear, so you’re saying the guy is saying Standard too, so you only do one database at a time. You could script those too, just like you would with mirroring. I’m trying to think if there’s anything that would be… have to have a cluster but you don’t have to have a domain with mirroring. But you don’t either with 2016 either. You can do it between standalone boxes.

Tara Kizer: With mirroring, if you want the automatic failovers, you need a witness. With AGs you do need a quorum but it could be a file share on another server, you know, on a file server that you have or a disk on a SAN could be a quorum. Mirroring does require another box, a VM, it can be Express Edition.

Brent Ozar: Yeah, I used to be the biggest fan of mirroring. I’m having a tough time coming up with advantages as 2016 is starting here.

Tara Kizer: I did a lot of failovers with mirroring, log shipping, and then later availability groups and by far I like availability groups best for DR failovers. It was just so much easier. You just run a failover command and you’re done. With mirroring, you’re doing it database by database. Log shipping is, you know, all sorts of restores going on. Mirroring is certainly easy, definitely easy, but I like the slickness of availability groups and readable secondaries and the choice of asynchronous and synchronous.

Brent Ozar: Yeah, that’s where I was going to go too. Because even in Standard, you get choice between synch and asynch now. And you can use one technology that works on your Standard stuff and your Enterprise stuff so you only have to learn one feature instead of learning two. That’s kind of slick too.

Tara Kizer: When we used mirroring, we would use asynchronous mirroring to the DR site then for high availability solution at the primary site we used failover clustering. So availability groups it just solves both solutions in one feature, plus reporting, we got rid of replication.
Jessica Connors: All right. Let’s move on to a question from Chris Woods, a regular attendee. He says, “Migrating MDF with LOB data, L-O-B data, I don’t know how you call that, from one drive to another with minimal/no downtime. Can you use log mirroring to mirror it to a new database on the same server that shut down the original during a quick downtime?”

Brent Ozar: You can’t do mirroring to the different database on the same server, can you? You can do log shipping, can you do mirroring to the same instance?

Tara Kizer: No.

Brent Ozar: You can do log shipping to the same instance. That will work. Your downtime will be super fast. Because what your failover process would look like is when it comes time for failover, you do a tail of the log backup up on the main database, then restore that tail-log on the other database. Rename the old primary as like, the old primary database just like “database old.” Then rename the new one as “database new” and then whatever the new database name is or the original database name is. So you could do that in like a 30-second outage. You don’t have to change connection strings because it’s all the same server still. So that’s kind of slick.

Tara Kizer: If this is a SAN drive, even moving from one SAN to the next, we did all this stuff live. I don’t know what the technologies are called but we would move arrays live. The SAN administrators did some magic and it just copied over the data and once the copy was complete, it did a switcheroo between the two pointers, or, I don’t know what the technology was but the SAN can handle this without any down time.

 

Jessica Connors: Rob is adding a new instance to an existing active active cluster. I think he’s talking me about his process so that we can say if it’s yea or nay. He says, “I would need to failover the existing instances to one node, install the new instance on the node with no instances. Service pack it up and failover the instance to the node I was just on. Then run the install in another node, apply SPS, then rebalance the instances.” Does that sound about right?

Tara Kizer: It does but you know we don’t recommend active active clusters. What happens if you lose a node? I don’t at least. I’ve had four-node clusters where all four nodes were active. It’s just a nightmare. If you lose a node, can your other nodes support all of the instances at the same time until you get that other node fixed?

Brent Ozar: Richie is showing something on his iPad. What I would say is…

Erik Darling: It’s too bright.

Brent Ozar: I still can’t see it. We do recommend active active with a passive node.

Tara Kizer: Yeah, okay. Right.

Brent Ozar: Yeah, multi-instance clusters, just have a passive in there somewhere. Your scenario is exactly why you want a passive node laying around.

Tara Kizer: At least what you wrote out here for the question, yeah, that is the process.

Brent Ozar: Also known as miserable.

Tara Kizer: Yeah. At least since SQL Server 2008 we’ve been able to have where it can install it just on one node. Prior to that, all nodes in the cluster had to be online and have the exact right status in order for the installation. Because the installation occurred across all nodes at the same time. Service packs, the engine, everything. On a four-node cluster, there’d always be one node that was misbehaving. It just says, “I need a reboot.” And you’d reboot it 20 times and it would still say, “I need a reboot.” Then finally that one would be okay and now another node would say, “I need a reboot.” It was just ridiculous. So I’m glad that Microsoft changed the installation process starting with 2008.

Brent Ozar: It’s like taking kids on a road trip. “Everybody ready…?” “No.”

Erik Darling: “I have to pee.”

Richie Rump: I got excited, I thought we had a Node.js question but I guess not.

Erik Darling: Never have, never will.

Richie Rump: Brent has.

Brent Ozar: I have.

 

Jessica Connors: Let’s take one more question. Let’s see here. “Good morning, Brent and Tara, Erik, Richie, and Angie. Says, “Yesterday we had a problem with the process that normally moves data from table queue and delete it after it’s done. This is a standalone database. We stopped the inflow of data but it didn’t help. We got thousands of deadlock alerts. I notice that the disk queue length on the log drive is higher than usual. Here is a sample of the deadlock.” He provides it. “Is there anywhere I could look for this issue?”

Tara Kizer: If you’re getting deadlocks you should turn on the deadlock trace flag 1222, maybe run an Extended Event to capture the deadlock graph. Having just the deadlock victim isn’t enough to be able to resolve it.

Brent Ozar: It’s a separate technique I think that not a lot of database administrators get good at because it’s one of those things where you’re kind of like, “Hey, you should fix your indexes in your queries.” Then people go off and do their own thing. It’s one of those where when you do want to do it, it takes a day or two to read up and go, “Here’s exactly how the [Inaudible 00:25:00].” There’s also not a lot of good resources on our site for it. We don’t go into details on deadlocks either. Have any of you guys seen resources on deadlocks that you liked?

Erik Darling: I like just hitting Extended Events for it. The system health session has quite a bevy of information on deadlocks and you can view the graphs and everything which is pretty swell.

Tara Kizer: I attended a session at PASS in 2014, Jonathan Kehayias from SQLskills, it was all about deadlocks. It was invaluable information. He went over different scenarios and stuff. He said that he loves deadlocks. It was like, whoa, I don’t know that anyone has ever said that before. But it was really great information. I haven’t looked at—I do read his blogs—but I suspect he’s got a lot of deadlock information on the blog to help you out.

Richie Rump: He also loves XML.

Brent Ozar: He loves XML and Extended Events. If you have a Pluralsight subscription. So Pluralsight has online training. I want to say it’s like $39 a month or something like that. I think Kehayias has a course on deadlocks. I’m not 100 percent sure but if you search for SQL Server deadlocks if Kehayias has a course on there, it would be wonderful.

Erik Darling: Also, if you don’t have Pluralsight but you want to try it, Microsoft has a Dev Essentials site I believe where if you sign up for that, you get a 30-day free trial of Pluralsight and you also get Developer Edition and a copy of Visual Studio that’s free, Visual Studio Community or something for free. So it’s not just the Pluralsight subscription for 30-days but you do get a couple other goodies in there that last you a little bit longer.

Richie Rump: The course is called SQL Server Deadlock Analysis and Prevention.

Angie Rudduck: Someday still has a Pluralsight account.

Jessica Connors: All right guys, that’s all we’ve got for today.

Brent Ozar: But thanks for hanging out with us. Man, time goes so fast now. Gee, holy smokes.

Erik Darling: And they’re sobering up.

Brent Ozar: Well, back to work. The Cliff Lede, ladies and gentlemen. Enjoy the High Fidelity. See you guys next week.

 

This week, Angie, Erik, Doug, Jessica, and Richie discuss DB migration, rebuilding large indexes, recommendation for SQL dev ops tools, best practices for disabling SA accounts, compression, 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.

Office Hours Webcast – 2016-06-08

Jessica Connors: Question from Justin. He always asks us something. Justin says, “Is it advisable to move the public’s role from being able to query sys logins, sys databases, and/or sys configurations in master?”

Erik Darling: Advisable for what? I’ve never done it. I never cared that much. But I’m not like a big security guy. Any other big security guys want to talk about it…?

Doug Lane: Yeah, I’ve never done anything with public’s role and I’ve never seen it be a problem, but again, we’re not security experts.

Erik Darling: Again, we always recommend that when people ask sort of offhand security questions, Denny Cherry’s book Securing SQL Server is probably the go-to thing to read to figure out if what you’re doing is good or bad.

Jessica Connors: Yeah, Justin says that they got audited and [Inaudible 00:00:47]..

Erik Darling: What kind of audit was it that brought those up? I’d be curious.

 

Two Servers, One Load Test

Jessica Connors: Let’s move on to a question from Claudio. He says, “I would like to load test a new SQL Server instance with real production data. Is there anything we could put between clients and two SQL Servers that will intercept the queries for them to both SQL Servers and return the response only to one SQL Server?

Erik Darling: Yes, and I also have a magic spell that turns rats into kittens. No. That’s a bit much and a bit specific. You’re going to have to come up with something else. If you want to get really crazy, you’re going to have to look at Distributed Replay and come back in three years when you finish reading the documentation.

 

How do I configure multi-subnet AG listeners?

Jessica Connors: Okay. Let’s see here. This is a long one from Richard. Let’s tackle this one. “I will be adding a remote DR replica, non-readable, to an existing local availability group on a multi-subnet cluster to be able to use the listener at the DR site. I know a remote site IP address will be added to the listener. Is there anything else that has to be configured in the availability group or cluster besides DNS and firewall rules?”

Erik Darling: Brent?

Doug Lane: Yeah.

Jessica Connors: Where are you, Brent?

Erik Darling: I don’t know actually. I would be interested so I want you to try it out and email me if you hit any errors because I would be fascinated.

[Angie Rudduck enters webcast]

Jessica Connors: Oh, hi.

Doug Lane: Oh, we heard Angie before we saw her.

Angie Rudduck: Thought I had my mute on.

Doug Lane: As for the AG mystery, we’re going to leave that one unsolved.

Jessica Connors: Unsolved mysteries.

 

How should I configure database maintenance tasks?

Jessica Connors: Question from David. He says, “For routine tasks, index maintenance, backup, etcetera, is it preferred to use agent jobs or maintenance plans? It seems to be the DBA preference. Any reasons to lean one way or the other?”
Erik Darling: Ola Hallengren. Angie, tell us about Ola Hallengren.

Angie Rudduck: Ola Hallengren is amazing. I tell every single client about Ola Hallengren. I used it at my last place and in production across every server. You can do all backups, full disk logs. You can do it separated for your user databases versus your system databases. You get your CHECKDBs in there, user versus system databases. You even get index optimize and even better, Brent, aka Erik, has a really good blog post about how you can use it to just do update stats which is a great follow-up from his post about why don’t do index maintenance anyway, right? Just update stats. I love Ola. I’m working on a minideck to pitch all of his stuff in one instead of just the indexing.

Erik Darling: Nice.

Angie Rudduck: But I’m too busy with clients.

Doug Lane: Plus, he’s a Sagittarius.

Angie Rudduck: Gemini.

Erik Darling: I’ve heard rumors that I’m a Scorpio but I’ve never had that confirmed.

Jessica Connors: Use your Google machine.

Doug Lane: [Imitating Sean Connery] Do you expect me to talk, Scorpio?

[Laughter]

 

How do I set the default port for the DAC?

Jessica Connors: Let’s take one from Ben. He says, “Oh, SQL stuff. Here’s one. In old SQL, we had to set a registry key to set a static remote DAC port. Is there a better way in SQL 2012, 2014, 2016? What’s the registry key?”

Erik Darling: A static remote direct administrative connection port?

Jessica Connors: Mm-hmm.

Erik Darling: Weird. No, I don’t know, I’ve never done that.

Doug Lane: Yeah, me neither.

Angie Rudduck: What is old SQL? Like what version is old SQL?

[Laughter]

Angie Rudduck: 2005?

Doug Lane: 2005 he says.

Erik Darling: Hmm, I don’t believe that’s changed much since then.

Richie Rump: Yeah, it sounds like a blog post you need to write, Erik.

Angie Rudduck: We’ve got something on the site about remote DAC because…

Doug Lane: That doesn’t say anything about the port though.

Angie Rudduck: No, but it’s pretty detailed, isn’t it? I don’t know maybe go check that out, Ben, and go from there. I think it’s just go/dac. I don’t know. I’m making up things now.

Erik Darling: Brentozar.com/go/dac, D-A-C.

Jessica Connors: What’s the oldest version of SQL you guys have worked on?

Erik Darling: ’05.

Angie Rudduck: 2000.

Doug Lane: In Critical Care, ’05.

Angie Rudduck: Oh, yeah.

Richie Rump: No 6.5 people? No?

Angie Rudduck: Tara is not here.

Jessica Connors: Yeah, she’d probably have a story about the oldest version she’s used. She’s got the best stories.

Erik Darling: “It was on a floppy disk…”

[Laughter]

Doug Lane: I worked on 7 once upon a time. I didn’t actually like do real work on 7, it was just, believe it or not, writing stored procedures in the GUI window.

Angie Rudduck: Query explorer or whatever it is?

Doug Lane: No, it was like the properties of the—it was crazy when I think back on it. There was like no validation of any kind except the little parse button. This was back when Query Analyzer and Enterprise Manager were separate and I was doing it in Enterprise Manager.

Angie Rudduck: We had a 2000 box at my last place and I knew nothing about 2000. I tried logging in there and I was like, “Wait, where is Management Studio?” That was really hard to try to figure it out. The management administrative part is really scary in 2000 and I was on the server directly. It was like already a precarious server about to tip over. So, scary.

 

What’s the best way to rebuild a 2-billion-row table?

Jessica Connors: Question from Joe. He says, “What is the best way to rebuild a very large index without taking outage or filling the log? Rebuilding after two billion record delete.”

Doug Lane: Oh, are you sure you need to delete two billion rows from a table?

Erik Darling: Maybe he was archiving.

Doug Lane: Yeah, I don’t know if you want to flag them as deleted and then move them out some other time or what, but, wow, that’s a lot of log stuff. You can do minimal logging if it’s a table that you really don’t care about it being fully logged on but there are disadvantages to that too.

Erik Darling: What I would probably do, I mean, if you’re on Enterprise you’re kind of out of luck either way, right? There’s no online index operations there. You can help with the log backup stuff if you put it into bulk logged and continue taking log backups, but at that point, if anything else happens that you need to be recoverable after it starts bulk logging something, you’re going to lose all that information too. So bulk log does have its downsides. It’s not a magic bullet. So depending on your situation, you might be in a little bit of a pickle. A better bet is if you’re deleting two billion records and depending on how many records are leftover, you might just want to dump the stuff that you’re not deleting into another table and then do some sp_rename and switch things around.

Doug Lane: You can actually just drop the index and recreate it. Sometimes that goes a lot faster.

 

Are there any problems with SQL role triggers?

Jessica Connors: Question from J.H. He says, “Anything to be aware of or downsides of setting up SQL role triggers, mainly sysadmin role changes?”

Erik Darling: All these security questions.

Doug Lane: Yeah.

Erik Darling: We bill ourselves as not security people.

Doug Lane: Like the one before, I think we’re going to punt on that.

Jessica Connors: Thomas Cline says, “No security questions.”

Angie Rudduck: Too bad the slides aren’t up.

Jessica Connors: Yeah.

Erik Darling: “For security questions…”

Angie Rudduck: “Please call…”

Erik Darling: Yeah, there we go.

Angie Rudduck: I’ll do them because it usually works for me.

Erik Darling: Attendees… staff… Angie. I’ll just mute you, just kidding. There we go. You are presenting.

 

What are the HA and DR options with Azure VMs?

Jessica Connors: All right, who wants to answer some Azure questions?

Erik Darling: Nope.

[Laughter]

Jessica Connors: Does anybody here know the HA and DR options with SQL 2012 Standard in Azure VMs?

Doug Lane: Oh, no. Not me.

Erik Darling: Using a VM? If you’re just using the VMs, I assume it’s the same as are available with anything else. It’s only if you use the managed databases that you get something else but I think it’s mirroring either way. I know Amazon RDS uses mirroring.

Richie Rump: Yeah, I think they have like three copies and if one goes down it automatically fails over to the other two or something like that. Don’t quote me.

Jessica Connors: Okay, we’re all being quoted. We’re actually all being transcribed. We’re all being recorded. We’re all being watched.

Erik Darling: Really?

 

Is there a better solution for replication than linked servers?

Jessica Connors: Question from Cynthia. She says, “My developers have a product that uses linked servers for parameter table replication. I’ve read that linked servers aren’t the greatest. Is there another way to do this?”

Doug Lane: Okay, that’s actually kind of a two-part question because you’ve heard that linked servers aren’t the greatest. You’re right. So with SQL Server 2012 SP1 and later, you don’t have to blast a huge security hole in order to get statistics back from the remote side in linked servers. It used to be that you had to have outrageous permissions like ddl admin or sysadmin in order to reach across, get a good estimate, when it then builds the query plan on the local side. That’s not the case anymore. The problem that you can still run into though is that where clauses can be evaluated on the local side. Meaning, if you do a where on a remote table what can happen is SQL Server will bring the entire contents of that remote table over and then evaluate the where clause locally. So you’re talking about a huge amount of network traffic potentially. That’s what can go wrong with them. The other question, “Is there a better way?” That kind of depends on what flexibility the app gives you because you say that this is a product. So I don’t know if this is something that you have the ability to change or not but if you’re talking about replicating from one side to the other, there’s any number of ways to move data from A to B.

Jessica Connors: And why do linked servers suck so bad?

Doug Lane: I just explained that.

Jessica Connors: Oh, did you? I didn’t hear you say why they suck so bad, sorry.

Doug Lane: Because you can end up with really bad plans either because permissions don’t allow good statistics or you end up pulling everything across the network just to filter it down once you’ve got it on the other side.

 

Are there any good devops tools for SQL Server?

Jessica Connors: Question from Joshua. This might be one for Richie. “Do you have any recommendations for Microsoft SQL dev ops tools?”

Richie Rump: There’s not a ton. I guess Opserver. I guess from Stack Overflow would be one of them but not that I know of that there’s like out-of-the-box ways to do that kind of stuff. I know when I was consulting with one firm, they had built their own dev ops tools. I think they had Splunk and then they just threw stuff out from SQL Server logs and then did a whole bunch of other querying to put dashboards up so they could do monitoring amongst the team and do all that other stuff. I think Opserver does a lot of that stuff for you but it’s a lot of configuration to get it up and running. I’d say test it out, try it out, and see if that works for you but I’m not aware of any kind of things you could buy and it’s kind of ops-y things. I don’t know, what do you think guys?

Erik Darling: I agree with you.

Doug Lane: I don’t live in the dev ops world.

Jessica Connors: I agree with you, Richie.

Angie Rudduck: Yeah, whatever the developer says.

Jessica Connors: What he said.

 

Should we disable the SA account and set the DB owner to something else?

Jessica Connors: Question from Curtis. He says, “I’m looking for a clarification on SA usage. sp_Blitz [inaudible 00:12:03] to having DB owner set to SA, not a user account. But what about the best practice of disabling SA? Should DB owner be set to a surrogate SA account?

Erik Darling: Nope. It’s not really catastrophic because it’s something that you should be aware of because usually what happens on a server is someone will come in and restore it. Someone will come in and restore a database, usually from an older server to the new one. They’ll be logged in with their user account so they’ll be the owner of that database. The owner of the database has elevated privileges on the database equal to SA, which you may not want always and forever. That’s why SA should be the owner, even if it’s disabled and the user account shouldn’t be. Even if the user is a sysadmin, you kind of just don’t want them to also be the owner of a database.

 

How do I migrate databases in simple recovery?

Jessica Connors: Question from Monica M. “We are migrating and upgrading from SQL 2008 R2 to 2014. We use simple recovery as our reporting/analysis rather than OLTP. Our IT department said after I copy/restore the databases to the new server it will take them two weeks to go live. By this time, our DBs will obviously be out of sync. What simple method would be best to perform this move?”

Angie Rudduck: Every time I moved, we did some server upgrades where we just created a new VM and ended up renaming it to the old server name eventually but what we did was we took a full backup like the day before, hopefully, but if you have to do two-weeks, we took the full backup when we knew and then we took a differential right when we’re ready to make the cut over. So let’s say at 6:00 p.m. the maintenance window is open and I’m allowed to take the database offline. I put it in single-user mode. I took a differential and then applied that to the new server. Then took it out of single-user mode on the new server. Then we did all of our extra work. So it’s not perfect for two weeks of data change, so if you could keep applying the fulls until like the night before, that would give you a little bit better change over.

 

Jessica Connors: Trying to find some questions here. You guys are real chatty today.

Erik Darling: Everyone is all blah, blah, blah, problems, blah, blah, blah.

Jessica Connors: “Here is my error…” They copy and paste it. I’m never reading those.

Erik Darling: “Here’s the memory dump I had.”

Angie Rudduck: Jessica likes to be able to read the questions and she doesn’t read SQL, so nobody reads computer. Nobody really reads computer, including us.

Erik Darling: “Yeah, I found this weird XML…”

Jessica Connors: Richie reads computer.

Angie Rudduck: That’s true, Richie reads computer.

Richie Rump: I was reading XML before I got on.

Angie Rudduck: That’s disturbing.

Erik Darling: Naughty boy.

 

How do I shrink a 1.2TB database?

Jessica Connors: Here’s a question from Ben. He says, “I have a large 1.2 terabyte [inaudible 00:14:51] queuing database. Added a new drive and a new file device. DBCC SHRINKFILE does not seem to be working on the original file. Seems that the queuing application reuses space before it can be reclaimed. Any suggestions?”

Angie Rudduck: Don’t shrink.

Erik Darling: I don’t know what you’re trying to do. Are you trying to move the file to the new drive or what are you up to? I don’t think you’re being totally honest with us here.

Angie Rudduck: Yeah.

Jessica Connors: But you shouldn’t shrink, huh?

Doug Lane: Spread usage across drives, okay.

Angie Rudduck: Maybe put it on one drive, I don’t know? I guess that’s hard to do with such a large file size.

Jessica Connors: 1.2 terabytes.

Erik Darling: So you have your database and you bought a new drive. Did you put like files or file groups on the new drive? Did you do any of that stuff yet?

Angie Rudduck: He says he has to shrink because the original drive is maxed and he needs workspace. I think it’s just not creating—maybe he has to do what you’re saying, Erik, about creating an additional file group to be on the other drive.

Erik Darling: Right, so what you have to do is actually move stuff over to that other file. So if you haven’t done it already, you have to pick some indexes or nonclustered or clustered indexes and start doing rebuild on the other file group.

Angie Rudduck: Then you’ll be able to clear out space to shrink your file.

Erik Darling: Hopefully.

Angie Rudduck: Maybe, yeah. Let us know next Wednesday.

 

Has anybody played with SQL Server 2016 yet?

Jessica Connors: Have we played with SQL 2016 yet?

Erik Darling: Oh, yeah.

Doug Lane: Yep.

Jessica Connors: No? Some of you?

Erik Darling: Yes.

Jessica Connors: Have you played around with the 2016 cardinality estimator and do you know if it works better than SQL 2014?

Erik Darling: It’s the same one as 2014.

Jessica Connors:         Is it?

Doug Lane: So there’s the new and the old. Old is 2012 and previous and the new is 2014 plus. There’s all kinds of other new stuff in 2016 but the cardinality estimator actually hasn’t been upgraded a second time.

Erik Darling: Yeah, Microsoft is actually approaching things a little bit differently where post 2014 with a new cardinality estimator, they’ll add optimizer fixes and improvements for a version but you won’t automatically be forced into using those. You’ll have to use trace flag 4199 to apply some of those. So even if you pop right into 2016, you may not see things immediately. You may have to trace flag your way into greatness and glory.

 

Are high IO waits on TempDB a problem?

Jessica Connors: Here’s a good question from Mandy. She says, “I’ve been on a SQL 2014 standard cluster with tempdb stored on SSDs for several months. The last few days we’ve been seeing a lot of alerts and spotlights saying that we have high IO waits on those tempdb files. The IO waits are as high as 500 to 800 milliseconds. Is this a high value? I’m new to using SSDs with SQL Server and I admit that I just don’t know what high is in this case. Any thoughts?”

Doug Lane: It’s high but how frequent is it? Because if you’re getting an alert that like once a day that you’re hitting that threshold, it may not be something you need to worry about too much depending on what it is that’s hitting it. So what you want to do is look at your wait stats and look at those as a ratio of exactly how much wait has been accumulated versus hours of up time. If you’re seeing a lot of accumulated wait versus hours of up time, not only will you know there’s a problem but you’ll also be able to see what that particular wait type is and get more information about what’s causing it. Then you can put that together with what might be happening in tempdb and possibly come up with an explanation for what’s going on.

Erik Darling: Yeah. I’d also be curious if something changed that started using tempdb a whole lot more or if maybe you might be seeing some hardware degradation just after some time of use.

 

What should I do when my audit stops working?

Jessica Connors: Question from James. He says, “I’ve installed a SQL Server audit and noticed it stopped working. Is there anyway to be alerted when a SQL Server audit stops or fails?”

Angie Rudduck: Is that the Redgate tool? Because I feel like Redgate had some auditing tool or encrypting tool that went out of support. When I was at my last place and we had to change over so I’m not sure what that is.

Doug Lane: If it throws a certain severity error then you can have SQL Server notify you of those kinds of things. But as far as like audit as a product, I’m not sure.

 

Will backup compression compress compressed indexes?

Jessica Connors: Then we’ll move on to J.H. Says, “When compressing all tables page option in a database does compressing its backup gain more compression?”

Erik Darling: Yes.

Angie Rudduck: Compression squared.

Erik Darling: Compression times compression. Are you really compressing all your tables to get smaller backups?

Jessica Connors: Is that really bad?

Erik Darling: No. It’s just kind of a funny way to approach it.

Doug Lane: I don’t know if that’s the purpose but…

Angie Rudduck: I think he has no drive space, tiny, tiny, tiny SAN.

Erik Darling: Buy a new thumb drive.

Doug Lane: Talk to Ben because he apparently has the budget to have new large drives.

 

Are there performance issues with SSMS 2016?

Jessica Connors: We have somebody in here that’s playing with SQL 2016. He says, this is from Michael, “SQL Server Management Studio 2016 sometimes goes into not responding status when using the object explorer window such as expanding the list of database tables. These freezes last around 20 seconds. Is there any known performance issues with SSMS 2016?”

Doug Lane: I found one. I was trying to do a demo on parameter sniffing where I return ten million rows of a single int-type column and maybe about half the time SSMS would stop working and it would crash and force the restart. So I think SSMS 2016, at least related to the RTM release, is a little bit flakey.

Jessica Connors: For now.

Erik Darling: Yeah, it might depend on just how many tables you’re trying to expand too. I’ve been using it for a bit and I haven’t run into that particular problem with just expanding object explore stuff. So how many tables are you trying to bring back would be my question.

Angie Rudduck: I was just about to say, we had that question last week or the week before about SSMS crashing when they tried to…

Erik Darling: Oh, that’s right.

Angie Rudduck: Remember? They were trying to expand their two million objects.

Erik Darling: Yeah, that’s not going to work out well.

Angie Rudduck: So maybe this is the same person, different question.

Doug Lane: I was going to say I think it might just be a little…

Angie Rudduck: Yeah. It’s brand new, what do you expect? It’s a week old. It’s going to be flakey.

Richie Rump: Something to work when you release it?

Angie Rudduck: No, come on.

Richie Rump: I’m just saying, it’s a crazy idea, I know. I have all these crazy ideas but…

Angie Rudduck: Unrealistic expectations, Richie.

Erik Darling: That would require testing.

Jessica Connors: Richie has never released anything with bugs.

Angie Rudduck: Who needs to test things? I did have a client recently ask me what test meant when I was talking about test environment.

Jessica Connors: I know, what?

Richie Rump: What’s this test you speak of?

Erik Darling: Just for the record, Richie wipes cooties on everything he releases.

Angie Rudduck: Kiddie cooties.

Doug Lane: All right, looks like we’ve got two minutes left. Lightening round, huh?

[Group speaking at the same time]

 

What’s the best SQL Server hardware you’ve ever worked on? And the worst?

Jessica Connors: Question from Dennis. He wants to know, “Tell me the best SQL hardware environment that you have ever worked on.”

Doug Lane: I would say when we went down to Round Rock last year. I got to play with I think it was a 56-core server, that was pretty fun.

Erik Darling: Yeah, I think my best was 64 cores and 1.5 terabytes of RAM.

Richie Rump: Yeah, I had 32 cores and 2 terabytes of RAM.

Erik Darling: Nice.

Jessica Connors: What about the worst you’ve seen with clients?

Erik Darling: Ugh. Probably an availability group with 16 gigs of RAM across them. That was pretty bad. And it had like one dual core processor. It was pretty, yeah. It was Richie’s laptop.

Angie Rudduck: Worse than Richie’s laptop.

Doug Lane: That sounds about like the worst I’ve seen is like dual core, 10 or 12 gigs of RAM.

Angie Rudduck: 500 gigs of data.

Erik Darling: I’ve had faster diaries than that.

Jessica Connors: All right, well, we’re out of time.

All: Bye.