Your business’s success hinges on the accuracy and integrity of your customer data. That’s why you go to great lengths to ensure proper disaster prevention and recovery. But while many companies back up their CRM database every day, a surprising number still don’t have a grasp on how to protect against database corruption.
Database corruption happens all the time, and it can quietly erode your data like tooth decay eats through enamel, compromising your backups too. When it comes time to back up, you may find you’re backing up from nothing. You’ll have to hire a consultant — or wait for Microsoft to respond — before you get your data back. You face downtime and lost revenue. And that’s the best-case scenario. At worst, you can lose your data permanently.
Thankfully, you can easily manage corruption risk with a few cut-and-dried procedures.
Here’s a quick guide on how you can nip this problem in the bud with an ounce of prevention.
What is SQL database corruption?
Let’s assume you and your team are utterly meticulous when it comes to data entry. Your CRM is configured according to best industry standards. You pay exorbitant amounts for state-of-the-art database software. You take every precaution when it comes to viruses and malware. And you back up every day of the week.
You’re still not protected. Why? Because database corruption usually occurs beneath the level of the interface where your SQL server software connects with your physical storage device. Your SQL server acts as a necessary broker. It enables your CRM to efficiently record information in a standardized format and recall many related data-points with one command. But this telephone game sometimes suffers gaps in the flow of communication that your data can fall through.
According to Microsoft Product Support Services (PSS) more than 95 percent of SQL database corruption stems from a “platform issue” — a failure of the hard storage device beneath your SQL layer. A disk, a controller, a CPU or memory misbehaves for reasons such as a power outage, over-loading, overheating or an improperly configured driver. As a result, a single page of data or more fails to write properly into your permanent storage.
Ensuring your hardware is properly set up guarantees fewer of these interruptions. But even perfectly maintained hardware can’t prevent all corruption. Just like brushing your teeth and flossing won’t prevent all cavities. (Even the best of us skip a night here and there.)
As consultants point out, the real issue with database corruption is that it can persist undetected for weeks, even months, before a user queries a missing page and gets an error message. By that time, it may be too late for a simple restore. You may have unknowingly backed up the corrupted sections well beyond the lifespan of your oldest backup. For most businesses, that’s just seven days.
Nipping SQL database corruption in the bud
Database corruption should never get to this stage, however, if a proper detection and prevention regime is in place.
If you use a cloud CRM provider, this risk is managed for you. Your vendor should provide state-of-the-art infrastructure and perform regular backups and integrity checks, plus keep a knowledgeable data center team on hand to walk you through any restore.
According to Mike Walsh of StraightPath Solutions, a robust integrity check strategy involves regular database integrity checks — and reviews of those checks. It means keeping transaction log backups with your database in full recovery mode.
Best practice is to run your integrity checks before every backup so you don’t end up copying corruption. You’ll also want to keep a complete, up-to-date set of log files corresponding with your data access and operations. Since these are much less data-intensive than backups, you can keep them a lot longer. This is your insurance policy: Should you have to tangle with corrupted backups, you or your hired help will need your log files to maximize your recovery chances.
Your SQL server comes with built-in tools to perform these tasks, plus you can partially automate your integrity checks with scripting. The most important utility is DBCC CHECKDB. This lets you check every page of data for corruption. You can also run Checksum while performing a backup, advises Walsh, adding this won’t catch every error. You’ll still need to perform and review DBBC CHECKDB regularly.
The bottom line
Database corruption can be a real headache for those forced to deal with it. But the myriad of online resources out there will teach you how to manage the risk in-house. With the right approach, you can minimize the risk while maximizing your chances of a successful recovery in a worst-case scenario.