none
Recovery model for Datawarehouse databases RRS feed

  • Question

  • Hi 

    We are building up a new Datawarehouse environment where there will be lot of of Inserts/Updates/Deletes will be happening. i would like to know what is the recommended recovery model for databases participating in these activities ? If i keep it in Full recovery model, does it effect my performance?

    Any help highly appreciatede

    -Kevin

    Wednesday, April 17, 2013 7:27 AM

Answers

  • There are a couple of posts above advising to us FULL recovery if the database is critical - NO, NO, NO!

    The choice of recovery model is NOT about how critical your database is.  It is about how often the data is updated and whether you need to recover to a particular point in time.  For example, you could have a Datawarehouse database that is extremely critical to the business, but if it is only ever populated in a once-daily ETL batch then you're better off keeping the database in the SIMPLE recovery model and performing a full or differential back up after the batch completes.

    Also, you would typically perform bulk loads in a Datawarehouse ETL process, and FULL recovery can hinder performance due to every transaction being fully logged.  If you need point-in-time recovery, and have chosen the FULL recovery model, then you may want to consider switching to the Bulk-logged recovery model for the duration of your ETL process, so that your bulk operations are minimally logged.

    Wednesday, April 17, 2013 10:26 AM

All replies

  • Hi,

    I think it will be a very critical database, so it is better to keep the recovery model in FULL so that you can have point in time recovery of your database. Also, make sure you have transactional log backups configured.


    Thanks & Regards RAJUKIRAN L Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.


    • Edited by rksqldba Wednesday, April 17, 2013 7:39 AM
    Wednesday, April 17, 2013 7:35 AM
  • If you set your db to FULL recovery model  you would have to  perform a log file backup in order to control the physical size of the log and be able to recover the data  AT POINT of time.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, April 17, 2013 7:37 AM
    Answerer
  • Choice of recovery model has to be made based on the amount of data loss affordable and performance of some bulk operations. Below article has some good information on choosing the recovery model for a database.

    http://msdn.microsoft.com/en-us/library/ms175987(v=sql.105).aspx

    With respect to performance, I can think of bulk operations and disk space, which will have different behavior in different recovery models

    Wednesday, April 17, 2013 7:52 AM
  • if the database is very important for your bussiness, you must set it to full recovery model.

    otherwise, you can set it to simple model.


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

    Wednesday, April 17, 2013 8:06 AM
  • There are a couple of posts above advising to us FULL recovery if the database is critical - NO, NO, NO!

    The choice of recovery model is NOT about how critical your database is.  It is about how often the data is updated and whether you need to recover to a particular point in time.  For example, you could have a Datawarehouse database that is extremely critical to the business, but if it is only ever populated in a once-daily ETL batch then you're better off keeping the database in the SIMPLE recovery model and performing a full or differential back up after the batch completes.

    Also, you would typically perform bulk loads in a Datawarehouse ETL process, and FULL recovery can hinder performance due to every transaction being fully logged.  If you need point-in-time recovery, and have chosen the FULL recovery model, then you may want to consider switching to the Bulk-logged recovery model for the duration of your ETL process, so that your bulk operations are minimally logged.

    Wednesday, April 17, 2013 10:26 AM
  • Usually most of the DBA's keep the DW DB's recovery model in simple. 

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers. Thanks!

    Wednesday, April 17, 2013 11:45 AM
  • Hi SQLUser786,

    Mostly data warehouse databases are in Simple recovery mode, due to the reason of maintaining the small log size. if you will use Full recovery model then all the transactions will be fully logged which will take more time to complete. So you can use Simple.

    **Keep in mind that recoveribility perspective Full is more safe rather than simple , because in full recovery model you can take TLog backups which will provide you point in time recovery.

    Thanks and Regards:

    Prince Kumar Rastogi

    Wednesday, April 17, 2013 7:58 PM
  • Thanks eveyrone for detailed answers.

    Our business can afford one day of data loss and we have ETL packages which does a whole lot of bulk inserts and normal inserts/update/delete operations. All the ETL packages throughout the night and will complete early in the morning hours and no ETLs will run during the day time.

    So can i say that putting the DB in full recobvery model will degrade performance if i have large ETL packages with lots of insert/bulk inserts/updates/deletes bcoz it has to log everything in the database??

    -sqluser


    • Edited by sqluser786 Friday, April 19, 2013 1:41 AM
    Friday, April 19, 2013 1:40 AM
  • if your bussiness allows one day of data loss, you can set the recovery model to simple.

    and taking one differential backup everyday can achieve the requirement.


    If you haven't all the things you want,be grateful for the things you don't have that you didn't want.


    • Edited by Wison-Ho Friday, April 19, 2013 2:32 AM
    Friday, April 19, 2013 2:32 AM
  • Hello,

    Please correct me if I am wrong but I think from performance point of view, simple or full recovery mode, both will be same as long as you are the data load in one big transaction., ie the amount of log growth and\or log used will be same. I am not talking about doing ETL multiple times or in small batches. i,e if two transactions (one in simple recovery and other full recovery mode) start with a clean log slate, performance wise it will be same.

    In other words, the amount of log generated either in simple or full recovery model would be same for one transaction.

    From management perspective, simple recovery mode will easier to maintain\manage as you  okay with loss of one day's worth of data.


    Hope it Helps!!

    Friday, April 19, 2013 2:43 AM
  • Hello,

    Please correct me if I am wrong but I think from performance point of view, simple or full recovery mode, both will be same as long as you are the data load in one big transaction., ie the amount of log growth and\or log used will be same. I am not talking about doing ETL multiple times or in small batches. i,e if two transactions (one in simple recovery and other full recovery mode) start with a clean log slate, performance wise it will be same.

    In other words, the amount of log generated either in simple or full recovery model would be same for one transaction.

    From management perspective, simple recovery mode will easier to maintain\manage as you  okay with loss of one day's worth of data.


    Hope it Helps!!


    It depends on the transaction.  Some types of operation, for example bulk inserts, can be minimally logged (i.e. just enough information is logged to allow the transaction to be recovered without supporting point in time recovery).  In SIMPLE or Bulk-logged recovery models they will minimally log these transactions, whereas FULL recovery model will fully log them.  There is an overhead in fully logging the transaction, as it is writing more data to the transaction log, so the operation will be slower.
    Friday, April 19, 2013 8:14 AM