Voice of the DBA

Tag: backup

Default Backups and Feature Awareness

Have you ever been asked a question about your software and thought that the person was asking about an obvious feature? Perhaps someone has suggested a feature to you and you answered that this was already in the product. Did any of you think your users were stupid or not very observant, or something else?

I’ve had that situation appear a few times, with me on different sides of the discussion. I watched someone ask a question that I knew the answer to, and thought, huh, why didn’t they know that? I asked a question about something in the Microsoft Azure world and got a quick answer, wondering why I hadn’t been able to find the solution myself, and that I shouldn’t have been cursing the Microsoft developers so much. I’ve had a few people do strange things at SQLServerCentral after the migration and wondered, why in the world…….? Then I remember that lots of people use the site differently than I do and I should expect that.

I thought about this while reading Joey D’Antoni’s piece on backups. It’s not hard to make backups. It’s not hard to set up a maintenance plan to run backups. It’s not hard to set up a history task to trim old ones. Heck, it’s not hard to implement Ola’s scripts.

The problem is all of these solutions require some knowledge.

I think plenty of people assume either the system backs itself up or their nightly file sweep by a central backup system will grab the database. Hint: it won’t. I think plenty of people think backups are someone else’s job, or they are in a hurry with an installation and assume they, or someone, else will return and set up backups later. Plenty of third party software may create a database, but never set up a backup process. We often don’t realize no backups are running.

That’s not good. While I like Joey’s suggestion, I’d go further. I’d have instance defaults set on install and an automatic, nightly full backup process set for all new databases with a week’s retention. I’d embed this in the DDL so the default is backups will be set on CREATE DATABASE, and you can use a switch to turn this off if you want to use some other backup process. I bet backup software, like SQL Backup, would even do this for you, turning off native backups when you turn on their scheduler.

I think backup is the most critical thing a DBA does, or an accidental DBA, or the accountant that installed Dynamics, or anyone working with a database. Next would be verifying things with a restore. Then comes security and the list goes on from that point. If you don’t have the data, because of ransomware, virus, bad hardware, or anything else, you are in a bad situation.

Please. Set up backups. If you find a server without them, inform someone in writing of the dangers and give them resources for creating backups. If you create databases as part of your software deployment process, set up backups somehow. Use a maintenance plan. Those aren’t great, but they’re better than nothing. There’s a reason why SQL Monitor has an alert for databases with no backup. It’s a critical one to have running.

Steve Jones

Listen to the podcast at Libsyn.

May 8, 2019
You Need Offline Backups

If you hadn’t heard about it, VFEMail may be dead. At least, that’s what the founder was thinking in this article. A malicious hacking incident took place last week, and though they’re back up and running, who knows if customers will stick by them, or maybe sue them out of existence. I wouldn’t be surprised as a large number of their infrastructure servers were wiped out by reformatting servers. These included mail servers, backup servers, and SQL Servers.

That’s quite an attack, and whether this was directed at the company or some individuals, a large number of people might have lost their mailboxes and previously stored mail that was in IMAP storage. This is the type of issue that is most likely an annoyance for individuals, but it would be potentially catastrophic for businesses. Imagine your small business hosted with them and all your mailboxes were lost with customer communications and who knows what else. Perhaps you could recover data or keep the business going, but it might be an issue.

Could this happen with a cloud provider like Azure O365, Google Apps or AWS? Possibly, and while I’m sure they have backups, I’m not sure how reliable those might be for the average individual or small business. This makes me worry slightly as I depend on GMail and wouldn’t even try to backup to few 100GBs of mail I have. I’m not even sure how to do it, though I don’t really keep anything in there that’s really important. In any case, I’d suspect that connecting and somehow wiping out Gmail servers, along with backups, would be very difficult.

This does make me think about a few customers I know that use online storage for backups. They assume that they will always have either a primary server or the online backup server/share/bucket/container and can download data. The problem is that online systems that connect to the primary can be accessed. If an attacker were to access one, they potentially could access the second.

The world seems to be moving towards more online storage, or in the case of cloud vendors, a reliance on snapshots. That might be good enough for cloud vendors, but it’s certainly not for any on-premise system. It’s likely that an attacker, possibly with insider help, would wipe out backups first, then primary systems. I’d always want some sort of disconnected offline backup of data, especially database servers. I have seen Murphy’s Law strike two systems at once, so an air gap between copies of data just feels prudent.

Steve Jones

February 16, 2019
Cloud Backup

I think that backup and restore are the most critical things for any database professional to master. Whether you’re a professional DBA, a developer setting up a system, or a seasoned DBA, I would argue having your data safe is the primary task. Security comes next, then performance, availability, and a number of other tasks that could go in any order, but if we don’t have a way to restore our data when hardware fails, we’re in trouble. And given enough time, or enough difference pieces and parts, something will fail. If you can’t restore a system when a problem occurs, that’s what Grant would call an RGE.

Over the years companies have moved to many different technologies to handle backups. Tape was common early in my career, but all disk systems, with de-duplication capabilities have become popular. I really don’t think about anything other than getting a second (or third) copy of data these days, so I can’t speak to any particular way of managing backups. However, for SQL Server, I do want the option to set full, differential, and log backups based on my RPO and RTO requirements.

There seems to be a new trend for companies that I ran across: they’re moving to the cloud. Here’s a short slideshow of some stats that show cloud use is increasing. This is a survey, so it’s not all companies, but the trends are clear. More data is being backed up, and it’s likely easier (and cheaper) to use cold storage in the cloud. That makes sense since it’s data that you expect you’ll very rarely need to use in a restore.

There are a couple of other interesting items I saw in the survey. The number of companies that are backing up more than 100TB grew quite a bit, even as the number of companies backing up < 25TB fell. That’s a sign that we’re capturing more data. Whether we need to, or whether legislation like the GDPR will get companies to trim some of that data, remains to be seen.

Another interesting item is that more companies are testing their DR plans. Fewer never test them, but the frequency is increasing with more companies testing quarterly or monthly. That’s smart as we become more dependent on computer systems. I know some organizations can’t roll back to paper, as we’ve seen in a number of airline IT issues. If you never test your DR plan, I hope that you don’t have an issue when you can ill afford to find another job, becuase you might suffer those consequences. Really, I hope that you actually know this isn’t professional and you start working on ways to test a restore of service.

More companies are moving to the cloud, and the resistance for security, cost, privacy, etc. reasons is going down. I think many of the concerns that both executives and IT professionals have had in the past are proving to be non-issues. This is especially true as more vendors institute government rated or more secure data centers as a part of their product offering.

If you don’t like the cloud, that’s fine. If you don’t know anything about it except rumor, guesses, or hearsay, I’d suggest you learn more. The cloud is likely coming into your career, so learn a bit about it. At least enough to give reasons why you don’t want to move your data there. If you do, you might be surprised that the cloud is not that bad a place to be, at least for some workloads.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

October 3, 2018
Testing BackBlaze Restores

The most important thing for a data guy is having a way to restore data. If security, performance, etc. are broken, those can be fixed is we have the data. If we don’t have data, does security matter? It’s important, but secondary.

When Crashplan stopped providing a multi computer setup, I needed to get a new solution for home. I asked on Twitter and then wrote a bit about my evaluation and choices. These were my particular requirements, and yours may be different. In any case, I decided to evaluate BackBlaze under their couple week evaluation and keep the system simple for both my wife and I. As a part of that evaluation, I tested restores.

Missing a File

I didn’t simulate a crash and restore all my files, but instead decided to test this while out of town. I was in the UK, at the Redgate Software office and wanted to check an expense report I’d submitted. I also wanted to get the latest expense report to add some items to it. I keep these on my desktop, but I haven’t setup a home VPN for access, nor would that have helped. My desktop was powered down for my 11 day trip.

I decided to see how BackBlaze was working. My first step was to connect to my account on the BackBlaze.com site. I logged in and saw my machine listed in the Overview. (I reshot these images later, so the dates aren’t shown)

Note: I didn’t have Backblaze software installed on my laptop.

I selected Restore and went to a new screen where I had a choice for the restore. I selected getting a ZIP file, but it’s nice to have other options for large files. If I were pulling back 2TB of images, I might just pay for a hard drive.

Below this, I saw my drives and I could navigate to select select files.

I also had options to see different versions of backups.

Once I found files, I picked the one I needed and started the restore. Just for a test, I picked a file on a different drive to see what would happen.

I went on with my day, and within an hour, I’d gotten an email. This let me know my restore was ready and I needed to securely sign in to get the download. I like that this was a sign in and now just a link to some random file.

I downloaded the zip file and inside were the files I’d selected. I unzipped them and filled out more of my expense report.

This was a smooth process and validates my decision to use BackBlaze. It’s easy for my wife, which she’ll appreciate, and I can easily access things on the road.

My trial goes until the end of June, but I’ll sign up for the service once I return home as this will work well for me.

July 5, 2018