James Luetkehoelter

OT: I can't catch a break

James Luetkehoelter — Wed, 30 Jul 2008 16:18:00 GMT

So I was suppose to give a webcast - no connectivity to the Live Meeting website. Oh, I can get here, yeah, but not the MS site. Apparently my ISP sets up routing via carrier pidgeon. So, if you were looking for that webcast, I apologize. I'll post the topic here instead (hey, this works).

Restoring Master - beyond the technical

James Luetkehoelter — Mon, 14 Jul 2008 21:58:00 GMT

Thank Greg Low for reviewing my book and pointing out one of the copy-edit issues in it (I wasn't involved in that process by choice and schedule, a mistake I regret). As Greg pointed out in his post, one of the most imporant aspects to dealing with backup and recovery is restoring Master. In my book, it makes reference to a screenshot that should show a basic restore statement in action - instead it is a screenshot of setting the permissions for instant file initialization?? Silly James, I don't know how that got in there, but was probably my fault to start with (and mine for not seeing it first).

The best resource for is BOL - seriously, the best documentation for any software product anywhere - MSDN also has a copy. SQL Server has to be started in single user mode (sqlserver.exe -m), then a simple restore statement works:

RESTORE DATABASE MASTER from disk='D:\SQLbackup\master.bak'

You're forced to restart - but what's next. Guess what happens to your user databases if they're in a new location (such as a new drive letter or directory) - in SQL 2000, they're all suspect. In 2005 they're just unable to go through recovery because the files can't be found and are unavailable. Scary, but you can always restore them with a "with move" statement to change the location (again, BOL people for syntax - if you don't live there, you should; memorizing syntax should come second to understanding process).

Now what happens if you happen to try to restore to an instance that isn't the same build level? With all of the post-SP2 cumulative updates, if you have a significant installation base it is quite possible (I've been there). It isn't pretty. There is a KB article on that, but the better question might be - before I start restoring this master.bak file, do I know how recent it is? Is it on the same build? Of course its always recommend that whenever a change is to the instance itself, especially a patch, Master should be backed up. This is one of the practical things I talk about in my book - in a disaster situation, the key is not to panic and get your bearings (it drives CEOs crazy if they see you trying to be calm though :) ). This particular issue is also relavant to the Model and MSDB databases.

We also know what happens with users in other databases when Master is restored. If they are SQL authentication logins, they can be out of sync with the user databases, requiring either a synchronizing script to move the secure IDs randomly created (SID) between the Master and user databases or with a script like sp_change_users_login (BOL people if you don't know it - if you do, you've earned a DBA stripe :) ). Common thought is that if Windows Authentication is used, this mismatched users issue doesn't occur because the SID is the actual Windows SID, so it would be the same in the Master database in the logins as well as all user databases. Dangerous line of thought!! What if you are restoring to a new domain? Guess what, the SIDs mismatch. Again, take time to think about the situation before diving in; once you restore Master, it isn't pretty to clean up if you have to...

So what's your Master horror story?

It's all about the data...

James Luetkehoelter — Mon, 16 Jun 2008 15:33:00 GMT

I read a very interesting post on Kimberly Tripp's blog which was about indexing, but she had a very interesting set of requirements for good indexing that I think apply to everything when designing, tuning, backing up, etc., any database. What strikes me the most are 1) and 2) - how can you tune any sort of system if you don't really know what the data *is* and how it is used? As Kim says:

"So, what is â€œfinding the right balanceâ€ in indexing? In my opinion, there are three requirements/pre-requisites:

knowing the data
knowing how the users use the data
knowing how the underlying structures and database stores/manipulates and uses indexes"

I can't count how many times I've run into clients where I'm asked to tune or create a disaster recovery plan or secure for a database where no one is really sure what things mean and how it is used. Everyone should start here, and I'd take it to the step of actually know not just the data, but the information; that is, what is the business meaning behind the data. It's a mantra to adopt - "Know thy data".

Do we need a SQL Developer-only certification exam?

James Luetkehoelter — Tue, 10 Jun 2008 17:16:00 GMT

Recently Oracle announced a new certification - a "SQL Expert". The target of that certification is for developers that write a significant amount of SQL, but aren't "database developers" (something I'm finding more and more common these days). I guess the idea is that it pushes developers to be sure that if they do write SQL, it's at least good SQL.

How many times have you run across SELECT *, or copious dynamic SQL, or poorly structured SARGs? I do all the time, and in the MS world it feels like it is getting worse, not better. Since everything revolves around data, I'm wondering if some sort of core TS exam like this should be required not just for the DBAs and database developers (man, we really need better names for what we do), but also the MSPD certification. It feels like really understanding SQL is becoming a "nice to have" for a VB/C# whatever developer, not a "must have". I see it in job postings as well.

I think (gulp), Oracle has the right idea with this certification. Personally I'd urge MS to do the same. Thoughts?

The "meaning" of NULL - a different approach

James Luetkehoelter — Thu, 17 Apr 2008 18:28:00 GMT

I know we've all beaten the discussion of NULL to death on this blog, but I had an epiphany of sorts when flying to Germany for the European PASS conference. Thus, I'm going to pick up the topic just one more time...

It occurred to me that the word "Null" has a very specific meaning depending on the language you are speaking - TSQL, "academic"-SQL, English or...German, where null literally means zero. Thus, I would naturally expect some confusion when seeing the word "NULL" come up in a result if it is shown to your average German users.

That brings me to what I think is the fundamental problem with the term NULL. In a very practical sense in SQL Server NULL is:

A recording in the NULL Bitmap for a specific row that a specific field (a tuple in the academic lingo) has the property of NULL, i.e., the tuple has a property, it has nothing to do with the value or lack of value in the column

The problem is that language comes in to play. No one likes trying to explain NULL to users in an academic sense, so we come up with definitions. Here are some of the more popular ones (correct or incorrect):

"The absence of value" - I think Codd would have approved of this, as I think C.J. Date and others would endorse. The problem I have with this definition is that there is a connotation with this phrase. To me (and others that I've experienced), the implication is "Why is there the absence of value? Did someone ignore the field? Did the clear it out deliberately? Is it not applicable? Did the consumer not supply an answer to the question?" Thus, NULL chaos ensues, where people take it to mean whatever the conceive of (regardless of the intent of the DBA or developer). Here we get reports supplying questionable information.
"Empty Set" - I wouldn't agree that this is an accurate definition, but again it implies - "Why is the set empty?" There must be some reason why there is a lack of data within the "tuple". Thus we get the practical affect of converting NULL to an empty string ('') or a zero (0). Was the logic of the NULL transferred properly from the DBAs and Developers to the end user - no. Again, what I call NULL chaos ensues.
"Nothing" - Again I would disagree a bit with the definition, but the connotations remain the same - "Why is it nothing? Did no one enter data? Did they remove data? Is the "tuple" not applicable?" All of these things come to mind naturally to me (althought one could question whether I'm an accurate representation of a "normal" person).
"Unknown" - As far as SQL Server goes, this is the best definition. The connotations are almost child like - "It is unknown. - Why is it unknown? - I don't know why....Why doing you know why?...ad infinitum". "Unknown" is also a very practical definition when it comes to understanding things like mathematics where NULLs are involved (6+NULL=NULL -->6 + "unknown" must equal "unknown"

In any event, we need to keep in mind as database professionals that the actual description used for NULL will naturally carry with it specific connotations, for good or for bad. Language plays a great role in the understanding and ultimate usage of NULL - let's remain aware of that.

OT: (again) My book description is finally correct!

James Luetkehoelter — Thu, 17 Apr 2008 17:44:00 GMT

I'll say something technical soon, I promise :) After numerous tries my editor (thank you Jonathan) has gotten the correct description for my book out there on Amazon and such. It's amazing how long changes like that can take. The previous description was almost 3 years old (when I started the book). By the way, if you're thinking of writing a book, be prepared to be steam-rollered. It is a very difficult process, and a complicated balance of business and creativity, deadlines and quality writing.

OT: Thanks to the European PASS conference attendees

James Luetkehoelter — Thu, 17 Apr 2008 14:23:00 GMT

It was a fantastic conference, and thanks to all of you who plastered me with questions after (both) of my sessions. I'll try to get to as many answers as I can on the blog site, but if I miss one, just email me (you can email me right through the blog site).

Of those topics coming:

*More on Full-Text Search and Backup/Restore (especially moving the location of the FT Catalog)

*Possible LSN chain breaks during a failover process, plus how to handle backup/restore during failovers to the mirror (lots of gotchas there)

Again, thanks for being cordial hosts to a humble American (with a German last name yet speaks extremely poor German).

OT: Apology to Wisconsin Users Group Meeting

James Luetkehoelter — Wed, 09 Apr 2008 19:13:00 GMT

Sorry to anyone that came to the meeting yesterday, expecting to hear me speak (all 3 of you). I'm fighting a serious stomach flu, and I tried my best to actually make it, but just couldn't do it. I'll make it up in additional blog posts (since I can't really move right now).

FYI: Speaking at Wisconsin SQL Server Users Group on What's New in SQL Server 2008!

James Luetkehoelter — Fri, 04 Apr 2008 17:09:00 GMT

If you're in the area and available, I'll be giving my first 2008 presentation on Tuesday April 8th. It's a general overview of the new features in SQL Server 2008, starting at 4:30 (I think the presentation starts at about 5:30). It will be held at the Microsoft office in Waukesha, WI. The address is:

N19W24133 Riverwood Dr., Suite 150
Waukesha, WI 53188

If you are in the area, it will be worth the trip. I'll be going over new features with my own skeptical/cynical/paranoid speaking style :) Not just a trumpeting of what's great, but also a warning of misuse...and a certain level of silliness :)

SOT: My book has arrived

James Luetkehoelter — Wed, 26 Mar 2008 05:49:00 GMT

My book from Apress, Pro SQL Server Disaster Recovery finally arrived at my house, with my complimentary copies to give to whomever I see fit. I have more books than people I know, so I'll pose a question to see at what geek level those of you are at. The first to correctly answer this question gets a free book:

There's a movie quote I often reference, "Who invented liquid soap and why?". In order to win a free book, you must give me the movie that is from, the character that says the line (important, who SAYS the line), and what is the origin of the line. You must also give me the names of the female and male lead from the movie. That's a lot to ask, but hey, the book is in hardcover!

That being said, I'm afraid to even look at the book - I'm a terrible self-critic. I'm always my worst critic. I hope it is at least received well (sales would be nice too...).

OT: A new pronunciation for my name!!

James Luetkehoelter — Mon, 17 Mar 2008 20:02:00 GMT

I just received a new item to add to my catalog of phonetic pronunciations of my name!

"L12"

I think that puts me at about 2,052 distinct pronunciations...

OT: Never leave home...

James Luetkehoelter — Thu, 13 Mar 2008 16:54:00 GMT

...without the proper power adapters. I'm teaching a class in Leeds, England this week and I forgot to pack a power converter. Actually, it's more like a plug converter - the power adapters on most modern devices can handle both Norther American and European voltages (at least England's voltage). There was one small power outlet in the bathroom of my hotel that would fit an American plug, but that outlet didn't have enough juice to charge my laptop. I left it in all night and I think it used my laptop battery to power most of the hotel.

One kind student brought in an extra power converter for me today. I will be forever grateful Emily. I've never felt so naked without having access to my computer, if nothing else but to work, much less internet access!

If anyone is going to the PASS Europe conference and needs a power adapter, I'm bringing extras just in case - I don't want anyone to have to go through what I've gone through in the past 5 days!

Silly: A day in the life of a DBA

James Luetkehoelter — Fri, 29 Feb 2008 15:51:00 GMT

Since it is leap day, I thought something semi-serious would be in order. See if this sounds familiar. I often tell a joke like this to a group before I speak and see lots of heads nodding. If this is your life, sound off!

[Queue film-noir music]: "My name is James. I'm a DBA. I was barely in to work one day when the phone rang..."

[Sully-Business Application Specialist]: "Hi James, sorry to say it, but it looks like this patch we installed last night caused some problems. I'm on the phone with support right now. Just a heads up"

[Queue film-noir music]: "Sully was a good guy. Didn't know the database side of things, so when we're testing a patch for his application he relies on my heavily. But, he takes responsibility if there's a problem with the app. He's a rare breed of IT staff..."

[Phone Rings again - Roger, Business Area Liason] - "James, there's a big problem with the database."

[Queue film-noir music]: "Roger was smart about the business side of things, not much of a clue about the database"

"Yeah, I know there's a problem, Sully is working on it. It's because of the patch we loaded."

[Roger]: "Well when will you fix the database?"

"It isn't a database issue, it's something to do with the patch. Sully is working with tech support"

[Roger]: I know, he called me this morning to tell me about it. So when will things be back up?

"You already knew about it? Why are you calling me? And if you want an estimated time of availability, you have to talk to Sully first."

[Roger]: I just thought you could find the database problem before Sully got an answer. Just get the database up, we need that application..."

"But there's nothing wrong with the database..."

[Dial Tone]:

[Queue film-noir music]: Roger hung up on me. He'll pay for that.

[Phone Rings]

[Queue film-noir music]: "It was Bob, my immediate supervisor..."

[Bob]: "I got a call that the database is down, when will it be up?"

"It isn't down, there's a problem with the applicaiton"

[Bob] "Well didn't they test and sign off on the application changes in the QA environment"

"Yeah, but apparently they missed something"

[Bob] "Well, just get that database fixed...I'll check in later."

"There's nothing wrong with the database..."

[Dial Tone]

[Phone Rings]

[Queue film-noir music]:"It was Bob, Bob's manager"

[Bob managing Bob]: "The database is down. When will it be up?"

"It isn't down, it's an application problem..."

[Bob managing Bob]: "Just get it fixed, and keep me in the loop"

"Isn't Bob talking to you as well"

[Bob managing Bob]: "I don't trust him - you know that database, not Bob"

"But there is no database problem..."

[Dial Tone]

[Phone Rings]

[Queue film-noir music]: "It was Bob, the CIO, who manages Bob who managers Bob"

[Bob managing Bob managing Bob]: "Hi James, how are you today? I hear the database is down"

"No, it isn't a database issue, it's a problem with the patch."

[Bob managing Bob managing Bob]: "Well we have to get them to test and sign off on those patches then..."

"We do...apparently the missed something."

[Bob managing Bob managing Bob]: "I'll look into that"

[Queue film-noir music]: Bob managing Bob managing Bob was a good guy, but he had a drive to find root causes as soon as possible, even before solving the problem at hand..."

[Phone Rings]:

[Queue film-noir music]: "It was Roger, and he was mad."

[Roger]: "What are you ratting me out to Bob for?"

"Which Bob? Bob or Bob managing Bob"

[Roger]: "The main Bob. Quit putting the blame on me and fix that database."

"But I didn't blame you at all - plus the database is fine..."

[Dial Tone]

[Phone Rings]

[Queue film-noir music]: It's a user of the application, someone I've never met nor heard of...I wonder how they got my number...."

[Unknown User]: "When will the database be up"

[Queue film-noir music]: At this point I've given up. "At least two hours, maybe more."

[Phone Rings]

"Hold on, I have another call"

[Sully]: "Good news, I have a patch from the company, things are fixed."

"OK, but do I need to bounce the database"

[Sully]: "Yeah, how long will that take"

"2 hours"

[Sully]: "Ouch. I'll relay the message"

[Queue film-noir music]: I keep the database down and leave for lunch. It's currently 8:30. Maybe I'll come back, maybe not.

Yes Virginia, there is a Wisconsin SQL Server Users Group

James Luetkehoelter — Thu, 28 Feb 2008 18:29:00 GMT

I've had a number of people ask me as of late, as I made an entry quite awhile ago on another blog about the creation of a SQL Server Users Group in Wisconsin. There is one, and its been running for a couple of years now. I'll be speaking at the April meeting about new features in 2008. If you're in the area, just go to http://wisconsin.sqlgroups.com to get registered for newsletters and find out about the meeting locations and such.

OT: My ebook download

James Luetkehoelter — Wed, 27 Feb 2008 17:29:00 GMT

It looks like my book is available on Apress.com as an eBook. If you're interested in it and want it at a low, low price, ebook is the way to go. It shows up on the main page in new releases.