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!
Tuesday, December 17, 2013
Thursday, November 14, 2013
#SQLSat255 - recap, thoughts from a first-time SQLSat presenter
>verbosity on
VERBOSITY IS SET TO HIGH
First, this is a long blog post. My wife said I'd given up any chance of being "The Terse DBA". Ha. :-\
TL;DR: SQLSaturdays are awesome. Even if you're not Old Grizzled Veteran (or maybe especially if you're not one), you should find a topic and look at speaking - the experience was wonderful.
And here's my slide deck + exact scripts used during my presentation: https://drive.google.com/file/d/0B3a287PS_UJIRDJBNWEtNVBJY0E/edit?usp=sharing
So, here's my full story of SQL Saturday 255 (Dallas TX 2013). Yes, it's long. There's a line of dashes below when I get to SQL Sat 255 itself.
First, let's roll back a couple years to April 2011. I'd been to PASS once, and then heard about this SQL Saturday thing... some sort of PASS event (denoting awesome quality), and the opportunity to get a full day of training was too good to pass up. And, hey, it's like $10, right? Slam-dunk. It was fantastic, and I saw several really interesting sessions, learned how to muck with Kerberos, verified my two-tier repliction was As Good As You Were Going To Get, and spoke with a couple people from Microsoft at length. I loved it.
Then 2012 rolled around and with it, SQL Rally. Again a blast (Fritchey's talk on parameter sniffing was a thing of beauty), but I saw a couple presentations and thought "well, I could do that - I know it at least as well as them". So, with a bunch of hubris and zero humility, I decided it was time to present.
Naturally, the first stumbling block came immediately - what do I present on? The easy ones were already "taken", in my mind, by people who already had presentations built and had presented multiple times. Yes, I know, everybody brings something different to the party, but theirs is already there - and says pretty much everything I could say.
At the same time, I was trying to track down some code changes. It was aggravating. Standards for deploys were coming, but I was trying to find a happy medium and something I could use immediately to more easily find them when they occurred (lots of DBA where I work). I'd looked at setting up Traces (a pain to collect), DDL Triggers (broke replication in fun new ways, and prevented me from applying an SP to our cluster), a third party tool (which was slow to scan and wouldn't reliably work with our TFS server - which was its main interface). So I'd gone towards Extended Events, which was the Next Big Thing, learning a lot (but not finding a solution to my problem). Hip deep in it, asking questions, and Jonathan Kehayias (who I'd seen present on XE at PASS) chimed in, and offhandedly mentioned that XE could be used for it, but why wasn't I using Event Notifications...
Several hours digging in the BOL, and I was intrigued. A couple days/weeks later I had code running, but with some significant holes in my knowledge. More coding, more reading, more time spent, more homework, and more Stack Overflow. At some point, I realized that this was my presentation - I knew the subject really well, as I'd been bitten by all the major bugs. Almost nobody had presented on it (only 2 sessions at PASS in 7 years), and even though I'd been at BOTH summits it was presented at, and I was the target audience, the title/abstract must not have made an impression on me (sorry, guys). And finally, and this really was the key - I was (heck, still am) jazzed about this - I use it pretty much daily, and it's an awesome tool.
More work, more slide decks, more demos... and finally, when I thought I was ready, I spoke to the local user group - and they had a spot I could present at. Easy as pie, right?
Yeah, not so much. I had a work laptop with my demos on it. Which was running Windows Server, and didn't have the drivers so it would actually WORK with the projector. Fortunately, I knew the material really well, the audience was both responsive and forgiving (*and suggested narrating my code instead of skipping an un-doable demo), and it all worked on in the end. A bit frustrating, but it worked! Ask about my newly-discovered interpretive dance-around-the-code skills!
So, one down, what's next? Well, a couple months later (and yet more servers deployed), I asked the DBA Virtual Chapter of PASS if they had an opening. This time I was on a laptop I knew well, had my VMs all set up (3 of em!), and was presenting virtually. This time a larger audience - 90 people watching was a great feeling, and (aside from not knowing if the laptop's cam was broadcasting) I thought it went really well, got some twitter followers, and some questions that showed they were interested.
So the next step was PASS Summit, right? I took my overly-grandiose title and abstract (which I'd written so that my past self would've been interested), and then changed them a bunch on advice from others, trying to make it more accessible and get my golden PASS ticket. Weeks passed and... nope. Insert sad trombone here. Honestly, the most frustrating part was the silence. No formal rejection letter, just seeing the accepted sessions and my name wasn't on there. No feedback on why I'd been rejected - was it a topic nobody cared about? Was there some other session on it by a Big Name? Was it a crappy title?
-------------------------------------
So I dusted off my original abstract, kept the overly-grandiose title ("Master of All I Survey" - I still like it), and tweaked my abstract a little (but only a little), hit submit, and waited.
A few weeks later, my coworkers heard a "whoop" from my desk and wondered what the hell I'd done now. I'd been accepted! Which meant.... more work. Working on the slide deck, work through my presentation, make sure the damn laptop would connect to a projector... then present to coworkers a couple weeks in advance. It's never a good sign, by the way, when in a small room 3 of the 5 fall asleep (it was warm! Seriously!). Not my shining-est moment . However, it did make me re-evaluate my presentation, so I changed the whole thing around - added slides, added demos, moved parts around and overall refactored (heh, code term) a whole bunch of things. Somewhere in there I also went to PASS, which worked on many levels - I'd been taking notes not just on the subjects, but on the presentation process itself. Also, I saw a great presentation by Mark Vaillancourt and picked up a bunch of tips.
Fast forward a couple weeks and a whole lot of practicing.
I'd been getting emails from Ryan, Sri, and Ganesh about the details of the SQL Saturday, and was pleasantly surprised to get an email about a Speakers Dinner - how cool is that?! Honestly, I hadn't considered that there'd be one. And, it was awesome - we met at one of the local Mercado Juarez restaurants, went to one of the rooms, and lo and behold! A bunch of people there that I'd either seen present (Grant, Dave, Mike), read articles by (Kathi), or even just met people for the first time and seen their abstracts on the schedule. One thing that did stand out - I asked about my PASS submission, and one of the long-time speakers said, (paraphrased) "no, you haven't presented enough, yet. For Summit, the quality has to be through the roof, and a first/second-time presenter isn't at that level when you're paying that kind of money". Put in that context, yeah, she was definitely right. Overall, though, the night was fantastic - a bunch of people who were all there for a common cause, having a lot of fun hanging out and telling stories. The term #SQLFamily fit perfectly here, even more so than at PASS Summit, which I didn't think was possible. And to finish the awesome evening, our hosts handed out shirts along with our speaker badges, gave us a sincere thank you, and made us feel utterly and completely like rock stars. I was humbled - but had a gigantic grin on my face the rest of the night.
Fast forward 12 hours, getting up far too early so that I'd be there at 7:30, and could get into the room early (and make sure my laptop worked; oddness presenting at work in full-screen mode). Ran into Glenda, a new friend from the previous night's dinner, and walked into the UTA hall. First order of business? Checking in, handled flawlessly by the tireless volunteers. Next? Double-checking the laptop. I had a couple different possible concerns, but had 3 different contingency plans, none of which wound up being necessary. That's why you have them, though, right? Next up - grabbed breakfast and listened to the welcome, then found my list of presentations-I-really-want-to-see. I didn't have to speak until 2:45, which was a mixed blessing. On the plus side, it gave me all day to prep/practice should I need it. On the minus side, I was up against Joe Celko. Yup, Celko's from Austin and he was presenting. No pressure, right?
So on to the presentations!
First up, Ami Levin - physical join operators. Enjoyed this thoroughly - Ami used two gigantic decks of cards as metaphors, and Grant Fritchey was kind enough to volunteer to be the magician's assistant. Mostly stuff I knew (always nice), but a couple tricks/pitfalls I hadn't considered (even nicer!).
Next? Khaladkar talking about SSAS, Tabular, and DirectQuery. We're looking at implementing Tabular SSAS, but were concerned since our compressed data set is over a Terabyte - which means we'll probably have to use Direct Query. The details were there, but I flinched when he said he was going to VPN in to show some demos - I'd seen enough issues in the past with dodgy wifi that I was pleasantly surprised when it (mostly) worked. Some interesting details in his presentation, with a whole lot of lessons learned, and I'd love to see it again once he's had a chance to further polish it.
For the 11am session, there were a couple I wanted to see, and wound up going to see Steven Ormrod (who just posted his blog entry on SQLSat - http://stevenormrod.com/2013/11/sql-saturday-255-dallas-revisited/) talk about memory. Honestly, I know more-or-less how it works, but the details have always hurt my brain. Fortunately, he did a great job putting it all together and I got some good takeaways.
Next up - lunch! Lots of choices, and a cookie. Mmmm... cookie. And a quick marketing spiel by Microsoft that actually showed off a couple cool Cloud-type things that even I (as a HIPAA-bound person) could use. Then it was time to take advantage of the awesome sponsors, as well as check my VMs prior to my presentation. Which, naturally, weren't talking to each other (and was about 25% of my presentation). Now I see the advantages of the Speaker Room - casual conversation with a bunch of people, while working on getting my VMs up (30 minutes later, finally run a profile trace, and it becomes obvious what's going on - the VMs have shifted their times outside the Kerberos-allowed skew).
A couple cool conversations occur - several cool stories from Joe Celko who has come in, and is definitely The Best Dressed DBA there - 3 piece suit, Superman cufflinks, and all. The man has Stories, people, mad stories! So I learned interesting Joe Celko facts - for instance, he's also known in India as SQL Satan, and in South America as Ming the Merciless. Makes me wish I'd gone with a Flash shirt for my presentation. One of the presenters' daughters was in the speakers room, and chatted with Joe. Which resulted in some other cool stories coming out.
Another interesting conversation was about SQL Saturday itself, and whether it was sustainable or would succumb to fatigue by the various parties (vendors, volunteers, speakers). Tim Mitchell opined that the first time he went, he didn't think it'd be sustainable - then mentioned that this was at SQL Saturday #3. As a first-time speaker, personally, I think it's going to continue to do well While I did recognize a couple of the vendors from PASS and SQL Saturday #237 a couple weeks before, all the rest were new. And equally importantly, they're hitting a different group of people. Aside from a handful of presenters who'd been to several SQL Saturdays, most of the people there hadn't been to other SQL Saturdays or even PASS, so it was a whole new tank of fish for the vendors to try and "catch". And I bet that's the way with all the SQL Saturdays - brand new groups of locals. It also was a good opportunity, IMHO, for a lot of the local firms to get their name out in front of DBAs. And as a first-time speaker to SQL Saturday, I think there are two keys. The first is making sure speakers are made welcome - and they did a marvelous job of that.
The second key will be encouraging and cultivating new speakers.
I've been doing SQL Server since 7.0 came out, and while I can be an extrovert, it's not my natural state. I hadn't presented before because I figured it was all Big Names, albeit names I didn't know, that were presenting, and that their experience and knowledge far outstripped mine. Obviously it's not - it's just a bunch of members of the SQLFam who have done the hard work and spent the effort. I think it's just a matter of getting more people exposed to the idea of speaking earlier on - while I still don't know everything, SQL Server is big enough that you're never going to know it all, so find something you know well, put in the effort, help out, and present... or at least volunteer to help. Thomas Larock said at PASS that he viewed everyone there as a future volunteer, and he's right.
Just my 2 cents.
What's left? My presentation! It went well - I had probably about 20 people in there, and there was definitely some interest (which means I'd better finish working on my new set of scripts!). And as it turned out, the room I was in had all its sessions recorded by Usergroup.Tv, another community outreach by some awesome people, so my presentation should be online in a couple weeks. Also, the previous month I'd been looking for a "wow" to bring to my presentation, and so put some extra effort in - which ended up with a SQL job and Powershell script that checks changed items into TFS automatically! It works well (my boss loves the thing) and got a lot of interest from the audience. I've just been querying the table directly or using an SSRS report, but I think I'm going to spend more time on the TFS part, especially after seeing how many questions were about it. ("you mean we don't have to give our devs VIEW DEFINITION?!" Ayup.) I was also really surprised to get my feedback papers immediately - I'd figured that they'd use it for future SQLSaturdays, and even figured they were using this as a "Farm League" for PASS Summit. I was thrilled with the results - probably 80% 5s, 15% 4s, a single 3, and one guy who said he wanted to see more presentations by me and loved this one - but gave me all 1s.
After my presentation? Floating on a cloud, that was me... so I was late to the final presentation, a one-hour distillation by Greg Galloway of a 350-page whitepaper on tuning Tabular SSAS. Thin attendance, probably because everyone's brain was full, but I got a ton out of it - and it was a precursor to a full-day seminar in Las Colinas, which we made sure to send somebody to.
Finally, the end-of-day kum-ba-ya (standing room only!), and all the awesome giveaways. Coworker had a very similar name to one of the winners, so there was some confusion - and after that, everyone made sure to check name tags. : )
Then, the after-party, which was a blast. It was at the Dave & Busters, and we had a "room" with a couple pool tables, grub (pub food, but pretty good), and a whole lot of people to talk to. So I spent the night talking to various people, both SQL and otherwise, some more amazing Joe Celko stories, networking, making friends, and even beat a coworker at a game of pool.
Then Monday rolled around, I got the skinny on the pre-cons, found out what all my coworkers saw and learned (almost everyone from my group went to one of the pre-cons), and then got the biggest surprise of all - my coworkers were so stoked from this one, they were ready to go see another! So, I've submitted to SQL Saturday Albuquerque 2014 - we'll see what happens...
VERBOSITY IS SET TO HIGH
First, this is a long blog post. My wife said I'd given up any chance of being "The Terse DBA". Ha. :-\
TL;DR: SQLSaturdays are awesome. Even if you're not Old Grizzled Veteran (or maybe especially if you're not one), you should find a topic and look at speaking - the experience was wonderful.
And here's my slide deck + exact scripts used during my presentation: https://drive.google.com/file/d/0B3a287PS_UJIRDJBNWEtNVBJY0E/edit?usp=sharing
So, here's my full story of SQL Saturday 255 (Dallas TX 2013). Yes, it's long. There's a line of dashes below when I get to SQL Sat 255 itself.
First, let's roll back a couple years to April 2011. I'd been to PASS once, and then heard about this SQL Saturday thing... some sort of PASS event (denoting awesome quality), and the opportunity to get a full day of training was too good to pass up. And, hey, it's like $10, right? Slam-dunk. It was fantastic, and I saw several really interesting sessions, learned how to muck with Kerberos, verified my two-tier repliction was As Good As You Were Going To Get, and spoke with a couple people from Microsoft at length. I loved it.
Then 2012 rolled around and with it, SQL Rally. Again a blast (Fritchey's talk on parameter sniffing was a thing of beauty), but I saw a couple presentations and thought "well, I could do that - I know it at least as well as them". So, with a bunch of hubris and zero humility, I decided it was time to present.
Naturally, the first stumbling block came immediately - what do I present on? The easy ones were already "taken", in my mind, by people who already had presentations built and had presented multiple times. Yes, I know, everybody brings something different to the party, but theirs is already there - and says pretty much everything I could say.
At the same time, I was trying to track down some code changes. It was aggravating. Standards for deploys were coming, but I was trying to find a happy medium and something I could use immediately to more easily find them when they occurred (lots of DBA where I work). I'd looked at setting up Traces (a pain to collect), DDL Triggers (broke replication in fun new ways, and prevented me from applying an SP to our cluster), a third party tool (which was slow to scan and wouldn't reliably work with our TFS server - which was its main interface). So I'd gone towards Extended Events, which was the Next Big Thing, learning a lot (but not finding a solution to my problem). Hip deep in it, asking questions, and Jonathan Kehayias (who I'd seen present on XE at PASS) chimed in, and offhandedly mentioned that XE could be used for it, but why wasn't I using Event Notifications...
Several hours digging in the BOL, and I was intrigued. A couple days/weeks later I had code running, but with some significant holes in my knowledge. More coding, more reading, more time spent, more homework, and more Stack Overflow. At some point, I realized that this was my presentation - I knew the subject really well, as I'd been bitten by all the major bugs. Almost nobody had presented on it (only 2 sessions at PASS in 7 years), and even though I'd been at BOTH summits it was presented at, and I was the target audience, the title/abstract must not have made an impression on me (sorry, guys). And finally, and this really was the key - I was (heck, still am) jazzed about this - I use it pretty much daily, and it's an awesome tool.
More work, more slide decks, more demos... and finally, when I thought I was ready, I spoke to the local user group - and they had a spot I could present at. Easy as pie, right?
Yeah, not so much. I had a work laptop with my demos on it. Which was running Windows Server, and didn't have the drivers so it would actually WORK with the projector. Fortunately, I knew the material really well, the audience was both responsive and forgiving (*and suggested narrating my code instead of skipping an un-doable demo), and it all worked on in the end. A bit frustrating, but it worked! Ask about my newly-discovered interpretive dance-around-the-code skills!
So, one down, what's next? Well, a couple months later (and yet more servers deployed), I asked the DBA Virtual Chapter of PASS if they had an opening. This time I was on a laptop I knew well, had my VMs all set up (3 of em!), and was presenting virtually. This time a larger audience - 90 people watching was a great feeling, and (aside from not knowing if the laptop's cam was broadcasting) I thought it went really well, got some twitter followers, and some questions that showed they were interested.
So the next step was PASS Summit, right? I took my overly-grandiose title and abstract (which I'd written so that my past self would've been interested), and then changed them a bunch on advice from others, trying to make it more accessible and get my golden PASS ticket. Weeks passed and... nope. Insert sad trombone here. Honestly, the most frustrating part was the silence. No formal rejection letter, just seeing the accepted sessions and my name wasn't on there. No feedback on why I'd been rejected - was it a topic nobody cared about? Was there some other session on it by a Big Name? Was it a crappy title?
-------------------------------------
So, fast forward a couple months. On a lark, I was showing one of my new coworkers this SQL Saturday thing, there was one in 2011 and 2012, maybe there'd be one in Austin or someth.... wait, what? This fall? Here? Schwing!
So I dusted off my original abstract, kept the overly-grandiose title ("Master of All I Survey" - I still like it), and tweaked my abstract a little (but only a little), hit submit, and waited.
A few weeks later, my coworkers heard a "whoop" from my desk and wondered what the hell I'd done now. I'd been accepted! Which meant.... more work. Working on the slide deck, work through my presentation, make sure the damn laptop would connect to a projector... then present to coworkers a couple weeks in advance. It's never a good sign, by the way, when in a small room 3 of the 5 fall asleep (it was warm! Seriously!). Not my shining-est moment . However, it did make me re-evaluate my presentation, so I changed the whole thing around - added slides, added demos, moved parts around and overall refactored (heh, code term) a whole bunch of things. Somewhere in there I also went to PASS, which worked on many levels - I'd been taking notes not just on the subjects, but on the presentation process itself. Also, I saw a great presentation by Mark Vaillancourt and picked up a bunch of tips.
Fast forward a couple weeks and a whole lot of practicing.
I'd been getting emails from Ryan, Sri, and Ganesh about the details of the SQL Saturday, and was pleasantly surprised to get an email about a Speakers Dinner - how cool is that?! Honestly, I hadn't considered that there'd be one. And, it was awesome - we met at one of the local Mercado Juarez restaurants, went to one of the rooms, and lo and behold! A bunch of people there that I'd either seen present (Grant, Dave, Mike), read articles by (Kathi), or even just met people for the first time and seen their abstracts on the schedule. One thing that did stand out - I asked about my PASS submission, and one of the long-time speakers said, (paraphrased) "no, you haven't presented enough, yet. For Summit, the quality has to be through the roof, and a first/second-time presenter isn't at that level when you're paying that kind of money". Put in that context, yeah, she was definitely right. Overall, though, the night was fantastic - a bunch of people who were all there for a common cause, having a lot of fun hanging out and telling stories. The term #SQLFamily fit perfectly here, even more so than at PASS Summit, which I didn't think was possible. And to finish the awesome evening, our hosts handed out shirts along with our speaker badges, gave us a sincere thank you, and made us feel utterly and completely like rock stars. I was humbled - but had a gigantic grin on my face the rest of the night.
Fast forward 12 hours, getting up far too early so that I'd be there at 7:30, and could get into the room early (and make sure my laptop worked; oddness presenting at work in full-screen mode). Ran into Glenda, a new friend from the previous night's dinner, and walked into the UTA hall. First order of business? Checking in, handled flawlessly by the tireless volunteers. Next? Double-checking the laptop. I had a couple different possible concerns, but had 3 different contingency plans, none of which wound up being necessary. That's why you have them, though, right? Next up - grabbed breakfast and listened to the welcome, then found my list of presentations-I-really-want-to-see. I didn't have to speak until 2:45, which was a mixed blessing. On the plus side, it gave me all day to prep/practice should I need it. On the minus side, I was up against Joe Celko. Yup, Celko's from Austin and he was presenting. No pressure, right?
So on to the presentations!
First up, Ami Levin - physical join operators. Enjoyed this thoroughly - Ami used two gigantic decks of cards as metaphors, and Grant Fritchey was kind enough to volunteer to be the magician's assistant. Mostly stuff I knew (always nice), but a couple tricks/pitfalls I hadn't considered (even nicer!).
Next? Khaladkar talking about SSAS, Tabular, and DirectQuery. We're looking at implementing Tabular SSAS, but were concerned since our compressed data set is over a Terabyte - which means we'll probably have to use Direct Query. The details were there, but I flinched when he said he was going to VPN in to show some demos - I'd seen enough issues in the past with dodgy wifi that I was pleasantly surprised when it (mostly) worked. Some interesting details in his presentation, with a whole lot of lessons learned, and I'd love to see it again once he's had a chance to further polish it.
For the 11am session, there were a couple I wanted to see, and wound up going to see Steven Ormrod (who just posted his blog entry on SQLSat - http://stevenormrod.com/2013/11/sql-saturday-255-dallas-revisited/) talk about memory. Honestly, I know more-or-less how it works, but the details have always hurt my brain. Fortunately, he did a great job putting it all together and I got some good takeaways.
Next up - lunch! Lots of choices, and a cookie. Mmmm... cookie. And a quick marketing spiel by Microsoft that actually showed off a couple cool Cloud-type things that even I (as a HIPAA-bound person) could use. Then it was time to take advantage of the awesome sponsors, as well as check my VMs prior to my presentation. Which, naturally, weren't talking to each other (and was about 25% of my presentation). Now I see the advantages of the Speaker Room - casual conversation with a bunch of people, while working on getting my VMs up (30 minutes later, finally run a profile trace, and it becomes obvious what's going on - the VMs have shifted their times outside the Kerberos-allowed skew).
A couple cool conversations occur - several cool stories from Joe Celko who has come in, and is definitely The Best Dressed DBA there - 3 piece suit, Superman cufflinks, and all. The man has Stories, people, mad stories! So I learned interesting Joe Celko facts - for instance, he's also known in India as SQL Satan, and in South America as Ming the Merciless. Makes me wish I'd gone with a Flash shirt for my presentation. One of the presenters' daughters was in the speakers room, and chatted with Joe. Which resulted in some other cool stories coming out.
Another interesting conversation was about SQL Saturday itself, and whether it was sustainable or would succumb to fatigue by the various parties (vendors, volunteers, speakers). Tim Mitchell opined that the first time he went, he didn't think it'd be sustainable - then mentioned that this was at SQL Saturday #3. As a first-time speaker, personally, I think it's going to continue to do well While I did recognize a couple of the vendors from PASS and SQL Saturday #237 a couple weeks before, all the rest were new. And equally importantly, they're hitting a different group of people. Aside from a handful of presenters who'd been to several SQL Saturdays, most of the people there hadn't been to other SQL Saturdays or even PASS, so it was a whole new tank of fish for the vendors to try and "catch". And I bet that's the way with all the SQL Saturdays - brand new groups of locals. It also was a good opportunity, IMHO, for a lot of the local firms to get their name out in front of DBAs. And as a first-time speaker to SQL Saturday, I think there are two keys. The first is making sure speakers are made welcome - and they did a marvelous job of that.
The second key will be encouraging and cultivating new speakers.
I've been doing SQL Server since 7.0 came out, and while I can be an extrovert, it's not my natural state. I hadn't presented before because I figured it was all Big Names, albeit names I didn't know, that were presenting, and that their experience and knowledge far outstripped mine. Obviously it's not - it's just a bunch of members of the SQLFam who have done the hard work and spent the effort. I think it's just a matter of getting more people exposed to the idea of speaking earlier on - while I still don't know everything, SQL Server is big enough that you're never going to know it all, so find something you know well, put in the effort, help out, and present... or at least volunteer to help. Thomas Larock said at PASS that he viewed everyone there as a future volunteer, and he's right.
Just my 2 cents.
What's left? My presentation! It went well - I had probably about 20 people in there, and there was definitely some interest (which means I'd better finish working on my new set of scripts!). And as it turned out, the room I was in had all its sessions recorded by Usergroup.Tv, another community outreach by some awesome people, so my presentation should be online in a couple weeks. Also, the previous month I'd been looking for a "wow" to bring to my presentation, and so put some extra effort in - which ended up with a SQL job and Powershell script that checks changed items into TFS automatically! It works well (my boss loves the thing) and got a lot of interest from the audience. I've just been querying the table directly or using an SSRS report, but I think I'm going to spend more time on the TFS part, especially after seeing how many questions were about it. ("you mean we don't have to give our devs VIEW DEFINITION?!" Ayup.) I was also really surprised to get my feedback papers immediately - I'd figured that they'd use it for future SQLSaturdays, and even figured they were using this as a "Farm League" for PASS Summit. I was thrilled with the results - probably 80% 5s, 15% 4s, a single 3, and one guy who said he wanted to see more presentations by me and loved this one - but gave me all 1s.
After my presentation? Floating on a cloud, that was me... so I was late to the final presentation, a one-hour distillation by Greg Galloway of a 350-page whitepaper on tuning Tabular SSAS. Thin attendance, probably because everyone's brain was full, but I got a ton out of it - and it was a precursor to a full-day seminar in Las Colinas, which we made sure to send somebody to.
Finally, the end-of-day kum-ba-ya (standing room only!), and all the awesome giveaways. Coworker had a very similar name to one of the winners, so there was some confusion - and after that, everyone made sure to check name tags. : )
Then, the after-party, which was a blast. It was at the Dave & Busters, and we had a "room" with a couple pool tables, grub (pub food, but pretty good), and a whole lot of people to talk to. So I spent the night talking to various people, both SQL and otherwise, some more amazing Joe Celko stories, networking, making friends, and even beat a coworker at a game of pool.
Then Monday rolled around, I got the skinny on the pre-cons, found out what all my coworkers saw and learned (almost everyone from my group went to one of the pre-cons), and then got the biggest surprise of all - my coworkers were so stoked from this one, they were ready to go see another! So, I've submitted to SQL Saturday Albuquerque 2014 - we'll see what happens...
Wednesday, October 23, 2013
#SQLSaturday255 - I'm presenting In Dallas on November 2nd!
Howdy, all. I'm proud to announce that I'll be presenting on Event Notifications (aka "Master of All I Survey") at SQL Saturday 255 in Arlington (Texas) on November 2nd. I personally will be presenting at 2:45, in Room 109, but there are almost 50 different sessions, including such luminaries as Joe Celko and Grant Fritchey, just to name a couple. So I'm definitely ramping up my presentation in response!
http://www.sqlsaturday.com/255/eventhome.aspx
What we'll be covering:
I think I've got a good mix of Presentation and Demos, and my goal is to send you home with code that you want to use immediately, and CAN use immediately!
See you there!
http://www.sqlsaturday.com/255/eventhome.aspx
What we'll be covering:
- What is Service Broker and why is it useful?
- What are Event Notifications and what good are they?
- How do we use Event Notifications to track code changes (DDL)?
- How do we centralize that for many machines?
- Reporting?
- EN Tricks: emailing errors, watching for Bad Things, and checking into TFS automagically.
I think I've got a good mix of Presentation and Demos, and my goal is to send you home with code that you want to use immediately, and CAN use immediately!
See you there!
Monday, October 7, 2013
[Event Notifications] Automatically check in code changes to TFS
I'll be presenting this at SQLSaturday 255 in Dallas, on November 2nd 2013.
Two note to begin, because this is a lengthy post: there are two parts of this, and two scripts.
* The Powershell script will script out objects and check them into TFS. No prereqs other than TFS and powershell. You could just run this on servers daily and it would give you a basic "here's what the code is like". Basic but functional.
* The SQL script will look for new changes using an existing Event Notifications setup, then call the PoSH script for those objects. More work, but IMHO more reward; you get real-time monitoring, information such as the LoginName of the user who changed it, and better comments.
So, I've been trying to figure out better ways to present code to our developers, as well as giving our group the ability to look back at code changes in production. Our devs have Source Control for code that's deployed, but our Operations team has to look at objects that have been changed over time, be it via indexes, tweaks made in production for emergencies, permission changes, etc.
There is one 3rd party app I know of that does this, but we had issues getting it to work.
So I decided to write my own.
And for me it seemed a natural to tie it in with Event Notifications. My original idea was to just use EN to check the changes in, but that's not always feasible - if a table gets a new column, how do you represent that? So I moved to a different model - EN detects the changes, and then a separate SP kicks off a powershell script that deals with TFS, by scripting out a new copy of the affected object(s) and checking it in. And because we're using EN, we get information like the username of the person who changed it, what exactly they did, etc.
While this method can be used with any source control server, our company uses TFS to track changes.
However, I was trying to set it up on an existing server and didn't want a "heavy" client on that server. Fortunately, I found on stack overflow a post (http://stackoverflow.com/questions/5503858/how-to-get-tf-exe-tfs-command-line-client, answer by ijprest) describing how to do this. Alternatively, you can install Team Explorer or Visual Studio with Team Explorer or use the Java TFS client. The way licensing works, as long as you have a CAL, they don't care what client you use.
The next step was the scripting-to-disk. I settled on using Phil Factor's code which uses powershell & SMO, then modified it for my use, including the ability to feed a server/database and have it script out the entire database and check it into TFS. All the credit goes to him, all the blame goes to me. Right now it's scripting "under" the database-level - you get all the changes within the database, but not changes ON the database, like changes to DB settings, files, and logins/permissions. Version 1.1 will script at the server level and below.
So, at this point we have TFS, a SMO object scripter, next up is code that runs, looks at our Event Notification table, and call the Powershell script. Our TFS does not allow every user to CHECKIN code, so rather than risk it failing I add their name to the COMMENT instead. We are calling the powershell script via XP_CMDSHELL; there's probably a better way and I'll look at it for a future version, once PASS 2013 is over.
Overall, it seems to work well. I need to work on some sort of dynamic exclusion list and a couple other tweaks, but overall I'm very happy with it. As are our auditors.
Now, the install, which is intimidating. But think of it the other way. Create 3 directories, copy over a total of 11 files, run one script, create two tables and add one job. The hardest part, personally, was getting the TFS workspace working.
First, create 3 folders:
C:\TFS_CLI
C:\TFS_CLI\App
C:\TFS_CLI\TF_Workspace
Next, copy the following 10 files from a
valid TFS install into the \App folder.
(Alternatively,
install Team Explorer, which includes TF.EXE; you’ll need to change
the commands as I’ve hardcoded the location of TF.EXE)
Microsoft.TeamFoundation.Build.Controls.dll
Microsoft.TeamFoundation.Client.dll
Microsoft.TeamFoundation.Common.dll
Microsoft.TeamFoundation.Common.Library.dll
Microsoft.TeamFoundation.dll
Microsoft.TeamFoundation.VersionControl.Client.dll
Microsoft.TeamFoundation.VersionControl.Common.dll
Microsoft.TeamFoundation.VersionControl.Controls.dll
TF.exe
TF.exe.config
--unsure if this first line is needed or
not. Run from command line in that folder
c:\TFS_CLI\app\>tf.exe workspace /new
tf_workspace /location:c:\TFS_CLI\TF_Workspace
Unable to determine the source control
server.
c:\TFS_CLI\App>tf workspace /new
/noprompt /s:http://your-tf-server-name:8080 TF_Workspace
c:\TFS_CLI\App>tf workfold $/YourENproject
c:\TFS_CLI\EN_Workspace /workspace:TF_Workspace /s:http://your-tf-server-name:8080
c:\TFS_CLI\App>tf workfold /unmap $/
Now, try and check out an object - I put a test object in mine to make sure this worked.
c:\TFS_CLI\App> tf get
$/Randolph/ftw-sv-db-03/assetexplorer/Tables/dbo.AaaAce.sql
Now, save the following script as sql_script_to_TFS.ps1 and place it in C:\TFS_CLI.
Since it's Powershell you'll either need to sign it or run in an execution mode of unrestricted.
Personally, I'd sign it.
https://gist.github.com/mbourgon/6873071 (in case it isn't directly below.
Once you have it saved, try adding an object!
--SSMS, on my_personal_server
use somedatabase
create table blah (id int identity)
Now from powershell:
C:\TFS_CLI\sql_script_to_TFS.ps1 -Server my_personal_server -Database somedatabase -objecttoscript blah -Author mydomain\validTFSuser
On mine, I get:
item exists; get, check out, script to override, check in
C:\TFS_CLI\YourENproject\my_personal_server\somedatabase\Table:
Checking in edit: dbo.blah.sql
Checking in edit: dbo.blah.sql
(followed by a confirmation that it checked in)
All good? Good. Now for the final step - using EN to kick it off.
Create a couple tables to hold the details. Mine are in my EN database, since that's where the data is.
CREATE TABLE
TFS_Checkin_Errors (id BIGINT IDENTITY PRIMARY key, time_job_run DATETIME, original_id INT, resultant VARCHAR(8000))
CREATE TABLE
TFS_Last_Time_Run (last_time_run DATETIME PRIMARY KEY )
Save out this script, then run it on the server.
Run once to make sure it's acting as expected, then set up a job to run it every 5 minutes.
....and..... you're done!
Check the error tables for issues; right now it doesn't handle spaces in the object name, I'm fixing that this week.
Subscribe to:
Posts (Atom)