Ian Kirk - SQL DBA

No - I haven’t gotten into Policy Based Management (PBM) as much as I really want to, especially after seeing so much of it at PASS this last November.  And no, I’m not going dedicate this blog post to lots of first-hand information that will help you get further in using it.  This is, for all intents and purposes, a really big tweet to @SQLBatman.

First, the MSDN PBM blog that isn’t updated too often, but the (current) top post for “complex” policies seems pretty helpful.

Next, Lara Rubbelke, who had a presentation at PASS that I missed.  She’s been working with Policies, PowerShell, and built an entire framework to help you get it done.

Third, Buck Woody, who seems to be a lot more of PowerShell kind of guy, but knowing PowerShell, I think, is going to be key to automating policy evaluation across your enterprise.  (Of course Lara may have taken care of that for us…).  Buck offers an awesome PowerShell framework that he presented at PASS on his blog as well.

As for my first hand experience, it came before PASS and it was a great experience.  I used a policy stored on my sandbox SQL 2008 server and evaluated 8 instances that were a mix of SQL 2000 and SQL 2005.  I had that policy check specific server configuration items (such as Max DOP) and correct the instances that didn’t meet the stated policy.  After creating the policy, it was just a click of the mouse to evaluate.  Like I said earlier, though, to automate, you’re going to need to know a scripting language like PowerShell.

As a final link, I’ll send you to the PASS Presentation Decks, where you can search through for the presentations and find a number of slide decks regarding PBM.  Specifically:

• DBA-320-M
• DBA-412
• AD-301-M

Enjoy - and be sure to let me know if you figure it all out because learning PBM is high on my personal list… it’s just that I haven’t had any time to devote to that particular list!

It’s obvious I’m still on what has been dubbed the “PASS high” and I certainly hope it doesn’t wear off any time soon.  It’s a great feeling, having so many new ideas and techniques at your fingertips.  Meeting all these new people then realizing how much I’ve been missing and diving in - that’s been a blast.

At the same time, it’s made me reflect on the things happening in my own team.  Here I sit, technically on Thanksgiving Day, on my laptop, winding down from cleaning house, and I get a ping in IM from our DBA in Japan.  It’s late in the afternoon for him and I know he was called in early.  Beyond that, I know this guy is a really sharp DBA and if he’s asking for something, it certainly isn’t trival.  I hesitate slightly, but I respond.

He’s hit a snag with replication - the distributor is complaining about the system generated replication procedures being missing on the target.  Amusingly, I’d spent the plane rides from Nashville to Seattle typing up some internal training materials on replication (which I’m realizing are way too in depth for a start) and I knew exactly what he needed to do.

Unfortunately, I explained it in way too little detail, and he didn’t grab it right away.  He toiled for a few minutes and I checked back in.  I knew from his response that my answer hadn’t made sense.  Suddenly I launched into a paragraph explaination of replication distribution and how to re-generate the procs that were missing.  He plugged away for a minute and came out victorious.  He was extremely thankful, given the time of night, and I told him it was pleasure enough knowing that I’d been able to teach him a bit more about replication.  This is the spirit of PASS - sharing with your fellow DBA as peers.

So - if you didn’t already know, here’s (my take on) the short version:

Replication transfers data uses parametered stored procedures and these stored procedures are system generated by the snapshot agent.  The snapshot agent leverages the system stored procedure sp_scriptpublicationcustomprocs to generate those procedures.  It then applies that script only to the target environment and at a later time the distribution agent, when attempting to transfer data, calls these procedures with the appropriate parameters.

Since the distribution agent is complaining that these specific table’s stored procedures are missing, all you need to do is to run that system stored procedure which will generate a script for you.  That script will have  all of the replication stored procedures for every published table in the publication you indicate.  From that large system generated script, you must locate the 3 procedures associated with the specific table that is creating errors and then run those procs on the target environment.

Those procs, for reference, are the sp_MSins_<tablename>, sp_MSupd_<tablename>, sp_MSdel_<tablename>, for INSERT, UPDATE, DELETE respectfully.  Also, the procedure sp_scriptpublicationcustomprocs needs to be executed in the publishing database and it takes a single parameter of the publication name.

Our little hang up came when we changed the primary key constraint on a published table, but the snapshot agent didn’t run to replace the procs.

If you see anything I’ve stated wrong, feel free to correct me!

Like most people returning to the office from the PASS conference, the first hours of my day were spent deciding which emails just went straight to the trash can, and which emails required direct attention. Luckily, the second bucket was considerably smaller than the first. Once I got through the emails, I took a short side trip down Twitter-lane, and installed TweetDeck.

I think I chose TweetDeck over the others for its simple black theme. I also think it’s notification system is becoming a giant distraction to me. I’ve seen countless times that I should turn off my Outlook notifications, but now that I have two message notification systems, I’m really beginning to notice the productivity drain.

Once I got through that, I dove into a stored procedure that Michelle Ufford (@SQLFool) had written and posted recently. Like I had said on her blog, it was almost exactly what I had envisioned while I was sitting in the replication troubleshooting session in Seattle. I worked with the code and exchanged a few emails with Michelle through the day.

This exchange may never have happened if it hadn’t before the PASS conference and the use of Twitter. Michelle followed me on Twitter, I found her blog, and then I found a solution to a problem. Soon I’ll have fantastic replication latency information that will help me understand my environment, and do it through an automated manner.

Beyond the replication latency procedure, I wrote a list of ideas for things I want to see me and the team complete. Things like creating policies and automating their evaluation via PowerShell. (Check that out - three big items all in one sentence, huh?) Getting in-person knowledge directly from people like Lara Rubbelke and Buck Woody make this an even more attainable task. The list didn’t end there, though - transactional replication base lining with the PerfMon counters, digging further into DMVs, and Centralized Management Server, just to name a few, ended up somewhere in the list, not to mention just trying to communicate everything we can to the rest of our team.

Today the team ran into a database mirroring problem, and I thought almost immediately to throw it out to the folks I’ve met on Twitter. No one has jumped on it, so we’ve headed to Microsoft. However, now I’m in a position for us to help the community - when we dig out of this particular mess, we’ll have people to tell and an avenue to do it.

We’re three days outside of PASS and yet I cannot wait to be at next year’s.

So I had been hanging out in the Twitter world for half a year, but I was certainly more of a lurker than a participant.  This changed when I ran head-long into a few of the Twitter monsters at the SQL PASS Conference this past week.  I realized what my friend Paul had been, well, twittering about for the past months - it’s a useful process of delivering basic information in near real-time.

It’s already been written, and will continue to be in ways I can’t expect to match, about how fantastic of a contribution Twitter had on the PASS event.  However, some of the inefficiencies were wildly apparent to me - I couldn’t quickly or easily see the hashtag conversations from my mobile phone.  Now maybe I was overlooking something, but it just wasn’t happening.  I would switch between my twitter update software and my Opera mini browser, one to update, one to be updated by the community I don’t already follow.  This quickly lead me back to Paul.

Paul (@pwnicholson) and my brother-in-law Garrett (@phragmunkee) set out together to resolve this very issue a few months ago.  Out of their hard work came a re-tweet bot.  Here, like-minded individuals will @reply or direct message to a central user account, and that account, under the control of the bot, will re-tweet it back to all followers.  This way, you don’t have to follow everyone around in your community / event / etc, nor do you need to bounce over to the Twitter search page to monitor the hashtag.

So - apply this retweeter to the PASS conference.  All of those crazy Twitterers that were keeping the hashtag warm would instead be direct messaging (for a cleaner look overall) or @reply to the @SQLPASS account.  Then, all of the people that are lurkers can simply follow @SQLPASS to get updates on all the latest gossip.

But why stop in Seattle?  Why shouldn’t there be a re-tweet account that lives beyond the PASS gathering and keeps all of these new faces connected as they scatter about the country?  And should it just be one - why not several?  @SQLQuestions anyone?  @SQLGossip?  The possibilities are endless.

Paul’s post on the re-tweet bot gives better examples and probably explains it all better than I have.  They haven’t really unlesashed the bot for the open market, but I’ve got connections, so I can get started as soon as I can think of the appropriate first account - maybe it’s @SQLPASS?  The bot is white-listed by twitter and in action for the Nashville Predators fan group, running as @PredFans.  It’s a beautiful sight - reading only the tweets on topics you want from a large group.

Comments, questions, suggestions?  I’ll let everyone know when I get an account up and running…