The Stedman Solutions team shares ways to reduce the impact of server corruption.
Transcription:
Steve Stedman 0:10
All right. Ways to reduce impact are backups, good backups, frequent backups, and off site backups with that, it’s not just SQL backups, but also virtual machine and system backups, not just backups, but backups that have been confirmed and tested. And tested recently. There’s one, one system I work with where we’ve got a, all the backups go off to one server, and then that server runs and does a random restore of those backups. And then does a check DB after it does the random restore of those and confirms that things are good. And if it ever runs into an issue, it alerts and it even applies transaction logs when it’s doing that. So that’s an example of knowing the backups work, because it’s going to alert us if the backups are broken somehow.
Derrick Bovenkamp 0:55
Yeah, and you know, that’s another good point. And there’s a lot of software that will do that for you know, veem will do that for you these days. The other thing I want to drive alone is, you know, have different types of backups, just because you have veem, or zurdo, or some other backup product or comm vault, backing up your virtual machine. Don’t think that that’s good enough for your business case, you don’t really explore it. In most instances, you want those virtual machine backups, and you want SQL Server Backup separate on top of that. And that gives you options. And options is what you need anytime you experience, you know any kind of system outage corruption or not.
Steve Stedman 1:39
So an example of that would be if you just had like a daily full virtual machine backup. And then you had SQL Server transaction log backups. If your full backups were happening at like 11pm, but you had corruption at three o’clock in the afternoon, if all you had was the full virtual machine backup that 11pm might be your only option to recover. But with those transaction log backups, you could do a recovery to that 11pm point, and then replay, do the sequel full backup and then replay the transaction logs up to moments before the corruption was introduced. And that may make the difference between losing half a day’s worth of business or not. Now, the importance of that may vary depending on how important your data is. But most people consider their data pretty important.
Derrick Bovenkamp 2:20
Yeah, and part of that testing goes along with I’ll mention it real quick, as you know, make sure that you don’t your backup systems aren’t fighting each other. And one of those is if you’re doing separate SQL backups with log backups, you want to make sure that your VM level backups are set to do a copy only and not truncate the transaction logs. Or you may very well feel like you’re getting great backups. And you got to restore them and realize that your transaction log has been stomped on, as Steve calls it, and there is no restoring except to the full.
Steve Stedman 2:53
Yep, so virtualization and storage. I mean, a lot of the times as a DBA. You’re thinking just about the SQL Server, but a lot of other factors go into this. And I know Derek, this is kind of your specialty, not mine.
Derrick Bovenkamp 3:06
Oh, yeah. So you know, we already talked about having notifications set up in SQL Server, you know, get those notifications set up on all your systems, get them set up on your storage, get them set up on your virtualization level, get them set up inside of Windows, and most of the server vendors out there, they’re not only are they have notifications, if you set them up, right where they notify you, if you’re under warranty support, they notify the vendor. And you know, there’s one vendor in particular that I’m thinking about, I had a hard drive failure a few months ago, and dry failed in the middle of the night while I was sleeping, automatically. re automatically took action use the hot spare rebuild, rebuilt the raid, so there was no data loss. And I kicked me an email. So I woke up to the email. And then I even better woke up to another email from the vendor saying Hey, your array home phone, bad hard drive, where do you want us to send the replacement drive, and we’ll get to you within four hours.
Steve Stedman 4:12
That’s that’s so much nicer than waking up to just your drive being gone. And data missing?
Derrick Bovenkamp 4:17
Yeah, database. So get those and the other thing is, there’s, there’s a lot of, especially at larger companies, where you know, maybe you’re not you’re the systems administrator and DBAs are separate. So you may not have insight into those systems. So you know, go take that system administrator up to lunch. You know, become friends with them, make sure that they have insight into the systems. And especially most of the customers I work with, the data is so important. They can’t afford not to have hardware and software support warranties on their system. So you know, there’s there’s usually cases that maybe you don’t need the warranty because you have enough redundancy built in and you have other options. But you know if it’s if you rely on that system to keep the doors open in your business, and you go out of business, if it goes down,
Steve Stedman 5:16
have have good warranties on that and have some support. So you can call them when you are having a problem with that storage array or that server. And you know, if you’re working with your management team or your finance or your budgeting team, about paying for that support, you can use this some of the examples from our presentation here as maybe case points to prove how important it is to have that