sexta-feira, 9 de março de 2012 20:30
So I've successfully setup alerting in SQL2008R2 for my newly established mirror:
Here were my steps for setting up my alert email:
1) I setup database Mail with a profile named SQLAlerts
2) I defined my operator
3) I setup SQL Agent Settings
4) I then restarted SQL Agent To Active Settings
5) I then defined my Alert (NOTE: This is the step I need assistance with)
Now the goal was to setup an email alert to notify me of a database fail over which I have successfully done via WMI event alert:
Now this works perfectly, but it sends the same generic email whether it's the Principal server failing over or the Mirroring server failing over. I have been tasked with figuring out how to have this alert specify the name of the machine that failed over in the body of the email. So with that said, is there a way to add WMI code to parse the computer name and input this information into the body of my email alert? Also if there is a better way of doing this outside of using WMI? If so, I'm open to suggestions. I'm continuing to investigate my options but would love to hear your suggestions. Again the goal here is to be notified via email that a fail over occurred on the server name of the server that failed over. =) (lots of failing over going on.) Thanks in advance.
Todas as Respostas
sexta-feira, 9 de março de 2012 20:38
I am not using Alerts in my environment, we have other ways of monitoring the system, but from what I see it seems the easiest way would be to just include @@servername in your queries column-list... This would at least tell you which server the event was fired from...
Disclaimer: This was just a quick shot. I don't even know if this is a feasible way...
sexta-feira, 9 de março de 2012 21:04
Thank you so to clarify, would I enter my WMI code as follows:
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE
Also, I found this article that identifies the "Properties of a Database Mirroring Change WMI Event" and I see one for ComputerName so I'm thinking there's a way to add this property to my current string which would then include the server name. How would I edit my current string (SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE) to include this property which would then include the server name in my email alert? Also, thank you for your response.
sexta-feira, 9 de março de 2012 21:10
what I would have done is something along the line of:
SELECT @@servername, * FROM DATABASE_MIRRORING_STATE_CHANGE
But it seems I am on the wrong ship with this idea... Give me a moment, I have another idea, but this time I want to try it out first. ;-)
sexta-feira, 9 de março de 2012 21:28
OK, my second idea also didn't work out that well, but here is what you can do:
In the alert response instead of "Notify Operator" use "Execute a job". There you have full control over the way your mail is sent and the information it has in it.... Not pretty, I know, but it works.
All WMI/Alerting experts please step forward with better solutions...
sexta-feira, 9 de março de 2012 21:42
I again thank you. I believe it would go something like this. One of these I think is the correct WMI string but I'm wondering if the server \ computer name would in fact appear in my email alert with either of these. If anyone could confirm this I'd appreciate.
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE ServerName = TEST1 OR ServerName = TEST2
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE ComputerName = TEST1 OR ComputerName = TEST2
I got this information from here: http://anyrest.wordpress.com/2010/11/25/create-alerts-for-sql-server-mirroring-failovers/
sexta-feira, 9 de março de 2012 21:51That's what I tried to do, but it seems that the "Notify Operator" option doens't give you any chance to add custom information to the email. That's why I went to the custom job...
sexta-feira, 9 de março de 2012 22:00
I cant test this currently because users are logged into the environment, but does the server name appear in the subject? Or anywhere in the alert? and which one did you test, the computername or the servername?
sexta-feira, 9 de março de 2012 22:05
The generic mail on my system only states that the alert has fired. For me I get the server name, but only because that's how I configured my mail profile. (Using the server name as the sender.) And what I saw is that when you use only the original query you had the alert fires on both servers, as both report a state change...
sexta-feira, 9 de março de 2012 22:23
My Subject is as follows:
Subject: SQL Server Alert System: 'Fail Over' occurred on \\
I get where the "Fail Over" part comes from... the alert name which I established when creating the alert. What I don't get is the "occurred on \\" part. Where is that coming from? Id be OK with adding the server name(s) to the subject. This is racking my brain. Thanks again.
sexta-feira, 9 de março de 2012 23:10That seems to be the instance... I am still not finding any way to modify this behavior. Is there a reason why you don't just configure the mail profile to specify the server name as a senders address? Something like "Servername <firstname.lastname@example.org>"
sexta-feira, 9 de março de 2012 23:58
In that case Id only want the server that's failing over (the Principal) to send out the notification. So would creating a mail profile on both servers as you've indicated with the following WMI string accomplish this? Or would both send out a notification based upon the fact that there's a state change? Can you give me details on how you would do it? That's a totally acceptable solution (just ran the idea by my boss =) I just want to be clear on how to implement it.
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8
sábado, 10 de março de 2012 04:07According to Technet that should be exactly the way it behaves. I have not tested it though, having some problems with my connection right now.
quarta-feira, 14 de março de 2012 14:25
I use the name of the alert to specify the server name:
"NOME SERVER - Failover mirror"
Fabrício França Lima | MCP, MCTS, MCITP | Visit my site: http://fabriciolima.net | Dicas de artigos SQL: follow me on Twitter - @fabriciodba.
quarta-feira, 14 de março de 2012 16:40Usuário que responde
Under the Alter properties -> options -> additional notification message to send it is possible to add to the message, for example:
I'm not sure if that's what you're looking for or not.