Post reply

DB Mirror, maintenance plan/backup job failover?

  • August 31, 2007 at 2:40 pm

    #118380

    Hi!

    Looking for suggestions on the best approach to tackle this issue.

    Background Info

    SQL 2005 Standard (3 systems) - mirroring & failover work fine

    Backup Schedule - Daily Full, 3hr Differential, 15min Transaction

    Backup location - DFS share (prevent single point of failure)

    Problem

    When failover occurs, the Mirror server does not automatically begin to backup the databases, and the primary server, when re-enabled, tries is unable to backup the databases because of the MIRRORed status. Currently, i'm using a Maintenance plan for backups on the Primary server.

    Goal

    Detect the failover, then have the mirror server automatically backup the (now primary) database. This should work both ways; when i fail the db back to the Primary server, the Mirror server should stop trying to backup the db and the Primary server should start the backups again.

    Ideas for Solution

    1. Create a "Mirror Monitor" job to run every x minutes, checking db status, then enable/disable the schedule in the Maintenance plan based on that status

    2. Add some kind of script/code into the Maint Plan to check db status, then conditionally execute the remainder of the maint plan

    - pros - single Maint Plan for both systems

    - cons - haven't figured out how to do conditional execution based on a query result inside of a maint plan

    3. Any other suggestions?

    Again, looking for any/all suggestions on the best route to take, and any scripts/pointers from anyone else who has already tackled this issue. If any additional info is needed, please let me know.

    Thanks in advance!

    Mike

  • September 1, 2007 at 2:54 pm

    #730918

    Hi Mike,

    Microsoft is your friend, they've almost done it Check http://www.microsoft.com/technet/prodtechnol/sql/2005/mirroringevents.mspx and download the word document. As you'll see, you can catch WMI events with SQL Server 2005, and there's a nice example how to catch a failover event with an alert, for which you can define response actions, for example a job which enables/disables the maintenance plan jobs.

    So choose Idea #1 and read that word doc. Have fun!

    -- Erik http://blog.rollback.hu

  • September 1, 2007 at 3:32 pm

    #730920

    how do i use sql to read data from my text files

  • September 2, 2007 at 9:14 am

    #730939

    Thank you Erik!

    I must have been using the wrong keyword combo in my searching - i found that mirror monitor (overview only) via google in BOL, but no handy scripts and it seemed very GUI only. Definitely have to scour technet directly in the future

    @ Nartey - few ways, need more specifics

    1. right click on db, click import, follow the wizard

    2. SSIS

    3. bcp

    4. OPENQUERY, OPENROWSET using text driver

    Mike

Viewing 4 posts - 1 through 3 (of 3 total)

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