Wednesday, October 17, 2012

Master of all I survey - using Event Notifications to track code changes and more across multiple SQL servers

Please check here if running my code.  I will post updates as I find/need them.  Google docs is current.
Latest version: 11/14/2012.

(last update 2013/07/31 - looks like it's alive and well in 2014!)



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.

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 a dozen 50 servers, and am slowly adding them to every server in our environment.

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

Wednesday, October 3, 2012

[SSMS 2012] Getting my CTRL+SHIFT+C back for comment

I'm sorry, I just can't CTRL+K, CTRL+C.  Too many years using Query Analyzer (which, to this day, still does things easily that SSMS doesn't).

How do you get it back?

  • First, Tools->Options->Keyboard.  
  • Show commands containing -> comment
  • Click in "Press shortcut keys" and do CTRL+SHIFT+C.  It will say "Shortcut currently used by:" write all of them down, if there's more than one (it's a drop-box)
  • Under "Shortcuts for selected command", remove each one that isn't CTRL+SHIFT+C
  • Now go to each of the commands that you wrote down earlier and remove CTRL+SHIFT+C from each.

Click OK, and you're done.