« August 2008 | Main | October 2008 »

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.

‘Big’ Backups in Dynamics NAV

When using the Dynamics NAV client to create a backup of a database, you might get into the situation that the backup (.fbk) file that you are creating exceeds 2Gb of data.

Well, in fact this will never happen, because as soon as the .fbk file reaches 2Gb, Dynamics NAV will create an extra .fbk file and continue in that one, until it also reaches 2Gb and then it will create a third file, and so on…

Sometimes this behavior might give an issue

The name of the extra backup files will contain a number. If you named the backup file backup1.fbk, then the second backup file will be named: backup2.fbk.

The problem occurs when you did not put a number in the name of the backup file AND the path of the folder in which you are storing the backup contains a number. Dynamics NAV will take the name and path of the backup file as a string, and will start searching from right to left for a number to increment. If one is not found then 1 will be added as the number.

So you might get into the situation that you are creating a backup named fin.fbk in a folder called: c:\temp1\fin.fbk. As soon as this backup file becomes greater then 2Gb, Dynamics NAV will try to create an extra backup file like this: c:\temp2\fin.fbk and if the folder c:\temp2 does not exist, an error will occur.

So think about this when you are creating a backup (.fbk) from a big Dynamics NAV database.

An interesting blog about Business Intelligence.

Recently, when I was looking up some information regarding Sql Server Reporting Services, I came upon this Blog: http://www.biblogs.com

I found it to be a very interesting blog and thought it's so interesting to share it with the Plataan knowledge blog addicts.

The goal of the blog is to provide that central place people can come to easily find information in the business intelligence and data warehousing space.

Hug a developer...

Someone forwarded me this today, and for all the developers out there, I thought I had to post it...

October 2011
Mon Tue Wed Thu Fri Sat Sun
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

powered by FreeFind
© 2005 Plataan bvba | info@plataan.be