Choosing the Recovery Model for a Database.
A recovery model is a database configuration option that controls how transactions are logged, whether the transaction log is backed up, and what restore options are available for the database. The recovery model you choose for your database has both data recovery implications and performance implications, based on the logging the recovery model performs or doesn't perform.
SQL Server provides three recovery models: Full, Simple, and Bulk-Logged.
The Full recovery model is the default and the recommended recovery model, logging all operations and letting you recover to the point of failure. The Simple recovery model minimally logs most operations and doesn't let you back up or restore the transaction log.
But, how do you decide upon which recovery model to choose for a Dynamics NAV database on Sql Server?
Well, you should consider both the recovery goals and requirements for the database and whether you can manage log backups.
Answering the following questions can help you determine the availability requirements and your sensitivity to data loss.
Recovery Requirements:
- How important is it to never lose a change?
- How easy would it be to re-create lost data?
- Do you have two or more databases that must be logically consistent?
Staffing Considerations:
- Does your organization use system or database administrators? è If it does not, who will be responsible for performing backup and recovery operations, and how will the individuals be trained?
Data Usage Patterns:
For each database, consider the following questions:
- How frequently does the data in the database change?
- Are some tables modified significantly more frequently than other tables?
- Are there critical production periods? If there are, what are the usage patterns during these periods?
- Does the database experience peak periods for insert and other update operations?
You might want to schedule data backups to occur during off-peak hours. When the I/O system is under heavy use, typically, only log backups should be used. - Is the database subject to risky updates or application errors that may not be detected immediately?
If the database is, consider using the full recovery model. è This lets you use log backups to recover the database to a specific point in time.
When to Use the Simple Recovery Model?
Use the simple recovery model if the following are all true:
- Point of failure recovery is unnecessary è If the database is lost or damaged, you are willing to lose all the updates between a failure and the previous backup.
- You are willing to risk losing some data in the log.
- You do not want to back up and restore the transaction log, preferring to rely exclusively on full and differential backups.
When to Use the Full Recovery Model?
Use the full recovery model and, optionally, also the bulk-logged recovery model if any one of the following is true:
- You must be able to recover all the data.
- If the database contains multiple filegroups, and you want piecemeal restore of read/write secondary filegroups and, optionally, read-only filegroups.
- You must be able to recover to the point of failure.
- You want to be able to restore individual pages.
- You are willing to incur the administrative costs of transaction log backups.
When to Use the Bulk-Logged Recovery Model?
The bulk-logged recovery model is intended strictly as an adjunct to the full recovery model. It is recommended that you use it only during periods in which you are running large-scale bulk operations, and in which you do not require point-in-time recovery of the database.
- When is the Dynamics NAV database subject to periodic bulk operations on the database?
Generally, the bulk-logged recovery model resembles the full recovery model, except that it minimally logs most bulk operations. A transaction log backup captures the log and, also, the results of any minimally logged operations that have completed since the last backup. This can make the log backups very large. Therefore, the bulk-logged recovery model is intended only for use during bulk operations that allow for minimal logging.
It is recommended that you use the full recovery model the rest of the time.
Management Summary
The simple recovery model is generally appropriate for a test or development database. For a production database, the best choice is typically the full recovery model. The simple recovery model is sometimes appropriate for a small production database, especially if it is mostly or completely read-only, or for a data warehouse.
Very important!
Make sure that you thoroughly test the backup and RESTORE process !
It is not a very pleasant scenario to discover after a database crash that your backups are corrupt or nowhere to be found or no one knows how to restore the backup(s)… So please test this scenario a couple of times until you feel confident about it.
Some people even say this: You do not have a backup/restore solution until you have successfully tested the complete process.
Recent Comments