DBA 101: Choosing a Database Recovery Model (Siemens Teamcenter)
The recovery model of a SQL Server database determines how changes are recorded and stored in the database’s transaction log. The three options for the recovery model are Full, Bulk-logged, and Simple:
Full: This is the best model for preventing critical data loss and restoring data to a specific point in time, and it is generally used by enterprise production systems. If the transaction log is available, it is possible to get up-to-the-minute recovery and point-in-time restore if the end of the transaction log is backed up and restored. The trade-off for the Full recovery model is that it requires more administrator attention and a more complex backup/restore strategy.
Bulk-logged: This model is for databases that have critical data, but for which the DBA wants to minimize storage or performance impact of bulk operations (e.g., bulkdata loads, table copies, index maintenance). It provides nearly the recovery power of the Full model, but with some limitations. For a list of operations that can be minimally logged please click here (be sure to select the version of SQL Server that you are using from the dropdown)
Simple: This model is appropriate if the data backed up is not critical, data is static or does not change often, or if data loss is not a concern for the organization. If an active database is lost with the Simple recovery model, the organization loses all transactions since the last full or last differential backup. This model is typical for test environments or production databases that are not mission critical or have very light activity.
In general, Teamcenter databases are frequently updated and hold information that is of high importance to the organization. Especially for large Teamcenter databases, full backups may not be feasible with high frequency (e.g., daily or more often). For these reasons, Siemens recommends using the Full recovery model with Teamcenter databases.
Administrators must be aware of the management implications of the Full and Bulk-logged recovery models. In particular, under these recovery models, transaction logs must be backed up regularly to prevent them from filling up or exhausting available disk space.
For more information on choosing a database recovery model for Siemens Teamcenter please see the Best Practices for Running Siemens Teamcenter on SQL Server whitepaper
_____________________________________________________________________________________________
Colin Stasiuk (MCP, MCTS SQL 2005/2008, MCITP DBDEV, and MCITP DBA) is an accomplished Microsoft SQL Server DBA who has been working with SQL Server since 1996. He is the founder of Benchmark IT Consulting and his specialties include SQL Server Administration, Performance Tuning, Security, Best Practice / Standards, Upgrades, and Consolidation. Colin is a proud PASS member, President of EDMPASS (The Edmonton Chapter of PASS), and has recently co-authored a book on SQL Server 2008 Policy Based Management.