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!