Microsoft SQL Server Recovery Model Options for Pharos Blueprint and Uniprint

What is the difference between the Recovery Models within Microsoft SQL Server, and how will they affect my Pharos software installation?

Microsoft SQL Server 2005 through 2012 offer three Recovery Models: Simple, Full, and Bulk Logged. Of the three, Simple and Full are the most common in normal production environments. Aside from their differences in a backup/recovery scenario, they also affect database performance and disk utilization if not chosen and/or configured correctly. Below follows an explanation of both Simple and Full recovery models, and their impact on performance and disk utilization.

Simple Recovery Model.

The real impact of the Simple Recovery model is that the database is only as good as the last backup. Transactions (inserts to, updates against, deletions of, and other data activities) are committed almost immediately to the database. The benefit is that the amount of disk space required by the SQL Transaction Log (the transaction log is a serial record of all the transactions that have been performed against the database since the transaction log was last backed up) is minimal, and usually only gets large when numerous transactions (bulk inserts, mass deletions) are occurring. Not having to maintain an active transaction history also speeds the performance of the SQL database, since more processing time can be used for queries and tasks versus managing the log. However, this all comes at the risk of catastrophic data loss (potentially, an entire days' worth or more), depending on the cadence of database backups and their success rate.

Full Recovery Model.

The Full Recovery model, when managed properly, allows a database to be restored to a definite point in time, utilizing the information in the transaction log (and backed up transaction logs) to arrive at that point. For example, if a large set of data was inadvertantly inserted or deleted from the database, the full recovery model would allow the restoration of the database to the time right before that action occurred (this would only be possible in a Simple model if the database was first backed up prior to engaging the action). The price for this ability is that the SQL Server must be managed more than many organizations are able, or want, to commit themselves. Most importantly, a regimented back up of the transaction log is required (Microsoft TechNet article http://msdn.microsoft.com/en-us/library/aa173551%28v=sql.80%29.aspx does a great job of outlining a sample plan). This allows the transaction log to remain small enough for continued database performance, but with enough information to readily recover from a database mishap.

If a regular transaction log back up does not occur, it is very possible to reach the point where database response to even simple queries is painfully slow, and/or the disk housing the log file runs out of space (remember, the transactions are only trimmed when the log file is backed up).

For a good jumping point, please visit the Microsoft article on Recovery Models for SQL Server, at http://msdn.microsoft.com/en-us/library/ms189275.aspx

Impact With Pharos Blueprint and Uniprint

By default, databases for Pharos Blueprint and Uniprint are built using the Full Recovery Model. Further, when installing on Standard or better (not Express) SQL Server editions, a SQL Agent job is created that will at least make sure that a regular backup transaction log file is managed. However, SQL Agent on many machines is disabled or not started (sometimes as a SQL Server security measure), and so the backup does not occur without some third-party or manual intervention, and the transaction log file grows bigger and bigger until performance and disk space suffer.

What To Do?

If your organization is already managing Full recovery mode databases, then keeping the Full recovery model selected is wise. However, if answers to the question "How do we manage disaster recovery for our SQL databases?" draws blank stares, giggles, or unanswered email, it is best to configure the database for the Simple recovery model, and make sure that some regular "full backup" is performed, and the resulting backup file stored somewhere safe.

To engage Simple recovery, run the following SQL command as a user logged in with "sysadmin" privilege:

USE master;

ALTER DATABASE <DatabaseName>
SET RECOVERY SIMPLE;

Be sure to swap out <DatabaseName> with your database name (do not include the < > characters) and execute. Within Blueprint, you will have to do this to the following databases:

> psbprint

> psjobs1 - ? (depending on your installation settings and organization size, this may be up to psjobs4 or psjobs13 or more)

> psreports (if using Pharos Blueprint v5.0 or greater)

In Uniprint, you simply have to worry about the 'pharos' database.