Getting Started with SQL Server Event Notifications

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    Comments posted to this topic are about the item Getting Started with SQL Server Event Notifications

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Scott Abrants

    SSCommitted

    Points: 1503

    Very nice work. Well written and very useful.

    Thank you

  • SQLRNNR

    SSC Guru

    Points: 281079

    Nice Article

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • ggoble

    Hall of Fame

    Points: 3335

    So if I want to implement this on another databases, I would have to create the stored procedure in each database and then sign the procedure with the cert correct?

    Also if I am currently using db mail in other stored procedures will I have to sign the certificate to all of those as well for them to continue to work?

  • Stan_Segers

    SSC-Addicted

    Points: 453

    You only need to set up the Event Notification for the database (or server). The stored procedure is only needed in the database where the queue resides. You could send the events to another database or even another server. Also, you are not required to use signed procedures.

    I did a blog-post some time ago to catch deadlocks with event notification. This shows you how to handle server level events. Deadlock Alerts Trough Event Notification and doesn't use signed procedures.

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    greggoble2 (12/22/2009)


    So if I want to implement this on another databases, I would have to create the stored procedure in each database and then sign the procedure with the cert correct?

    Also if I am currently using db mail in other stored procedures will I have to sign the certificate to all of those as well for them to continue to work?

    Trace events are server scoped, so you don't need multiple Notifications per database for this. If you used DDL Events at the database level, it would have to be created per database, and to send email through queue activation from that database with DatabaseMail would require signing it with a certificate as the most secure method. I covered the purpose behind using certificate signing to access DatabaseMail from a non-msdb database on my article last week Using a Certificate Signed Stored Procedure to Execute sp_send_dbmail [/url].

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    Stan_Segers (12/22/2009)


    You only need to set up the Event Notification for the database (or server). The stored procedure is only needed in the database where the queue resides. You could send the events to another database or even another server. Also, you are not required to use signed procedures.

    I did a blog-post some time ago to catch deadlocks with event notification. This shows you how to handle server level events. Deadlock Alerts Trough Event Notification and doesn't use signed procedures.

    You can create the server level items in msdb to get around the signed procedure but since that isn't a recommended practice, I don't offer ideas like that in online articles. It is one way to go about it though. I keep these kinds of things in DBA_DATA database on my servers personally, but everyone has their own methods.

    This is the first in a series I have written on using Event Notifications and there is an article on capturing Deadlock Graphs pending publication, though I take things a bit further in my article than you did on your blog post by parsing the XML and retrieving additional information regarding the executing statements like their query plans which are important in deadlock analysis.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • ggoble

    Hall of Fame

    Points: 3335

    Thanks for the info! Great stuff!

  • @SQLNumpty

    SSCrazy

    Points: 2844

    Excellent article. Looking forward to more like it !!!!! ๐Ÿ™‚

  • louis.young

    Valued Member

    Points: 53

    I've copied the T-SQL from this article exactly and executed it. All works fine, but when I view the queue:

    SELECT *

    FROM EventNotificationQueue

    there are no records. What am I doing wrong?

  • Adam Haines

    SSC-Insane

    Points: 23197

    Great Job Jonathan! I look forward to part 2.

  • philcart

    SSC-Forever

    Points: 47713

    louis.young, One thing I can think of is to make sure you are in the same database that you created the queue in.

    Great article Jonathan, looking forward to more. Especially interested in extended events.

    --------------------
    Colt 45 - the original point and click interface

  • Jonathan Kehayias

    One Orange Chip

    Points: 26667

    philcart (12/23/2009)


    Great article Jonathan, looking forward to more. Especially interested in extended events.

    Event Notifications and Extended Events are completely different animals in SQL Server. Extended Events are new in 2008 while Event Notifications exist in 2005, and the eventing for the two is very different. If you'd like more information on Extended Events, you can read my whitepaper on MSDN,

    Using SQL Server 2008 Extended Events

    as well as Paul Randal's article on Technet:

    Advanced Troubleshooting with Extended Events

    If you'd like some other references I have a tag on my blog for Extended Events posts I write:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/tags/Extended+Events/default.aspx

    and so does Paul:

    http://www.sqlskills.com/BLOGS/PAUL/category/Extended-Events.aspx

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Baskar B.V

    SSCrazy

    Points: 2763

    Hi,

    I am trying to create notification in server A and want the read the queue data using stored procedure from server B. Can you help me with sample article of how to achieve this? I am struck with creating notification in server A and making it to pass the data to server B. Where do i mention that the data should go to server B. Greatly appreciate your help.

    [font="Arial"]BASKAR BV
    http://geekswithblogs.net/baskibv/Default.aspx
    In life, as in football, you wonโ€™t go far unless you know where the goalposts are.[/font]

  • vinay.kumar

    SSC Rookie

    Points: 33

    HI,

    Thanks For the Post.

    I tried the sample Event Notification given in the blog. And it looks all fine.

    For the each Event(ERRORLOG) it is using one Conversation Group for all the raised errors. SO i manually ended the conversation and then i raised a custom exception. This time no messaes sent to any Queue.

    Please let me know your thought

    Thanks,

    Vinay K

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply