Recovery modes: simple vs full

Microsoft SQL is a transactional database server. 

It is designed to provide both high availability and high recoverability in the case of a disaster. 

As is typical of most database servers, Microsoft SQL uses transaction log files to maintain database integrity. This means changes to the database are first written to the transaction log, then committed to the database. Incomplete or missing transactions can be rolled back and replayed again to ensure the database integrity is maintained.

Simple recovery mode

In simple recovery mode, SQL maintains a single transaction log file.

• The log file is automatically grown when space is required to commit new transactions.
• Once committed to the primary database file, transactions within the log file may be overwritten by new transactions.
• Total storage size of the log file eventually reaches an equilibrium and stops growing larger.

Full recovery mode

Full recovery mode is intended specifically to preserve all transactions recorded between backups.

This feature was designed to operate in the era of classic file-level tape backups, which typically would be performed only once per day, after hours, to avoid impact to user operations. Recovering data from a backup which might be a full day old (or more) is often considered intolerable in a high-availability RPO environment, and full recovery mode is designed to reduce the amount of potential data loss due to infrequent backups.

In full recovery mode, one or more transaction log files is generated.

• Transactions performed on the database are added to the logs, growing them in size indefinitely.
• Transactions are only purged from the log files when a SQL transaction log type backup is performed by a VSS (Volume Shadow copy Service) backup application.

Typically, you would configure transaction logs to be stored on different physical media than the core database itself.  If a catastrophe occurs, destroying the database volume, the database can be recovered from the previous backup (potentially days old) and the transaction logs on the intact, separate storage media can be replayed to recover all data up to the point of system failure.

x360Recover backups and SQL

x360Recover performs a block-level, image-based backup of disk volumes.  

This type of backup captures a moment in time, by freezing the disk data in a VSS (Volume Shadow copy Service) snapshot.  All data, including both the database and all transaction logs, are frozen together into a consistent state and copied to the appliance as a whole. 

Incremental backups can then be performed as frequently as every 15 minutes, making it largely unnecessary to leverage full recovery mode to ensure minimal data loss if the system becomes damaged or corrupted.  

Axcient recommends configuring Microsoft SQL in simple recovery mode.

Simple recovery mode (with frequent incremental backups) generally provides (a) sufficient RPO to meet most backup SLA requirements and (b) the simplest configuration to deploy and manage.

But what if my client requires full recovery mode for some reason?

Full recovery mode is still completely supported by x360Recover. 

Backups (and recovery of SQL databases) made in full recovery mode will operate essentially the same as those made in simple recovery mode. 

The database and transaction files are still captured in a consistent state during the backup and may be recovered together in a single step during a disaster.

Dealing with transaction log truncation 

However, the primary concern when using full recovery mode is dealing with transaction log truncation.  Since x360Recover does not perform a file-based transaction log backup of SQL, the log files do not get truncated and will grow forever, until the disk volume is full.

• To handle transaction log truncation, use SQL Manager to configure a nightly maintenance job for each database. As part of the maintenance job, select the option to perform a transaction log backup. This will truncate the log files.

Note: You should also perform other actions in the maintenance job, like shrinking files and optimizing storage usage, etc. Also, schedule the maintenance job to run outside of business hours to avoid impacting users.

Other SQL considerations

Third party SQL backups

Third party vertical applications built around SQL often include their own database backup utilities. Technical support for these vertical applications is sometimes contingent on having their own, familiar backup data available for troubleshooting and recovery purposes. Third party SQL backups can conflict with x360Recover, unless special configuration steps are taken.

• Backing up SQL (and truncating log files) requires a VSS-aware backup process. When VSS backups are taken, this data captured by the backup application is marked as protected within VSS. If one backup application takes an incremental backup, followed by a second backup application taking an Incremental backup, each has marked change data as protected - which will be lost to the other application.

To solve this conundrum, x360Recover supports performing SQL (and Exchange) database backups in COPY mode. 

COPY mode parses the entire database with every backup, without marking any of the data as protected. (Note that this can potentially cause the backups to take longer.) In this fashion, x360Recover gathers a complete copy of the database on every backup -  without interfering with the third party backup application’s ability to detect changes between its own backup cycle.

To enable VSS COPY mode:

Modify the x360Recover agent configuration file, aristog.cfg and add the following configuration statement:

BACKUP_TYPE=VSS_BT_COPY

Microsoft Exchange versus Microsoft SQL

Exchange and SQL are both database applications.

• Setting BACKUP_TYPE in the agent configuration file affects both Exchange and SQL equally.
• While Microsoft SQL requires a special backup operation to truncate log files (Transaction Log Backup), Microsoft Exchange performs log truncation normally, during a standard data backup.
• Both Exchange and SQL have a ‘Simple’ and ‘Full’ transaction logging mode.

Note: Exchange calls this ‘Simple’ mode Circular Logging Mode, in which Exchange generates only four (4) log files and rotates through them. By default, Exchange operates in full logging mode and continuously generates a series of numbered 10MB log files. Committed log files are automatically deleted during VSS backup operations.

What if I have both Exchange and SQL services?

If you have (a) a protected system with both Exchange and SQL services, and (b) you require setting BACKUP_TYPE=VSS_BT_COPY to support a third party database backup process, you will have to configure Exchange to use Circular Logging Mode. (This is needed to prevent transaction logs from eventually consuming all available disk space.)

 SUPPORT      | 720-204-4500 | 800-352-0248

• Contact Axcient Support at https://partner.axcient.com/login or call 800-352-0248
• Free certification courses are available in the Axcient x360Portal under Training
• To learn more about any of our Axcient products, sign up for a free one-on-one training
• Subscribe to the Axcient Status page for a list of status updates and scheduled maintenance

968