Voice of the DBA

Author: way0utwest

  • Cramming for Interviews

    SQL books
    Are you tackling any of these subjects in 2013?

    On SQLServerCentral, there are a lot of common questions that I see asked over and over. Perhaps the most common is the “why is my transaction log so large it filled my disk” question, which seems to occur constantly (C’mon, Microsoft, change the default recovery model).  However there is also no shortage of people asking for help with interview questions.

    In general, I don’t mind helping people learn more about their craft. I’s how I learned, and I think honest effort should be rewarded. If you spend some time trying to learn something or understand a concept and have questions, I am more than happy to try and clarify things. However I don’t think that anyone on a phone interview, completing some type of employment exam, or even just memorizing a number of new concepts is doing themselves, or the employer, any good.

    Cramming for an evaluation of your skills makes sense. I did it for school exams, and I see plenty of people working in groups or through exhaustive simulations of features in SQL Server to prepare for the MCM exams. However in those cases, it’s a refreshing of the concepts and knowledge that one will need to complete the exam quickly. This is knowledge the person already has learned in the past.

    As long as you are trying to brush up your skills, and are not deceiving anyone about your talents in an area, I think cramming some facts and ideas into your brain is a good way to present yourself in an interview. Attempting to misrepresent your ability to produce professional work in some area to get a job isn’t acceptable. I won’t help you, and I would encourage others to avoid helping as well.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Full-Text Search – Word Breakers and Stemmers

    There are numerous components to the Full-Text Search (FTS) subsystem in SQL Server that help provide efficient, relative answers to queries. Full-text Search is a little complex, and as I’ve been working with the system in an effort to learn more about it, I decided to document how a few things work.

    Word Breakers and stemmers are two interesting parts of the FTS system. They deal with certain language specific operations that help searches work better. They are related as the two items are loaded together if you use third party word breakers and stemmers.

    I haven’t seen third party word breakers, but some work from other products. As an example, here’s a post to load the Greek FTS search word breaker and stemmer from Sharepoint server if you are on SQL Server 2008. It’s included in SQL Server 2012.

    Word Breakers

    Let’s start with word breakers, which do just what the term implies: they break words. It would seem to be obvious that spaces are the word boundaries, and they are in English, but not necessarily in all languages. There are also the issues of characters in Asian languages like Japanese and Chinese. You can’t count spaces as the word boundaries on those languages.

    Word breakers use the lexical rules of the language to determine word boundaries. Essentially they find what the words are, and then further action can be taken in building the FTS index or processing the query.

    The “words” that the word breaker spits out are seen a “tokens” to the FTS index, and each can then be processed by stemmers, stoplists, thesaurus, etc.

    Stemmers

    Stemmers are an interesting part of the full-text search system. They remind me of my high school Latin classes, where we had to conjugate words. A stemmer takes a word and generates inflectional forms, or conjugations. The example in Books Online, and an easy one to understand is “run”. There are various forms of "run” that we would want to consider as equivalent when performing a search. For example, you would want to consider:

    • ran
    • running
    • runs
    • runner (perhaps)

    The same could be said for “lay”. That would generate

    • lie
    • laying
    • lain
    • lays

    This is one of the big advantages over the LIKE predicate in that stemmers can match these forms of the word being searched for. The index would relate all of these to the core, base word.

    More

    The Books Online page for Word Breakers and Stemmers has technical information on checking what’s installed, some troubleshooting, language settings, and some drier documentation on what you can do with word breakers, but not a lot of explanatory detail.

    I used a little of the information in Books Online, and some from Pro Full-Text Search in SQL Server 2008. You can read more, but unfortunately I haven’t found a lot more documentation on the details of how things work.

    You would probably learn more if you write your own Word Breaker and Stemmer, and there is a sample in the Windows SDK to get you started, but that’s beyond what I want to do.

  • Global Insecurities

    Don't find yourself in this situation. Change your defaults.
    Don’t find yourself in this situation. Change your defaults.

    Reading this article about the insecurities found from various scan in 2012 is a little scary. I wonder how many of my former employers have videoconferencing systems, remote control/access systems, or some commercial software with a default password connected to the Internet and unsecured? Reading the article I’d bet at least one of them does.

    I also wonder how many of them have old versions of software with known vulnerabilities that can be exploited, not just by dedicated hackers, but by script kiddies. Lots of people have excess time available, powerful computing resources, and mischief in their hearts.

    This is slightly maddening to those concerned about the security of computer systems. How hard is it to change the default passwords on the installation of an application? How much more time does it take to configure a system properly? It doesn’t’ ta

    ke much at the moment, but it does take time in advance. Proper security requires knowledge, which means that an administrator much have spent time learning how to properly configure a system, or getting a comprehensive list of vulnerabilities and their patches.

    I’d love to see vendors publish a best practices document, or a couple of them, for each version of software they release. Give people specific steps to follow on the installation of the software to ensure it is securely configured as well as known vulnerabilities and the patches available. I can publish information, and there are likely any number of blogs out there that my give some best practices, but for new users, the vendor’s site is the only resource that many people will follow.

    I know I’d be willing to allow vendors to link to any best practices I published, or republish the information on their own site if they wanted to. I’m sure others would feel the same way. Now if only the vendors would agree to use the information.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • The Auditing Poll

    Auditing is an important part of your database application if you have any sensitive information.
    Auditing is an important part of your database application if you have any sensitive information.

    Our database systems become more important to the operation of our world all the time. We store data from sensors, back commerce systems, and more. We know software has bugs, and there will be security lapses, perhaps even from insiders. I suspect that as our software evolves, auditing will become more and more important all the time.

    This week I’m curious to what extent you have auditing in your environment. For some of us it’s included in the software we write, for others we might have to retrofit auditing when some issue occurs, but most applications I’ve used have very limited auditing or logging capabilities.

    How extensively is auditing deployed in your environment.

    Whether it’s something simple like a Trace or Extended Events, the built-in SQL Audit, or some third party product, what percentage of your applications use some sort of auditing or logging? Is it a requirement, perhaps for certain types of systems? Or is it rarely implemented?

    I think auditing will become more prevalent in the future, perhaps even a core function that we expect to find in every piece of new software.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.