Re: Running a script when Database Mirroring fails over

Hi,

I'm also interested in this. I assume you're not using a witness server?

ELS wrote:
Hi all-

I'm trying to determine the most efficient method for running a script after my Principal fails over to my Mirror within Database Mirroring.

I have seen a lot of information on blogs, etc. related to setting up alerts using WMI or a Service Broker. But from my testing, those are configured on the Principal server. If my jobs are configured to send a notification and run a script when the Principal fails over, how will those jobs execute if the Principal server completely crashes?

So, my thought was to create jobs on both the Principal and Mirror servers that monitor the sys.database_mirroring table. The jobs look to see if the mirroring_role for the database has changed (e.g. on the mirror server, I check to see if the mirroring_role for the database has changed to 1. If it has, send the notification and run a script). My job also disables itself and enables a second job to see if the role has switched back to the Mirror.

This works and I'm fine with it, but I thought I'd post to see if there is anything better (more efficient, less intrusive, whatever) than what I've come up with. I have to believe that other people smarter than I have encountered this, so I'm interested in alternative solutions.

Thanks in advance!


--
Gerry Hickman (London UK)
.

Relevant Pages

  • Re: How to send E-mail without an external SMTP server ?
    ... you're sending email using external server. ... I want to find a way to send email without an external smtp server. ... Actually I was writing a script that can submit jobs automatically on a remote server. ... I should not submit the jobs all at once, because this will take up all resources of the server. ...
    (comp.lang.python)
  • Re: Deleting all jobs in remote print queue
    ... remote machine is Server 2003 or XP. ... We have situations in which we need to delete all print jobs, ... print queues, via a script. ...
    (microsoft.public.windowsxp.print_fax)
  • Re: Copying jobs to a new server.
    ... You can generate the script for all jobs (Enterprise manager -- Right click ... node in SQl Agent -- Jobs| All tasks| Generate sql script, ... script in the destination server. ...
    (microsoft.public.sqlserver.server)
  • Re: Wut does SQL do in this case?
    ... I don't usually script ... out the jobs as I don't usually change the standard replication jobs (these ... Paul Ibison SQL Server MVP, ... Dim objReplication ...
    (microsoft.public.sqlserver.replication)
  • Re: script all MSDB jobs in TSQL
    ... Server is Source Server where you want to script jobs from ... owner is the new owner of the jobs. ...
    (microsoft.public.sqlserver.server)