Every morning when I leave the house I get in the car and the first thing I do is throw my jellied toast face down in the floor of my car. Why not, that’s where it’s going to end up anyway when the asshole in front of me slams on his breaks. Now at least it doens’t piss me off when it happens. Then when I get to work the first thing I do is run some kind of wild query that fills up all the memory and CPU and locks everyone out of the system. Why not, that’s what’s going to happen as soon as my favorite report writer logs in.

Some days it just doesn’t pay to get out of bed and go through the hassle. Then again, if you’ve done what you should as a DBA, it shouldn’t be that bad. Hopefully you’ve setup things in your DB that keep things from getting too out of hand. Hopefully you’ve been able to teach your report writer some basic dos and don’ts (sp?).
But what if you’re in a new place? Can you still be effective? Of course you can. Again, if you’re a good DBA you’ve collected a nice gaggle of scripts that you take from place to place. Years ago when I was just getting started, I didn’t get that. I always thought, why be lazy, just write the damn scripts when you need them. But that’s the wrong attitude. It’s not laziness, it’s practicality. There’s just no reason to work out that logic every time.

So if you follow some of your own best practices and set yourself up for success, maybe you can withstand the bad times. But there’s nothing you can do about traffic, so I guess you’ll have to keep throwing your toast on the floor. Life goes on.

For those of you who follow my blog, you know that I’ve used LiteSpeed for many years now. And one of the things that’s always bugged me is the lousy way they run their upgrades. To upgrade LS typically consists of having to do some version of manually deleting the current repository tables and then running the install program. And this is after uninstalling your previous version manually as well. And if you want to save your data in your repo tables you have to create new ones and copy the data over, or just rename the old tables. And it still isn’t that easy because of the relationships. If you just rename the tables you have to rename the relationships too or the new install will fail. And you have to do this for every box you own. What a pain in the ass!!

Well, I have a problem with LS not backing up one of my DBs even though it’s been working just fine for almost 2yrs. So I just upgraded from v.4.7 to the latest 4.8 (yes, I know v.5 is coming out soon, but I can’t wait). And let me tell you this… it looks like they finally got these upgrade problems fixed. My upgrade went smooth. I didn’t have to manually uninstall the old version, I didn’t have to play my usual shell game with the repo tables, and I didn’t have to burp the service. Life is good.

Now, I’ll have to see if it actually fixed my problem. But I’m happy to be able to report something good about LS for the first time in a long time. They’ve mostly been adding features, and very slowly at that, and haven’t been really concerned with our actual problems. Maybe this is a turning point.

Anyway, good job LS guys.

One thing that’s as varied as ways to code is how to manage the code itself. Or I guess I should say architect instead of manage, but it all comes down to management.

The 2 major ways are to componentize and to not. And by componentize, I mean taking all the individual components and turning them into small independent chunks that can be called from many sources. A good example is taking a date calculator routine in your SPs and turning it into a function so the SPs just call it instead.
So those are the 2 ways. You either put it inline with the code or you make it into a component and call it from all the modules you need.

Of course, I’m mainly talking about SQL code here, but it really applies to any kind of code I suppose.

It’s easy to see the advantages of developing a component solution. This is the DB equivalent of COM, right? But what’s not easy to see is what it does for you (or to you) support-wise. What this solution can do is put you in a new form of .dll hell from the old days.

Let’s take a simple example…
Let’s say that you have sn SP in prod that’s giving you problems and you want to track down where the degraded performance is coming from. So you open up the SP and start looking at the code. You see that a major component of it is an external SP call. OK, so you open up that SP. Now you see that that SP calls 3 other SPs. And each one of them is calling more SPs. And so on and so on. This is the definition of spaghetti code isn’t it?

I’m actually in the middle of doing this very thing right now. I wanted to stop and blog about it while I was still pissed off. I’ve been doing this for an hour now and I’m not any closer to a solution than I was when I started. So you can see that while going the COM route can be helpful, you can take it too far. And that’s really what happens, isn’t it? Devs take a good idea and drive it into the ground until it’s so hard to manage it’s not even worth having it anymore.

I’ve been trying to come up with some guidelines for this and it’s tough. But roughly, I like to say that if several SPs use the same code, or are calculating the same thing, it’s ok to pull out into COM. The same goes with code that gets changed a lot. If you’ve got code that changes often and is used in a lot of SPs, by all means put it into it’s own SP so you only have one place to change it.

Anyway, I guess I’ll get back to my plate of spaghetti now.

This post is dedicated to all those field DBAs who like to call up the prod DBAs and tell them how busy the server is based on the number of spids or short-term blocks returned by sp_who(2).

See this is the hidden cost of generic logins that have too many rights. Everyone on the planet can read BOL and try to interpret the results. And whatever they mark in their heads as being the sign of a server being too busy is what they’re going to call you with. Our users have 2 criteria for a busy server. The number of spids (active or inactive), and how many short-term blocks they see.

Of course I used to try to explain to them that you could bring the server down with a single spid so the number doesn’t matter… and that blocks are fine as long as they don’t persist. Since I’ve been here for quite a while though, and none of them have gotten the hint yet, I usually just thank them for letting us know and that we’ll get right on it.

One of my favorite analogies used to be that judging the server on the amount of spids is like loading your car up with people and declaring that traffic is really bad today. Somehow that still didn’t get the point across.

It’s almost never fun to lose a DBA, but it’s a fact of life. People leave jobs, and sometimes jobs leave people. This is another reason why it’s really important to not tax your DBAs too heavily. If you’ve got 2 DBAs and they’re both working like dogs, what happens when you lose one? I’ll tell you what happens… deadlines start to slip, backups start failing and don’t get looked into, maint starts getting behind, security gets relaxed, etc. You want your DBA to have time to do his job and be able to pick up some slack when you lose someone. And it could be quite a while before you get a replacement. Good DBAs are really hard to find and you don’t want someone to just warm the seat.
I talked about this recently in my IW blog. Of course, this really only goes for production DBAs, right? I mean, you can work your devs as much as you want. They’ll never get a call in the middle of the night because the server’s down or because a package failed. So again, DBAs are insurance policies. We’re kinda like a clustered server. You don’t use the inactive node. It just sits there waiting for something to happen to the primary node. It seems a terrible waste and managers hate spending that money for a box that just sits there. And while DBAs aren’t quite that useless, we really should be used in the right way. So it really is just like a multi-node cluster. You never run the primaries at full capacity because one day something will happen and one box will have to take on its workload and one of the downed nodes. So if they’re all running at 100%, they can’t failover and resume work. So you run them at 50%… give or take, right?

So again, let your prod people do their prod jobs and don’t put them on too many actual projects. Afterall, that’s what prod means.

And yeah, we’re losing our other DBA so I’m all alone now. We’ll see how it turns out.

OK, I’m also changing this in the original post, but in case you don’t think to go back and check.
When I sent my admin password solution to a buddy at MS, he tried it and it didn’t work. After a fairly short discovery, I discovered that it worked for me because I had a drive mapped to the admin share on my DC. So PSExec was using that IPC to connect. So unless you’re lucky enough to have something like that, you prob won’t be able to use this solution. However, it’s a good backdoor so it may be a good idea just to setup a mapped share on your box anyway just in case something happens and you get shut out of your DC.

On that note. This is a really excellent reason why you never want to run SQL under an admin acount and especially not under a domain admin acct. I’ve seen that more than I care to and it always comes out bad eventually. It only takes a very basic knowledge of SQL to discover that someone with regular user rights can setup a SQL job to promote their user acct to admin because the job will run under the context of the agent acct at the OS level. And if you’re running your services under an admin acct they’ll have full rights to do whatever they like. And if you’re running it under a domain admin acct, then they can promote themselves to domain admin pretty easily by running a .vbs or prob even powershell. Pretty much any scripting language will prob do, huh…
And that goes for running SQL on a DC under local system. That’s the same as domain admin as far as the DC is concerned. So be smart and run your SQL accts with non-admin accts and just remove that from the equation. There are enough ways for internal and external hackers to elevate rights. Don’t help them.

Last week I somehow forgot the domain admin acct in my lab. I tried for 3 days to remember it and I finally came to the realization that I just wasn’t going to. So I started looking around on the web for different things I could do. I wasn’t really in a hurry cause my domain was running just fine.

I found many methods for changing the local admin acct, but not many for changing the domain admin acct in a windows 2003 domain controller. I did find this method here.

Anyway, I checked with an SE friend of mine on the windows support team at MS and he said that was a good method and told me to run with that one. Not that I minded, but I just didn’t want to bring down my DC and take the time to copy all that stuff to floppy, etc. So I kept looking.

I finally had an idea that I just had to share with everybody. It’s so simple it’s scary.
I used the SysInternals tool psexec.exe. What it does is run programs remotely on the box of your choosing. The problem of course, is that you have to make an admin connection to that box, and if you could do that, you wouldn’t have to use psexec to reset the password. So I looked at the help file for psexec and found a parameter that just amazed me. -s is its name.
What that does is tells psexec to connect with the local system acct of the remote box. And since the local system acct has full admin rights, you have the rights you need to change the admin password.

So at a command prompt go to the dir where you have psexec and type the following command:
psexec.exe -s \\machinename cmd
That tells you to bring up a command shell on the remote box and log in as the machine’s local system acct. So now you’ve got a shell open on your box just like you were standing in front of the console on the remote box. Now you just type the command for changing the admin password: net user administrator newpassword

That’s it. It’s simple, elegant, beautiful, and it really saved my ass.
I hope this helps someone else.

Just for grins, here’s a Camtasia I made of the process just to make it even easier. Click here

UPDATE:
Here’s an update to my original post. I sent this solution to a friend at MS and he found that it didn’t work for him. After a little investigation, I discovered that it worked for me because I had the admin share on my DC mapped on my box and PSExec was using that IPC. You can read more about that here.

Wow… I really don’t get a chance to write in this blog very often because my company has been blocking the address. For some reason though, they just opened it up so I’ll be writing here a lot more. So lookout, I’m back.

Man, you ever have one of those customers who gives you Tourette’s for like a week? You know the one I mean… he calls you up with something ridiculous he’s promised a customer and it falls to you to get it done, only it’s a month-long task that he’s promised in 12 days. Or the one who keeps getting you out of bed because he won’t quit monkeying around in the system and he keeps deleting the wrong data and you have to recover every 3 days.
And what do you do when that happens? You walk around for a week muttering obsenities to yourself. You find yourself telling him off in the shower. You see his face in stop signs. You hear his voice asking for a restore every night just as you fall asleep. Then you wake up and mutter more obscenities to yourself. He’s like a bad song. Those of you who are parents, you know what I’m talking about. How often have you tried to not hear the Oobi theme song in your head, or that god-awful map song from Dora?

Oddly enough, I just finished a bout of Tourette’s myself. I’m not going to go into the details, but it’s something that got me muttering for over a week. Now I’m feeling much better and back to the good ole days falling asleep to the Oobi theme.

I think it should be easier to find the service packs on the SQL site. There used to be a link on the front page, and then it moved to the download page. Now you have to search for it and it does eventually take you to the download page, but it’s not easy to find like it used to be.

I think I actually found it this time by accident looking at an article for SP hotfixes… and that had a link to the SP2 download page. Come on MS, make it easy for us to find these things, and let us know the last time the SP was refreshed.

OK, so here’s the SP2 page for Yukon. I found it from the SQL Support page here.