SQL2008R2 ALERTS: How To Specify Server Name In Alert Email

Question

So I've successfully setup alerting in SQL2008R2 for my newly established mirror:

http://social.msdn.microsoft.com/Forums/en-US/sqldatabasemirroring/thread/d435cfb8-3a46-43e4-80c1-41d29cbe71fe

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.

Answer 1

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...

Answer 2

Thank you so to clarify, would I enter my WMI code as follows:

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE
@@servername

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.

Answer 3

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. ;-)

Answer 4

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...

Answer 5

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/

Answer 6

That'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...

Answer 7

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?

Answer 8

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...

Answer 9

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.

Answer 10

That 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 <[e-mail]>"

Answer 11

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

Answer 12

According to Technet that should be exactly the way it behaves. I have not tested it though, having some problems with my connection right now.

Answer 13

I use the name of the alert to specify the server name:

example:

"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.

Answer 14

Under the Alter properties -> options -> additional notification message to send it is possible to add to the message, for example:

Server: $(ESCAPE_SQUOTE(WMI(ServerName)))

I'm not sure if that's what you're looking for or not.

-Sean