Virtual Machine Snapshots Virtual machines provide exciting possibilities concerning backup, recovery, and cloning of databases. Convenient mechanisms are available for obtaining consistent snapshots of data...
Virtual machines provide exciting possibilities concerning backup, recovery, and cloning of databases. Convenient mechanisms are available for obtaining consistent snapshots of data volumes in virtual machines. Correctly implemented and tested snapshots can significantly simplify backup tasks. These snapshots can also clone databases to other environments. Snapshots of virtual machines to backup databases may be undoubtedly suitable for test and development environments. Snapshots of virtual machines are popular with system administrators because they standardize implementations of backups across organizations and remove the need for application-specific backup configurations.
Snapshots of virtual machines can perform point-in-time restores of virtual machine images and individual files contained within virtual machines to the last snapshot point. However, backups of databases allow restoring to any point in time using a combination of full and differential backups of databases and backups of transaction logs.
For databases that use full or bulk-logged recovery models, clearing of transaction logs only occurs after logs are backed up using database backup commands. Some tools for snapshots of virtual machines offer options to perform transaction log clearing after taking snapshots of virtual machines. However, when conducting concurrent backups of databases for virtual machines, such options can break log backup chains for backups of databases.
Using snapshots of virtual machines exclusively also removes some essential database recovery capabilities that are available with backups of databases (such as restore of individual database files and pages, and piecemeal restores). Consequently, it is essential to evaluate requirements for recovery time objectives and recovery point objective (which tend to be short for databases) to meet expectations.
For example, snapshots of virtual machines allow freezing of virtual disks at a point in time for a time duration. Consequently, virtual machines suffer from stun moments during phases of consolidation for snapshots. Stun moments can be lengthy and disruptive to throughput. The more demand for input and output is present on virtual machines, the longer the duration of stun moments will be. For production databases and high-performance workloads, underlying snapshot technologies may introduce unacceptable pauses to the throughput of databases. At the very least, for production systems, these types of backups need to be scheduled during periods of low activity where pauses are tolerable.
Even with frozen virtual disks, snapshots of virtual machines do not guarantee consistency of data unless such snapshots include other mechanisms. Database applications require that data structures on disk remain consistent with memory contents at any time. Consequently, database applications need to be aware of ongoing backups so that they can flush their data buffers to disk and suspend pending disk transactions until backups complete. This process is called quiescing. Snapshots of virtual machines quiesce file systems but not databases. While quiescing file systems guarantee consistency of data on the file level, database applications remain unaware of backups and restores. Consequently, database applications may not have consistent data files with snapshots of virtual machines. Consequently, snapshots of virtual machines with live databases with database files residing in virtual machines can lead to apparently successful backups of inconsistent data.
The lack of a guarantee for consistency of data usually means that snapshots of virtual machines need to place databases in hot backup mode. Alternatively, databases running in the Microsoft Windows operating system may be able to take advantage of the Volume Shadow Copy Service. The Volume Shadow Copy Service coordinates activities of providers, writers, and requesters in creating and usage of shadow copies, furnishes default system providers, and implements low-level driver functionality necessary for any provider to work. Similar system services may also exist in other operating systems. However, any such system services create significant complexity for their correct implementation, configuration, and verification.
Further, some tools for snapshots of virtual machines may provide generic quiescing mechanisms via user scripts. However, actual implementations of quiescing depend on end users. Pausing databases during snapshots may not be possible or feasible. Further, it is required to perform backups of databases with database agents that use application programming interfaces for these databases to perform application-aware backups. Otherwise, by performing snapshots of virtual machines, consistency of data is only guaranteed for file systems and not for applications. This potential inconsistency of data defeats the purpose of solutions for protection of data. Consequently, it is not recommended to backup highly transactional databases with substantial input and output via snapshots of virtual machines.
While virtual machines offer exciting mechanisms for backing up databases, there is usually no substitute for the flexibility and safety provided by tools dedicated to backing up and recovering databases, especially for production databases and high-performance workloads.
View backup histories with the free SQL Backup Status Reporter. Access more features with SQL Admin Toolset’s Backup Status. Level up to back up faster than native SQL Server, save space using dynamic compression, reduce failures from network problems, automate backups, ensure organizational compliance via policy-based management, back up to cloud storage, instantly access data from backups, receive alerts, and create reports with the full-featured SQL Safe Backup.
Also, discover instances on networks and check for versions with the free SQL Instance Check. Access more features with SQL Admin Toolset’s Discovery, Patch Analyzer, and Inventory Reporter. Level up to automatically discover new instances, find SQL Business Intelligence Services, identify databases on Always On Availability Groups, and organize using tags with the full-featured SQL Inventory Manager.
Contact our sales team to get a personalized demo of our database management software for SQL Server!
