1

I've set up some alerts, based on this article

Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same machine of the SQL instance, and then the C# application sends the information to other machine).

My application can accept TCP connections so I was thinking about using that, but any other form for the SQL server to communicate with my application is also welcome.

Any suggestions in how I can do this?

*edit:*Being able to do an insert in another database(in other instance) via T-SQL would also solve my problem.

Background info: I'm doing this to monitor when the Principal server is running exposed, and when the Mirror server is synchronized. So that if the witness goes down, and then the principal also goes down, based on the records of these alerts I can tell if its safe to promote the Mirror to Principal.

Note, that in this case scenario I cant read the mirror database without break the mirroring configuration, so using a table inside the Mirrored database is not a solution.

Improve this question
1
  • Could you create a CLR Function and call that from TSQL?
    – JNK
    Jan 3, 2012 at 20:21

2 Answers 2

Reset to default
5

The article linked uses the dreaded WMI middle-man for delivering the notifications. That is so error prone that is not worth talking about. Cut out the WMI middle man (that also looses events...) and go straight to the source: create an event notification for the Database Mirroring State Change Event. Deliver the notifications locally to your [msdb] database. Then you can react in your activation procedure directly to the state change notification. Yo do not need any TCP or other communication, each mirroring partner can do this monitoring independently and each partner reacts to the local state change.

For a C# application to react to the notification simply have it monitor a queue (WAITFOR(RECEIVE...)) where, again, you have the system deliver event notifications for mirroring state change event. You can even use the external activation app.

The Pro SQL Server 2008 Mirroring book has an example showing exactly this technique.

Just don't use WMI... and absolutely don't connect to client back from server via TCP.

Improve this answer
1
1

The only way to do this would be to setup a SQL CLR procedure which either sends the TCP string, or makes a call to a web method.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.