Category Archives: Admin

Admin

Whatever, I’m done.

19 Comments

Well, since I hear everyone is enjoying these today, I just finished my last phone screening and here are the nuggets I’ve discovered this time.  Man you just learn so much interviewing.  If any of you really cared for me though you’d shoot me right in the ass.

Q:  What’s a bookmark lookup? (his resume says he’s a query tuning expert)

A:  If there’s no PK on the table, SQL will keep a bookmark table that tells it what the PK should be and it uses that to lookup the data.

Q:  Say you have a prod server with the DB on D:\ and you need to restore it to a dev server that has no D:\.  But it does have an E:\… how would you restore that DB to E:\ instead of D:\?

A:  You have to restore it to the full recovery mode.

Q:  What is IsAlive vs LooksAlive? (he’s also a big clustering guy)

A:  LooksAlive mode when you want to hide the cluster from hackers so you put it in LooksAlive mode and IsAlive mode is when you have an internal box and you want to allow it to be seen from the network. (I’m so far beyond speechless I don’t even know what to do with myself.  Now I know why VanGough ripped off his ear.)

Q:  When would you use log shipping vs mirroring?

A:  Log shipping is what you use when you want to run it in full mode and mirroring is the same thing only you do it in simple mode…

Somebody make it stop!! The voices are getting so LOUD!!!

Admin

Somebody kill me.

12 Comments

I just had an initial phone screen with a guy who wants the DBA gig I’ve got open.  Here are the last 2 questions I asked him with his answers.

Q:  Say you have a prod server with the DB on D:\ and you need to restore it to a dev server that has no D:\.  But it does have an E:\… how would you restore that DB to E:\ instead of D:\?

A:  FTP.

Q:  Can you name 2 server-level security roles?

A:  DBA and Dev.

Sorry I asked.  I’ll just be over here.

Admin

Who’s afraid of cursors?

1 Comment

Ok, so one of my last posts talked about using a cursor for something and I came upon some criticism and/or some wincing about the fact that I used that horrible word.  However, cursors like anything have gotten a bad rap (xp_cmdshell is another).  People, there’s nothing wrong with using cursors as long as it’s what’s actually needed.  The problem comes with frontend coders writing cursors for everything and severely abusing this feature. 

But there’s nothing inherently wrong with them… esp for admin tasks.  There are so many legitimate uses for cursors I can’t even come close to naming them all, but here are a few…

Doing something to every DB on a server.

Doing something to every schema in a DB.

Doing something to every table in a schema.

Doing something to every user/login acct (like running changeUsersLogin to correct a ghosted user acct issue).

These are all perfectly legitimate uses of a cursor, whether it’s T-SQL or powershell.  So let’s get ove rthis hangup we’ve got against both the concept and the word.  There’s nothing wrong with it as long as you do it right.  Afterall, what is a cursor anyway?  It’s a way to store a list of values so you can perform an action on each one of them.  And the alternative is to hardcode each one of those lines yourself or use the GUI for everything and we all know that’s completely unacceptable. 

And in powershell everything you do is a cursor… even things that don’t need them.  Of course, they call it a foreach loop, but that’s just a more acceptable name for a cursor isn’t it?  It’s more specific… foreach one of these things, I want to do this…  hell, even almost every backup routine I’ve seen in the shops I’ve been in, and on the web cursor through the DBs on a server and back them up.

So deciding to not use cursors (or at least wince at them) because they get misused by some is like refusing to use a hammer on a nail because of someone you saw trying to open a banana with one.  It wasn’t the right tool for the job so it messed things up.  But it IS the right tool for a nail.

Admin

Should you be in Simple or Full Recovery mode?

5 Comments

Lately I’ve been bitching about vendors in my Marklar blog, but I thought I’d switch things up a bit this time and do some complaining here instead.  So this is a rough conversation I had with a vendor like 2 days ago.

Me:  The prod DB is twice the size of the archive DB.

Vendor:  That’s because the prod DB is in simple mode.

Me:  I don’t understand.

Vendor:  Well, DBs have 2 modes you can use to control when the log files get truncated.  Simple mode is… (interrupted)

Me:  Yeah, I know what the recovery models are.  I’m a DBA.  What I want to know is how the recovery model being in simple means that the prod DB is larger than the archive DB.

Vendor:  Well that’s what I was trying to explain to you.  When your DB is in full mode,  you can actually control when your log is truncated and you’ve got finer control over the size so you could go in there right now and truncate it.

Me:  But it’s in simple mode now so it truncates whenever it checkpoints.

Vendor:  EXACTLY.  So you have no control over that and that’s why prod is bigger.

Me:  That makes no sense.  Simple mode keeps the log truncated so you don’t have to control it.  It’s always truncating it.  So how does that make the DB bigger?

Vendor:  You don’t understand what’s going on here.

Me:  No, actually I don’t.  Why don’t you educate me.

Vendor:  Well like you said, you’re a DBA, so you should already know this stuff, but I’ll be happy to go over it again.

Me:  (laughs a little) Yeah, I’d love for you to do that.  I didn’t get it all written down last time.  Go ahead, I’m taking notes.

Vendor:  When you’re in full mode, you can take log backups and that will truncate your logs.  So you can control how big your logs get, and therefore your DB, by taking log backups at certain times.  You don’t have that option with simple mode.  You can’t take log backups, so you can’t tell it to truncate the logs.

Me:  I see… so if you’re in simple mode how does the log get truncated?

Vendor:  It truncates it automatically.

Me:  Ok, so if it truncates it automatically, when does it do it?  Is it on a schedule or what?

Vendor:  I really don’t know, I just know you can’t tell it to truncate it.

Me:  It truncates it whenever the DB writes a checkpoint.  So basically after every transaction.

Vendor:  Ok.

Me:  So since it’s almost always truncating, how does that make my prod DB bigger than the archive?

Vendor:  You still don’t get it?

Me:  Nope.

Vendor:  Well, we’ve got to get through this, maybe we’ll have time to get into it later.  For now, I’d like you to go ahead and have you switch it to full mode so we can get the size of the prod DB down.

Me:  No, I’m not going to do that.  That’s just stupid and it makes no sense.

Vendor:  Well, we can’t go any further until you do. 

Me:  Why not?

Vendor:  Because the prod DB is too big and this operation will take a long time. 

Me:  And how are the updates we’re doing on these tables effected by the size of the log.

Vendor:  Because it’s a lot more data to move.

Me:  But we’re not moving any data.  We’re only updating data.

Vendor:  Well, like I said, we don’t have time to get into all of this right now.  I don’t have time to teach you how all this works.  Just go ahead and switch to full mode and you’ll see what I mean.

Me:  No I won’t see what you mean because it’s stupid and it only shows you have no idea what you’re doing.  So you let me manage the space and my backups, and you worry about the updates.  And this is only a 4GB database anyway so it’s not like anything we do will take hours.  We’re only talking about the difference between a few seconds.  And there’s no way how much data is in the log will have any effect on how fast updates are.  So you just do your thing and leave me to do my thing.

OK, so that’s what I did the other night.  And it seems like I’m having these types of talks more and more with vendors.  It’s incredible isn’t it? 

Anyway, I hope you guys enjoyed this.  And for the record, simple mode truncates the log whenever the DB checkpoints so it’s always truncated.  And full mode needs a log backup to be able to truncate.  In case some of you were confused as to why this was a dumb conversation.  It’s actually dumb on a number of levels.

Admin

You’re the center of my universe

2 Comments

It will never cease to amaze me how people can be so narrow in focus.  It never even crosses their minds most of the time that someone who supports them may not be working right beside them all day long and may not be involved in their day to day activities enough to pick up on all their slang and terms.

So when I get a group of users like that I always have to laugh a little as I set them straight.  Yes, I’m the DBA for this company.  Yes I do backups for the servers in my control.  And yes I’m the one you need to call whenever you need anything at all SQL related.

However…

NO — I don’t have all your servernames memorized.

NO — I don’t know how big your DB is off the top of my head.

NO — I can’t tell you what rights everyone in your group has.

NO — Sending me a ticket that says “export my favorite tables” will not get you what you want.

NO — I don’t remember that conversation you had with the vendor about what we need to do to upgrade your DB.

NO — I didn’t realize that your app was renamed 2yrs before I got here and half of your group still refers to it as the old one.

So when you guys out there (and you know who you are) send requests to your DBA team,  know that they’re here to help you but you have to know that many times they’ve got several hundred servers to monitor and they can’t always keep your info on the top of their heads.  So at the very least whenever you send them a request, include the ServerName, DBName, etc with it.  They need to know what you’re talking about.

Admin

Have you had a chance to do this?

3 Comments

Users can really stick it to you sometimes and today I got caught on a call with a user who I suppose was trying to stick it to me somehow. There were about 9 of us on the call and she asked me:
Have you had a chance to look at the old backup files yet?

And I was like, what old backup files?
She said, I wanted you to go through and make sure there aren’t any old backup files sitting out there from 3yrs ago taking up space. We’ll want all that stuff cleaned up before we switch servers.

I said, well was I supposed to do that? Is that something you’ve asked me to do?
She said, no, I was just wondering if you’ve had a chance to do it yet.
I said, yeah I’ve had many chances, but since nobody has asked me to then I haven’t done it. I’ve also had many chances to eat bananas and grind up cherries with a sharpie, but nobody’s asked me to do that either.
Would you like me to go look for extra backup files?

Yes, she said.

I said, ok then why didn’t you just ask me to instead of trying to make it look like I’m not doing something I’m supposed to do? I’ve had many chances to do many things including delete all the data in your system, but since nobody’s asked me to, I haven’t done that.

Well, she said, I was just trying to find out if you’ve had a chance to do that yet.
Yes, I get that, but you haven’t asked me to do it yet so how could I have had a chance? Now you in here trying to make it look like you’ve asked me several times and I’ve just been slacking.

OK, so that’s basically how it went. Dealing with users sometimes can really be hard… especially when they forget a key component and then try to cover it up like it’s been on your tasklist forever. And something this trivial is just ridiculous. It takes 5mins to check for old backups and admitting that you forgot until now is no big deal. Sometimes we spend more time on CYA than actual work.

Admin

When is a ticket not a ticket?

I get this from time to time where a user will open a helpdesk ticket and assign it to me or my team and it has nothing to do with anything. Apparently it’s rather difficult to get users to understand what a ticketing system is all about.

Let’s take the ticket I got this morning. Here’s the exact wording pasted directly from the ticket itself:

I have a question about SQL.

Typically whenever I get a ticket like this I close the ticket immediately with the response:
OK.

For some reason today though I was feeling more mellow than usual so I wrote the user to inform him what ticketing systems are for and why that wasn’t really an appropriate ticket. Then I asked him what his question was and that if he really felt the need to put something like that in a ticket, to at least print the question with it. Of course I’ve heard nothing back from him. I did close the ticket however.

Like I said above… I get this from time to time at my various gigs and it’s really hard for someone like me to not be a complete smartass about it. I got one at my last gig that said:
I looked up what you were telling me about indexes and you were right.

Now, while I suppose I have no real problem with you creating a ticket to officially document that I was right about something, don’t assign it to me. Assign it to my boss.
I just wanted to officially document that Sean was right about one of the basic principles of indexes that he discussed with me. He should not only get a raise, but should have his pick of your daughters.

So now that I’ve ranted a little, here’s how to correct the situation. And this is to you users out there… tickets are for verbs… stuff you actually want us to do for you. You’re free to come by or send a regular email if you want to praise us for being right, or to ask a question because we’ve always been right in the past. But unless you have something for us to actually do, keep it out of the helpdesk system.

Admin

Being the Boss is Hard Sometimes

7 Comments

Sometimes the difference between being a boss and a regular DBA shines through in the worst way. 

I’m interviewing a few guys right now for a position I have open on my team and I’ll say I’m getting all walks of life.

I’m getting everything from guys with lots of experience to those just wanting to get started.  This post is really about th guys with lots of experience though.  More often than not what I see in guys with lots of experience is that they’ve never bothered really learning DBs very well.  I spend most of the time I’m talking to them trying to figure out questions that are low enough for them to be able to answer.  One guy in particular comes to mind.  I don’t know if he reads my blog or not, but I guess we’ll find out huh?

This is really a unique position because I’ve interviewed him several times in the past few years.  In fact, every time I have an opening he applies.  The problem is that he’s always demonstrated on a perfunctory knowledge of SQL and this last time I was hoping for more, but he hasn’t improved at all that I can tell.  In fact, he gave me all the opposite answers I needed.  Always use @tables instead of #tables.  Never use ‘select into’ because it’s a lazy way to code.  You should use ‘insert into’ instead.  Those were some specific answers he gave me this last go around.  Unfortunately those are the same things he said last time too.  So while he’s been working all this time, he doesn’t know any more than he did like 3-5yrs ago. 

So this time I really felt compelled to write him and tell him how bad he was.  I don’t do this kind of thing often, but since I’ve talked to him so many times and I’ve specifically seen how he hasn’t improved, I wanted to be honest with him in the hopes that it might spur him into doing something about it.  So I sent a very honest email and of course these things always sound very harsh but what can I do? 

So it’s times like this when being the one who’s in the position to tell people yes or no means sometimes you have to hurt someone’s feelings by telling them the truth.

How about you guys… have you ever had to tell someone the honest truth like that?  How did it go?

Admin, Powershell, Time-savers

How to save your life with powershell.

3 Comments

I’m always preaching the wonders of powershell, but I think quite often I forget to stress the impact that it can provide. And today is one of those times when I’m able to give you a real life impact of how PS can save your life… ok, your job anyway.

One of the things PS does really well is scripting DB objects. It’s always one of the first things I show that’s really useful in my classes and it always gets a appropriate amount of gasp. But other than creating dev systems from prod, what good is it? Well, it’s a fabulous way to script your prod environment on a regular basis just to have the scripts handy. If you’re able to use PS to easily script your tables, SPs, views, users, etc. then you’re going to have a history of your DB that you can pull from anytime. And if you’re smart enough to store those scripts in txt files with dates appended to them, then you can always get back a complete version of your DB for any given day. Cool huh?

And the implications of this are huge. Not only can you create a complete environment for any given day so that you can compare it with the current version, or any other version you like, but much more likely is the scenario where someone makes a mistake in prod and needs some small piece of code replaced. So someone drops an SP, or alters a view, or kills a job, or anything else along those lines. Of course, this won’t help you with data accidents, but imagine the possibility of being able to pull your butt out of the fire because you were able to recover from a mistake you made… or anyone else made.

This came not only in handy, but actually saved my ass at my last gig where I had these scripts running and accidentally dropped all the SPs in our prod DB. I could have sworn I was on dev, right? So I had my PS scripts to import the objects again too, so as soon as I realized my mistake I recreated all the SPs and all was well with only a minor hiccup to our reporting cycle. But that’s exactly what I’m talking about. If you have these things ready to go you’re ready for whatever kind of disaster comes up and if you not only have the scripted objects sitting there, but also the scripts to put them back, then you’re in the best shape you can possibly be in.

My favorite way to store these like I said is in txt files.
I put them in a folder named for the DB, then another subfolder after the object type, then I name the files by schema.objectname.txt. So a sample path to an object would look like this:
c:\scripts\DB1\SPs\dbo.SP120100714.txt

Now to parse the date back out is a simple matter and can be handled with a simple PS script. And the scripts you need to restore can be as easy or complicated as you like. You can have any or none of the following:

Restore all tables
Restore all indexes
Restore all SPs
Restore all views
Restore single SP
Restore single view
etc…

You could also combine them into a single PS script more or less that you just pass an object type to and it figures out the path and does the restore for you that way. Either way is good really.

So setup your PS scripts to script your DB objects and store them someplace. It’s only txt so you can keep them pretty far back if you like, and even archive them in zipped files if you like.

Good luck.