locked
Can you minimize logging in a SSIS data flow when the database is in a SQL Server 2012 Always On Group? RRS feed

  • Question

  • We have a file that we are loading 1st into a staging database then into a production database that contains over 5 million rows. Both databases belong to a SQL Server 2012 AG. We would like to minimize the logging in the staging database but t the same time keep the staging database in the AG. I know about fast load and setting the buffer settings in SSIS but I've read that this doesn't work on replicated tables and I am assuming that speaks to the AG.

    Are there any articles or someone's personal experiences with this type of scenario that could point us in the right direction and offset some of the initial data load into staging by minimizing logging?

    Thanks,

    Sue


    Sue

    Monday, December 15, 2014 6:36 PM

All replies

  • Do not stage in a database under AlwaysOn, not sure why it is replicated, what kind of replication?

    Under AlwaysOn the database has to be in Full Recovery mode.

    5 mil rows is not a large number, perhaps you have slow hardware


    Arthur

    MyBlog


    Twitter

    • Proposed as answer by ArthurZ Saturday, December 20, 2014 5:08 PM
    Monday, December 15, 2014 8:21 PM
  • Hi Sue,

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
    Thank you for your understanding and support.

    Regards,
    Katherine Xiong


    Katherine Xiong
    TechNet Community Support

    Wednesday, December 17, 2014 7:02 AM
  • Hi Sue,

    I'm agree with Arthur's point. I don't think there is a way to minimize the logging  on a always on database, since the recovery mode must be full.

    Unless, you disable the AG, do the ETL, and then rebuild it. Obviously, it isn't a good choice.  

    Regards,

    Doris Ji

    Friday, December 19, 2014 8:08 AM
  • Upped the memory but we're going to move them off the AG to their own server for staging.

    Thanks,


    Sue


    • Edited by OutThere Friday, December 19, 2014 8:17 PM
    Friday, December 19, 2014 8:15 PM
  • Pretty much what I thought but wanted to put the question out there (no pun intended). I've made some recommendations to move staging off to another server entirely which they are going to do. We can make those DBs bulked logged or simple recovery.

    Thanks,


    Sue

    Friday, December 19, 2014 8:17 PM