none
Bulk Loading with Availability Group

    Question

  • We have a critical database that is bulk loaded every 30 minutes.  The database is currently in simple recovery model for the obvious reason of keeping the transaction log manageable.  I would like to add it to our SQL Server 2012 availability group so the database is always available.  It has to be set to full for the recovery model.  Is there a good way to keep the transaction log from getting unwieldy without doing backups as often as the loads take place?  The database is a little over a GB and will only grow about 1-2% a month.


    Thor


    • Edited by Thor Bev Wednesday, August 13, 2014 9:32 PM
    Wednesday, August 13, 2014 9:07 PM

Answers

  • > Is there a good way to keep the transaction log from getting unwieldy without doing backups as often as the loads take place?

    That's a pretty small database.

    What's wrong with doing backups as often as the loads take place?  You can perform the backups on the secondary replica and the log space will be reused on all replicas.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, August 13, 2014 9:54 PM
  • If your load is high, then you can plan like if it works, if the db size is small plan full backup daily durning non-load hours,but log backup you need to schedule the log backup frequently inorder to reduce the log usuage.

    if the database size huge, plan daily differential _weekly once full backup+frequent logbackups(everyday -you need to choose the timings).


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, August 15, 2014 8:06 AM

All replies

  • > Is there a good way to keep the transaction log from getting unwieldy without doing backups as often as the loads take place?

    That's a pretty small database.

    What's wrong with doing backups as often as the loads take place?  You can perform the backups on the secondary replica and the log space will be reused on all replicas.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, August 13, 2014 9:54 PM
  • I was hoping to keep down the overhead of running the backups so often.  There are multiple availability groups (AGs) on the cluster so other AGs could be running as primaries even though the group in question is secondary.  I figure I must be missing something in transaction log maintenance since AGs are replacing mirroring.  I guess I'll go with the backup option, secondary or not, unless there are any other ideas out there.



    Thor

    Thursday, August 14, 2014 9:26 PM
  • If your load is high, then you can plan like if it works, if the db size is small plan full backup daily durning non-load hours,but log backup you need to schedule the log backup frequently inorder to reduce the log usuage.

    if the database size huge, plan daily differential _weekly once full backup+frequent logbackups(everyday -you need to choose the timings).


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Friday, August 15, 2014 8:06 AM