March 12, 2010 at 1:30 pm
GOAL: Setup a basic messaging process that emails an laert/msg to a specified user via DB Mail when some event occurs such as the firing of Triggers that are set to send an alert when the trigger is fired and the test in it returns TRUE meaning someone needs to be made aware of the change.
We have a couple of tables that our primary application uses and while no one accesses any tables directly (all DML is done thru the application) there is still cases where an undesired data change can occur and when it does I'd like to have an alert emailed via DB Mail which we have up and running and use in SQL Jobs currently.
Originally I was looking at setting up a Trigger that would call the sp_Send_DBMail SP in MSDB when an undesired data change occurred but I ran into some quirks with security and calling this SP indirectly verses directly and when I found out that we'd need to do this kind of allerting on more then just the 2-3 tables I was intially setting this up for I got some replies (from another post on a different site) suggesting the use of a custom messageing queue. The suggestion was to have a SQL Job check a table every X minutes and if there were any new/unhandled items in the table then the job would take each new item and call the sp_Send_DBMail SP passing to it the spoecifics of each message like the Subject, the detail of teh mesaage and and CC or BCC email addresses for the alert.
My question is this, would Service Broker be over kill for something like this and so sticking with a custom table and SQL JOb would be not only easier to setup but less prone to problems or should I investigate using Service Broker for something even as simple as this? Because I'm not well verse in Service Broker I don't know if this is actually a dumb question or not so please be kind and keep that in mind.
If anyone else has had to do this and has some sampel code or sugegstions on a custom message queue I'd love to see/read the details.
Thanks
Kindest Regards,
Just say No to Facebook!March 12, 2010 at 1:54 pm
I guess the answer is "it depends".
But there's one thing I wouldn't do: using sp_send_dbmail directly within a trigger, since the mail sending process would become part of the insert transaction which may have a significant impact on performance (especially if your "primary application" performs row-based inserts).
Do you need immediate email alert or would a scheduled email be enough?
Do you need details per related insert statement or a summary?
Depending on the answer to the questions above I'd either use Service Broker. Or a "message table" filled by insert triggers for the tables in question together with a scheduled job to send me the summary.
Side note: Service Broker is an interesting feature worth to get used to and I don't think it would be "overkill" for that task. It's better to start with a rather easy task to see how it works rather than waiting for a much more complicated scenario...
March 15, 2010 at 10:56 am
lmu92 (3/12/2010)
I guess the answer is "it depends".But there's one thing I wouldn't do: using sp_send_dbmail directly within a trigger, since the mail sending process would become part of the insert transaction which may have a significant impact on performance (especially if your "primary application" performs row-based inserts).
Do you need immediate email alert or would a scheduled email be enough?
Do you need details per related insert statement or a summary?
Depending on the answer to the questions above I'd either use Service Broker. Or a "message table" filled by insert triggers for the tables in question together with a scheduled job to send me the summary.
Side note: Service Broker is an interesting feature worth to get used to and I don't think it would be "overkill" for that task. It's better to start with a rather easy task to see how it works rather than waiting for a much more complicated scenario...
Thanks for replying; I was kind of suprised there was only 1 reposne for this kind of inquiry.
I'm not going to call thge MSDB directlry from within a trigger but from a SQL Job. The Trigger will poopulate the table that serves as the messages queue which the SQL Jjob will check on a specified incriment. I got the idea for doing it this was several months back from another lon time SQL Server Central memeber Jack Corbett. In that post I explained problems I arn into with permissions and using that MSDB stored procedure and Jack suggested a message queue setup similiar to what I described.
I have almost no experience with Service Broker and when I went to read about in hopes of possibly using it I was very much discouraged by what looked like apurposefully confusing setup. It may be that this allows for a great level of control with SB (Service Broker) down the road on more complex uises of it but for a simple message queue or alert system it seemed like a look of kludge to me. Its very similiar to SSIS when compared to DTS. While SSIS is clearly extremly powerful and flexable its also very much over done when you want to do something simple or at least that was siple in DTS.
As for your questons...
Do you need immediate email alert or would a scheduled email be enough?
Fopr tyhe most part know as the reason for this to begin with is FYI and BTW messages. For example one would be to alert a member of our accoutning team that a Vendor was setup with a key piece of data missing. because this key data is not always required it is left optional and so when a new vendor is setup it would help a lot (especially come the end of the year when you do Vendor 1099's for the IRS) if the head accountant could catch at that time when a vendor was created who did not have this key info and that should have gotten it.
I however would also to eventaully have the ability for handling more urgent messages. This is for a database used by our primary accounting software system and so we can't just change the DB's schema and so we have to work around its design. And so to that end there are scenarios where the normal approach is not feasible because we can't just alter the DB Schema (short of adding in our own passive objects) and risk loosing our support on the peoduct.
Do you need details per related insert statement or a summary? Sometimes we woudl and at others a short subject line tyupe of message is enough. I ahve a base table setup that can store all message specific info I would use. I however don;t have the SQL Job or ther objkects in place yet.
SERVICE BROOKER NOTE: While I'm not favorable of SB right now that doesn't mean I wouldn't like to learn more so if you ahve any links/references on SB I'd love to check them out.
Thanks again
Kindest Regards,
Just say No to Facebook!March 17, 2010 at 6:57 am
YSLGuru (3/15/2010)
Thanks for replying; I was kind of surprised there was only one response for this kind of inquiry.
I remember seeing this thread - I noticed that Lutz had answered it very well, decided I had nothing to add to it. so moved on. Just so you know the rest of us weren't deliberately ignoring you or anything...:laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply