Citrix Database Recovery Model

Last Updated on May 30, 2020

Full, Simple and Bulk-logged are the recovery models that can be assigned to a database. Choosing a Database Recovery Model for Citrix Databases seems straightforward if we go by Citrix Virtual Apps and Desktops product documentation i.e. Full should be the Citrix Database Recovery Model if you are using Mirroring.

https://docs.citrix.com/en-us/citrix-virtual-apps-desktops/technical-overview/databases.html

Chris Gilbert wrote a very detailed blog on Transaction Log usage where he recommends that Simple should be the Citrix Database Recovery Model if you are not using any form of Database replication or if you are using SQL Express. He further advises that Full should be the Citrix Database Recovery Model if you are using Mirroring or Always-on availability groups.

In this article, we will explore why Full should be the de facto and de jure standard of Database recovery Model for Citrix Databases even if you are planning to use standalone Database server or let’s say you are relying on the hypervisor for database high-availability. Let’s start with some Basics.

What is a Database Recovery Model?

Most simplistically, a recovery model is to determine how you want to deal with database Transaction logs. According to Microsoft, “Recovery models are designed to control transaction log maintenance. A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.”

The recovery model of a newly created database will be the same as the recovery model of the model system database. Creating and setting up the database from Citrix Studio or Studio generated Scripts will always set the Recovery Model to Simple. You can configure or change a recovery model of a database from the options menu of its properties. To do that, login to the SQL server using SQL Management Studio, right click on the database, click properties and select the new recovery model from the Recovery Model drop-down list.

Citrix Database Recovery Model

What is a Transaction Log?

Transaction Log is a log file (.ldf) that is used by every SQLs server database to record all transactions and the modifications made by those transactions. SQL Database Engine further logically divides that log file into multiple virtual log files (VLFs).

Transaction Log file

This document does a better job at explaining SQL Server Transaction Log Architecture.

Why Transaction Log is so important?

Transaction Log is import for following operations:

  1. Recovery of individual transactions.
  2. Recovering incomplete transactions when SQL Server is started.
  3. Rolling a restored database, file, filegroup, or page forward to the point of failure.
  4. Supporting transactional replication.
  5. Supporting high availability and disaster recovery solutions: Always On availability groups, database mirroring, and log shipping.

All type of write operations from Citrix Delivery Controller services are not directly applied to database files for performance reasons. SQL Server Database Engine keeps them in buffer pool in memory (known as dirty pages), then periodically issues Checkpoint to write dirty pages and transaction log information from memory to the disk, and also record the information in the Transaction log.

Every Checkpoint is like a recovery point in Windows OS. SQL Server Database Engine uses them to redo, undo, rollback and recover transactions that are recorded in the Transaction Log after an unexpected shutdown or crash. Detailed information about Internal, Manual, Automatic and indirect Checkpoints can be found at SQLShack.

Before Checkpoint can be processed, Log truncation needs to occur [see next topic What Log Truncation is].

So why Transaction Log is so important?

In short, POINT IN TIME RECOVERY OF THE DATABASE IS NOT POSSIBLE if transaction logs are not backed up.

A frequent back up (every 15 minutes to the minimum or every 30 minutes to the maximum) of Transaction log is recommended for these operations to complete successfully. This requirement is in addition to the Full and Differential back up of the databases. If no full back up of the database has been taken previously then backup of Transaction Log will fail.

If your company is less tolerant to work-loss exposure, consider taking log backups more frequently.

What is Log Truncation?

I cannot define it in better words than Microsoft so here is what Microsoft documentation for Transaction Log Architecture has to say about Log Truncation:

Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. When the checkpoint is performed, the inactive portion of the transaction log is marked as reusable. Thereafter, the inactive portion can be freed by log truncation

How Transaction Log relates to Citrix Database Recovery Model

Few paragraphs above in this article, we discussed how important is to back up Transaction Log. The Recovery Model you choose will determine if the backup of the Transaction Log is possible or not.

Backup of Transaction Log in Simple Recovery Model is not possible. Transaction Log is Simple Recovery Model will be marked as inactive and truncated automatically after committing the active transaction.

In Full Recovery Model, until Transaction Log is backed up, it will not be truncated automatically. Once back up is complete, Transaction Logs from the Transaction Log file will be marked as inactive and available for reuse.

Backing up Transaction Log is possible in Bulk-logged Recovery Model too; however, point-in-time recovery of the database is possible as long as your last transaction log backup does not include a bulk operation. Ahmad Yaseen at SQLShack explains minimal logging technique and Bulk-logged Recovery Model right here.

Conclusion

For point-in-time recovery of databases in the event of a crash or unexpected shutdown, Full should be the recovery model of at least Citrix Site Database, irrespective of the high availability option you will adopt for the databases.

Database Recovery Model recommendations for Citrix Databases:

  1. Keep the transaction log file in a separate drive from the database data files, as placing both data and log files on the same drive can result poor database performance.
  2. Use a fixed size transaction log. This stops it ever filling up the disks. It also has the advantage that the transaction log is pre-zero’d and won’t auto-grow.
  3. With fixed size transaction log, Setup a SQL Alert and Job so that when the transaction log reaches 50-80%, the transaction log is backed up, thus freeing it OR Automate a SQL transaction log backup maintenance plan to truncate the inactive portion of the SQL transaction log, and include it to the complete backup strategy with the Full and the Differential backup jobs

Good Reads

Be the first to reply

Leave a Reply