How SQL Server recovery models impact your SharePoint databases

SQL Server and Microsoft Office SharePoint Server 2007 are intricately connected with SQL Server as the back-end data repository for Office SharePoint Server 2007. Administrators sometimes have difficulty understanding this relationship, which can prevent them from having a full appreciation for it.

One of the main challenges faced by SharePoint administrators when managing a SharePoint infrastructure is figuring out how the SQL Server recovery models impact their ability to cut data loss during database recovery operations. Each recovery model handles recovery differently. Specifically, the models differ in how they manage logging, which governs whether or not a SharePoint database can be recovered to the point of failure. The three recovery models associated with a SharePoint database in SQL Server are full, simple and bulk-logged.

Full recovery model

More on SharePoint and SQL Server Does your SQL Server database improve SharePoint performance?  SharePoint performance demands finely tuned SQL Server, storage

The full recovery model captures and logs every database transaction, which makes it possible for administrators to restore a SharePoint database to an arbitrary point in time. When using this model, administrators must conduct backup maintenance on the transaction log to prevent logs from growing too large and disks from becoming full. After each backup, disk space is opened again and can be used until the next planned backup.

Some SharePoint administrators may notice a slight degradation in SQL Server performance when maintaining a transaction log. Don't sound the alarm. This is typical when logging all transactions to the SharePoint databases. SharePoint administrators who insist on preserving critical data often overlook this issue because they realize this model offers the highest level of recovery.

Simple recovery model

SharePoint administrators using the simple recovery model have the least number of options when recovering data because the transaction log gets truncated after each backup. This means changes after the latest full or differential backup are unprotected. As a result, you can only recover a database to the point of the latest successful database backup.

For instance, if an administrator performed a full or differential backup at midnight, and a SharePoint content database crashed at 4:00 p.m., it loses all changes that were made after midnight. In addition, data entered into the database after a successful full or differential database backup is unrecoverable. This model has some advantages. It requires the least amount of administration because transaction log backups are not required. SharePoint administrators who store data that is not deemed mission critical tend to like the simple recovery model.

Bulk-logged

This final recovery model maintains a transaction log and is similar to the full recovery model. However, the main difference is that transaction logging is minimal during bulk operations to maximize database performance and reduce the log size when large amounts of data are inserted into the database. Bulk import operations such as BCP, BULK INSERT, SELECT INTO, CREATE INDEX, ALTER INDEX REBUILD, and DROP INDEX are minimally logged.

Because the bulk-Logged recovery model provides only minimal logging of bulk operations, SharePoint administrators cannot restore a SharePoint database to the point of failure if a disaster occurs during a bulk-logged operation.

In these situations, administrators typically must restore the database, including the latest transaction log, and rerun the bulk-logged operation. This model is typically used by organizations running large bulk operations that degrade system performance and do not require point in-time recovery. Bulk-logging is not a common selection with SharePoint databases.

Choosing the appropriate recovery models

It is much easier to choose the correct recovery model if the administrator considers the recovery goals and requirements for a database. SharePoint administrators must determine, at the very least, if they can risk losing data, how frequently data in the database changes, whether or not tables change significantly, what the financial or business impact will be if data is lost, and whether or not there is more than one database in the SharePoint farm that needs to be logically consistent.

Use the simple and bulk-logged recovery model if you don't require a point-in-time recovery and you are willing to lose data. On-the-other-hand, use the full recovery model if it is important to recover data to the point of failure. By default, the SQL Server system databases are configured with the simple recovery model.

In addition, the SharePoint Config, Content and Admin databases are set to FULL and the SSP and Search databases are set to Simple. The recovery model on each database can be changed on the fly, so go ahead and choose the best recovery models for your SharePoint databases.