Post reply

Backups on mirror server

  • January 23, 2008 at 1:48 pm

    #185961

    I have a database set up on a server with backup and maintenance plans scheduled. I want to set up mirroring. I know that I need to do a full and a transaction log backup and then restore them on the mirror server with NORECOVERY.

    Do I schedule backup and maintenance jobs on the mirror server? Seems like I'd have to to truncate the log, but does the db being in norecovery mode make a difference? The only thing I can find in documentation is that I need to copy the jobs etc. to the mirror server.

    Thanks.

  • January 23, 2008 at 2:27 pm

    #771061

    Hello Linda,

    You can't create backup schedules and maintenance jobs on the mirrored database as it will be in restoration state every time and it will be only be accessible when the principal fails over and the mirror becomes the principal. Please leave the backup schedules and the maintenance jobs on the principal server and if you want then you can go ahead and create a job for a snapshot of the mirrored database in order to access it.

    Hope I'm clear otherwise drop a reply.

    Thanks


    Lucky

  • January 23, 2008 at 2:45 pm

    #771075

    OK. Can I have a copy of the job on the mirror server, so that I could just schedule the job and not have to recreate, if the mirror became the primary?

    Thanks,

    Linda

  • January 25, 2008 at 4:45 pm

    #772069

    Yeah, since you can't mirror system DBs you can copy all your jobs and SSIS packages, etc over to be ready in case it has to failover.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 28, 2008 at 8:09 am

    #772445

    Thanks, this is starting to make sense now. Just one more question. (I hope 🙂

    If the mirror never becomes the principle, will the transaction log just keep growing? Do I need to make the mirror dbs live and run backups occasionally? Or do any other maintenance on the mirror?

  • January 28, 2008 at 8:17 am

    #772451

    Well, keep in mind that the mirror in unavailable to anything until it's brought online. That means that there really isn't any maint you can do on it. You can't back it up, reindex, nothing. And anything you do on the primary is going to be done on the mirror. So all of your maint (except backups) will be done on the mirror as well. Mind you, these are just things that change data. CheckDB won't be pushed to the mirror, but a Defrag will.

    And no, you really shouldn't need to recover your mirror for maint. It should be exactly what your primary is, and by mere definition they have to be identical.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

  • January 28, 2008 at 8:31 am

    #772462

    Thanks for your help. I think I'll start with one of our small dbs and monitor it for a bit, then do the others.

    Linda

  • January 28, 2008 at 8:34 am

    #772464

    An excellent plan if I've ever heard one...

    If you have it in synch mode, just remember that anything you do on the primary has to be done on the mirror as part of the same transaction. So reindexing will take at least twice as long. So for your maint windows, switch it to asynch mode and it won't hold up the progress of your reindexing on the primary.

    Watch my free SQL Server Tutorials at:
    http://MidnightDBA.com
    Blog Author of:
    DBA Rant – http://www.MidnightDBA.com/DBARant

    Minion Maintenance is FREE:

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply