Monday, July 21, 2014

[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

Tuesday, July 8, 2014

[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

Monday, June 30, 2014

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


Friday, May 30, 2014

[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


Monday, May 12, 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

Saturday, May 10, 2014

[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

Wednesday, April 23, 2014

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



Tuesday, April 15, 2014

#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

Wednesday, March 19, 2014

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

Friday, February 14, 2014

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