Voice of the DBA

Category: Editorial

  • A New Recovery Tool for Your Toolbelt

    Those of us that administer databases and are responsible for ensuring the integrity and availability of data know that being able to restore systems is a core skill. In the SQL Server world, this should mean more than just being able to run a RESTORE DATABASE Sales FROM DISK = ‘Sales.bak’. Many of us would easily recognize that we should be able to use the WITH MOVE option. We should also understand when to use (and not use) NORECOVERY and STANDBY. Perhaps more importantly, we need to know the default option is RECOVERY.

    If we delve further, perhaps we could debate and discuss when a tail log backup is needed. Many might mention how to programmatically use FILELISTONLY and HEADERONLY to generate scripts.  A few of you would note that restoring databases means restoring logs as well and an understanding of the STOPAT option is important. Perhaps learning the ins and outs of restoring a filegroup is important in your environment. There are many nuances and options with restoring a database that a system administrator could learn.

    Apart from performing a restore, a careful DBA might think that we should try to avoid restoring databases if possible. Perhaps a DR system using Log Shipping can be used to recover data quicker than a database restore. Maybe periodic snapshots are valuable in recovering from those “whoops” mistakes. As our data sizes grow larger, we may need to develop more creative ways of recovering from user mistakes.

    Recently I saw the SQLCAT team had written a post on using Temporal Tables to recover from an “oops” mistake. If you haven’t looked at these structures, they are new in SQL Server 2016. Temporal tables, or system versioned tables, allow a user to view a row as of a certain timestamp, which can be especially useful for DML mistakes. Have you ever updated every row of a table to the same value because of a poorly written query? I have. A temporal table would have been handy in this case. Perhaps even more useful are the cases where a table is receiving regular inserts and updates, which can be very difficult to recover from with backup files.

    Certainly the majority of us don’t yet have temporal tables, and we might not even set up the option on many tables even if we were running SQL Server 2016. Over time, however, some of us will use these system-versioned objects, and perhaps we will run into a situation where recovery using temporal queries is more efficient than restoring an entire database. If that were to happen, wouldn’t you want to have some skills in this area?

    As SQL Server continues to evolve, many of the techniques and skills we’ve built across time will continue to work. There will be new ways of accomplishing work, based on features and enhancements that we ought to consider, especially when they might be more efficient than our past techniques. As you work with SQL Server, I hope you keep learning about the “new” ways that you might accomplish the tasks for which you’ve developed solutions in the past. Sometimes the old way might prove to be a better choice, but you won’t know that unless you learn, practice, and measure the effects of a new method.

    Steve Jones

    The Voice of the DBA Podcast

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

  • THE Database Engine

    Is there a “best” database engine? Is there one database system that you can use for all of your needs? The question is addressed in a long, but captivating, piece at O’Reilly. The author talks about finding an HTAP database, which stands for Hybrid Transaction/Analytical Processing. In short, the ultimate database that can handle your OLTP, ODS, BI and analytical needs, while also addressing all the other types of features most of us would want.

    Tl;dr No.

    The piece is worth a read in that it examines the requirements and features needed to achieve a high performing database system. This system doesn’t look at RDBMS over NoSQL or NewSQL systems, and does a fair job of evaluating why trade-offs are needed by many of the types of databases that have been used by companies in an effort to find a better platform. It’s almost a bit of a history of database platforms.

    What I like about this piece is that they look at the differences between startup companies and enterprises. The former is bound by legacy constraints, but more importantly, their failures are just inherently less impactful. For enterprises, however, there is a certain inertia, not to mention existing skill sets that make adoption of new technologies more difficult. There’s also that pesky transaction requirement. Some domains might not need these, but in some situations transactions are really important, and as the article states, “Transaction support was useful in certain cases in spite of its overhead.”

    Throughout the piece, the author seems to move back and forth in examining the advantages of moving to one platform over the other. We have certainly spent time trying to build large monolithic databases, then lots of effort to move data around to other systems. Who remembers the ODS/large data warehouse trend, which then fractured into further movement to “data marts” before it seems many people have moved back to a large single DW against which analysts can run queries?  Movement of data, gluing complex ETL (or ELT) pipelines together requires a lot of resources, and often these can be somewhat brittle, prone to regular, albeit small, failures.

    The nirvana is a single database platform, but I’m not sure that’s possible. All of the requirements we place on databases require some trade-offs. I don’t think anyone has found a way around the CAP Theorem, which will inherently limit the ways in which we can meet many requirements. However, I think we can “fake” the success of our platforms at times, especially since our end users don’t care how we meet their needs. Just that we meet them.

    The Azure platform seems to be moving in the right direction, in my opinion. There is a separation of storage from compute, which in database terms means a separation of data storage (the storage engine) from the query engine (where compute resources are needed). Certainly this isn’t complete or clean, but already in Azure SQL Data Warehouse, I can scale up or down my compute needs separately from the storage for my data. The Data Lake allows me to store disparate types of data, without specifying a structure, and query the bits from multiple applications. Even the ability to instantly “copy” a database in Azure SQL Database could be used to “fake” the instant replication of data to another system.

    Granted, there are issues with clients constantly querying databases and maintaining up-time (or switching clients to a new copy), with reconciling changing data across many clients, and certainly the management of all this data. However, as we search for ways to build the ultimate OLTP/ODS/BI/Machine Learning database platform, I’d like to remind you we don’t need to build a single engine. We just need to make our clients to think we did by providing a data source that appears to handle all their needs.

    Steve Jones

  • Local Database Issues

    There was a report that Spotify is writing lots of junk data to users’ local drives. Some people have noted GB a day or writes, even when they aren’t using Spotify for listening to music. Some debugging by users show that a local database (SQLite) is getting a number of maintenance calls to the database that are being repeated over and over. This appears to be a bug, and one that is hopefully being fixed. I know it hasn’t hit me, so perhaps there are more factors in play than just using Spotify.

    This does bring to mind a few things to me that we should be careful of as software and data professionals. The first is that while bugs will creep into our software, being able to deliver fixes is important. I’ve seen updates made to Spotify in a smooth fashion, one that doesn’t seem to require much effort from me. While I don’t usually get to decide when these updates will apply, and they can be annoying if I reboot and attempt to use the application, this isn’t a critical piece of software for me. For some of us, though, our users can be impacted if we don’t allow them the chance to choose when updates occur, or at least, when the final time is when the old version might no longer be supported (or stop working).

    The bigger issue, to me, is that the local database is being used in a way that users don’t expect. There’s likely a bug here, causing lots of additional activity, but what’s a normal level of activity? What would our users expect? Do they even know that we’re keeping data locally on their system? In many cases they might not care, or even assume we are, but we should disclose this to our users. Security concerns over data leakage or loss may be an issue in some environments, and it’s important that we ensure our users are aware, or at least they can find, information about the data our software may collect, store, and use.

    Data becomes more important all the time, and we are constantly finding new issues surrounding its capture, storage, security, and use. The way we even think about data is somewhat immature. Data ownership, protection, lifetime, and more are concerns. The problem is that many of us would struggle to articulate how exactly we want to handle the data that is related to our lives. In fact, we may have vastly different ideas on how different types of data are handled. Perhaps we don’t care about our musical listening habits, but we care deeply about someone learning about our reading history.

    If you develop software that uses a database, and many of you do, it would be nice to document (maybe self document with code) the ways in which data is stored and managed. However, if you’re systems are like mine, there might not even be any policies on how data is managed and handled over time. That’s something I hope changes in my lifetime and we start to treat data like a very valuable asset that can impact our lives.

    Steve Jones

  • The Google Solution

    I was watching a talk from Google on how they back up their data. After all, one of the biggest assets Google has is the tremendous amount of data that they have collected over the years. There are many systems at Google, and certainly lots of data in each of them. In this case, Gmail was the system being discussed, and the data is in the exabyte range. If you don’t have an hour to watch the talk, then there’s a nice summary at highscalabilty.

    In the talk, there are certainly things that Google aims to do with their approach. First, they can’t lose data. That’s a priority, as it should be for all of us that manage data. They also focus on restores, not backups. In fact, if they can make restores easily by adding work and complexity to backups, that’s a trade-off worth making. I haven’t typically viewed the restore process this way, though I do think restores are ultimately the most important part of any recovery task. However, I haven’t really thought about how I could actually make restores easier by changing something at backup time. I’ve often tried to make backups quicker, or take them more often, but perhaps this is an area to re-examine. Are there things you can think of that would make restores easier? Maybe not easy enough for your cat to kick off (as discussed in the video), but easy for the average sysadmin at your company?

    Google wants redundancy, which includes people. They can’t depend on any one machine, one tape, or one person. Therefore, they need to have multiple copies of data and more automation that reduce those single points of failure. Along those lines, our clients and customers don’t need to know if we have 3 copies, 7 machines, or any other configuration. Our responsibility is to ensure our customers can access data.

    Why should we care what Google does with GMail or any of their systems? Well, I only see our databases growing, with sizes going from GB and millions of rows to TB and billions of rows, or even to PBs. There are lessons that we can learn about the management of data at scale, and the ways in which our customers might perceive the availability and accessibility of their information. Google has learned they need to be more efficient with resource usage. Whether that’s disks or people, they can’t require 1000 times more resources for 1000 times more data. We should take note of that.

    Perhaps the best lessons from Google are in the areas of testing and expectations. They test constantly, to ensure that they can actually recover data. While I think the SQL Server backup system is very solid, I’d be regularly testing restores to ensure that I really can recover from backup files, from external disks and tape, or even complete a restore on a backup system. The other lesson is that Google expects things to fail, so they plan for that, and aren’t surprised by failures. SQL Server gives us options here with Always On and other HA technologies if we can take advantage of them. With a single RDBMS instance, there isn’t a lot most of us can do, but we can at least be prepared to rebuild our instance elsewhere as a last resort.

    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.