On The Importance of Database Backup and Recovery

Things break. When a new database is created or a new application goes online, everything is fresh and new—and running as designed. But the environment changes over time. New hardware and software is added, along with more users, more data, more requirements—more, more, more. Systems wear down as they are taxed on a daily basis to deliver service.

The DBA must be prepared for situations where a failure impacts the availability, integrity, or usability of a database. Reacting to failures and service disruptions is a key component of the DBA’s job. The ability of the DBA to react accordingly depends directly on his having a well-planned approach to database backup and recovery.

I know that many DBAs believe that ensuring optimal database and application performance is the most important task in their list of job responsibilities, but it is not true. These DBAs are confusing frequency with importance.

Most DBAs probably are conducting performance-related tasks more often than building backup plans – and they better be managing performance more frequently than they are actually recovering their databases or their company has big problems! But recoverability should be at (or near) the very top of the DBA task list, definitely before performance. Why? Well, if you cannot recover your databases after a problem then it won’t matter how fast you can access them, will it? Anybody can deliver fast access to the wrong information. It is the job of the DBA to keep the information in our company’s databases accurate, secure, and accessible.

So what do DBAs need to do to assure the availability and accuracy of our database data? This chapter will outline the basics of database backup and recovery and provide guidance on building a robust backup and recovery plan for your databases.

Preparing for Problems

Numerous daily hazards can cause system failures. As you plan your database backup and recovery strategy, be sure to consider all of these various threats to database integrity and availability. Of course, it is wise to take precautionary measures to prevent failures. Techniques such as UPS systems, mirrored disks, and failover technology can minimize the need to recover, but no amount of planning and regulation can prevent unexpected failures.

Database failures that may require recovery can be divided into three categories:

  • Instance failures are the result of an internal exception within the DBMS, an operating system failure, or other software-related database failure. In some cases, an instance failure can result in corruption of data that requires a recovery, but usually such failures do not damage data, so the DBMS simply needs to be restarted to reestablish normal operations.
  • Application (or transaction) failures occur when programs or scripts are run at the wrong time, using the wrong input, or in the wrong order. An application failure usually results in corrupt data that requires a database restore or recovery. The sooner an application failure is identified and corrected, the smaller the amount of damage to the database will be.
  • Media failure is likely to damage data, too. Media failure includes damage to disk storage devices, file system failures, tape degradation or damage, and deleted data files. Although less common in practice, damaged memory chips also can cause data corruption. After a media failure, the database will likely be in a state where valid data is unreadable, invalid data is readable, or referential integrity is violated. Outages due to media failures can often be avoided by implementing modern disk technologies such as RAID.

Businesses today are demanding higher throughput and around-the-clock availability even as they increase the amount of stored and processed data. Not too long ago, we talked about data in terms of gigabytes. Now it is common for organizations to manage a terabyte or more of data on a single database server. Therefore, more data needs to be constantly available and has to be processed faster than ever before. Organizations rely on data to conduct business, so it is imperative that you are prepared with a plan to counteract failures. A sound backup and recovery plan can be thought of as an insurance policy for your data.

Image Copy Backups

A fundamental component of a database backup and recovery plan is creating backup copies of data. When an error occurs that damages the integrity of the database, a backup copy of the data can be used as the basis to recover or restore the database. However, the full story on backing up a database is not quite that simple.

Backing up databases involves making consistent copies of your data, usually in the form of image copies, which are the output of a COPY utility. The name of the copy utility will vary from DBMS to DBMS. Common names for the backup utility include BACKUP, COPY, DUMP, and EXPORT. Some DBMSs rely on the native operating system’s file system commands for backing up data. However, even if the DBMS supplies an internal backup option, the DBA may choose to use facilities that operate outside the realm of the DBMS.

Full vs. Incremental Backups

Two types of image copy backups can be taken: full and incremental. As a DBA, you will need to learn the difference between the two and implement the proper image copy backup strategy based on application needs and database activity.

A full image copy backup is a complete copy of all the data in the database object at the time the image copy was run. An incremental image copy backup, sometimes referred to as a differential backup, contains only the data that has changed since the last full or incremental image copy was made. The advantage of taking an incremental backup rather than a full backup is that it can sometimes be made more quickly, and it occupies less space on disk or tape. The disadvantage is that recovery based on incremental copies can take longer because, in some cases, the same row is updated several times before the last changes are restored.

If the DBMS supports incremental image copy backups, it may also support incremental copy merging. A merge utility, sometimes referred to as MERGE or MERGECOPY, can be used to combine multiple incremental image copy backups into a single incremental copy backup, or to combine a full image copy backup with one or more incremental image copy backups to create a new full backup.

Some DBMSs support making backup copies of indexes. Indeed, some DBMSs require indexes to be backed up, whereas index backup is optional for others. Index backup can be optional because the DBMS can rebuild an index from the table data. Therefore, a typical recovery scenario would involve recovering tables or tablespaces and then using the table data to rebuild the indexes. If the DBMS supports index backups, you can choose to copy indexes and then recover using the image copy backups of the indexes.

As a DBA, though, you will need to examine the trade-offs of copying indexes if your DBMS supports index backup. The question DBAs must answer for each index is “Rebuild or recover?” The more data that must be indexed, the longer an index rebuild will require in a recovery situation. For larger tables, backing up the index can result in a much quicker recovery—although at the expense of the increased time required for backup. When multiple indexes exist on the large table, backing them up, again, leads to faster recovery. However, keep in mind that index backups will require additional time to execute during your regular backup process. As a DBA, you will need to weigh the cost of recovery versus the cost of backup in making your decision.

Backup Consistency

Be sure your backup plan creates a consistent recovery point for the database object. In order to ensure backup consistency, you need to be aware of all relationships between the database objects being backed up and other database objects. This includes application-enforced relationships, referential constraints, and triggers. If you use an image copy backup to recover a database object to a previous point in time, you will need to recover any related database objects to the same point in time. Failure to do so will most likely result in inconsistent data.

If your DBMS provides a QUIESCE utility, use it to establish a point of consistency for all related database objects prior to backing them up. The QUIESCE utility will halt modification requests to the database objects to ensure consistency and record the point of consistency on the database log. Use the QUIESCE utility even when some database objects do not need to be copied, so that you can provide a consistent point of recovery for all referentially tied tablespaces.

If the DBMS does not provide a QUIESCE option, you will need to take other steps to ensure a consistent point for recovery. For example, you can place the database objects into a read-only mode, take the database objects offline, or halt application processes—at least those application processes that update the related database objects.

Document Your Backup Strategy

Once your backup strategy has been established and implemented, the backup system can run for a long time without any DBA intervention required. Such automation is a mixed blessing, though. Over time, things can be forgotten and the DBA staff can change, both of which can cause confusion during a hectic database recovery. For this reason it is imperative that the backup and recovery strategy, implementation, and procedures be thoroughly tested and documented by the DBA.

The most important aspect of any backup plan is to match it against your recovery requirements and options. More on that in the next section.

Database Recovery

When problems impact the database, the DBA can use the image copy backups and the database log to recover the database. Whatever the cause of the problem, the DBA must be able to recover data quickly so that the business can continue to operate. When data is unavailable, your company may be losing thousands or even millions of dollars. Recognizing the need for a database recovery is quite different from actually performing a recovery in a speedy and proper fashion. Database recovery can be a very complex task that is prone to errors and difficult to manage.

Recovery involves much more than simply restoring an image of the data as it appeared at some earlier point in time. A database recovery will involve bringing the data back to its state at (or before) the time of the problem. Often a recovery involves restoring databases and then reapplying the correct changes that occurred to that database, in the correct sequence.

Simply stated, a successful recovery is one where you get the application data to the state you want it—whether that state is how it was last week, yesterday, or just a moment ago. If you planned your backup strategy appropriately, you should be able to recover from just about any type of failure you encounter.

Determining Recovery Options

When a failure occurs, the DBA will need to ascertain whether recovery is required. If recovery is required, you will need to determine what resources (backup copies) are available and how best to perform that recovery.

Of the different types of recovery performed, the first one that usually comes to mind is a recover to current, to handle some sort of disaster. This disaster could be anything from a media failure to a natural disaster destroying a data center. Applications are completely unavailable until the recovery is complete. To successfully recover to current, the recovery process must be able to reset the contents of the database to the way it looked just at (or right before) the point of failure. To recover to current, the recovery process must find a valid, full image copy backup and restore that image copy. Then the recovery will roll forward through the database log, applying all of the database changes.

If the last full image copy is lost or destroyed, it may still be possible to recover if a previous image copy exists. The recovery process could start with the older backup copy, apply any incremental copies, and then roll forward through the archived and active logs. Of course, more database logs will be required in such a case, so the recovery process will take longer.

If no image copy is available as a starting point, it may be possible to recover the database object using just the database log. If the data was loaded and the load process was logged, recovery may be able to proceed simply by applying log records.

Another traditional type of recovery is point-in-time (PIT) recovery, which is usually done to deal with an application-level problem. PIT recovery is sometimes referred to as partial recovery because only part of the existing data will remain after recovery. Recovery to a point in time removes the effects of all transactions that have occurred since that specified point in time.

To perform a PIT recovery, an image copy backup is restored and then changes are applied by rolling forward through the database log (or log backups). However, only the log records up to the specified time are processed. Sometimes the recovery point is specified as an actual date and time; sometimes it is specified using a relative byte address on the database log.

To successfully recover to a point in time, the recovery must be able to reset the contents of the database to the way it looked at a prior consistent point. The key is to retain all of the good changes to the data, while removing all of the “bad.” PIT recovery can be accomplished in one of two ways, depending on the features of the DBMS and the amount of data to be recovered. It could:

  • Restore the image copy by rolling forward through the logs and applying the database changes up to the recovery point, or;
  • Not restore the image copy, instead rolling backward through the logs and removing the database changes that occurred after the recovery point.

If the DBMS supports both types of recovery, the DBA should choose to deploy the one that creates the least downtime. If a significant number of changes need to be removed, then restoring and rolling forward usually results in the least downtime. If the number of changes that must be removed are minimal, then rolling backward through the logs should result in less downtime. If your DBMSs does not support backward log rolling, third-party products may be available that can make it a viable technique.

Transaction recovery is a third type of recovery; it addresses the shortcomings of the traditional types of recovery: downtime and loss of good data. Thus, transaction recovery is an application recovery whereby the effects of specific transactions during a specified timeframe are removed from the database. Third-party software is required to perform a transaction recovery.

Traditional types of recovery, both recovery to current and PIT, recover at the database object level. In direct contrast to this level of granularity, transaction recovery allows a user to recover a specific portion of the database based on user-defined criteria. This can be at a transaction or application program level. In this context, a transaction is defined by the user’s view of the process. This might be the set of panels that comprise a new-hire operation, or the set of jobs that post to the general ledger. The important point is that there may or may not be a correlation between the transactions you are trying to fix and transactions (or units of recovery) in the DBMS.

Once you have identified the transaction to recover, you have three recovery options:

  • PIT recovery. You can try to identify all of the database objects impacted by the application and perform traditional point-in-time recovery to remove the effects of the transactions. You would then manually rerun or reenter work that was valid.
  • UNDO recovery. Remove only the effects of the bad transactions.
  • REDO recovery. Remove all the transactions after a given point in time, and then redo the good transactions only.

PIT recovery was discussed earlier, so we will not cover it again here. The other two forms of transaction recovery are SQL-based application recovery techniques. The basic idea is to read the log and generate the proper SQL statements that can be run to achieve the desired recovery results.

Let’s first examine an UNDO recovery. UNDO recovery is the simplest version of SQL-based transaction recovery because it involves only SQL. To accomplish an UNDO recovery, the database logs must be scanned for the identified transaction and anti-SQL is produced. Anti-SQL reverses the affect of SQL by

  • Converting inserts into deletes
  • Converting deletes into inserts
  • Reversing the values of updates (e.g., UPDATE “A” to “X” becomes UPDATE “X” to “A”)

Once the anti-SQL is generated, it is run using an interactive SQL script to perform an UNDO recovery.

A REDO recovery is a combination of PIT recovery and UNDO recovery, with a twist. Instead of generating SQL for the bad transaction that we want to eliminate, we generate the SQL for the transactions we want to save. Then we do a standard point-in-time recovery to eliminate all the transactions since the recovery point. Finally, we reapply the good transactions captured in the first step.

Unlike the UNDO process, which creates SQL statements designed to back out all of the problem transactions, the REDO process creates SQL statements that are designed to reapply only the valid transactions from a consistent point in time. Since the REDO process does not generate SQL for the problem transactions, performing a recovery and then executing the REDO SQL can restore the database object to a current state that does not include the problem transactions.

So, what is the best recovery strategy? It depends… on things like the amount of data impacted, subsequent modifications, log availability, and so on.

Recovery Testing 

It is important that the DBA thoroughly test each different type of recovery in a test environment. Be sure that you can recover from a media failure, an instance failure, and several types of application failures. Document the type of backup taken for each database object, along with a schedule of when each is backed up. Be sure that all of your databases can be recovered and that all DBAs on-site have firsthand experience at database recovery. The DBA group should schedule periodic evaluations of the backup and recovery plans for every production database.

Every DBA should prepare a recovery plan for each database object and test it frequently. A recovery plan describes the procedures you will use to recover in the event of hardware failure or a local site disaster, such as fire.

To develop your recovery plan:

  • Write all aspects of the recovery plan out in detail, documenting each step.
  • Include all the scripts required to back up and recover each database object.
  • Review the plan with everyone who may be called on to implement it.
  • Include a contact list with names and phone numbers of everyone who may be involved in the recovery.
  • Keep the recovery plan up-to-date by modifying the plan to include every new database object that is created.

Testing your recovery procedures on a regular basis includes running regular recovery tests for individual database objects, databases, and the entire database system. By testing recovery procedures, the DBA assures that the backup and recovery scripts work and that every database object is indeed recoverable. Furthermore, regular recovery testing is on-the-job training for the inevitable, stress-filled production database recovery. A DBA that tests his recovery plan will be more familiar with the tools and scripts needed to perform recovery.

That’s enough for one blog post. Happy backing up and recovery testing… but hopefully no actual recovery needed!

Posted in backup & recovery, DBA | 1 Comment

Take a Big Data and Cloud Computing Quiz

Are you concerned about new technology and industry hype rendering your experience obsolete? Are memes like Big Data and Cloud Computing confusing to you… or are you comfortable that you know what you need to know?

Why not take a short Interactive Master Class quiz on cloud and big data integration?

I got 9 out of 10… but I won’t share with you which one I missed!

You might also want to take another, similar yet different, quiz on Big data analytics tools and best practices.

I got 8 out of 8 on this one…

How did you do?

Posted in Big Data, cloud, data | 1 Comment

On Writing

A question that I get asked frequently is “How do you write so much?”… and “Do you have any advice for folks wanting to write their first article (or blog post, or book, or…)?”

Well, if you want to be a writer the first thing that you have to do is make the time to write every day. Even if it is only for a half hour or so, sit down at the computer or pull up a lawn chair outside with a tablet and pen and just write what you are thinking about. It could be personal or technical; funny or serious.  The bottom line is this: if you are serious about writing, you need to set aside time to do it.

Don’t worry about getting everything perfect as you write down your ideas. You can always edit it later. Capture your immediate thoughts as they come to you.  Then take that beginning essence of an idea and flesh it out over time.

As far as writing your first article goes, it is as simple as this: write what you know. Write about the last project you worked on, the current database you are designing, or that particularly thorny performance problem your team just helped you solve. If you write about your experiences you have a better chance of succeeding than if you decide to write about a topic you must research because you know nothing or little about it.

Once it is written, don’t be shy about submitting it to your favorite publications. Most technical journals and web-based publications are interested in publishing first-hand experiences about solving problems with technology. And it is unlikely that an editor will beat down your door for material – especially if you have never written before. Choose the publication wisely – make sure your article is a good fit by reading the magazine before submitting your article. More articles are rejected because they are not good fits for the publication than any other reason. For example, Computer World is unlikely to publish your manifesto on migrating from one set of DB2 utilities to another… but a more technical publication might… and that is an excellent first blog post for that new blog you’ve been thinking about starting up!

Posted in DBA | 1 Comment

On Becoming a DBA

One of the most common questions I am asked is: How can I become a DBA? The answer, of course, depends a lot on what you are currently doing. Programmers who have developed applications using a database system are usually best-suited to becoming a DBA. They already know some of the trials and tribulations that can occur when accessing a database.

If you are a programmer and you want to become a DBA, you should ask yourself some hard questions before you pursue that path. First of all, are you willing to work additional, sometimes crazy, hours? Yes, I know that many programmers work more than 40 hours already, but the requirements of the DBA job can push people to their limits. It is not uncommon for DBAs to work late into the evening and on weekends; and you better be ready to handle technical calls at 2:00 a.m. when database applications fail.

Additionally, you need to ask yourself if you are insatiably curious. A good DBA must become a jack-of-all-trades. DBAs are expected to know everything about everything — at least in terms of how it works with databases. From technical and business jargon to the latest management and technology fads, the DBA is expected to be “in the know.” And do not expect any private time: A DBA must be prepared for interruptions at any time to answer any type of question — and not just about databases, either.

And how are your people skills? The DBA, often respected as a database guru, is just as frequently criticized as a curmudgeon with vast technical knowledge but limited people skills. Just about every database programmer has his or her favorite DBA story. You know, those anecdotes that begin with “I had a problem…” and end with “and then he told me to stop bothering him and read the manual.” DBAs simply do not have a “warm and fuzzy” image. However, this perception probably has more to do with the nature and scope of the job than with anything else. The DBMS spans the enterprise, effectively placing the DBA on call for the applications of the entire organization. As such, you will interact with many different people and take on many different roles. To be successful, you will need an easy-going and somewhat amiable manner.

Finally, you should ask yourself how adaptable you are. A day in the life of a DBA is usually quite hectic. The DBA maintains production and test environments, monitors active application development projects, attends strategy and design meetings, selects and evaluates new products and connects legacy systems to the Web. And, of course: Joe in Accounting just resubmitted that query from hell that’s bringing the system to a halt. Can you do something about that? All of this can occur within a single workday. You must be able to embrace the chaos to succeed as a DBA.

Of course, you need to be organized and capable of succinct planning, too. Being able to plan for changes and implement new functionality is a key component of database administration. And although this may seem to clash with the need to be flexible and adaptable, it doesn’t really. Not once you get used to it.

So, if you want to become a DBA you should already have some experience with the DBMS, be willing to work long and crazy hours, have excellent communication and people skills, be adaptable and excel at organization. If that sounds like fun, you’ll probably make a good DBA.

Posted in DBA | 2 Comments

On Data Growth

Businesses today are gathering and storing more data than ever before. We are said to be living in the “information age,” and data is the capital of the new economy. With this explosion in the amount of data being stored, organizations are relying more than ever on data storage, technology and management to get a handle on corporate data and extract useful business information from that raw data.

I visit a lot of different organizations each year as a part of my job, and one thing is consistent: DBAs tell me that their databases are growing in size. I’ve never had a DBA say to me, “You know, my databases are getting smaller, and I just can’t handle it.” Nope, it is always just the opposite. Organizations everywhere are struggling with the burgeoning size of their corporate databases.

The Big Data phenomenon is one aspect of this burgeoning data growth. As organizations generate, manage and store more and varied types of data, they are looking for ways to use that data for competitive advantage. One of those ways is to turn advanced analytics loose on all of that data. But doing so is not simple. It requires people who understand the data and the correlations within the data – data scientists, if you will.

If you attend the keynote of any major technology event these days you’ll hear those statistics that are created to amaze and wow you. You know what I’m talking about, things like “…more data has been created in the last 2 years than in all of recorded history before that.”

No doubt, these are interesting little tidbits. And they might even be accurate. But here is what I think is important about data growth and Big Data these days:

  • Data practitioners are finally in the limelight. We have an industry meme that is actually about DATA… and that is something we all should embrace. Isn’t it time we talked about the importance of data (whether or not we call it Big is irrelevant IMHO). We should use this opportunity to raise the awareness of the importance of data integrity, data accuracy, backup and recovery, data governance, data management, and so on. It all applies to Big Data because it all applies to any data!
  • Don’t worry too much about defining Big Data in terms of the three (or 4… or 7… or who knows how many) V’s. It doesn’t matter. Big Data will soon be supplanted by some other term in the hype cycle, so if it looks big to you and it is data, then gosh darnit, call it Big Data, gather up the budget allocated to Big Data projects and move on.
  • Realize that the most important aspect of Big Data is analytics… that is, how we examine the data and gather insight from it. Analytics is not new, but the amount of attention it is getting is. Use that to your advantage…

So, yes, there is a lot of data out there. And there is more being added every day (hour, minute, second… you get the idea). Take advantage of this moment in time when your executives care about Big Data to improve your data infrastructure… because the moment will pass. Believe me. And you’ll be sorry if you didn’t take advantage of it when you could have!

 

Posted in analytics, backup & recovery, Big Data, Data Growth, Data Quality, DBA | 2 Comments

Inside the Data Reading Room – Memorial Day 2014 Edition

Welcome to another edition of Inside the Data Reading Room, a regular feature of the blog in which I review the latest and greatest database- and data-related books.

The first book up for review today is Microsoft Access 2013 Programming by Example with VBA, XML, and ASP by Julitta Korol (Mercury Learning and Information, ISBN 978-1-938549-80-9). Although I usually write more about enterprise database issues, Microsoft Access is fair game, especially since it is one of the more popular and widely-deployed DBMS packages out in the field.

This particular book is an updated edition of Korol’s popular Microsoft Access book (previous editions are available for Access 2003, 2007 and 2010). If you have one of the earlier editions and have moved along to Access 2013, it would be a wise move to get the latest edition of Korol’s book, too. And if you are just learning how to program using Microsoft Access, this is probably one of the best books to use to do so. The books assume that you have some familiarity with the Access user interface, but even for novice users the book is very straightforward and explanatory.

If you want to automate some of your day-to-day tasks in Access, this book can help. It introduces programming concepts using simple-to-follow, hands-on lessons. The exercises are thorough and well-written.

By following the explanations provided in this book, you can learn how to write and test code with the built-in Visual Basic Editor, use common VBA programming structures, code a message box, reprogram characteristics of a database, among many other useful explanations.

If you are looking for help building solutions with Data Access Objects (DAO) and ActiveX Data Objects (ADO), look no further — Korol offers up tactics and techniques to help you learn.

Overall, the material is presented systematically, with good examples, and helpful descriptions. I recommend this book to anybody looking to learn VBA with Microsoft Access.

The next book on our bookshelf today is Freemium Economics: Leveraging Analytics and User Segmentation to Drive Revenue by Eric Benjamin Seufert (Morgan Kaufmann, ISBN 978-0-12-416690-5). Of course, the first question you may ask is what the heck is Freemium Economics? The best quick-and-dirty explanation is to start by thinking about those “free” games that are popular on smartphones and tablets. Many of them are free to download, but as you play, there are optional in-game purchases that make the gaming experience more fun and enriching. So you start with a free platform that has some value and then enrich the value with pay-to-play add-ons.

What does any of this have to do with data and databases? I’m getting to that! Seufert’s book provides extensive guidance on analyzing the data generated by a freemium product to boost retention and drive revenue. By collecting and deploying analytics on large amounts of data generated by users of the product, through all stages of development and usage, the author explains how you can optimize your implementation of the freemium model.

If you are interested in the freemium model, big data and analytics, or want to learn more about how data analytics can drive revenue, then give Freemium Economics a read.

The final book we’ll take a look at today is Learn SQL Server Administration in a Month of Lunches by Don Jones (Manning, ISBN 978-1-617-29213-2).   This concise little book offers up an ideal way to tackle learning how to administer Microsoft SQL Server.

In short chapter, each designed to be read and worked through in about an hour, the author describes a specific area of SQL Server administration. By taking the time to read (and practice what you just read) during your lunch hour, you can learn the basics of Microsoft SQL Server database administration in just about a month.

Of course, you won’t have the valuable on-the-job experiences that make an ideal DBA (e.g. dealing with end users’ performance issues in real-time or recovering production data under less than ideal circumstances). But Jones’ book can get you moving toward becoming a SQL Server DBA…

And that concludes this edition of Inside the Data Reading Room. If any of these books sound interesting to you, consider clicking on the links above to order them from amazon.com… and be sure to tune in regularly to this blog for future database book reviews and other helpful data-related reading!

Posted in analytics, book review, books, data, Microsoft Access, tools | 1 Comment

A Few Thoughts on DBA Priorities

I get the chance to talk to a lot of DBAs in my line of work, and that sometimes gets me thinking about the things that DBAs spend time on and what they feel are the most important aspects of their job. Too often DBAs conflate frequency with importance. In other words, just because you do something a lot does not make it the most important thing that you do!

I think that too little emphasis overall is placed on the integrity and recoverability of the data – and too much is placed on performance. Yes, performance is probably the most visible aspect of database systems, at least from the perspective of the end user. But what good does it do to quickly access the wrong data? By this I mean you better get the integrity of the data correct before you even start to worry about performance. Anyone can give you a wrong answer quickly, but most of us would rather wait a little bit if it means getting the right answer, wouldn’t we? So database design, integrity constraints, and so on need more emphasis in DBA training and in actual database implementations.

Taking this a step further, how sure are you (you meaning the DBA) that every database under you care is recoverable to a useful point-in-time should an error or failure occur? Is there a database backup job running in a timely manner for each and every database structure such that recoverability can be achieved within the agreed service level? What’s that? You don’t have service levels for time to recovery for each database, table space, and/or table? You should! These are commonly known as RTOs — or Recovery Time Objectives — and they are every bit as important as your performance-based SLAs.

Finally, when was the last time you tested the recoverability of your database(s) using the backups you’ve made? Or do you just assume that they are all there and working as planned and will be available as soon as you need to recover? Failing to conduct a periodic, planned testing of all your backup and recovery plans and implementation is a sure-fire way to lose data when you need to recover during a hectic timeframe (after all, aren’t all recovery situations hectic?)…

So DBAs, take a moment away from focusing solely on performance and spend some time on the integrity and recoverability of your databases. You’ll be glad you did.

Posted in backup & recovery, data integrity | 1 Comment

Surrogate Keys or Natural Keys?

If you’ve worked with relational database systems for any length of time you’ve probably participated in a discussion (argument?) about the topic of today’s blog post… surrogate keys. A great debate rages within the realm of database development about the use of ‘synthetic’ keys. And if you’ve ever Googled the term “surrogate key” you know the hornet’s nest of opinions that swirls around “out there” about the topic. For those who haven’t heard the term, here is my attempt at a quick summary: a surrogate key is a generated unique value that is used as the primary key of a database table; database designers tend to consider surrogate keys when the natural key consists of many columns, is very long, or may need to change.

Proponents of the surrogate key argue that a concise, manageable key is easier to deal with than an unwieldy multi-column key. A surrogate key provides immutability because it is assigned and therefore its value need never change even as other data values change. Surrogate keys also provide uniformity and compatibility. If you are using several different database application development systems, drivers, and object-relational mapping systems it can be simpler to use an integer for surrogate keys for every table instead of natural keys to support object-relational mapping. Some advocates even go so far as to claim performance advantages for surrogate keys thinking that it is easier to optimize a query against a single key column than for the multiple columns of a natural key.

Those arguing against surrogate keys outline their disadvantages. Perhaps the strongest claim is the disassociation of the generated surrogate key from the real-world meaning of the data. Without the data in the natural key columns that would have been used as the foreign key, the meaning of child rows becomes less obvious and each foreign key needs to be joined to the parent table to give the data meaning. Another issue is that tables that are otherwise identical (for example, a test version and a production version) could contain the same key values but represent different data, leading to confusion and data quality issues. Other negative issues that can arise from using surrogate keys include the inadvertent disclosure of proprietary information (depending upon how the surrogate keys are defined), improper database design (failing to create a unique index on the natural key), and improper assumptions based on the generated key values (for example, are higher key values necessarily for newer accounts?).

I doubt that there is any “final word” on this topic. Indeed, the argument has been raging on for years and years now. However, when I get to the performance portion of the argument I am rather skeptical. Does fewer columns in a join really translate to better performance? I can see how it might be easier to code for a programmer, but not necessarily for the DBMS to optimize. If you’ve created an index on those multiple columns how much “worse” will the performance be, really? Sure, the SQL is more “difficult” to code but will a join over 4 or 5 indexed columns perform that much worse than a join on one indexed column? I suppose if the number of columns required for the natural key is absurdly high (e.g. 12 columns???) the impact could be greater. And perhaps if you are swapping a variable length key with a surrogate having a fixed length key performance can be impacted.

At any rate, consider this: for the parent table at least, the natural key columns are still going to be there — after all, they’re naturally part of the data, right? So the surrogate (synthetic) key gets added to each row. This will likely reduce the number of rows per page/block because the row length is now longer. And that, in turn, will negatively impact the performance of sequential access because more I/O will be required to read the “same” number of rows.

And what about the impact of adding data? If there are a significant number of new rows being added at the same time by different processes there will be locking issues as they all try to put the new data on the same page, unless, of course, your surrogate key is not a sequential number and is, instead, something like the microseconds portion of the current timestamp. But then that must be tested to avoid duplicates, further degrading performance.

The one thing that usually causes me to tend to favor natural keys is just that – they are natural. If the data is naturally occurring it becomes easier for end users to remember it and use it. If it is a randomly generated surrogate nobody will actually know the data. Yes, this can be masked to a great deal based on the manner in which you build your applications to access the data, but ad hoc access becomes quite difficult.

I guess the bottom line on surrogate keys versus natural keys is that “it depends” on a lot of different considerations… and that should really be no surprise to DBAs at all!

Posted in data, data modeling, Data Quality, database design | 4 Comments

The Problem with Prediction

Predicting the future is a messy business. I try to avoid making predictions about the future of technology for many reasons. First off, nobody can see into the future, no matter what some fortune tellers say. Secondly, technology changes quickly and sometimes something revolutionary will show up and render your prediction (guess) irrelevant. As an example, few people in the late 1980s foresaw the Internet revolution. Or blogs like this one, for that matter!

Every now and then I may try to alert readers about some technology that I think will be disruptive, but I don’t usually try to predict what software or companies will succeed or perish.

Now as my regular readers know, I undertook an office cleaning project late last year — and I wrote a blog post about some of the material I encountered organizing my office closet (see On Being Skeptical in the Face of Hype).

Well, that closet is as clean as it is going to get for the time-being, but I periodically attempt to reorganize my many bookshelves, too. While doing so, I happened across an old book written by John C. Dvorak called Dvorak Predicts. Dvorak is an industry pundit and popular technology writer. I readily admit to being a fan of his work. He is entertaining. But his powers of prediction are poor. And why wouldn’t they be? No one knows what tomorrow will bring…

Now I don’t do what I am about to do to discredit Dvorak specifically, but to alert everyone that even the most talented writers and pundits have a hard time predicting the future. Here, direct from his book, Dvorak Predicts (published in 1994 by McGraw-Hill, ISBN 0-07-881981-4) are some real stinker predictions:

“We can expect IBM to someday quit the mainframe business just as it quit the scientific computer business.” (page 20)

Although this prediction may come true some day, 20 years later the mainframe is still viable and still a core offering from IBM. Why didn’t he predict that IBM would quit the PC business (which it did in 2004)? — Craig

“Voice recognition will be the killer application of the 1990s.” (page 21)

Didn’t happen. Oh, IBM and Dragon had some voice recognition applications that sold in so-so amounts, but killer app? Nope. Still not even a semi-killer app today. — Craig

“Microsoft will open stores.” (page 64)

Never happened; good thing for Microsoft, too since some of the other companies that opened stores didn’t fare all that well (e.g. Gateway). — Craig

Furthermore, Dvorak predicted that Unicode would lead to the death of ASCII by 1995. Well, in the long term I’m sure that prediction will come true, but the”by 1995″ part didn’t! Did he ever really believe that? I mean, even though Unicode is important today, ASCII is still very much alive and kicking.

And a few things that Dvorak does not even mention include the World Wide Web, Java, XML, and spam. One would think that a prescient prognosticator would foresee these three facts of everyday life in the world of IT. But no…

Of course, Dvorak did get some things absolutely correct. For example: “Piracy will increase despite efforts to stop it.” He wrote that prediction about software piracy but it is absolutely applicable today with regard to media, especially music and movies. But predicting that people will continue to do bad stuff is simplistic and easy to do.

Furthermore, Dvorak said that “Gerstner will be good for IBM,” which he undoubtedly was. And he predicted a rosy future for recordable CD and optical media (then again, back then, who wasn’t predicting that?)

A lot of the predictions (and there are many more than are mentioned here) strike me, here in the future, as of the “who cares” variety. By that I mean, I would expect a useful book of predictions to predict about things that matter in the future, and not about things that are just dust bunnies from the past (e.g. Apple Newton, OS/2). And he never foresees the Palm or Windows PocketPCsjust calls the basic idea behind the Newton “stupid” (page 83)… nor does he anticipate the tablet or smartphone.

So, what good is a book of predictions if the majority of them don’t come true. Exactly! No dang good at all — other than (maybe) as an amusing read. The book is out of print, but if you find a copy you might shell out the dollar or two it takes to buy it these days. And here in the future, 20 years later, you might get a chuckle out of some of it… or at least enjoy reading it as an historical piece on an interesting time in the history of computers — that being, the timeframe right before the Web exploded.

What does any of this have to do with database management? Well, not much, at least directly. But the next time someone tells you that some new database technology is going to render everything you know to be obsolete (hmmm, NoSQL? Hadoop?), slow down and think about the problem with prediction before you make any hasty moves.

Posted in books | 4 Comments