Friday, December 12, 2014

[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?
  • ($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.

Tuesday, December 9, 2014

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

Tuesday, December 2, 2014

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

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. 

  • 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)
#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.
[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