Protecting SQL Based Applications
by Sean Curiel, on Sep 17, 2015 12:51:08 PM
Whether it’s construction, manufacturing, law offices, or healthcare – these markets all use some type of Customer Relationship (CRM) , Enterprise Resource Planning (ERP), Accounting and Inventory software. This means that there is critical data that must be protected, which is accessing some type of Database, often through the Microsoft SQL product line. Many clients may not even understand how the back-end side of this works, they simply want to know that their data is securely protected without a whole lot of administration or intervention required.
Examples of these types of products include; Sage, vTiger, Casengo, Microsoft Dynamics, SAP, PeopleSoft and Seibel.
One question that often comes up is – what is the best way to protect these SQL databases? Will performing a “full-file backup” of a production database capture all of our data, or will it leave us struggling when it comes time to restore? In virtual environments, where should my backup software be installed?
To be sure, there are different ways to approach these scenarios. To start with, we need to have a better understanding of Volume Shadow Copy Services (VSS), and the various levels of backup consistency.
What is Microsoft’s VSS? (Volume Shadow Copy Service):
VSS is a mechanism for creating consistent point-in-time copies of data known as shadow copies. It helps to create consistent backups of open files and applications.
Think of your functioning SQL Database as a post office, and the mailman as your memory accessing data between multiple locations.
Simply performing a file copy of a production database, for example, will mean that you aren’t getting the full picture of your data. Imagine a file changing before the copy is complete, and not capturing those pending items in memory – resulting in an Inconsistent Backup.
However, if your backup solution utilizes the system wide VSS component, your backup will attempt to grab the data which is in memory. One good example of this is NovaBACKUP Server which will utilize Windows VSS to backup open files. Think of it as if you performed a backup while your system was in Hibernate mode. This is far superior to backing up without VSS, as all data is captured at the same time, resulting in a Crash-Consistent Backup. But depending on the complexity of the applications there is still the potential to lose data in memory.
Finally, when dealing with business applications such as Microsoft SQL, the manufacturer will often write their own application-specific VSS writer which is integrated deep within the core of the application. This application-specific type of VSS is usually realized in a backup solution as a plugin, for example NovaBACKUP Business Essentials contains plugins for both Microsoft Exchange and Microsoft SQL. The result in this case will be an Application-Consistent Backup, offering the optimal level of backup with all data captured at the same time, and the memory contents are saved to a file. This type of backup is comparable to backing up an application after it has been properly shut down.
So coming back to one of our original questions regarding how to best protect a virtual environment running Microsoft SQL; NovaStor recommends installing a single copy of NovaBACKUP Business Essentials on the Hypervisor system itself, the system that is hosting the virtual machine, offering the ability to quickly take snapshots of your virtual machines as needed. Additionally we recommend installing a single copy of Business Essentials on the virtual machine which is running the Microsoft SQL application. This will take full advantage of the application specific VSS writer / SQL plugin within our backup solution and offer the most complete level of protection.
A rather simple answer, with something of a complex explanation.
For more information, check out our recorded webinar on the SQL based applications and how to protect your databases: