Data Backup with Backup Agent and VSS Writer
In addition to the topics previously discussed in this chapter, both PSQL Server and PSQL Vx Server also provide the following solutions for data backup:
•
Backup Agent
•
PSQL VSS Writer
If your backup software is not aware of the Microsoft Volume Shadow Copy Service (VSS), you can use Backup Agent with your backup software. If your backup software is VSS aware, PSQL VSS Writer is automatically invoked during VSS backups. You do not need to use Backup Agent if your backup software is already VSS aware.
Backup Agent and PSQL VSS Writer can be used together, but there is no advantage in doing so. Your backup process will be more streamlined if you select one method or the other.
Backup Agent
Backup Agent is an optional product. By default, it is not installed. You must install it after you install PSQL Server.
Backup Agent provides a quick and simple method for you to set and manage Continuous Operations on your PSQL database files. Setting and managing Continuous Operations is a critical piece when backing up your PSQL databases without using Microsoft Volume Shadow Copy Service. Backup Agent handles setting and managing Continuous Operations on your open files so that your data is still available from your application during your backup. Once the backup procedure is complete, stopping Backup Agent automatically takes the files out of Continuous Operations and rolls in all the changes captured during the backup.
Backup Agent is compatible with many popular backup applications on the market. Note that the backup application must be able to issue commands to start and stop other applications (so that the commands can start and stop Backup Agent).
For details on Backup Agent, see Backup Agent Guide, which is available on the PSQL website.
PSQL VSS Writer
The Microsoft Volume Shadow Copy Service (VSS) consists of Writer, Provider, and Requestor components. PSQL v12 supports VSS with only a Writer component, PSQL VSS Writer.
PSQL VSS Writer is a feature of the database engine and is enabled for PSQL v12 Server. PSQL VSS Writer is available for use after that product is installed. PSQL VSS Writer is currently not available for use with PSQL Workgroup.
PSQL VSS Writer is available only on Windows operating systems. For more information on Volume Shadow Copy Service, refer to the Microsoft Website document, A Guide for SQL Server Backup Application Vendors.
Overview
During VSS snapshots, PSQL VSS Writer quiesces all disk I/O write activity to all PSQL data and transaction log files, regardless of the volume on which they reside. After the snapshot is taken, PSQL VSS Writer allows all disk I/O to resume; this includes any writes that were deferred during the quiesced period.
PSQL VSS Writer never quiesces disk I/O read activity, allowing normal database processing to continue during the quiesced period as long as writes are not required. PSQL VSS Writer operates normally during the backup phase, although performance may likely be reduced due to the backup activity of the VSS service and VSS Requestor.
The Microsoft Volume Shadow Copy facility allows Backup and Restore products to create a shadow copy for backup in which the files are in either one of the following states:
1
A well-defined and consistent state
2
A crash-consistent state (possibly not suitable for a clean restore).
Files in the VSS snapshot will be in the well-defined and consistent state if all of the following are true:
1
The file’s writer is VSS-aware.
2
The Backup and Restore product recognizes and notifies the VSS-aware writer to prepare for a snapshot.
3
The VSS-aware writer successfully prepares for the snapshot.
Otherwise the writer’s files are backed up in the crash-consistent state.
VSS Writer Details
The following items discuss specifics about PSQL VSS Writer.
•
Supported Operating Systems
The same Windows operating systems that support the PSQL server products also support PSQL VSS Writer. PSQL VSS Writer is functional on the same bitness as the machine’s operating system and the installed PSQL server product. PSQL VSS Writer 32-bit is supported only on 32-bit machines, and 64-bit is supported only on 64-bit machines. If the bitness does not match, PSQL functions properly, but VSS Writer is unavailable.
•
Supported Backup Types
PSQL VSS Writer supports manual or automatic backups of data volumes. PSQL VSS Writer is supported on Full and Copy Volume backups. Incremental, Differential, and Log backups are not supported. VSS recognizes PSQL VSS Writer as a component. However, PSQL VSS Writer does not support component backups. If the VSS Requestor does call PSQL VSS Writer in a component backup, the VSS Writer performs the same actions as in a Full or Copy Volume backup.
•
Virtualized Environment Support
PSQL VSS Writer supports VSS Requesters that trigger VSS backups in virtualized environments. Performing a VM snapshot does not invoke a VSS backup.
•
Multiple Volume PSQL Data Files
PSQL files and transaction logs can reside on multiple volumes. When backing up PSQL files, remember to backup the transaction logs and related files on other volumes simultaneously. Files that are independent of one another may not need to be backed up at the same time as related PSQL files.
•
Backup Solution Compatibility
To determine if a particular backup product recognizes PSQL VSS Writer and will notify the Writer to prepare for a snapshot, start a backup with the product. After the backup is in progress, consult the PVSW.LOG to determine if the PSQL VSS Writer logged the Frozen or Thawed states. If the backup and restore product did not notify PSQL VSS Writer to prepare for the backup, another solution must be used. For example, you could use Backup Agent to backup PSQL data files in the well-defined and consistent state.
•
PSQL VSS Writer and Restore Operations
Stop the PSQL services prior to performing a Restore operation with the Backup software. Failure to do so causes the VSS Writer to inform the VSS Requestor that it cannot participate in the Restore. Transaction logs will need to be restored along with the data files to guarantee the integrity of the data. If PSQL data and transaction log files are restored while PSQL is running, the results are unpredictable and could lead to data corruption.
•
PSQL VSS Writer and PSQL Continuous Operations
You may have an existing backup process that already uses PSQL Continuous Operations or Backup Agent. If you choose, you can continue to use that process with PSQL and PSQL VSS Writer. PSQL VSS Writer does not interfere with Continuous Operations or Backup Agent. However, there is no advantage to using both PSQL VSS Writer and Continuous Operations (or Backup Agent) together. Your backup process will be more streamlined if you select one method or the other.
When PSQL VSS Writer is called and files are in Continuous Operations, be aware that VSS Writer operates independently from any Continuous Operations. If files are in Continuous Operations when a VSS backup is in progress, view PVSW.LOG after the backup completes. Ensure that the Frozen and Thawed states completed successfully and that the data is in a well-defined and consistent state.
Also note that PSQL VSS Writer requires the Microsoft VSS framework. Backup Agent does not use the Microsoft VSS framework. Consequently, Backup Agent does not participate in the backup when the VSS framework calls PSQL VSS Writer and I/O operations are quiesced. Backup Agent must be added separately to the backup process. The backup process must also start and stop Backup Agent.