Tips & Tricks: Ensuring Your Database Is Backed Up

Think about all of the information you have ever entered into your dispatch (I/CAD) and records management system (RMS). That data is very important for many reasons. This information is used for police investigations, transferring injured patients between hospitals, accessing location history on addresses for dispatch teams, and more. Now imagine if all of that information just disappeared.

Although it is a scary thought, it is highly unlikely that your agency is not backing up your database. But are you doing it the safest and most effective way possible? When creating a database backup strategy, a good starting point is Scott Hanselman’s 3-2-1 rules – also known as The Backup Rule of Three. Here is a brief explanation to get you thinking about database backups.

Have at Least Three Copies of Your Data
Your primary copy of your data is stored within the principle database on your database server. Second and third copies should be your database backup files. The mirror/secondary replica databases should not be included in your list. If a change is made to your primary database, the change is automatically copied over to the mirror/replica databases.

Creating database backups takes both disk I/O and processor cycles away from your primary database. On most modern hardware, this additional load doesn’t cause any decrease in database performance, but it is important to remember this when setting up your databases. When using SQL Server AlwaysOn, we have the ability to offload database backups to a secondary replica database. This also has the added benefit of moving the database backup files off the primary database server.

Archive databases should not be regarded as a backup of production. As your site grows, the amount of data within the archive database will increase. The time required to restore this database will also rapidly grow. In a disaster recovery situation where time is critical, you don’t want to be spend time restoring 10 years of data when you only require one year. Archive databases should have their own backup strategy which follows the 3-2-1 model.

Keep Your Backups on Two Different Media
When designing your database backup strategy, the location of your database backup files is very important. If you store all of your backup files on your database server and the machine has issues, you might lose all of your backups as well as your database. Whenever possible, your backup files should be located away from database. For sites which do not have offsite facilities, offline media such as external USB and tape drives can be used. Once a backup has been completed, these external media devices can be disconnected and stored away from the database servers. Being disconnected is very important, because it helps to protect against environmental damage such as power surges or viruses and malware attacks.

When running I/CAD within a virtual environment, your backup strategy must also take into account where the virtual machines are hosted. For example, if you are copying your database backup file to another server you need to know where both servers are hosted. If the virtual machines are located on the same physical host and that host machine has an issue, both copies of your data could be lost.

Store One Copy Offsite
One of your copies should be located offsite – outside of your primary location. This offsite location could be a disaster recovery site located in separate building away from your primary location. Setting up and maintaining a secondary site has high associated costs. However, with the decreasing costs of remote cloud storage many companies are looking at how to leverage these solutions to reduce costs. When using an external cloud storage solution, security is very important. All backup files should be encrypted before they leave your environment. When designing your database backup strategy, remember recovering from cloud storage is limited by download speeds. It shouldn’t be your primary data recovery process.

Hopefully these rules act as a good starting point for you when you are designing your own database backup strategy. 

About Steve Dabell

Steve Dabell is an application consultant for Hexagon Safety & Infrastructure in Canada. He works with our incident management suite of solutions.