Security, performance, and scalability are some of the more talked about concepts when it comes to telematics and database management these days. One database management task that is equally important and often overlooked is backup and recovery. Having a good backup and recovery procedure gives you the ability 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.
While any type of hardware failure impacts server and database availability, things like motherboard or network card failures don’t have any real impact with respect to data loss. However, there are two main hardware concerns that can cause data loss. The first and most obvious hardware issue is storage/drive failure. If drives are failing, then depending on your configuration you could end up losing data.
The second and maybe not so obvious issue is power failure. When a power failure occurs, all the data that resides in memory may not have been written to disk. Now keep in mind that most database software tries to account for this, but it’s a risk nonetheless.
Whether it’s a bug in the software that stores its data in your database or a virus running on the server that hosts your database, there are many different ways that a software issue can cause data corruption or even the loss of your entire database.
This is the most difficult error to protect against as anyone with direct access to the database could inadvertently delete data or even an entire database by mistake. Support personnel, developers, or even the database administrator could make a mistake and remove something inadvertently thinking they were say on a test server when they are actually on production server. Someone could also be making a simple database update to workaround a software bug and think they were updating a single record and by mistake update an entire table.
All of these database issues highlight the fact that 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 the least amount of data loss possible.
Types of Database Backups
Regardless of the database platform there are two main types of backups. Cold/Offline backups are taken when the server that hosts the database is shutdown at which time every file on the server is backed up. This type of backup can also be taken using some type of technology that allows a point-in-time snapshot of the entire server to be made while the server is up and running.
Hot/Online backups are a little more database specific. All the major database platforms have some sort of transaction-logging mechanism that allows each transaction executed against the database to be logged and backed up at some configured interval. 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 the past.
Telematics Data Backup and Recovery at Geotab
At Geotab, we use the backing up of these transaction logs along with full server backups in order to provide 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 the server every 10 minutes. The following graphic illustrates this process.
How Backup and Recovery Protects Against Failure
It doesn’t require much explanation to see how point-in-time recovery protects us against both software and user error. We can simply restore the MyGeotab database to a point in time just before the error occurred and there is no loss of telematics data.
The process of recovery from hardware failure is a little more complicated. There is redundancy built in at the hardware level to ensure that the likelihood of a hardware failure is very low. In the rare circumstance that we do encounter a hardware failure since the backups are moved off the server every 10 minutes we can guarantee that you would lose at most 10 minutes of data. This 10 minute loss of data is just at the MyGeotab database level though. If your database does require a restore we can tell one of our Gateway servers, which handles the flow of data between the vehicle and the MyGeotab database, to resend this missing data to your MyGeotab database as these servers store two days of vehicle data.
All of ensures that customers have continued access to their telematics data for optimizing the performance of their fleet.
For more information on maintaining system security for your fleet, please read: Dialing Down Risk with Audit Logs
How Long Does Geotab Keep My Data?