Voice of the DBA

Tag: disaster recovery

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

  • Cloud Safe

    I saw a question recently from an individual that was trying to decide if it made sense to move their databases to the cloud. In this case, management wanted to move, but the technical staff had concerns about Disaster Recovery in the cloud. These are very valid concerns for any system, as technical staff is often responsible for data, regardless of who decides on the architecture of a system.

    The concerns got me thinking a bit. Is the cloud safer for DR? It’s certainly an “it depends” question, especially as the “cloud” isn’t necessarily the same thing for each of us. Some of us would use IaaS, with VMs in the cloud. Others might choose PaaS, with RDS or Azure SQL Database as a platform. Still others might want something like Managed SQL Instances, which is like IaaS+, or maybe PaaS#. I’m not completely sure how to classify this.

    In any case, your choice of cloud architecture can mean better or worse DR. The closer you are to IaaS, the more that you still have the same responsibilities that you might have inside your own data center. The difference is that hardware replacements or options are often quicker to procure, though perhaps with limited choice.

    If you choose PaaS, then you have different DR capabilities and responsibilities. Your vendor might handle some aspects of DR and remove the need for you to worry about hardware, or regular backups, but you might need to worry about other items. Your vendor might give you PIT recovery, but you might not want a database replacement in a busy system, especially if you’ve processed a few thousand transactions since someone ran that UPDATE without a WHERE batch. In that case, perhaps you want to ensure you can restore your database elsewhere, or you have other options.

    Many of us know that managing systems is complex work. Not every environment can be handled in the same way, and we often implement exceptions in both technology and staff knowledge. Ensuring your application and environment can recovery from a DR situation often requires detailed knowledge of both requirements and capabilities of the environment. While I’m not afraid of migrating to the cloud, I’d want to be sure I was prepared to answer questions from management if there are issues. After all, they’re going to look to me, not some vendor, for answers.

    Steve Jones

    The Voice of the DBA Podcast

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