Ian Kirk - SQL DBA

Continuing a bit of a theme in my recent posts, I ran into an item today that caused me to pause and make sure I’d shared it with the rest of the DBA team - something I’m trying to be a little better about doing, as sometimes I’ll just drop my head and go headlong into a task.  Now, not only will I make sure those around me know what I know, I’ll go ahead and make a mental note to share with everyone else, too.

I initially encountered this “problem” at 3am in the middle of a high visibility database re-host where I had to relocate the data files from one server to another using a detach / attach process.  After detaching the database through Management Studio, I remoted into the server to copy the file, only to receive the error “Access is denied. You don’t have permissions or the file is in use.”  I thought to myself, of course I have permissions - I’m a local administrator on this server.  Next, I thought why would the file be in use - I just detached it!

After about 30 minutes of chasing our tails, we discovered that with SQL Server 2005, when you detach a database, the file system object will have its NTFS security changed so that only the Windows account you used to detach the database will have any access to the file.  This becomes an issue for my team because we log into the SQL engine through SSMS using one account, but when using remote desktop, we use a different individual account.

The fix, so long as you are a local administrator, is to right click on the file(s) and alter the security to re-grant the appropriate (likely administrators) Windows group(s) full access to the file.  At that point, you’ll be able to perform the necessary actions like deleting, copying or moving the file(s).

I can’t say I have any idea what happens when you do this using SQL authentication, nor can I speak to whether or not this occurs in SQL Server 2008.

Anyone else experience this one?  Does anyone know about SQL authentication?

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

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!

As I had mentioned earlier, we needed to make sure we understood the internal details of all our SQL servers, so to accomplish that, I set out to collect the data via WMI calls wrapped up in a little C# app.  In an effort to spare someone a bit of time, I’ll outline the particular classes and objects I’m referencing.

From the Win32_ComputerSystem class, I pull:

• Server Manufacturer = “Manufacturer”
• Server Model = “Model”
• Server Name = “Name”
• Number Of Processors = “NumberOfProcessors”
• System Type = “SystemType”
• Physical Memory = “TotalPhysicalMemory”

So to flesh these out a bit - Manufacturer, Model and Name are pretty self explanatory.  With these you can sort out that you’ve got a HP ProLiant DL585 G2 named SANDBOX01.  Now, though, if you’ve got some odd problem with one DL585’s power supply, you can quickly sort out which other boxes you need to look into in case they’ve got a similar problem.

Number of Processors, in Windows 2000 and 2003, doesn’t give you any opportunity to sort out things like HyperThreading or mutli-cores - you get what you can see through task manager.  Does this mean Windows 2008 does give you this information?  I think so, through a second object, but I don’t have any Windows 2008 to test with!

System Type is going to tell you you’ve got an X86 or x64 system.

Physical Memory will be the actual amount of physical memory you’ve got in the server, reported in bytes.  Of course, divide twice by 1024 and you’ve got gigabytes.  I had a bit of fun getting to this particular class / object - I tried using the Win32_OperatingSystem class, but its memory objects weren’t giving me the try amount of memory, leading me to chase a few geese that didn’t exist.

To round things out, I make a call to the Win32_Processor class and pull a few more objects:

• Processor Manufacturer = “Manufacturer”
• Processor Name = “Name”
• Processor Speed = “MaxClockSpeed”

The Manufacturer and Name objects return slightly redundant information.  This is, you’re quite likely going to realize a Xeon processor comes from Intel or that an AMD Opteron is, well, from AMD! Processor Speed will report back for each processor in megahertz, but since each processor is the same, I don’t think its a big deal.

All of this data gets pumped into a table in our metadata repository for us to toy with and report on.  This week I have flipped it around a few different ways and sent it on to the boss for his magic - making the standards get met.

Maybe this is the kind of information somebody else in the shop should be or could be collecting, but we’re the kind of crew that doesn’t prefer to sit and wait for that to come along.  The business is growing at a fast pace and documentation standards have been slow to keep up, so while lots of this information could have been captured on the front end, it wasn’t.  Now that we’ve got questions headed our direction, we figured it was a good time to find those answers.

I wouldn’t have been able to do the majority of this work without having a fine tool like the WMI Code Creator which will allow you to dig through all the WMI classes and objects, as well as create VB.Net, VB Script and C# code to collect the data for you.  This tool, combined with the MSDN Library I was able to get some good information to start testing with.

Oh - and I can’t forget thanking my coworker (who shall remain nameless) for all the C# development help!  I’ve had plenty of annoying questions, and they’ve handled them all with a smile.  If anyone wants to see the C# code, let me know and I can wrap it up for you… after I’ve made it prettier.

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!

Chris Shaw posted a SQL Quiz, setting it up as a game of tag, and I got my tag from SQL Fool (@SQLFool on Twitter) when she answered the question -“What are the largest challenges that you have faced in your career and how did you overcome those?”

I got tagged while I was standing in the middle of the Magic Kingdom in Florida with my family.  I briefly considered answering the call, but realized I’d better focus on the Mouse first.  I’ve been back for a week and I’ve still not gotten my head back 100%, so the reply just hasn’t come up until now.

Database Administration - isn’t that a little twisted?

I think the first challenge simply has to be my choice to take this career path, and the process of getting to where I am today.

From the age of 13, I knew for absolute certain that I would be a police officer.  I was consumed by the culture and the idea.  I bought a radio scanner and listened to their communication.  I played every one of the Police Quest games on my computer.  I even got a four year Criminal Justice degree because my parents told me I couldn’t skip college to take my “dream job.”

So many seeds of my current path had been sewn during that time without my quite realizing it.  First, I was on or around a computer all the time.  My grandfather had my parents drive to his office and pick up the Apple IIe each weekend for us to have around the house.  I was five years old and from this point forward I was surrounded by computer technology.

My father ran an IT based small business from home when he was laid off from his engineering job.  My grandfather and uncle amassed a collection of both Apple and IBM machines.  I went to BASIC programming camp at least one summer.  Yet the entire time I was growing up, I actively rebuffed people’s ideas that I would take a job in IT.  I was going to be a cop and that was that.

Fast forwarding many years, I find myself in the middle of the 6 month long police academy in my hometown - my life’s goal achieved - and I realize that it isn’t anything like what I had dreamed of.  While I had thoroughly enjoyed the people I worked with as a civilian there (doing, what else, Access database work), the daily grind of an officer was looking less appealing.  The people were different here, and I looked inside myself to find that this was not my dream job.

I pulled out of the academy, an immensely difficult thing for me to admit, and I went back to my job at the mall - selling computer software and games - that I had done for more than 8 years prior to now.  I dove into the certification process, grabbing up every book I could find for an alphabet soup of certifications.  I self studied my way to an A+, I-Net+, Server+, MCSE, MCSA, and finally, the last one, a MCDBA.  This was over a six month period of time where I balanced moving in with my younger sister, doing the mall gig and building a solution in MS Access for a family friend’s business.

I knew going in that despite a litany of certifications, I wasn’t going to get in anyplace with some experience.   Luck, though, was on my side.  I got a call one day at work about a 3 week contract with a local manufacturer - their DBA was going on vacation and they needed someone to cover for a week.  To this day, I have no idea how I got the call, but I took it.  I worked with their guy for a week, learning the environment as much as possible, and then it was mine for a week.  I had backup from non-SQL DBAs, but it was basically mine.  The third week, he came back and we reviewed what had gone on.  They called me two weeks later and asked me to do another three weeks so they could speed up a project - I’d made an impression.

I can’t say that I was suddenly on board with this place, but I finally had my foot in doors - I finally had some experience.  From there I landed a gig an old friends from my mall job… which leads me to the next challenge.

Repl-what?

Several months into this gig with a small business where we did bench work for a few other computers, they decided to take on developing software for the retail stores of our major customer.  It was going to be backed out of SQL Server, and I was the DBA.  Long story short - we decided that we’d drop a SQL Server into each of the 80 stores and replicate, over the public network, the data back to the home office.

Yeah - the public network.  Cost savings.  And the “cost savings” continued into the fact that we didn’t get static IP addresses on the end points in the retail stores.  I fought for weeks to sort out merge replication, passing out ID seeds, and working with FTP replication in SQL 2000.  We tested in the “lab” and when the day came, we all drove out to our first stores to set everything up.

There were problems - the app wasn’t quite right, our store setup routine was brand new and clunky, and then the replication just wasn’t working.  At this point, remember, I’m the guy.  We’re 10 hours from home, sitting in a retail store for 14 hours, a few days in a row, while the app code is tweaked and I hammer my head against the desk over replication.

Eventually we pulled the plug on the replication.  It just wasn’t going to happen in the time frame we had, and we had to start moving forward.  The first three stores were allotted more time than the others, and that time was running out.  We sorted out a routine of seeding all the tables’ ID columns with the store ID and decided to figure out how to get the data centralized later.

All I know is that I was in the hot seat, pushing the very edge of my abilities and I had to make a nasty decision to say, I’m sorry - I can’t do what we wanted to do, but we can do [X] instead.  I was lucky and my boss was understanding.  She was technically oriented, understood the situation, and we moved on.

Once we got out of those first stores, my buddy Paul and I ended up working (or driving) for nearly 40 hours straight.  I will never forget that whole process, and the lessons learned.

Into the shark tank

Eventually the retail business worked on my nerves, and I found a new job out of town.  This was a relative start up and they had big contracts in the pipe.  I was part of a two man team preparing to handle a good bit of health care data for some big customers.  My partner was an experienced DBA and I felt very comfortable.  I learned a tremendous amount from him about the day-to-day maintenance of servers.

Being a start-up with big ideas, things eventually unwound and I was in the market.  Luck shone upon me once again and I had stumbled into a contact through playing ice hockey.  This guy, a substitute player on my team, was a DBA manager in town and he was looking for somebody.  I’d turned him down once, with the promise of the start-up still gleaming in my eye, but when I came back a couple of months later, he was still in the market.

I joined up, despite hearing stories of long hours, and I quickly realized I was going to get run over by six runaway trains if I didn’t get moving fast.  I dove in, tearing through the ticket queue, learning about the hundreds of SQL instances in the environment, and generally flying low, but steady while I learned.

Eventually the team shrank, we changed directors and even managers, but by then, I realized I’d positioned myself pretty well.  I suddenly found myself full of institutional knowledge - people on the team would ask about X job on Y server and I could tell them who requested, why they wanted it, what it did and when it ran.  I knew the outage windows for dozens of servers by heart.

I worked really hard to be outgoing, friendly and approachable - something I’d wanted to do from the very start of working in IT.  I never wanted to be the guy to push the user away from the mouse and do the work.  This paid off - I got noticed, and I got added to critical support teams with huge visibility.

At this point, I’m a Senior DBA at this job and I’m working hard to be the mentor that the former teammates were to me.  We pull some long weeks, but I’m working with technology and processes that I just don’t see being used around me.  I’ll trade a few extra hours at work for the experience I’m gaining.

While we were discussing his interview process, I told the former manger, who is now an architect with the company, that I realized I never could have passed his interview.  If I had walked in and faced him, I wouldn’t have seen past the interview room.  His reply?  “I have a greater faith in potential than I do experience.  I knew from talking with you that you had a great potential and I wanted that on my team.”

OK… I was a little late to the party, and I was a lot long winded.  Thanks to all that, I’m not sure that there is anybody left to tag… so - if you’ve read this far and haven’t been tagged, then I tag YOU.

(Yes, I’m on vacation.  No, I’m not writing this while I’m on vacation - I scheduled it in advance.)

Amongst plenty of other things, Kristine and I spent the week trying to re-create the errors we saw in our production environment.  (Giving credit, where credit is due - this was mostly Kristine’s work)  To our dismay, we haven’t been able to reproduce the errors!

According to Microsoft Support and to the previously mentioned forum post, the culprit is the sp_getapplock / sp_releaseapplock being used within the database.  However, we’ve identified the two stored procedures in the production database that make this call, and we’ve executed them multiple times.  The lock procedure have to be executed within a transaction, so we left a few transaction open, and ran our tests to see if this might help - not at all.

We’ve tried with an empty database, we’ve tried with a restore of the original database.  We’ve tried with manual tests via SSMS and we’ve tried to create the error with people using the application - absolutely no combination of tricks has given us the results we want / expect.  At this point we’re stuck wondering if CU10 will or will not fix our problem.

While I’m away, maybe Kristine will find the magic test that gives us a chance to confirm the fix as well as test the application and then we’ll be ready to move on the patch before the holidays are upon us.  Maybe.

So a week or so ago one of my coworkers stumbled into the fact that we had a database mirroring session that was broken.  We won’t discuss why someone had to stumble into it - that’s being resolved elsewhere.

Anyway, this session had broken, but the log was still building, so it wasn’t the end of the world - let’s just start it back up.  No - we couldn’t be so lucky.  Instead, we found on the target side a log full of minidumps and errors.  Now, for the Googlers (which included me and my teammate!), the errors:
Error: 17066, Severity: 16, State: 1
SQL Server Assertion: File: <loglock.cpp>, line=818 Failed Assertion = 'result == LCK_OK'. This error may be timing-related.
If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
Error: 3624, Severity: 20, State: 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption.
To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Error: 1454, Severity: 16, State: 1
While acting as a mirroring partner for database '<DBName>', server instance '<ServerInstanceName> encountered error 3624, status 1, severity 20.
Database mirroring will be suspended.  Try to resolve the error and resume mirroring.

Of course, with a database mirroring target, you can’t fire of a DBCC CHECKDB command - it’s a database in recovery!  Further, as I mentioned earlier, simply restarting database mirroring didn’t change a thing.

Amusingly, this was about 3 days after we’d returned from Seattle, so my first thought was to send out a plea for help from the twitter gang, but after no response, we turned to calling Microsoft.  Actually, most of the work from this point goes to my co-worker Kristine.  She spoke to support, passed the mini dumps and got confirmation of what we’d found in the one reasonable result of our Google search - the use of “sp_releaseapplock” in the source database.

Our source database is a third-party, shrinkwrap peice of sofware for Workforce Management named Aspect.  In a recent upgrade, one or more of the stored procedures for this applicaiton included the use of this particular command.  In turn, that command wrecked our mirroring process.

Just like the Google result above, Microsoft has suggested that CU8 will resolve the issue, but we were already looking for CU10 for this same server, for a different problem, so we’re headed down the route of testing CU10.  Once we’ve confirmed we can make the mirror break happen in test, we’ll see if CU10 is the fix, and I’ll post an update.

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.