Voice of the DBA

Author: way0utwest

  • Missing Full Text Extensions in Express

    I was tasked recently with removing the full text indexes in Adventureworks for a demo. The full text indexes were causing a few extra items to appear in a SQL Compare demo and weren’t needed. The individual that had set up the VM I was using wasn’t sure what to do, so they asked me.

    I logged on to AdventureWorks and right clicked the Production.Document table. I knew that had full text indexes because I’d tested them before. However, what I got was this:

    fts1

    The Full Text index part was grayed out. Strange, since the database was attached, and with a query, I could see FTS indexes below.

    fts2

    I suspected that the FTS extensions weren’t installed. I decided to check by running setup. When it started, I clicked the top item to "add features", as shown here.

    fts3

    That brought up a list of instances. The default is the top radio button below, but I selected the second one, which let me select an existing instance.

    fts4

    Next, I saw the features, and sure enough, FTS wasn’t checked.

    fts5

    I checked it and then clicked next to continue the installation.

    fts

    Once this was done, I could run SSMS and sure enough, I could delete the FTS indexes (shown below).

    fts6

    I actually had two instances on this VM, but this FTS feature isn’t in SSMS. It comes from the instance. After I deleted these three indexes, I connected to the second instance and tried to delete the FTS indexes, but things were grayed out, as shown in the first image above.

    I had to re-run setup for the second instance and add the FTS components there as well to delete the indexes from that database. Once that was done, I could easily delete all the FTS indexes and complete this simple task.

  • Where Do You End Up as a DBA?

    I saw this question pop up on the forums: Where do senior DBAs land finally? It’s an interesting question, and for people that are searching for the next challenge, I would guess that they’re interested in an answer. Here’s what I think:

    There is no landing, senior or not. There are only stops along the path of your career.

    As you gain experience, and talent, you will have both more, and fewer, choices. If you expect to constantly gain salary, you will find that harder and harder over time. That’s the nature of any business. The best artist/athlete/programmer/whatever will reach a point where they can’t necessarily raise the market price for their services. The more you want to get paid, the fewer people that will afford you.

    If you look at money as a measure, you will be disappointed at some time. Not that money isn’t important, but I recommend you keep things in perspective. Money is important, but as you earn more, I would hope that value would start to diminish.

    At the same time, the challenges and opportunities at a job may matter to you. If you always want a harder problem to solve, a more complex system to manage, you’ll find the same limitations at some point. Fewer and fewer extremely complex (or very large) databases exist. You have less choice if this is important to you.

    Some people are restless and want to look for new opportunities on a regular basis, no matter what their situation. If that’s you, I would think you should consider consulting, either with a company or by yourself. Which you choose probably depends on your tolerance for the non-technical parts of a business, as your own consulting business will have lots of non-technical work to manage.

    Some people value stability, and if you value that highly, you’ll sacrifice some challenge and excitement. However stability is very relative these days, and you need to keep that in mind. You always work for yourself and no job is guaranteed for the rest of your career.

    I have moved back and forth from consulting to FTE, and like both, but I tend to value the co-workers I have and environment more than other things. I have to enjoy going to work. I find the DBA/developer job to be pretty much the same in most places, but my co-workers make the difference.

    It’s good to investigate and consider other options, but don’t think of this as the game of Life, with a few choices and an ending point. There are many paths, many directions to go. Some routes may cross, but many do not. You choose the one that matters to you, but don’t be afraid to cross to another one if you find yourself wanting.

    Where do you think Senior DBAs end up?

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and LibSyn. feed

  • Powershell Quick Parameters for Scripts

    I was working on a script recently to manage a particular process and wanted to make it generic by allowing the user to pass in a parameter. I have seen lots of examples, especially those that work with SQL Servers, using text files and other items as parameters, but in this case I wanted an easy, quick, command like parameter.

    This post looks at what I chose to check parameters. I had a couple requirements.

    • display message if no parameter is passed in.
    • display some help if /? is passed in.

    I know that my cmdlets should contain help from the PoSh command line, and I’ll get to that. For now, I’m managing things the way I was taught when I wrote C. A /? should get me help.

    $Args

    I did a little research on parameters and found a few things, but decided to use the $args variable. This is an array of undeclared parameters. I grab the first value (the only one I care about like this.

    $instance = $args[0]

    Note the [0]. As with many things in Computer Science, we’re zero based arrays.

    I could allow for other parameters, but this gets me what I want.

    Testing

    The test for /? is easy. That’s like this:

    if ($instance -eq "/?") {
      write-host "Please enter the instance you wish to detach all databases from as a parameter."
      }

    If this is equal to my help request, write something out.

    Next I needed to add another test. In this case I found that I could easily look for NULL variables, or blanks, with the !. As in this:

    if (!$instance -or $instance -eq "/?") {
      write-host "Please enter the instance you wish to detach all databases from."
      }

    That worked well and lets me remind myself if I’ve forgotten to pass in a parameter. The one thing I experimented a few times with was the OR clause. I tried these, none of which worked:

    • if (!$instance OR $instance -eq "/?") {
    • if (!$instance) or ($instance -eq "/?") {
      if (!$instance) -or ($instance -eq "/?") {

    A little experimenting got me to remember that PoSh is fairly consistent, and the plain -or should work inside the parenthesis.

    Everything Else

    When I first ran this without a parameter, my script froze. That’s because I hit the IF clause, wrote out the message, and then executed.

    Fortunately I’ve done this type of stupid programming before, so I added this:

    if (!$instance -or $instance -eq "/?") {
      write-host "Please enter the instance you wish to detach all databases from."
      }
    else {

    The rest of my script fits in the else clause.

    Reference

  • Your Tools

    I was watching a few developers present recently and they made extensive use of tools to make their coding go very quickly. At one point in the presentation, after having shown a very efficient coding session, one of the developers stopped to answer a question. The question was about how the presenter managed to write code so quickly with so few keystrokes. The answer was with Resharper, but I was surprised when the developer went on to say he had purchased the tool himself a few times at different jobs because it was so valuable in hits work.

    He noted that even if an employer wouldn’t pay for them, there are some tools that are really worth buying. They make your job easier, and so much less stressful, they’re worth the investment. He pointed out that mechanics often buy their own tools, chefs buy their own knives, why shouldn’t we buy a few tools ourselves?

    I know early in my career I never would have purchased any tools if my employer wouldn’t. However I’ve matured, and I realize that there are benefits from tools that go beyond what my employer receives. I think that if I were to move to a design role, I’d have to use a tool like ErWin, or at least Visio, to do the job. Even if I had to purchase it. If I were doing T-SQL development, I’d get a copy of SQL Prompt and SQL Compare to make my life easier. If I were tuning code, I’d be sure Plan Explorer was on my machine, and I’d be really grateful to SQL Sentry that it was free.

    I know that many companies don’t like to add extra tools to their systems, but I know I’d find a way to argue that these tools are really essential for an efficient IT worker. At least on developers’ and administrators’ workstations. Without them, not only are we working inefficiently, but we’re also working under more stress and frustration than we should.

    Steve Jones

     

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.