Voice of the DBA

Tag: disaster recovery

  • 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.

  • Pause and Backtrack

    One of the main functions for anyone that manages a database is ensuring that they can recover the system in the event of any issues. My view is that restores are the most important skill and task that need to be performed on a database. Since restores require backups, I’d then rank backups as a 1a important task. They’re a dependency and necessity to ensure that we can restore data. Having a set of the data, in a transactionally consistent state just feels critically important to me, over everything else..

    I saw this new feature from Amazon Aurora for their MySQL compatible database. You can use Backtrack to rewind your database to a previous point in time. On one hand that’s an amazing feature. Make a mistake, have an error, click a few buttons and get the database restored back to the minute (or second) when you made a mistake. On the other hand, if you delete a table, do you want to roll all tables back to that point in time?

    This seems like an amazing feature. Amazon takes some of the hassles of managing some backups backups. You determine how far back you want to go, in hours, up to three days. Depending on the activity in your database, they charge differently. To me, that’s interesting. It makes sense to me as a customer. I do more, they track more, I pay more. This also seems to be a way to capture more money for Amazon by cutting some of the consumer surplus that exists with flat fee pricing, which is something many of us prefer.

    The way this works is also different than Azure. The Azure point in time feature allows you to go back, but you can’t restore on top of your existing database. You’d need to restore elsewhere, then play the rename game or move data between databases. While that seems inconvenient, if you’ve ever had someone restore a local SQL Server backup over a database you needed, you might appreciate the safeguards of not allowing a restore on top of an existing database. While the process might seem like a hassle, this does help prevent mistakes during a stressful situation.

    Which of these do I like? I prefer the Azure one, though I’d like the restores to be more granular than a minute. The reason is that I rarely want to restore in a disaster over the existing database. In most applications I’ve managed, there are updates to multiple parts of the database. A mistake in one table doesn’t necessarily mean that data changed in other tables should be discarded. Even during deployments, when things go wrong, I’ve often just broken one set of tables and rolling back the entire database in a restore is painful. Usually I’d prefer to undo what I can and get the any missing data from a restored copy of my database.

    Perhaps it’s just me, but I find the idea of allowing clients, or even many technical people, to easily roll back an entire database after a mistake to be very dangerous. By the time we recognize the mistake, verify data, notify others, we might have lots of changes in many tables. Abandoning that data for the sake of convenience is something that’s unnecessary. I also worry many people trying this feature don’t think through the implications of rolling back an entire database. If you feel differently, let me know. There are cases this is certainly helpful, but I think I’d rather have a “restore to a new db and rename both” automated task instead of AWS Backtrack.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Reading Through the Logs

    Have you ever tried to read a transaction log? I mean used a query against fn_dblog() to read data and try to reconstruct what happened with a transaction or a series of transactions? It’s a cumbersome process and takes a lot of knowledge, practice, and most importantly, patience. It’s not something I’d want to wish on anyone. There are a few products to help, but no one really does this that often, and it’s almost easier to just change some data by applying your own manual fixes.

    If you’re in the UK, you might have heard about the TSB bank meltdown. If you’re unlucky, you’ve been affected by the outage, which has been going on over a week as a system cutover failed. You can read some reporting about the plans, the rollout of some services, the initial problems , and the warning signs. If you go to the end of the third link, you’ll find this awesome tweet. Beans and bombs, he he.

    There are a lot of potential issues that we could discuss here. I’ve been a part of a failed rollout and I have sympathy for the IT staff dealing with this. The thing that I wonder about is the data. With the magnitude of customers (millions), the seemingly long list of places where things failed (notifications, scheduled payments, inquiries, etc.), and the rate at which people can bang on a system from their phones and various applications, how much data has been mangled and altered?

    I’d guess a lot, in which case, we aren’t just talking about updating rows on the basis of someone’s authority. Whoever is tracking through data needs to essentially read transaction logs, unwind the actions where data was converted incorrectly and then (potentially) subsequently changed. Then they need to work out the reversing entries. The database needs help from DBAs, developers, and probably financial staff to understand why things are in a state. Why are closed accounts are open, why payments are scheduled years in the future, where balances are, and more. With the possible cross contamination of data between accounts, this is an area where TSB needs to be thorough and careful.

    Data is important in today’s complex, interconnected world. There are certain areas where data problems are highly disruptive and can have lasting repercussions if mistakes are made by the data processors. The financial and medical areas certainly fit in these categories, and it’s sad that people are going to go through pain and problems that may affect them for years. Hopefully TSB will get things working soon and data issues corrected. If there’s one thing I learned from this is that for certain issues, I need to ensure I have my own paperwork to prove my side of the story.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Reliable DBA

    Many of us would consider ourselves to be reliable at work. Our employers count on us, co-workers may assume we’re handling certain tasks, and it’s human nature to think that we are meeting our obligations and responsibilities. Maybe not 100% of the time, but certainly I know my goal is to complete tasks I’ve committed to, on time, and to avoid dropping any balls that I’m juggling. I think I am mostly successful here, but certainly am late or forgetful about things at times.

    However, as a group, department, or even a set of services, does your organization think highly of the databases? Are your systems meeting their SLAs for performance and availability? If not, does your group respond in a timely manner? We sometimes think that we are, but without feedback and communication can we be sure? This certainly could be taken the other way, with other groups constantly complaining about your performance, even while you are meeting your commitments.

    I ran across a talk from Uber on reliability. It’s more of a high level architecture talk about distributed systems and being able to detect and respond to issues. Certainly Uber works at a rate and scale that few of us will reach in our organizations. Add to the highly public nature and real time demands, and reliability is extremely important for their business. Mistakes can have dramatic hard dollar effects instantly, and there is a lot of pressure on their staff.

    For most of us, our databases do continue to become more important, and even when we are meeting our SLA commitments, are we providing reliable service from the entire staff? Is information being shared, with root cause analysis or retrospectives that help knowledge transfer among all of the individuals that might respond to an issue? Are you dependent on a superstar that must be called in to solve issues with the database, network, or storage?

    I’ve been the main person on call, the expert for a system that received calls on weekends, vacation, and other downtime. It’s no fun to be in this position, and it certainly distorts work life balance, not to mention upsetting the rest of my family. My goal is to be there if necessary, but train others so that they can provide a consistent, similar level of service to customers and clients if need be.

    Becoming too dependent on any one person isn’t much different than becoming too dependent on one server or disk drive or network cable. At some point you’ll have a failure and that item will no longer be available. If you don’t have a spare, you’ll have regrets.

    Steve Jones

    The Voice of the DBA Podcast

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