July 22, 2009 at 2:18 am
Hi
We created mirroring of a sql server 2008 with witness. everything works fine, i also tested automatic failover etc.
the problem is that i need to do additional tasks if the principal changes. my idea is to create something like a trigger/event that fires if the principal changes. i searched for this now for hours, but didnt find any solution.
as alternative i could create a windows-scheduled checking the witness for principal every minute, but this i consider as overhead, comparing to the first idea.
thank you in advance!
July 22, 2009 at 4:47 am
Hi YN,
I remember Ross Mistry talking about this at the European Pass Summit in April. He is avery knowledgable on HA and likes Database Mirroring, but warns against automatic failover for just this type of problem as you lose some control over what and when this happens.
If you take a look at this article: http://www.mssqltips.com/tip.asp?tip=1564 you will see a way of monitoring and getting things to happen when a database fails over to the mirror.
You basically setup an alert and watch WMI for an error log entry for a database failover and then react to this failover with a custom script. You can either failover other related databases if they are there, or you can run a script/batch command or whatever you want to do. Even something simple like send you an email so you can react.
Hope that helps.
EDIT: The link that I supplied talks about SQL 2005, but should still apply to SQL 2008 as the same error can be thrown here. You are still looking at WMI events and can still query the DATABASE_MIRRORING_STATE_CHANGE information.
Regards,
WilliamD
July 22, 2009 at 8:28 am
Hi WilliamD
Thank you very much for the answer. it looks like it is excatly what i need.
i will check it out.
regards
YN
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply