Latest version: 11/14/2012.
(last update 2013/07/31 - looks like it's alive and well in 2014!)
----------------------------------------------
Howdy!
I specifically went with a grandiose title to get people interested in this - it's (Event Notifications) a wonderful feature that very few people use (or even know about!), and now that I use it I find it absolutely vital. I hope I get across how great this feature is, especially since it works from SQL Server 2005 - 2014.
Below is a link to my slide deck, demos, scripts and reports.
https://docs.google.com/folder/d/0B3a287PS_UJIcnY3Q1pvX3p1eEE/edit
The end result is a table that keeps track of code changes across potentially hundreds of servers. I've got it running on more than
One of the takeaways: once you have it running, you can run the following report in SSMS - right-click on an object, choose the RDL above via "Custom Report...", and it'll show details. Changes, index maint (if a table), etc. Works on SP/fxns, tables, databases, and servers - click on an object, it'll provide details on that object. Click on a DB, it'll give you all changes for that DB. Click on Server...same thing.
This works in SSMS 2005/2008/2012. And let me state for the record, it sucks that you have to develop it in BIDS 2005 in order for SSMS 2008 to view it properly.
I'll be tweaking this report; it's bare-bones and not particularly pretty, but is a really handy way to track down changes.
A couple examples: (apologies for the size, but otherwise you can't easily see the commandtext which shows the actual commands executed - click to enlarge)
In order for the reports to work in SSMS, you will need to create a linked server on each monitored server, pointing at the server that holds EventNotificationRec, with the name ENRepository (collation compatible true and RPC OUT on). This way when you right-click on an object it will send the server/database/objectname across on each server - this is needed because SSMS does not let you use a different data source.
The version of the report shown this evening is EN_ReportSP. I will be tweaking it, as well as the other code. Watch this space.
Please feel free to use, share, improve. Please contact me for commercial use (hey, a guy can hope).
3 comments:
I am very curious how did you get this to work without certs when BOL states that EN require "the full security model". I tried the scripts and indeed got to errors about missing keys and certs. Am I missing something ?
Mitack, It works without the certs - the "full security model" includes Kerberos, which doesn't require certs, just active directory. On your two machines, are they on the same domain? Which script did you run that gave the errors? I'd love to know because I used the scripts while building my demos for my Pass Virtual Chapter presentation, and the only thing I had to make sure of was the Active Directory working.
Feel free to respond here or ping me on twitter (@ mbourgon), and I'll do what I can to help - doubly so because I want people to adopt this and if I can address your problems, that makes it easier for everyone. Thanks!
Here's my code for my endpoint. Note the kerberos. Once this is going, just grant the SQL Server Service owner (for the box you want to watch) CONNECT to both the Endpoint and "SQL" on the centralized server.
CREATE ENDPOINT [ENAudit_Endpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = SUPPORTED ALGORITHM RC4)
GO
Post a Comment