Microsoft Office SharePoint Server(MOSS) 2007 and SQL Server are intricately connected with SQL Server as the back-end data repository for MOSS 2007.SharePoint 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 affect their ability tocut data loss during database recovery operations. Each recovery model handles recovery a little 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.
Options for SQL server recovery models
The three recovery models associated with a SharePoint database in SQL Server are:
Full recovery model
This approach 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 database cause 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 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 recovery model
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 INDEXREBUILD, 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 best recovery model
It is much easier to choose the correct recovery model if administrators consider the recovery goals and requirements for a database. SharePoint administrators must determine, at the very least, how much data they can risk losing, how frequently data in the database changes, whether tables change significantly, what the financial or business impact will be if data is lost, and whether 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 recovery model. 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.
About the Authors.
Ross Mistry is a principal consultant at Convergent Computing, an author and a SQL Server MVP. He installs SQL Server, Active Directory, SharePoint and Exchange Server software within Fortune 500 organizations in the Silicon Valley. His SQL Server and SharePoint specialties include high availability, security, migration and virtualization. You can follow him on twitter @RossMistry.
Shirmattie Seenarine is an independent technical writer with more than 10 years of experience. She has contributed to many books, including Windows Server 2008 Unleashed, Exchange Server 2007 Unleashed, SharePoint Server 2007 Unleashed and SQL Server 2008 Management and Administration.