THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

James Luetkehoelter

Nearly any SQL topic presented at times in a slightly eclectic manner.

  • Kalen's latest editorial from SQL Mag newsletter

    If you haven't already read it, Kalen has another great editorial in the latest copy of the SQL Mag e-newsletter. What I love about this is not just pointing out some behaviours that others may not be aware of (she focus an database recovery issues, near and dear to my heart), but in what she is really trying to say with the post. MS gets tons of requests for more information (and we see them on newsgroup posts all the time) for information not just on when someone has done something wrong, but a warning before they do it. I know that's true. The more I think about it, my reaction is - what the hell????

    If you were an Oracle DBA (ohoh, I said the O word), you are expected (or at least were from versions 9i back) to truly understand the platform. And if you weren't sure about something, you would invoke support. There is still this common mindset out there that SQL Server is this simple database platform where you really don't need a DBA, nor really understand the platform. I've seen it over and over again. I know most of you have too. I think MS is partly to blame for this, with marketing approaches (SQL 7 - it tunes itself!) to gearing the primary user of SQL Server to be Visual Studio. But it is also a fundamental problem with those that manage SQL Servers. Yes, I know a lot of you are DBAs by default. What everyone must understand is that SQL Server is a complex system, and if there's a way to make a mistake, you can do it without the system conveniently popping up to say "Danger Will Robinson!". Education is a never-ending thing - there isn't one person out there that can't learn something from someone else. And for pete's sake, if you aren't sure what is going to happen, FIND OUT first. Let's step up people. Take some personal responsibility rather than blame MS for everything. SQL Server isn't a TV you can simply turn on and off, and adjust the volume, or change the channel...and it never will be.

    Kudos Kalen, another great editorial.

    BTW, for those of you that don't have it, Kalen has some intense training DVDs out there for unbelieable prices - check her posts to get the address (sorry Kalen, I don't have it off hand).

    And for those that might be offended by my position - tough. This is reality. Expecting all the answers to come from someone else without you lifting a finger is, well, irresponsible.

    End rant.

  • The devil in the details...

    I haven't written a post in awhile, mostly because my fridge keeps falling on me and trapping me for months. Seriously, I've had some changes and have moved back into a permament position doing a plethora of activities (DBA to BI to AppDev and maybe even Sharepoint). I've found it at times frustrating, but very fulfilling experience. I've been out as a consultant/trainer/speaker/whatever for quite some time, and I often found myself dealing with very esoteric issues, or training people on fundamentals like how locking works, or just being thrown the most difficult scenario possible to try and resolve. I enjoy that, I always have. But what I discovered recently is that by doing just that, my skills to do day to day or basic code writing (syntax!!! - you will be my downfall!) diminished significantly. Juggling knowing PL/SQL vs TSQL vs ANSI SQL vs VB vs C# vs SSIS vs DTS vs Informatica vs SSAS vs Hyperion has really challenged me to get back to writing, well, very basic stuff.

    I find myself improving, slowly, and my colleague Steve (who will probably respond to this with a sardonic comment :) ) will attest to the silly things I do. Miss a comma. Miss a closing quote. Forget basic techniques that I've taught years ago. He constantly hear's me mutter "Stupid James". We have a beer tally going, and let's just say he'll never have to by beer again in his life :)

    Why am I saying this? It's to all you out there are that aren't authors, or trainers or consultants or MVPs (nothing against those that are - please don't come back and punch me in the nose any MVPs) really DO know the product quite well. Yes, there are others that need help - we see it on newsgroup posts all the time with questions like "Why is my transaction log filling the disk?". But there are so many out there that do the detailed work day in, day out that really deserve a shout out. Never, NEVER believe that there isn't something you can learn from a colleague. And companies and managers out there - your employees opinions about what to do are often more applicable to your situation than a consultant coming in for a day - value the people who work for you and get things done. Yes, we all know there are nightmare situations out there in the SQL world, but I think the people I'm addressing know who they are. Kudos to you all.

  • The concept of a data steward

    At PASS this year, Adam Machanic and Peter DeBetta gave a great session presenting things as sort of "anti-patterns". They would have slides that have would say things like "Naming standards are irrelevant" or "always use heaps". It was a blast. The audience chimed in with equally bad ideas (including myself). Kudos guys!

    What really struck me though is when they were talking about security and pointing out that most security issues happen "outside" of the database. A backup tape disappearing, a web application exposing things with no protection, or an employee walking out with credit card data. Adam then quite eloquantly talked about how we DBAs are the "stewards" of the data. We need to understand what is happening before the data hits the database, after it is in the database and once it leaves the database. I couldn't agree more. Someone has to be responsibility for data, and if the not the DBA, who else?

    What does everyone thing - is this the role of the DBA or someone else?

  • An open letter to all 3rd-party vendors: DO NOT USE SA ANYWHERE IN YOUR APPLICATION

    I've run into this problem again and again. Sometimes I've had luck in convince clients that if a 3rd-party application is hard-coded to use SA is shouldn't even be considered. Sometimes not. With all of the issues that have come up with the SA account over the last 10 years, I find it inexcusable that vendors still hard-code their application to use SA. Some at least let you pick your password, which you can make absurdly complex and then throw out. Others still go with SA and no password (yes, even under SQL 2005). Far too many vendors for this just to be a James-like rant.

    How do we solve this? DEMAND that software be changed. Refuse to purchase software where SA is used at all. Only two weeks ago I saw a dictionary attack against the SA account, and that on a SQL 2005 box behind a firewall (meaning it was an employee in all likelihood doing the hacking). I urge all of you - do NOT LET COMPANIES GET AWAY WITH THESE NIAVE SECURITY PRACTICES!

    Who's with me? Or am I just insane (a true possibility)

  • OT: I can't catch a break

    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

    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...

    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:

    1. knowing the data
    2. knowing how the users use the data
    3. 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?

    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

    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? 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!

    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

    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

    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!

    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

    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!!

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


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

Privacy Statement