In public safety our databases are just like our vehicles in many aspects.  We ask a lot of them and require unparalleled reliability.  Our vehicles receive routine preventative maintenance because a failure can result in the loss of life.  Our databases are no different.  They require maintenance and protection to deliver the reliability demanded by the communities we serve.

Also like our vehicles, database performance degrades over time.  We don't think twice about replacing spark plugs or air filters to keep our vehicles performing at their best while keeping gas mileage in check.  When was the last time your database received a tune up?  Failure to implement a maintenance program will result in application slowdowns, frustrated users and delayed responses.

Database corruption has many causes - a bad hard drive sector, an SSD at the end of it's life, a well-intentioned IT professional who runs the 

wrong query against the wrong data, etc.  Any of these problems can start small and be easily repaired if caught early enough.  If allowed to go unchecked, however, they can lead to timely and costly repairs or even a complete loss of consistency, requiring the restoration of a known good backup.  (You are making regular, safe backups of your database, right?  More on that later!)

Many agencies just assume that their software vendors build these checks into their application or database itself and are shocked when they find that isn't usually the case.  Because each agency is so different in demand, peak hours and number of system users this type of routine maintenance must be customized for each individual agency.  A rural EMS provider whose bedroom community is generally asleep by 10 p.m. on a Friday night has very different maintenance windows from an urban provider whose city doesn't really come alive until 11 p.m.

There are different types of backups used to protect databases:

Full - A complete image of your database.  This single file can be used to restore your database back to the time it was taken.  Any changes made from the time the backup was started to the present time will be lost.  These backups take the longest to complete and result in the largest file size as they can be the same size as your full database if not compressed.  Depending on database size many agencies choose to do full backups either weekly or nightly.

Differential - A collection of all database changes since the last time a full backup was taken.  Differential backups take less time to complete than full backups and result in smaller files since only changes since the last full backup   Due to this misconception I have decided to provide a detailed explanation of both how they work and why they must be backed up.  

What should you do then?  Just keep buying more disk space as the log grows and grows?  Of course not.  Transaction logs should be backed up regularly.  Once backed up, your transaction log will allow itself to be overwritten, preventing the file from growing in the first place.  A properly managed database will have both it's database file size and transaction log file size set by a knowledgeable database administrator and neither will exceed the space allocated to them.

In the event of a disaster you can restore your transaction log backups to a specific point-in-time.  This can be invaluable for "rolling back" a case of human error.  A customer of mine once accidentally closed their payroll period in their timekeeping application before the period ended, effectively making it impossible for any employees to clock in or out...this was on the first Wednesday of the new two week pay period!  The last full database backup was taken the Sunday night prior and the last differential was taken the Tuesday night prior.  They didn't want to lose half a day of time clock punches by reverting back to the backup made the night prior.  Transaction log backups to the rescue!  I was able to restore their database to a point exactly 1 minute before the payroll period was closed.  Instead of losing half a day of payroll data, they had to manually clock two employees in who reported for work during the time required to do the recovery.

On go-live day your CAD was probably pretty fast.  Calls went from the calltaker's screen to the dispatcher almost instantly.  You could open a unit's location history while putting another unit in service while dispatching a multi-car accident with no wait and no hourglass.  You could run an activity report for a particular unit for the last couple of weeks in a second or two.  Life is grand!

Fast forward a year and now it seems like it takes a little bit longer for every call to make it to dispatch.  You don't bother with checking location history anymore because it locks your workstation up for 3-5 seconds.  You dare not do anything when dispatching a multi-unit response because you aren't sure how long it will take.  You tell the supervisor requesting an activity report that she'll need to run it from her desk because it takes too long and you have calls holding.

What causes this slowdown?  

Second only to overloaded hardware, index and statistics maintenance are the single most common culprit when troubleshoot a slow database. Think of your database as a giant hand-written ledger book.  Each call occupies a single line in the ledger.  Each page holds 100 calls.  Your agency runs 125,000 calls a year.  If you need to find a call at 1234 Main St. that occurred "around" June the 2nd last year, that's a lot of pages to search through!  If only someone had kept an index at the end of the ledger updated so you knew exactly which page to turn to without having to go through all of them!

Your SQL server has indexes as well.  These allow very fast searches on key points within your database to make accessing and reporting on data go much more quickly.  Much like the indexes in a hand-written book, database indexes require regular updating.  If you need to find 1 call out of your 125,000 and you have an index that is sorted by address you can do that pretty quickly.  If you don't have an index, or worse if your index is in a completely random order it could take longer to go through the index than it would to just go through every page in the book!  The indexes in a SQL database experience this randomization over time.  They require very specific maintenance procedures run at regular intervals to stay in top shape.

Some of the best feedback I've received from a customer whose database I began maintaining is below: