Ian Kirk - SQL DBA

I just wanted to share a little fun fact that we stumbled head-long into late last week, in the midst of a bumper crop of replication alarms.

SQL Server 2005 added the “Initialize from backup” option for Transactional Replication and only recently did we finally dive in and use it.  We had encountered a situation where it was the entire database being replicated (one table…!) and we didn’t want to schedule a snapshot to get things back in line.  Instead, we decided to leverage this “new” technology we’d been sitting on for more than a year.

I won’t get into all the details, because so many others have already, but I will point out that this is an option you set at the publication level.  Once the option is turned on, the distribution database mantains all of the replication commands, based on the LSN, from the last backup.  (I haven’t tested to see if this is a full backup or transaction log backup - we were operating in simple mode).

This becomes a bit of an issue when you’re inserting and/or deleting several million records and your distribution database is sitting on one of your smaller mount points.  Despite running the distribution cleanup command over and over, with an ever shrinking max retention number, our ms_replcommands table grew wildly out of our control as more and more transaction were pumped in.  Eventually we stemmed to flow of transactions by stopping the log reader agent while we researched the problem.

Basically the distributor was waiting for the next subscriber to come along, saying I used X backup and I need to catch up - can you tell me what I’ve missed?  Since we weren’t going to have another subscriber come along, we really should have flipped that switch to the off position after we’d finished the initialization process - its a completely online change.  As soon as we turned off that option, the distribution clean up ran and swept all but the most recent changes out the door.

That’s not one they seem to mention, so I figured I’d let it rattle around in the Google-bin.

For reference, here is the Microsoft Books Online link:

• How to: Initialize a Transactional Subscriber from a Backup

The past few weeks have been a whirlwind of activity, both professional and personal.  In that whirlwind, I completely left the blog behind, languishing in inactivity.

So in the final days of 2008, we had plenty of scrambling within the company to meet deadlines before the big production freeze.  This lead to plenty of late night, last minute code reviews and releases.  Beyond that, we had several long-running critical incidents crop up - one associated with the MS CRM app we’re using, so not exactly a great place to see slowdowns.

Things kept quiet, though, on the final day of the year and I was able to finish my on-call cycle with a quiet night on the couch.

As 2009 starts, pretty official today, what with it being the first Monday, I’ve got plenty of things to stare down.

Biggest on the plate is a SQL 2008 cluster - 5 nodes using Veritas Clustering technology.  The business wants to see data from all over the enterprise in several different places as quickly as they can get it.  As such, we’re going to stand up a very sizable set of replication distributors so we can stop worrying about it.  Right now a lot of the work is done through self-distributors, but that’s only because one of the remote distributors is a Pentium III server.  Let’s just say that isn’t exactly speed demon these days.

While I’m waiting for all the infrastructure to get in place, I’ll be testing VCS with SQL 2008 in the lab.  I don’t expect much to happen there - the VCS SQL Agent doesn’t seem to be the most complex piece of work, but maybe I’m overlooking something.

I’m just polishing off a little C# applet to dig through the enterprise and collect physical configuration data about our servers.  Things like physical memory, vendor, etc.  This way we can identify the servers that aren’t meeting our standard and work toward bringing them in line.  We realized the other day that one of our clusters is operating with a lot less RAM than we thought… oops!

We’ve got a big datacenter move coming along, so I’ll play a role in all that planning.  We’ve got a server exhibiting CPU pressure issues, but I’ve deferred a lot of that to my teammates, since I think I’ve got plenty of things hanging around and I can’t dedicate myself appropriately.  Of course we still need to sort out this database mirroring problem - that hasn’t gone anywhere for weeks and we’ve just been running with log shipping as the alternative.

The boss is in town this week, too, so we’ll all get a little time to get the feel of things from him, which is nice.

I’m not really one for resolutions, but I do know that I’m going to re-dedicate myself to the blog, keeping it updated better.

Finally, for a bit of fun, I’ll share a photo from my other new thing for 2009 - the Flickr 365 project, a photo a day gig.  This was taken with the theme of “What I do at work when no one is looking” but I figured, since its from work, then it makes some sense to be here!

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.