Voice of the DBA

Tag: backup

  • Wow. Just Wow

    Wow. Just Wow.

    Yes, I meant to use a capital letter there. The Red Cross’ Blood Service in Australia had database backups on a website that anyone could access. That means that anyone could download the backup, which contained PII (Personally Identifiable Information) about donors. This was a mysql dump file, which is shockingly easy to restore, so anyone could have read the file inside of an hour.

    To be fair here, this wasn’t the Red Cross’ fault. A partner had put the file on a website, perhaps for developers or an analyst to download, but the web server had directory browsing available and was serving data on a public IP. That’s three major faults, and perhaps a fourth since the backup file wasn’t protected at all.

    How many of us have innocently taken a backup, put it in a location for someone else to download inside our company, and not thought anything of the action? If you have done this, would you know if the file were accessed and downloaded by the wrong person? In most cases we wouldn’t because the correct person might download the file as well and we wouldn’t think anything had ever gone wrong.

    That’s a problem with data. We don’t get necessarily notified if someone makes a copy. We certainly have no way of knowing if the backup files we create are ever restored by an unauthorized user, much less just copied. We often just assume that data at rest is just at rest and isn’t ever mishandled without our knowing about it.

    This is a good reason why we should be practicing security by default in most every case. All our backups should be protected at the very least with passwords. Even if everyone in the organization knows the password, some random person that might steal a laptop or browse an unsecured web server won’t know it. The password also shouldn’t be easily guessed, and if possible, the data should be encrypted. Always.

    What’s more, I would hope that we would use secure ways of moving information around with our fellow employees and partners. I know Dropbox is a very convenient and tempting mechanism to use, but is it really appropriate? It might be good enough if you’ve protected the data in other ways, but I’d be very, very wary of using any public service that doesn’t allow for some sort of security that can be tied to individual, authenticated users.

    No matter how you choose to protect and transfer database backups, I’d also be sure that you don’t leave the information there indefinitely. Have expiration times where files are removed. All encryption and protection can be cracked, given enough time and resources, so don’t leave your files there forever.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Why We Need Backups First

    One of the things I show when I talk about TDE is that the backup of the certificate is very important. When you create a certificate, SQL Server warns you right away that a backup of the certificate is needed. Following Murphy’s Law, if you continue on with enabling TDE, right after the encryption is completed, your master database will become corrupt and you won’t be able to get to your data. It just takes a few seconds, so back up your certificates.
    I was reminded of this while reading a short piece on some police body camera footage being lost. I don’t want to focus on the police actions or cameras or anything but this statement: ” A backup system had been purchased but hadn’t been set up. “
    That’s a fairly egregious breach of duty for some IT personnel in this case. The system was installed in 2009, and this event apparently occurred during a software upgrade in 2012. While I understand a backup system might not get configured immediately, I think 3 years stretches the limit of an acceptable delay. However, even if the backup system were not in place at the time of the software upgrade, wouldn’t a backup before upgrade be required? Isn’t that a standard practice?
    There have been other reports of similar issues, which is disconcerting. It appears the use of technology for archival of data isn’t well developed, or properly understood by the IT staff in charge of the process. Of course, this happens in plenty of enterprises and other organizations where IT staff seem to think that backups are important in and of themselves. In case you’re confused, they are not. Restores are the important things to have, which means you must verify your backup process allows the data to be retrieved.
    Many of us have scripted methods that practice this regularly, refreshing other environments, restoring databases for DBCC, or some other task. Few of us actually practice this ourselves across all databases on a regular basis, ensuring our skills for tail log backups, NORECOVERY and STANDBY restores are fresh and ready for emergencies. However, we should not take this task lightly. It is important that we are able to recover data in almost all situations.
    If we can’t do that, then we are truly disregarding our duty as the custodians of data.
    Steve Jones

  • Triple Check Your Restores

    I used to work at a nuclear power plant. Not really in the plant, but as a network administrator in the building next door to the plant. Probably a good thing since I struggled to get to work on time and everyone going into the plant had to go through a metal detector like that at most airports. My tenure might have been shorter if I had been late every day to my desk.

    However, there was one thing that got drilled into me by each person I knew who did work closely with the power generation side of the business. Everything was (at least) triple redundant. Not only did they want a backup for a component, or a system, but they wanted a backup for the backup. There was a limit to the paranoia, but in critical places, where radiation was concerned, we had multiple backups. One notable, and impressive, area was the power for the control rooms and pumps. In addition to batteries for short term power loss, there was a large diesel generator for each of our two reactors, plus a third that could take the load if either of the first two failed. Those were impressive engines, each about the size of a very large moving truck and jump started with a few dozen large canisters of compressed air that could spin the crankshaft in a split second.

    This week there was a report that the database for the US Air Force Automated Case Tracking System had crashed. Apparently the database became corrupted, which happens. However, the surprising part of this story is that the company managing this system reported they didn’t have backups and had lost some data going back to 2004. They are looking to see if there are copies in other places, which I assume might mean exports, old backups or something else, but the reports make this seem like a completely unacceptable situation. I assume this is an RGE event for a few people, perhaps all of the staff working the system.

    I was reminded of my time at the nuclear plant because we had a similar situation. We didn’t lose any data, but we found a backup system hadn’t been working for months. Those days we had a tape drive system that automatically rotated 7 tapes. I think this would last us about 4 or 5 days, so it was a once a week job for one administrator to pull the used tapes and replace them with new ones. We had a system where tapes were used 4 or 5 times before being discarded, and our rotation had a tape being used every 3-4 months. However, the person managing the system rarely restored anything.

    One day we decided to run a test. I think this was just my boss giving us some busy work to keep us occupied but in a useful way. When we went to read a tape, it was blank. Assuming this was just a mix-up, we grabbed one of the tapes from the previous day and tried it.

    Blank.

    At this point, my coworker turned a bit red and started to stress. He was in his 40s, with a family and mortgage. I was in my early 20s and had no responsibility here, but I could appreciate his concern. We frantically loaded tape after tape, even looking at the oldest tapes we’d just received from our off-site provider. None were readable, and most were blank. We nervously reported this to our boss, who had us request a sample of tapes from off-site storage going back over 6 months.

    Eventually we realized that we hadn’t had any backups for about 4-5 months. The tape drive had stopped working properly, hadn’t reported errors, but dutifully kept retrieving files and rotating tapes each week, unable to properly write any data. No databases, no email, no system was being backed up.

    A rush order to our computer supplier had been placed the first day to get us two working tape drives that we manually loaded tapes in each day, and checked them the next morning. Eventually we replaced the drive in our tape leader and instituted random weekly restores to be sure we had working backups. I’m not sure if the plant manager or upper IT management was ever told, but I’m glad we never had to deal with a hard drive crash during that period.

    Backups are something we all need to perform. I note this as the #1 thing a new DBA or sysadmin should perform on systems. However, backups are only good if you can read them and actually restore data. I’ve made it a point to regularly practice restores as a DBA, randomly restoring backups with diffs, logs, or to a point in time. Not only do I test the backup, but I test my skills. I’ve also tried to keep an automated process around that restores all production systems to another server to test both the restore as well as run a DBCC CHECKDB. Corruption can live in databases for a long time. It flows through backups, at least in SQL Server, and this is something to keep in mind.

    I’d suggest that you make sure you ensure that your backup plan is actually working by performing a few restores. Build an automated process, but also run some manual restores periodically. You want to be sure that you can really recover data in the event of an emergency.

    Steve Jones

  • The RGE

    I first heard this little acronym from Grant Fritchey (b | t). He used it when talking about backups and restores, and I like it. However I realize that I’ve never actually noted what it is, so a short blog to do so today.

    An RGE is a Resume Generating Event. This is usually when you make a mistake so egregious that you’ll be packing up your personal effects and exiting the building. If it’s really bad, such as releasing financial or other confidential information, you might be escorted out and someone else packs up your things. I’ve seen it happen, and it will shake you. Don’t do this.

    We talk about forgetting about backups, or writing bad code or some important task we often perform as causing an RGE. In my experience, that doesn’t happen too often. Companies usually have a fairly high tolerance for mistakes.

    However, that tolerance is usually extended only once. Don’t make the same mistake again. I’d also note that some managers can be very short tempered, and a single, large issue might be an RGE in their eyes.

    I don’t usually worry about causing an RGE, but I keep the acronym in mind. Especially when I do something that could affect the core parts of my organization’s business.