[Video] Office Hours 2017/05/17 (With Transcriptions)

This week, Erik, Tara, and Richie discuss enforcing password expiration for SA logins, keeping things in sync between servers, development/prototyping missions of on-premise vs. cloud, ways of backing up databases, and more.

Here’s the video on YouTube:

You can register to attend next week’s Office Hours, or subscribe to our podcast to listen on the go.

Enjoy the Podcast?

Don’t miss an episode, subscribe via iTunes, Stitcher or RSS.
Leave us a review in iTunes

Office Hours Webcast – 2017-05-17

Should I enforce password expiration on the SA account?

Erik Darling: Richard wants to know, “if there are any issues with enforcing password expiration for SA logins; like something may stop working when the password expires or auditors want it turned on global.”

Tara Kizer: I don’t like it for accounts, or as the application, service count, because you’re going to have to set a reminder to fix these before it expires because you don’t want, you know, for it to expire at three in the morning on a weekend, and you know, suddenly somebody’s got to work on it. So I don’t like it, and at least when I’ve had auditors at previous companies, you can push back on those auditors and say we’re just not going to do this one.

Erik Darling: Yeah, for me, you know, it’s less of an issue because I don’t like letting anyone use SA anyway, I let apps use it, I don’t like anyone losing it. So like [crosstalk]

Tara Kizer: I guess the was specifically the SA login and I jumped into just, you know, authentication of – SQL authentication.

Erik Darling: Yeah, like if it was domain accounts I would say go to hell because what’s that going to change, if it’s a domain account? But for SA, like, I mean, I’m surprised that the auditor remark was that they want a password enforced when they usually just – usually auditors are like, we want this disabled.

Tara Kizer: Exactly, I don’t even use the SA account so that’s fine if it’s going to expire. Usually, we either disable it or rename it, so it’s a different name at least, but yeah disable it.

Erik Darling: Have like a proxy SA account [crosstalk] has a weird name, I don’t know…

Is anything cool happening at Google IO?

Erik Darling: Wes – well that’s not a SQL question, “can we spend the next 45 minutes praying to the Google gods for awesome stuff to be announced at 10PST?” Sure, you can spend [crosstalk]…

Tara Kizer: What’s going on? Is there a conference or something? What’s happening in the next 45 minutes?

Erik Darling: I don’t know, I’m unaware. Alright, it’s going to be a quick Office Hours.

Should I change this global temp stored proc?

Erik Darling: Kirk, Kirk is asking the question that he asked yesterday on dba.stackexchange, Idera analysis is suggesting that he use tempdb and he makes some ANSI set commands and then he alter a global temporary stored procedure. Kirk, Tara and I both left comments on your Stack Exchange question asking for more information. Please [crosstalk] I can’t tell you if you should make that – because I’m pretty sure it’s dynamics, that’s what those pound, pound ZDP things usually are, and unless you tell us what the rest of that stored procedure does, I don’t know if I can tell you to change it. It’s a silly question.

Tara Kizer: Probably ignore Idera here unless you’re in control of the software that is upgrading these because you’re not going to be able to make the changes.

Richie Rump: It’s GP dynamics.

Erik Darling: It is, so I don’t really know what you’re going to get out of changing that, what’s it going to do?

Tara Kizer: Probably can’t even change it.

How do you keep things in sync between servers?

Erik Darling: Michael says, “I have an ETL process that runs on two servers independently, each row gets an ID, what ways have you guys used to keep these in sync between two servers?”

Tara Kizer: I haven’t ever needed to keep things in sync between two servers, besides reporting and things like that, but you’re talking about something separate, but you know. If it’s an ID, it’s an identity column I suspect you’re referring to, the, I mean, what people usually do is they reserve identity values on one server and then reserve another set of identity values on the other server. Like negative numbers for one server and positive numbers, and then to keep them in sync, you just, you know, replicate between the two, bi-directional replication between the two, and there’d be no collisions.

Erik Darling: Yeah, I’d be curious what the use case is to have IDs synced by UTL processes on two servers. That seems like something that should be resolved during the, whatever, L to main data source you’re going to end up with. It doesn’t [crosstalk]

Richie Rump: I’ve always worked on systems where we have a single point of truth, right, and then that gets federated to all the other systems. Now, we may have one system and it may be on multiple servers, right, but that bit of data’s truth is on that server, and then that gets sent around all over the place wherever it needs to go. So yes, having it – is that for speed, I guess, maybe? Maybe there’s a lot of data coming in and you’ve got it being processed in two separate systems, then you need to zipper them back up, and at that point, what’s your alternate key to align them back up again? That’s about as best as I could gather for you, man.

Erik Darling: Yeah, I don’t even know what – since it’s running, it’s ETL running in two separate places, I don’t even know, like, how you would resolve, like what the resolution would be if IDs were mismatched, like what would happen [crosstalk]…

Richie Rump: well typically, I mean, you’d go to the natural key, right. The natural key would tell me if it’s the same or not, but not all data has a really good natural key, and if you don’t, well how am I going to zipper that up in with using some sort of made up ID that’s out of the ether of SQL Server?

Erik Darling: what’s the worst data you’ve ever dealt with? Coming up with something like relations for or something, what’s the worst data you’ve ever had to deal with?

Richie Rump: I think – I had to deal with a bunch of – like large data, like 60TB stuff, but that wasn’t bad in itself. I think the worst data I had to deal with was, I was working really early in my career, so it had to be over 20 years ago, and it was weather data coming from the government. And, you know they – I was given this blue book, right, this thin blue book. It looked like an exam book, and it had all these formats in it, and we had to figure out which format that you’re dealing with. And it was a ton of just flat-file data, and it didn’t even have an end-of-line, you know, the whole deal, and it’s…

Erik Darling: Yeah I’ve dealt with healthcare data like that, that was awful. It’s like the healthcare encoding, I forget what it’s called but there’s like specific encoding that you have to use for stuff that’s miserable, and when I was in market research, would get all these, like, this time zone and zip code information that was just, like, a nightmare. Like FIPS code and DUNS code…

Richie Rump: The codes are a nightmare.

Erik Darling: Oh it’s awful.

Richie Rump: The codes are a big problem of why we have our health problems today.

Erik Darling: Yeah, like all the electronic records – I don’t want to touch you, you’re gross.

Richie Rump: You gross, I don’t want to touch you.

Erik Darling: It’s all gross. Let’s talk about something else, let’s stop talking about gross data.

Are you celebrity DBAs?

Erik Darling: Michael asks, “he would love to hear the thoughts of the Office Hours celebrity DBAs”…

Richie Rump: I’m not a DBA [crosstalk] I am not one.

Erik Darling: Yeah, we don’t have any DBAs or celebrities.

Tara Kizer: You’re now a celebrity, people in Poland saying hi to you and…

Erik Darling: Oh, that’s one guy from Microsoft, that’s not people in Poland.

Tara Kizer: You’re not celebrity level. Richie and I still have to work towards that.

Richie Rump: Yeah, yeah…

Erik Darling: [crosstalk] Just blog more and you’ll soon reach the very heights of SQL Server…

Richie Rump: What I blog about people don’t want to hear, it’s like I get like ten comments. Brent is does, like, some crazy, you know, hey what do you think about this, and all of a sudden there’s 100 comments coming in.

Tara Kizer: you’ve got to leave a question at the end, that’s – some of mine that have a lot of comments I’m, you know, asking people.

Richie Rump: I don’t want to ask people anything. What do you think about Entity Framework? I don’t – I know what you think, okay, I know, I don’t want to hear it.

Erik Darling: That’s like 50 angry responses, no one [crosstalk]

Richie Rump: Only? Wow, it’s a good day.

Should I develop apps in the cloud or on-premises?

Erik Darling: Alright, let’s see here – anyway, forgot what I was doing… Michael says, oh boy, let’s see, “implementing SQL Server via on-premise database platforms versus Azure or cloud-based database platforms, things like development prototyping issues, lifecycle costs and administrative DBA considerations. Jeez, that’s like seven books.

Richie Rump: At least.

Erik Darling: That’s a lot. Richie, what would you say about the development and prototyping issues of on-premise versus cloud, if anything? You can tell me to screw myself if you want to…

Richie Rump: Well, off camera, because I don’t want to say it. It’s different, right. So it’s significantly different. Not happy, happy, I’m trying to click not happy, but…

Erik Darling: Tell me more.

Richie Rump: It’s – so in the cloud, you’re essentially, if you’re dealing with a pure cloud application that you’re going to build in there and you’re talking about services talking to one another, and there’s a whole different philosophy that you’re going to have to go and develop your application. If you’re talking about taking an application and then setting up some VMs and having that run in the cloud, I don’t consider that a cloud application, I consider that an on-prem application that’s running in the cloud. So there’s a lot of things that we say, it’s in the cloud but it’s not really in the cloud type stuff.

I haven’t really messed with SQL Server database a whole lot. I’ve actually messed with Postgres in the cloud a lot more than doing SQL database. So I’m going to have to punt over to you guys about some more of those questions.

Tara Kizer: All I know is that the – our clients that are in the cloud are having severe performance issues, especially severe slow I/O. They’re not spending enough money on, you know, what they’ve selected for their server and are experiencing pains because of it. It’s not cheap to get really good performance out of the cloud.

Erik Darling: If performance is your number one consideration, you should – you need to either be prepared to stay physical or spend a lot of money on upsized cloud instances, especially for what Tara’s talking about. The stiff that is at the biggest premium up in someone else’s computer is storage and networking. So as soon as, you know, you need to write data to disk or you need to move data from your server somewhere else, you can run into some tremendously slow speeds from both of those, avenues I guess we’ll call them. Just because it’s, you know, the cloud disk if you want, you know, a good fast storage… You know, like when we were messing with Google compute, it’s not only tied into the type of disk but also the size of the disk. So like a bigger SSD means it goes faster, like if you spin up a 50GB SSD you get worse than, like, SATA speeds. So there’s a whole lot of stuff you have to consider. It’s a little bit easier if you have a current physical workload that you can compare it to and compare metrics to and know. That makes it a little bit easier, but if you’re starting from scratch, then you know, you’re just going to have to be prepared to size-up regularly.

Richie Rump: Yeah I think, you know, if you could find a small application and then bring it up to the cloud and then do some learnings on that, maybe if it’s an application that’s not even in production, or whatever, and so that you can say how will this application work in the cloud. It’s really hard for us to understand if the cloud is right for you without understanding what your needs are. Does it need to be fast, does it – how much traffic does it get, I mean what’s the performance? I mean, there’s a lot of stuff that goes on here, and since you run the app and you run the system, probably best to get into it and figure out, hey, will the cloud work for this app or will the cloud work for another app? Is it worth for us to start prototyping this stuff out? And then you can – there’s hard numbers, right, so how much is this costing on-prem versus how much would this be costing us in cloud? There’s a ton of stuff there that, you know, needs to be sifted through.

Erik Darling: Yes, you know, and another thing that comes up always is staffing complexity, you know, up in the cloud depending on how you choose to, you know, provision things. You could either have, you know, the cloud taking care of backups and, you know, high availability for you and all that stuff. But I don’t know if that meets your needs, because, you know, if you need to offload reporting or something else then you still need to come up with your own sort of solutions there.

Richie Rump: Yeah, it’s still in its infancy, I mean, there’s still a lot of growing pains in the cloud. It’s a lot easier if you’re Greenfield development. If I’m building something new in the cloud, things get a lot easier as opposed to I have something old and I’m bringing it up. We could code around a lot of different issues because we’re actively developing as opposed to I have this thing, this app, that was built to have a server right next door to it, right, talking to one another, and now I’m bringing it up into the cloud and everything’s going to crap. Well, those servers aren’t near one another, that’s just kind of the way it works out there. And they may not always talk to one another, and you know, if a switch goes down in your data center, everything goes crazy. If a switch goes down in the cloud, they yawn, you know, and they move on.

Erik Darling: Yes, cool.

Is there a better way to do backups than the backup command?

Erik Darling: Renee asks a question that I find particularly odd. I think Tara’s going to screech at this one too. “Do you know about a way to backup databases besides SQL Server built-in functions, stuff like copying data files and log files and pasting them?”

Tara Kizer: What are you trying to do?

Erik Darling: Stop, put the database down, stop, I’m begging you. That’s stop with an A and 15 H’s, “Stahhhhhhhhhhhhhhhp.”

Tara Kizer: Whatever backup software you’re intending to use, make sure it’s VSS aware so that it does a, it quiesces the database files and when it does the snapshots so that the data files and the transaction log files are at the same moment in time so that you’re not doing a dirty snapshot. Also SAN snapshots, but again, it needs to be quiesced.

Erik Darling: We’re not even there yet. Renee is asking about copying and pasting data and log files.

Tara Kizer: I know.

Erik Darling: We’re not even at the [crosstalk] part yet. So a long time ago I inherited a process that was an archival process. And the archival process would go through and it would find databases no longer in use on this awful clown car of a server that had upwards of 5000 databases on it. It would take them offline and then it would copy and paste the data and log file – it wouldn’t copy and paste, but it would use an SSIS-like package to move stuff over.

And there were times when the data transfer would be interrupted or go awry in some other way, and so one of the files would be incorrect, and so you wouldn’t be able to bring the database online, because say like a data file would have some issue inside it or a log file would have some issue inside it. And so I really can’t recommend enough to not do that, at all, ever. Like, aside from the permissions issues nightmare that you face trying to do that, you can also screw yourself up in a lot of different ways. Even, you know, if you wanted to do something like zip the files up and then move them, it’s still a heck of a problem. You’re really – take a backup or, you know, use backup software from a known vendor who does things reliably. Don’t copy and paste, please.

Richie Rump: Keyword, reliably.

Erik Darling: Yeah, because another problem with copy and paste is if you have large databases and you use copy and paste, that data has to buffer somewhere and it’s going to be on your server. So you’re going to have this horrible RAM nightmare when you copy and paste. [crosstalk] It happened to me once using PowerShell. I used a move item command to try to move a 5TB database from one drive to another. Do you know what happened? The server ground to a halt for about 45 minutes. Nothing else going on in there, that circuit was closed.

Tara Kizer: [crosstalk] Snapshot or something?

Erik Darling: We didn’t have that available to us, unfortunately.

Richie Rump: So you’re telling me it’s – you ran, you ran so far away?

What’s your second favorite Flock of Seagulls song?

Erik Darling: I did, I did, I ran all night and day. What’s your second favorite A Flock of Seagulls song?

Richie Rump: They had something other than one?

Erik Darling: Yeah.

Richie Rump: Really?

Erik Darling: I swear to god. Well Ah-Ha is a real tough one to come up with a second favorite for. A Flock of Seagulls is easy; an astute observer of new wave music like yourself, Richie, should have a very easy time with your second favorite A Flock of Seagulls song.

Richie Rump: I’ve been in alt-rock the last two years, and the Hamilton’s soundtrack, so – bet everyone knows that.

Erik Darling: You have a new wave playlist, you showed it to me and we argued about U2 being on it.

Richie Rump: [crosstalk] That’s the early U2…

Erik Darling: I don’t know about that. But no second favorite Flock of Seagulls – Tara, how about you?

Richie Rump: I can’t think of one.

Tara Kizer: I love that genre of music but I don’t have the bands memorized, so I’d have to look up what are their songs. I know the band name and I definitely know their music.

Erik Darling: Alright, so Space Age Love Song is an obvious second favorite, in case anyone needs to know, in case anyone is curious out there, that’s the second best A Flock of Seagulls song.

When I cancel a query, do I need to roll it back, too?

Erik Darling: Alright, Alex Ozar, wow, he is a [crosstalk] Ozar. He executed a long complex query with a BEGIN TRAN and a rollback in management studio and he clicked cancel – yes…

Tara Kizer: he’s asking whether or not he has to issue the ROLLBACKTRAN command or if the cancel would roll it back.

Erik Darling: The cancel should kill the transaction and roll everything back, as far as I know.

Tara Kizer: You’ll know if you try to close the window. Try to close the window and SSMS is smart enough to know whether or not you still have an open transaction.

Erik Darling: Yes, or you could run sp_WhoIsActive and see if you have any – or if you’re too lazy to download WhoIsActive you could run DBCC OPENTRAN. [crosstalk] Like the 90s, just like the 90s.

Richie Rump: Or you could just run ROLLBACKTRAN yourself?

Tara Kizer: Yeah, go ahead and run it. It will just error if you don’t have an open transaction.

Erik Darling: Alright, let’s see here. Ronnie is asking something about logins. Ronnie, that’s a better question for Stack Exchange because I don’t see an error message or anything in there, so you’re just kind of asking us to blindly troubleshoot your login issues, which is impossible, sir. Unless it’s an SPN issue, in which case, go look for SQL soldier SPN things and you’ll find good information.

Does SQL Server need the log files after a clean shutdown?

Erik Darling: Nick is asking, “when SQL Server stops cleanly, is everything in RAM flushed to data files such that the log files don’t contain anything important anymore? Just curious, I promise I’m not backing anything up with copy and paste.” Yeah, I believe part of the shutdown process is checkpoints, right?

Tara Kizer: Yeah, so any uncommitted transactions in the transaction log file are rolled back and anything that was completed get applied. So, but if your transaction log files still has the transactions in them, because that’s going to be dependent on your recover model and if it is full or bulk-logged, then it’s going to depend on your log backup frequency. It’s not necessarily about SQL Server stopping and restarting; that stuff still exists in there.

Erik Darling: Yes, so one kind of interesting thing you’ll see when you start up SQL Server is, if you go in the error log and you look at the very beginning, you may see a whole bunch of messages like, X amount of transactions rolled forward in this database, and stuff like that. So that’s how you know SQL is keeping track of what’s going on. That’s all I have to say about that; I got nothing else on that one. You guys?

Tara Kizer: No.

Richie Rump: No that’s – oddly enough that’s all the questions we have too.

Erik Darling: That is, alright, so unless you guys really want to hang out and babble, we can just call it a day here.

Tara Kizer: Sounds good.

Erik Darling: Alright, cool. Thanks everyone for showing up, we are leaving five minutes early because we have no more questions, but thanks for stopping by and we’ll catch you next week.

Tara Kizer: Alright, bye.