Security, performance and scalability are popular concepts these days when it comes to telematics and database management. One important database management task that is often overlooked is backup and recovery. A good backup and recovery procedure is a vital part of any sound cybersecurity policy as it allows you to restore your database to a consistent state after a database issue.
Types of potential database issues
Database backup and recovery can protect you against three main issues: hardware failure, software error and user error.
1. Hardware failure
While any type of hardware failure can impact your ability to access data, not every hardware issue results in actual data loss. There are two main hardware concerns that can cause data loss.
The first and most obvious is storage or hard drive failure. Failure can occur when a hard drive reaches the end of its natural life, or has a physical defect or imperfection. Hard drives are easy to damage and can become compromised if a computer is struck or dropped.
The second type of hardware failure is power loss. During a power failure, data residing memory may be lost before it can be written to disk. If the failure is caused by a power surge, additional parts of the hardware can be damaged in the process.
2. Software error
Software errors can come from bugs, malware or viruses. A bug or compatibility issue may interfere with how your data is communicated to the database, leading to input errors or erroneous entries. Sometimes, the data itself will be fine, but the error causes it to display improperly when you attempt to retrieve it.
Malware and viruses are the most dangerous type of software error. Hostile programs can cause numerous issues with data including stealing, deleting or locking it behind a ransom demand.
3. User error
User error is the hardest type to guard against. Even the most experienced and savvy employee can still inadvertently delete data or even an entire database by mistake. Support personnel, developers or even the database administrator could accidentally delete entire directories. Someone could also be making a simple database change to work around a software bug and think they were updating a single record and by mistake update an entire table. No company, no matter how big or technologically adept, is immune to user error.
Types of database backups
Clearly, it is incredibly important to have a backup and recovery procedure in place. Testing the procedure is also essential for ensuring you can recover from any one of these failures with minimal data loss.
Regardless of the database platform in question, there are two main types of backups.
Cold/Offline backups are taken when the server that hosts the database is shut down. At this time, every file on the server is backed up. This type of backup can also be taken using technology that allows a point-in-time snapshot of the entire server to be made while it is up and running.
Hot/Online backups are a little more database-specific and are usually seen with larger database platforms. Databases with hot backup systems use a transaction-logging mechanism that periodically records and backs up database transactions at predefined intervals. Oracle calls them redo logs, Postgres has WAL files, and SQL Server uses a TRN file. By using these files along with a full database backup, you can restore a database to any specified point in time in the past.
Telematics data backup and recovery at Geotab
At Geotab, we use backups of transaction logs along with full server backups. This gives Geotab engineers the ability to restore a MyGeotab database to any point in time. We take full server backups nightly and the log backups are moved off server every 10 minutes. The following graphic illustrates this process.
Backup and recovery process for MyGeotab
Backup and recovery protects against failure
Point-in-time recovery gives the strongest protection against software and user errors. We can simply restore the MyGeotab database to a state just before the error occurred and there will be no loss of telematics data.
The process of recovery from hardware failure is more complicated. MyGeotab’s hardware has redundancies in place to reduce the likelihood of failure. In the rare circumstance that we do encounter a hardware failure, data can be restored using backups. Since backups are moved off the server every 10 minutes, users will at most lose 10 minutes of data.
Fortunately, even this 10 minute window of data is not truly lost. Geotab’s Gateway servers, which handle the flow of data between the vehicle and the MyGeotab database, store data for up to two months. When a restoration is performed, the gateway will send the missing 10 minutes of data to the database and create a full recovery.
These safeguards help customers maintain access to their telematics data and continue optimizing the performance of their fleet.