SQL Dumbass

Fighting dumbasses, one query at a time…

Make Certain It Is DBO

What a nice email today, a developer sent along a request to have some databases migrated and included some details regarding a sql login.

It seems that these databases are part of a vendor application. And it would also seem that this vendor requires this sql account to be a sys admin (of course!)

Well, the developer then goes out of their way at the end of the email to state: “Please also make sure that this sql login is assigned dbo privileges on the databases”.

Sure thing, I would hate to skip that step and have the app cease to function for you. While I am at it, let me add the login to all of the fixed server roles, and not just sys admin, just in case it needs that as well. In the meantime, perhaps you could glance through some documentation regarding database security. After all, you were hired to work with this technology, you might as well have an understanding of what it means to be a sys admin.

Locked out

We have an upgrade to an application being deployed and as part of the upgrade there is a database piece. The app team decides (rightfully) to make certain that no one tries to connect to the database while the upgrade is happening, and they tell their users to not use any apps that connect to the database. So far, so good.

Well, one account continues to open a connection, and no one has any idea about the account. I recognize it and point towards the person on a different team, as it is a service account used by a web pool to connect to the database. Unfortunately, it cannot be turned off anytime soon, as we do not host the servers making the connection. So, what to do?

I decide to put the db in single user mode, kill all connections, and have them run the upgrade. It fails, because the upgrade opens about thirty connections to the database. Why? Good question.

I decide to switch the database to dbo use only, take it out of single user mode, and have them run the upgrade. Before the upgrade starts, that service account connects again. Turns out the account is a sys admin. Why? Good question.

We remove the service account from sys admin and try to get things going again. It reconnects. Since it is not a sys admin, and not a dbo, and not an alias, we are stumped as to how it can connect. You would think that I would know the answer as to how it is getting in, but I don’t. Suddenly, my colleague realizes that we could lock the accounts.

Lock the account! Why did i not think of that! We lock a few accounts, just to be safe, and the upgrade gets underway, only a few hours later than desired.

slash?

We have a sql 2005 database server in mexico.  It has a couple dbs on it.  Backups should have been setup on it, right?  Well, no.  No backups.  No maint plan, no backup to tape.

So I go to the db, and create a maint plan.  Simple… let’s backup to the local drive, with sub folders.  Let’s then go and make the cleanup steps for all the db’s.  Simple.  Schedule it, and viola! we have backups!

Couple days or weeks go by, and I check back with the system.  No backups.  Hum… odd…

I look at the job history, and the maint job has failed each and every time.  It simply says it could not start step 1. 
I look into rights?  Is the sql agent configured correctly, running under a good account? Is the owner of the job correct?  Are the steps in the job setup right?  Linked to each other properly?  Anything…

After a while, i give up on that maint plan, and create a new one.  A simple one.  Backup 1 db only.  It fails too.  Same error. 

I finally turn to google with the error message, and come across someone that had a similar problem.  The job step has a property for the package.  It was missing a slash at the beginning of the value.  If i edited the step, and added the slash, all was well…  backups going, job succeeding, universe was righted.

Who’s the dumbass now?  yeah, it was me.

Memory equals diskspace divided by four

Frantic developer storms into the office wants to know the meaning of the error that has just appeared on their screen is….the error is something similar to : insufficient memory space in database…

Now, without a sqlcode or msgno or anything else I go off to the magic world of google and BOL looking for an explanation nothing, nada, zilch….  which was really to be expected, I mean thats about as generic as an error can get.  After a while the developer comes back and will not accept my answer of this cant be a real database issue and to come see me when they get a real dbms error code.  So, after a bit of prodding I turn on a sql trace for the developer, guess what shows up in the trace.  not 1 single SQL, at this point the developer is looking beaten and retreats to their office.  A few days later I ask what ever happened to that error and you’d never guess that I was pleasantly surprised at the great story that was told. 

After spending at least a combined 20 hours the developer decides to call the vendor of the application for some support.  the vendor has a great explanation, Apparently this is a common problem, the client disk space cant be a number divisible by four or this error occurs, all we have to do is create a few 100mb text files in the temp directory so that the free space isn’t divisible by 4 and the problem will be solved.  at this point all I can ask is “Are You Kidding Me?”  and sure enough, modifying the disk free space fixes the issue, nope no database problem here.

All in a weeks work I suppose!

I geta call from Microsoft today

Gentleman says he is from Microsoft and has a couple questions about Microsoft databases.  He asks if I use the database…. I say yes, and in fact, i am editing a dts as we speak. 

He asks me what versions I have used.  We have both 2000 and 2005.  But nothing yet using 2008, or developers even using a CTP. 

He proceeds to ask me what i like most about the new version, SQL 2007.  No i didn’t typo the 2007.  He asked me that…. I correct him and say 2008.  He makes a crack about how funny the name is, that its 2008, when its being released in 2007… i sigh…

So, now, I am painfully aware of this individuals knowledge of the product…

He continues to ask me weird questions, and i provide apparently confusing answers.

At one point he asks me what my title is.  I respond, DBA.  He tries to guess what the letters stand for… and give a couple good tries, but fails.  As i explain the acronym, my wife cracks up in the background.  Even she knows what it stands for, and was mystified that this person had no idea. 

From there, the call went downhill.  Fun.

The Analyst From Hell and the Never-Ending Meeting

I was sitting in a meeting room along with two application analysts.  They called the meeting for the purpose of reviewing a new application we were migrating towards.  Both the current product and the new product were dependent on SQL databases so I was involved from a standpoint of the DBA.  Both analysts were from the same team and were in the process of a warm hand-off since the two products would be under the responsibility of different analysts from an application standpoint.

We reviewed schedules, database specifications, security items, networking issues, and hardware requirements.  The nature of the database warranted hosting the database on a dedicated database server and the application footprint would reside on its own application server.  We discussed these hardware requirements for at least 30 minutes of the 45 minutes spent in the meeting.  Both analysts have been with the company for years, handling quite a few applications each.  Sure, the one was a little flighty, but she was on her best behavior during the meeting so far.  My opinion of meetings is well noted in my company.  To me they serve no purpose other than to waste time and hone PowerPoint skills.  However this meeting was progressing nicely.

“This is great, there are no questions; we’ll be done with this meeting 15 minutes early!” I thought. 

As I was gathering my notes and coffee mug I stood up and then heard THE QUESTION.  Nothing good has come from a question that starts with the four words I heard uttered by “Ms. Flighty” but they escaped her lips nonetheless.

“I was just wondering,” she voiced up as my hand was on the door knob of the conference room, “What is the difference between a Database Server and an Application Server?”

The meeting ran for another 45 minutes while I went an led a 100-level IT class for Ms. Flighty and her teammate who shot daggers from her eyes for the better part of the extended meeting.  Did I mention that Ms. Flighty was an application analyst for well over 10 years at various companies?