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

#SQLSat271 - Jan 25, 2014, Albuquerque - Woot!

I'm proud to announce that I will again be presenting at a SQL Saturday, this time at SQL Saturday 271 in Albuquerque, New Mexico, on January 25th 2014. 

I'll again be presenting on using Event Notifications to track code (aka DDL) changes.  I'll be showing a soup-to-nuts solution you can use, including things like Errorlog tracking, automated emails, SSRS reports, multi-server setups, SSMS plugins, and (what I've been working on lately), a fully automated TFS checkin process, where it automatically checks in any changes within 5 minutes of that change occurring.  You can baseline your servers and see what changes get made over time.  Your devs will love and hate you. 

I'm psyched, honestly - 2 coworkers expressed interest in attending another, and that gave me the push to submit.  It looks like there will be a couple great pre-cons, I'm looking forward to meeting some acquaintances again, and meeting new friends.  Hope to see y'all there!