[Presenting] What do I present on next?

Title says it all.  At this point I've pretty much hit every SQLSaturday that's easily reachable from here.  I've had a ball doing it, and the coworkers I drag along with me have had a lot of fun as well.

But I've presented on that topic (Event Notifications) at them.  So what's next?

I need to come up with a new session.  I can obviously just GO (maybe as a volunteer), but I think I have more fun when I'm there to present.  I wouldn't have thought so when I first started doing it, but it's the case and I'm cool with it. 

I just don't know what to present on.  Mark V, in his session, said that any topic is a good topic - everyone brings their experiences to it, and everyone explains it differently.  That being said, there are people who (in my mind) own their topic - they're of the MVP/MCM level (which I'm not in one case, and can never be in the other case), and their topic is freaking fantastic.

So, I want to come up with something useful to many people, and preferably something not often covered. 

Here are some of the thoughts that have been going through my head:

• The $25000 (per core) Pyramid - what enterprise features don't you use?

aka 

• ($25000 per) CORE - What is it good for?

Would probably be a relatively high-level session.  Cover Peer-to-Peer replication, Hekaton, Columnstore/Clustered Columnstore, things-that-used-to-be-Enterprise-but-are-now-standard,  AO, online restores, online indexes, mirrored backups, partitioning, compression, distributed replay, CDC etc.  Could even do non-Engine features like Tabular Model (BISM) and DQS.

---------------------------------

• Save all the things!  

A session about saving out various things for later, and parsing them for info.  I've got a solution built in Powershell called the EPR that's saved my bacon a bunch (using it right now to figure out when code was last run on all my servers), but could also cover things like pulling out connection strings from SSIS packages, etc.  But I could also expand parts into using Powershell, using SMO, etc.

---------------------------------

• SP_Server_Diagnostics for fun and profit

This would be a decent amount of work - while I have code that will shred out the results, there's a LOT more to it than that.  And I could expand and cover system_health.  The one problem is that Kehayias had a decent session about it at PASS 2013.


---------------------------------


Aaaaaaand.... that's all I have right now.  Any thoughts gratefully appreciated.

[Presenting] SQLSatDC (SQL Saturday #347) was a blast!

A gigantic thank you to the whole team at SQLSaturday347, aka SQLSatDC 2014.

I had an absolute blast.  Rubbed shoulders with a bunch of people I know of, chatted with some SQLSat friends, met a bunch of new people I want to see more of, and even managed to take in the sights (and some tapas!).

Let's see, what did I see/do?

Thursday/Friday - tourista!

Friday night - speaker's dinner.  A nice little Spanish/LatinAmerican restaurant.  After a bit of "how do we order", managed to get everything straightened out, and had a blast.  Tapas, Paella, lots of SQL and non-SQL conversations, etc.

Saturday morning - going by the vendor tables, some groups I haven't seen before.   Always nice to see.  Wow, a Linchpin People table! (First time I've seen it, though I've run across at least 5 of them at different events, and they've been uniformly amazing folk)

 8:15am - me!  Standing room only, which as a presenter is honestly pretty cool.  Some good questions and comments, which means I need to incorporate them as well as strip out other things.  At this point I've gotten my presentation up to right at 70 minutes, sans breathing, so it's time to strip out some of the less-important slides.


9:40 - David Dye, Dealing with Errors in SSIS 2012.  Some interesting stuff from a SSIS developer perspective, how to save errors out, prevention, dealing with them, etc, etc.  Less interesting for me as a DBA, though if my devs did those fixes now I'd know where to look.  As is, I have to keep using my own email notifier for SSIS Project Deployment Model failures.


11:05 - Jason Brimhall, Murder They Wrote. It's the one-hour summary of the day-long summarization of a week long training session.  *Whew*!  Very nicely done - while I knew many of them, he did a great job explaining them.  The only odd part was that very few people seemed to know what Clue was(!).  And anybody who can make a "boot to the head" reference (Yay Frantics!) is a hero to me. 

Lunch: Fajitas!  And a head shot!  (Thanks, Chris!) And chatting with one of the attendees for a bit, talking about EN, CI, and other two-letter acronyms. And ended up by getting pinned down by one of the redgate folk and looking at their SQLLitehouse tool, which does a lot of what EN (and therefore my presentation) does.  Wonder if I can steal any ideas?  Is that legit, if I'm doing this for fun?

1:30 - Jimmy May, Columnstore, Flipping the Faster Bit.  Apparently I missed the part where he did a handstand, but I'll tell you what, he was the most animated speaker I've seen in a while.  Very knowledgeable, some useful information (and I'm running CS/CCS!), and I got to pick his brain after.  My only suggestion (and I made this to him already) was that if we're in the session, we don't need as much "marketerization" slides (but he knew that already). But it did explain some of the tuple mover issues I've seen, so... WIN!

2:55 - Samuel Vanga, SSIS Data Flow under the hood.  Something I haven't seen much of, specifically a closer-to-the-metal session about tuning SSIS, almost from a DBA perspective.  I swear I've seen some of it before (Tim Mitchell, probably), but it's nice to see more internals covered.  Normally it's very much a "oh, everything works", but this was more about low-level tuning, async vs non-async, spilling to disk, etc.

Anyhow, I'll definitely try and come back - they had a great turnout with some fantastic speakers!

[Presenting] SQLSatDC, aka SQLSat347, this weekend (Dec 6 2014)!

Come see me speak in Washington DC!  (Okay, fine, and another 29 speakers like Grant Fritchey, Allan White, Steve Jones, Jessica Moss, Randy Knight, Jason Brimhall, Jimmy May...).

There's even a pre-con on Friday - day-long training on BIMLScript (different venue, though, I think).

SQL Saturday DC 2014.
A mere $10 gets you in - and that includes lunch!
Doors at 7:30am, starts at 8:15am.
https://www.sqlsaturday.com/347/



Microsoft MTC, 5404 Wisconsin Ave, Chevy Chase, MD, 20815

[MSMQ] Using powershell to PEEK at several thousand records

A bit of a detour today - delving into stuff not easily done with T-SQL.

I needed to look in some MSMQ queues, without removing the messages, pulling out some details.  After multiple attempts (and a couple questions on StackOverflow) I got exactly what I was looking for. 

Basically:

• PEEK 500 messages off a queue (PEEK differs from SELECT because it leaves them in the queue - for you T-SQL types, the difference between SELECT and RECEIVE in Service Broker)
• Pull out the (binary) bodystream 
• Convert bodystream to ascii
• Strip off the non-XML header
• Get the ID from the message (both inside and before the XML is included below)
• Return it all to a grid.  

Not elegant, but works pretty well (and pretty fast for my limited set - 10 seconds for 500)

 Here's what my header looks like, FWIW, since it drives several parts of the code (the split around the colon, for instance)

F:01.02.03:1234567890:Message:clear
#MSMQ.bodystream is in decimal format, so this is our converter.
$enc = [System.Text.Encoding]::ASCII

#get-msmqqueue finds that one queue (it accepts wildcards).  
$msmq = Get-MsmqQueue -Name "my_msmq" `

#now that we have the queue as an object, PEEK at the first 500 messages.  

#Give up after 5 seconds (since it will hang or crash if there are none)
#We assign the end results to a variable so that we can return in one set.  
#If writing to a file, putting the write inside the loop might speed things up, would def save memory usage
 
$var = Receive-MsmqQueue -input $msmq -Peek -Count 500 -Timeout 5000 -RetrieveBody | foreach {

#BodyStream is an object of decimals that we need to convert to an array, then a string.
#Was originally, but in this case I can combine
# $sr = $_.BodyStream.ToArray()
# $pr = $enc.GetString($sr)

$pr = $enc.GetString($_.BodyStream.ToArray())

#Clean is an XML version of the object, minus the crappy pre-xml header  
#MANY thanks to Matt & Swonkie for the speed boost.
#http://stackoverflow.com/questions/26812831/faster-way-to-delete-up-to-the-4th-colon-in-a-variablesemicolon
[xml]$clean = $pr.Substring($pr.IndexOf('<'))

#parsing the swtxnum - the select-xml works and takes about the same time, 
#but showing other options via the pre-xml header.
#this would work too: [string]$swtxnum = select-xml -xml $clean -XPath "/msmg/Data/myid"
$myid = $pr.Split(":",5)[2]

select-xml -xml $clean -XPath "/msmg/Data" | foreach {$_.node } | select @{Name="myid"; Expression={$myid}} , fielda, fieldb, fieldc, fieldd, fielde  #| ` # select is optional

}

$var|out-gridview

[SQLSaturday] 2 more coming in Dec/Jan (DC, Austin)

Yup, there are two more SQL Saturdays coming up with me in it! 

"More of me to see" - 2 different SQL Saturdays, and... um.... I'm not able to ride my bike as often.  But the chocolate is still there anyhow.

SQL Saturday 347, December 6 2014: Washington D.C.

SQL Saturday 362, January 31 2015: Austin, TX

Hope to see y'all there!

[Extended Events] converting results of rpc_completed , originally VARBINARY, into XML

Maybe there's a better way to do it.  After futzing with various combinations for an hour, I gave up.

What I'm trying to do:
we have a stored procedure called from a custom app.  Pulls off a Queue, then runs the stored procedure using the contents of the Queue (which is XML).

When I have 1, it's really easy:

declare @msg varbinary
set @msg = 0x300000000134985709sdg8fs0d9f8fa0s7df90 (which would be hex in the real world)
select convert(xml, @msg)

However, I needed to do that for several hundred rows.

So,  the conversion from the native to varbinary(max) was failing - if you CAST or CONVERT it, it actually seems to CHANGE it... which causes the field to no longer be proper XML.  And so the CAST to XML fails.

And thus.... the fix is..... Dynamic SQL (yeah, I cringed too)


[...code to extract the varbinary string removed - multiple CTEs]
create table finished_trace (data_value xml)

DECLARE @min int, @max INT, @sql NVARCHAR(MAX)
SELECT @min = MIN(event_number) , @max = MAX(event_number) FROM #t3
WHILE @min <= @max
BEGIN
    SET @sql = NULL
    SELECT @sql = 'declare @msg varbinary(max)
    set @msg = ' + data_value + '
    select convert(xml, @msg)' FROM #t3 WHERE event_number = @min
    INSERT INTO finished_trace (data_value)
    EXEC (@sql)
    SET @min = @min + 1
end

[Event Notifications] my current slide deck

Thought I'd share this - the entire folder I run off of is shared, but here's the slide deck, embedded.  It should get new changes as I make them. 

[Event Notifications] adding a new box to your environment

Short and sweet - just realized I don't already have this on here.  This makes several assumptions in my environment, and you can modify as needed . Yes, it's ugly.  Yes, security holes due to xp_cmdshell and the like.  You've been warned.

Practically: you run this on the box that already has the repository.  You tell it the name of the new server.  It connects to that server, makes sure each side can reach the other, then enables Event Notifications on that new server.  It ends by making a change, waiting several seconds, then selecting from your repository table.  If it shows, you're golden.  If not, time to roll up sleeves and figure it out.

[Event Notifications] Tackling Database Lifecycle Management and Database Drift (via Redgate)

First, I'd like to thank Bill Fellows, Megan Longoria, and the rest of the Kansas City crew for an amazing weekend at SQLSaturday #300, aka #SQLSatKC.  We hit 2 BBQ places with them, another BBQ place and Boulevard Brewing with some other attendees, then went to an amazing baseball game.  Saturday was a full day with some awesome training (everything from "What's a DBA" to collecting ultra-detailed memory-consumption information), even more awesome BBQ, and ended at the nearby amusement park. 

My presentation went quite well, aside from all 3(!) VMs deciding to BSOD on me 15 minutes before I went on.  Good number of attendees (always a concern for me, personally).  Had some good questions, I like the audience participation & humor I've added, and had a good conversation on it afterwards. Overall, a definite win.  Now I need a new presentation so I can come back next year!


Additionally...


Normally I wouldn't just link to someone else's article, but they are more eloquent than me.  A good article on why you should track database changes in production (like I do with SQL Watchdog, aka "Master of All I Survey", aka "Evil Overlord").  That being said, I love the term "Database Drift", and I'm glad to see more companies looking into solving this issue.

https://www.simple-talk.com/blogs/2014/09/10/tackling-database-drift-with-sql-lighthouse/#.VBbX7AevYv4.twitter

[Event Notifications] Getting auto-TFS checkin set up for TFS 2013 (SQLWatchdog)

We recently upgraded to TFS 2013, so here are the steps (written as I do it) to get it running.

Aside: Since I'm using Powershell to run all this, why do I use the TF.cmd application instead of the Cmdlets?  Simplicity.  The Cmdlets appear to need the FULL version of a TFS client installed, plus you have to grab and install the cmdlets... and there are bugs if you don't use the right (x32 vs x64) version of the cmdlets with the right version of powershell.  So I built it around the TF CLI (Command-Line Interface).  The original post was for 2008, the new one (plus script below) are for 2013.

IMPORTANT:
Upgrading from 2008 to 2013 blows, since the config and cache can be screwed up.
What I had to do was completely drop the workfold and the workspace (PROTIP: first, list your workfold and workspaces and SAVE THOSE DETAILS), delete the entirety of the cache folder, delete everything from my EN_Workspace folder on disk, then rebuild from scratch.
One other oddity: before, from the root of my installation (c:\tfs_cli) I could run the script, and it could properly list the folders underneath it.  That appears to have changed for some reason; my Powershell script now CDs into the subfolder before it proceeds.  Not a major change... but took several hours to diagnose, since I was also fighting with other issues on the setup.  Caveat Emptor and all that.

Also for the TFS newbies: the default path you use for a LOT of things is:
http://myservername:8080/tfs/DefaultCollection .

Steps to get going:

• Install Java.  Got it from Oracle, which doesn't appear to have the same cruftware that Sun's does. Version 7-something
• Grab Team Foundation Everywhere 2013. Reading the docs, it works for TFS 2010, 2012, 2013, and Online. http://www.microsoft.com/en-us/download/details.aspx?id=40785
• Unzip into one massive folder (I called mine "c:\TFS_CLI\APP_2013"), then try running (since I'm on Server 2012, in Powershell):
• ".\tf.cmd" 
• it works - success!  Sorta.
• Found this page which seems to make the below setup easier: 
• http://msdn.microsoft.com/en-us/library/hh873092.aspx
• For the rest of this, I'm doing it as the Service account that SQL Server is running under on the box that will run the job. Oddly enough, it wasn't set that way when I built it on 2008, but it continued to work even though my password changed.  Note that nowhere in here do you actually put in a password.
• Type ".\tf eula", then accept ("y"). 
• muddle through the web site (tfsservername:8080/tfs) and add the SQL Service account as an admin to the branch I'm using (YourENProject).
• Using the MS page above:
• .\tf workspace -new TF_Workspace -collection:http://mytfservername:8080/tfs/DefaultCollection
• the collection URL is pretty standard - it should work in 2010,2012,2013.
• .\tf workfold -map $/YourENProject -workspace:TF_Workspace c:\TFS_CLI\EN_Workspace
• This line has several names.  Let's break them out.
• $/YourENProject is the "Branch" in TFS.  Call it something like $/ProductionEN or something like that.  I created the branch using SSDT before I did any of this, but you can create one using the TF.exe
• TF_Workspace is the workspace you created in the step above.
• c:\TFS_CLI\EN_Workspace - the physical folder on your EN server (NOT the TFS server).  I named them differently to make it more obvious what each part of the command is doing.
• now try and check something in.
• Get to the workspace
• cd c:\TFS_CLI\EN_Workspace
• new-item test.txt -type file
• &cmd /c "c:\tfs_cli\app_2013\tf add test.txt"
• &cmd /c "c:\tfs_cli\app_2013\tf checkin /author:memyselfandi"
• I use that weird syntax to catch errors and return as text into Powershell, so it can properly eat and pass on errors as necessary, as opposed to having PS choke on it.
• Pull and update something: this is where I had massive problems. (use script)
• Finally verify it worked (I used my "real" TFS client on my desktop).

Now, set up the SQL Script (SP), the Powershell script, and a job that calls the SP.

SP:





 Powershell:

[Event Notifications] SQLWatchdog! (Coming to KC in a week)

I'm pleased as punch - presenting at SQL Saturday #300, Kansas City, in a week.

The title is "Master of All I Survey", because I wanted a grandiose title that would get butts in seats.  I was the target audience during 2 different PASSes where it was presented, yet didn't go to it because the title/abstract didn't sound relevant.  Sad to say, since I was the target audience!  Queue the "better" title. 

Alas, I seem to be the only one in love with it, so I changed it to "SQLWatchdog", based on a coworker's suggestion (HI, JASON).  It gets the point across - a Watchdog for SQL. 

If you look in this blog, you'll see it referenced as a couple of things, but the tag I try to use is "Event Notifications", so you can find them at http://thebakingdba.blogspot.com/search/label/Event%20Notifications


And as the KC SQLSat group appears to know how to have a good time, I'm ALSO going to fill my belly with BBQ, share it with the world after hitting roller coasters, and even sneak in a game.  A wonderful weekend to be sure.

See you there!
http://www.sqlsaturday.com/300/eventhome.aspx

[Event Notifications] saves the day again...

Got the morning ERRORLOG email courtesy of EN.  And I got several hundred messages from ERRORLOG.  During a patch, the Agent XPs were disabled but not reenabled.  So the Agent service was up, but didn't do anything.

Ironically enough, it wasn't just the ERRORLOGs that saved me - it was SQL Sentry, which hit (and failed) the XPs once a minute.  Which, yay, caused an ERRORLOG message, but didn't actually TELL me it wasn't working.  (I've submitted a bug fix for that).

Just another of those "my god, how do I live without this" posts.

Reminder:
ALL my EN code is in this share:
https://drive.google.com/folderview?id=0B3a287PS_UJIcnY3Q1pvX3p1eEE&usp=sharing

Additionally, my Gists (source code of the code I use in production) are at:
https://gist.github.com/mbourgon

[SQLSaturday] Thanks for coming to see me at Oklahoma City!

Thanks again for all the generous hospitality, and the 28(!) people who came to my session.  It seemed like everybody enjoyed it!  I got a bunch of good reviews, and a couple suggestions I'll implement for SQL Saturday 300 (This. Is. Kansas City!) but feel free to let me know what I could have done to improve things - I know the room was warm and I saw people flagging near the end.

Matt Brimer did an amazing job, and I will definitely return.  Great venue, most of the speakers were good, food was awesome.  All around, a fantastic SQLSat.

Slides and everything are available off the SQL Saturday Web site, at:
http://www.sqlsaturday.com/viewsession.aspx?sat=309&sessionid=20771

Note that that's not only all my slides, but the scripts as well.  I have a newer, complete version of the Repository - I'll have that put up on here in the next day or so (updated: https://drive.google.com/folderview?id=0B3a287PS_UJIcnY3Q1pvX3p1eEE&usp=sharing , see "EN Full Repository Setup").  That should be everything required to run the code on your servers.  Installers, new databases, everything.

Michael

[Interesting] Linked Server is cached for a session

Since I spent 20 minutes trying to diagnose it this morning...

Say you have a session open.  That session invokes a query that hits a linked server.

Now change the linked server.  Drop and recreate, point it at a different server.

New sessions get the new definition, but existing sessions use the old definition.

[Presenting] Last minute - SQLWatchdog at the Fort Worth SQL Server User Group, Wednesday, August 20th

Just had a last-minute invite, and it seemed an awesome opportunity to present & hone it one more time before my forthcoming SQL Saturday presentations (OKC this weekend, KC next month)

http://www.fwssug.com/ for more details.  It'll be Wednesday, August 20th, 2014, at 7pm.

Yes, this is the presentation I did there almost 2 years ago, expanded, fleshed out, and with Cat Pics. Because if there's one thing a presentation needs, it's Cat Pics.

See you there!

[Presenting] Double your TheBakingDBA exposure! Come to the Kansas City SQL Saturday!

Just got the email - I'm presenting at SQL Saturday 300, Kansas City!

Despite the name, this is the same presentation I've been giving (aka SQL Watchdog) - I submitted this before changing my title.  We'll be adding a few other features as well, based on past suggestions from both audiences as well as my own internal folks.

It sounds like it'll be awesome - Bob Ward is speaking, they're going to have a BBQ Crawl on Friday, sounds like there's an event at the ballpark, there's a group going to a local theme park to ride the coasters...

Man.  Cannot wait.

http://www.sqlsaturday.com/300/eventhome.aspx

Sep 13 2014 
Cerner Corporation's Riverport Campus
6711 NE Birmingham Rd, Kansas City, MO 64117

[Presenting] Woot! I'm presenting at SQL Saturday 309, Oklahoma City!

Pleased and proud to have been selected to present at #SQLSatOKC, aka SQL Saturday 309, aka SQL Sat Oklahoma City.  Doing my presentation (continually updated) on Event Notifications, and capturing DDL changes (along with other fun stuff like Deadlocks and Errorlogs) in real time, and what you can do with that.

As always, I'm trying to figure out how to include all the details necessary to do it, not TOO many details, and add some fun stories on "heh, look how I saved the day". 

While actually remembering to breathe - a lot of details, and honestly, I get excited about presenting it.  The goal is to make the passion shine through, without seeming like a Spaz or newbie speaker. 

SQL Saturday Oklahoma City:
Aug 23 2014 at 13301 South Pennsylvania Avenue, OKC, OK 73170. 

http://www.sqlsaturday.com/309/eventhome.aspx

[Presenting] PASS Summit 2014 - Bummed and Congrats!

Got back from vacation on Tuesday.  Check twitter sometime that morning - and lo and behold, the PASS Summit 2014 sessions are picked!  Check email - nothing.  Check the schedule - well, I guess "not picked"  doesn't deserve an email.  Thanks.  Check on twitter (because honestly, a "why" would be nice) - no, I should have gotten something.  Long story short - 4 hours later, I get the email.  I already knew from looking at the schedule that I wasn't picked - which was a kick in the pants, having presented it 5 times at this point.

However, a silver lining - it appears I was rejected because someone else was presenting on something similar.  Indeed!  So a hearty congrats to Colleen Morrow!  While I personally think EN curb-stomps SQL Audit, I'm glad to see the subject of checking for code changes in the environment will be presented at PASS.  Colleen's already shown (via her chapter in Tribal SQL) that she knows quite a bit about all the options, and writes passionately on the subject, so I have no doubt it'll be a great session.  Wish I could see it.  : )

[WAT] more screwy date conversion

So, let's convert some dates

DECLARE @my_month DATETIME = '20140530'
SELECT SUBSTRING(CAST(@my_month AS VARCHAR(8)), 5, 2) + '/01/' + SUBSTRING(CAST(@my_month AS VARCHAR(8)), 1, 4)

Returns:
30/01/May

But what if we set that date to an Int?
DECLARE @my_month int = '20140530'
SELECT SUBSTRING(CAST(@my_month AS VARCHAR(8)), 5, 2) + '/01/' + SUBSTRING(CAST(@my_month AS VARCHAR(8)), 1, 4)

Returns:
05/01/2014

SQL Saturday 308 - SQL Watchdog - scripts and slide deck

Someone mentioned a problem with the scripts being corrupt on the SQL Saturday website.  Here's a copy of my most recent presentation.  All the scripts, including the repository creation scripts (zip-within-the-zip). 

Holler if you have any questions, thanks again to Nancy and Allen for allowing me to present and treating us like rockstars, and thanks to everyone who attended SQL Saturday Houston!

TBD

https://drive.google.com/file/d/0B3a287PS_UJIRDJBNWEtNVBJY0E/edit?usp=sharing

and the whole folder of goodies (old versions, warts and all) is available at:
https://drive.google.com/folderview?id=0B3a287PS_UJIcnY3Q1pvX3p1eEE&usp=sharing

[Event Notifications] Hekaton prevents EN?!

Nooooooooooooo...

http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx

Memory-optimized tables and natively compiled stored procedures cannot be created or dropped if there is a server or database event notification for that DDL operation. Remove the server and database event notifications on CREATE TABLE or DROP TABLE and CREATE PROCEDURE or DROP PROCEDURE.

What happens if you attempt to do it?

 Msg 10794, Level 16, State 124, Line 1
The feature 'EVENT NOTIFICATION' is not supported with memory optimized tables.


Why is this a big deal?  Because, honestly, I'm scared that if they're not supporting things like this, that EN is on its way out.  I use it extensively (see my presentations on it, coming soon to a SQL Saturday near you), and it's IMHO a must-have feature.  It also means that whenever we want to do something with Hekaton, we have to drop the EN, do the Hekaton-based changes, then we're less likely to use Hekaton or EN.  

Please vote on my Connect item:

https://connect.microsoft.com/SQLServer/feedback/details/874185/event-notification-please-make-in-memory-oltp-tables-natively-compiled-sps-work-with-en

[Event Notifications] Using Certificates for Authentication instead of AD/Kerberos

My normal environment that I run is pretty simple.  One trusted domain.  So security for Event Notifications is pretty simple too - I just use Kerberos.  It's simple, easy to read, nothing fancy involved, and I wanted to make sure that people weren't going to look at the prerequisites and immediately say "I don't want to deal with keys and certificates and all that - so I'm not going to do it". 

A former coworker isn't as lucky - he has multiple, untrusted domains.  So he had to get it working.  And much to my surprise, it was considerably easier than I'd thought, from reading about Service Broker certificates (literally, one of the demos in the book I read used _7_ certificates).  Yet again, I dig what MS has done with EN, since it's a simple use-case of Service Broker.


Note that we're doing encryption, creating master keys and certificates.  You may need these keys in order to restore the database - that's what I've heard over the years.  But from what it sounds like, technically you don't need it as nothing is encrypted.  However, I'd be really paranoid and test this out on a test server first, specifically the whole "restore the databases without the keys" part.. 

TL;DR -  Script is below, test restores before putting in production.  You'll create 1 key & 1 cert on your Repository, and then on each monitored server you'll create 1 key & 1 cert, swapping keys with the Repository. 

Many thanks to James for doing all the hard work on this!

#SqlSat308 I'm pleased to present in Houston on May 10th, 2014. "SQL Watchdog"

Woohoo!










I'm honored to have been chosen to present at SQL Saturday #308, Houston, on May 10th 2014.

This will be my Event Notifications presentation, more refined, running on SQL Server 2005 & SQL Server 2014.  We'll cover concepts all the way through a multi-server environment and more.  You'll see the advantages of running Event Notification - not only can you know within a second that code was run that might compromise or break your enviroment, but you can do other things with it, from TFS checkin of production changes (for later issues), ERRORLOG monitoring, killing unauthorized backups and more.

Naturally, in addition to me, there's a ton of big SQL people that'll be there.  Aaron Bertrand, Tim Costello, Tim Mitchell, John Sterrett, Denny Cherry, Lori Edwards, Chris Bell and more!  Sixty sessions in all!


http://www.sqlsaturday.com/308/eventhome.aspx

DB_Ops Evolved - Be The Proactive Hero

Since I love to share...

https://docs.google.com/presentation/d/1EtLdhoewsmCsZ28PbqR0ELzVzlHlTAt-CCenRspUvtI/edit?usp=sharing

Feel free to ask any questions.

Michael

[Wat] Leading Dots work in SQL queries? Well, that's some interesting syntax there, Lou.

Wound up looking at a failing piece of code.  The first thing was how it was called. 

EXEC .dbo.the_sp_name

...but it turns out, that part works.  As does this query: SELECT * FROM .sys.tables .  Had never seen that before.

[Presenting] Changing session title - what should I call my EN presentation?

So, SQL Saturday #271 (Albuquerque 2014) is over.  It was a blast.  We got to drive through Amarillo, see the Cadillac Ranch, get our Kicks On Route 66, etc, etc.  The venue was awesome, they treated us like kings, and it looked like a good turnout. 

I felt I did pretty good on my presentation, though there are a few things I need to tweak.

Interesting, though, while everybody who went loved it, it looks like a decent number of people didn't come because of the title.  My current title is (take deep breath):
"Master of All I Survey - track code changes and more across your environment".


Part of the reason for that overly-grandiose title was because, while I am definitely the target audience for the presentation, I didn't see either of the 2 times it was presented at PASS - and I was at both!  But since my title isn't working either, what should I change it to?

Here are the titles of the other two events

• "Auditing Events in SQL Server 2005" (PASS 2005)
• "Using Event Notifications in SQL Server 2005/2008"  (PASS 2011)

The abstracts (and presentations) are both great - but IMHO the titles could be stronger. I saw "auditing events" and said "I don't do auditing"; for "Using Event Notifications" thought "not familiar with those, not sure what it is, read abstract later".


I found some others online:
Monitoring and Recording DDL changes on SQL 2005 (NARC)
Auditing DDL Changes in SQL Server databases


Current ideas.
Evil Overlord - know when your minions are changing SQL code/tables/etc in production
Who changed that code? Finding production changes in real-time using Event Notifications.
Real Time Spy - being proactive by knowing when code changes in production
DB_Ops Evolved - Be the Proactive Hero by knowing when things change!
DB_Ops Evolved - be proactive by knowing when DDL changes occur

The question becomes - what do I focus on?  I thought I did pretty good - catchy bit, then tells you what it does (track code changes) and that it focused on multiple machines. 

What else could I focus on? 

• Real-time - I really think this is an big benefit over the other trace methods.
• Trace Events like Failed Logins, ERRORLOG, Deadlocks, etc
• Being Proactive  
• SOX-type stuff - failed logins, code changes, etc.

 I'm working on a couple presentations under the Aegis of "DB_Ops Evolved" to introduce advanced concepts and a healthy respect for the engine), hence that last title.

I'm wide fricking open, here.  And at a loss.