Voice of the DBA

Author: way0utwest

  • The Auto OS

    Will we see more integration of computers and cars in the future?
    Will we see more integration of computers and cars in the future?

    Those of you who have followed SQLServerCentral for a long time know that I like cars. I used to do car updates periodically, and still get in car debates on times over Twitter. I love my cars, and despite the age of a few of them, they’re all doing well. My Prius still gets 47-50 mpg (depending on weather) and it’s proven itself to be a great investment. It’s even paid off, and as gas prices rise, I use it more and more.

    For much of automotive history, the advancements have taken place under the hood or in the mechanical systems of the car. Computers have been a part of design and even operation for a long time, but primarily to keep the mechanical systems operating at a high efficiency. The interactions with drivers have mostly  come from entertainment or climate systems.

    That changed a little with the introduction of navigation systems, that allow a driver to spend less time finding destinations and concentrate on driving. I know that hybrids that surface efficiency information to the driver have changed the way I drive, even when I’m in another car. I suspect the same is true for many people that purchased cars with the idea they would save money on fuel. I suspect in the future we will get more interactions with cars in new ways, perhaps even in ways we hadn’t considered before.

    Cars are becoming more complex, and many have their own OSes inside. This might be the next OS war, after the mobile phone competitions that are ongoing between iOS, Android, and Window Phone. I suspect that these two are closely intertwined as I think integration between a mobile vehicle and a mobile phone is essential and I hope there are good APIs and standards that allow any phone to work with any car.

    Data integrity and security will become more important in the mobile platforms as we use them for more services. I hope that companies are paying attention to these issues, and ensuring they not only hire great developers and software engineers, but also good data professionals to help them build their systems. I suspect that isn’t the case, but perhaps a few of you will find jobs in the automotive industry and become strong advocates for data security.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • Building a Full Text Index

    I hadn’t used full-text indexing in production throughout my career. We hadn’t had the need in the applications I worked on, all of them depending on LIKE searches in specific, normalized data.

    However I have always been interested in it and as I try to find data more often in various systems, I’ve been playing with it on the SQLServerCentral systems. One of the first things I had to do on a copy of the system was build an index. It’s surprisingly easy. I’ll build a basic index and explain a few options.

    If you don’t know what a full-text index and full-text search (FTS) is, here’s a short introduction from BOL.

    Creating an Index

    Let’s take a basic table. In this case, let’s look at the AdventureWorks 2008 database. There’s a table called ProductDescription in there without an FTS index. Let’s add one there. First we right click the table and select the full-text index item.

    fts1

    This starts a wizard that we can use to pick the full text index. The first step is basing this on a unique index. The wizard is smart enough to only show those valid indexes for you to choose. I tend to choose the PK in most cases. That’s what I’ll do here. If your situation calls for something different, be sure you understand why.

    fts2

    The next step for is to choose from the available columns that are valid for full text indexes. In this case we only have one, so I’ll pick it.

    fts_2

    Once I do that, I then can examine the other options. The middle item is for Word Breakers. These are the rules by which we decide where word boundaries are.

    fts_3

    These rules can vary by language, and as you see above, there are multiple language choices. We’ll stick with English.

    The last column is for the “type” of data stored in the column we are indexing. This is for the use of iFilters for our data.

    fts_4

    In this case there is no other column that makes sense for a type. If you examine the full text index on the Production.Document table, there is a “FileExtension” column that is specified here.

    fts_6

    That column is named appropriately, but the name has nothing to do with the use here. If the column were named “Type” or “Extention” or even “Blue” it would still work as long as it contained the correct file type extension for the data stored in the full-text indexed column.

    Next we have the change tracking. You have three choices: automatic, manual, or do not track. The default is automatic, which I’ve often used.

    fts_5

    If you have a large index, with large changes, you might choose to manually update the index, but you then need to do that. If you don’t want an automatic population to start after the wizard, then you need to choose “Do not track changes” as noted at the bottom.

    The catalog is like a filegroup for the full text index. There can be multiple indexes in one catalog, but an index is only in one catalog. Note that you can select the actual filegroup in which this will be stored at the bottom.

    fts_7

    I haven’t had enough experience here to give guidance. I’ve just had one catalog for the systems I’ve worked on.

    The stoplist is the list of words ignored in the index. This was called the “noise word list” in SQL 2005 and prior. You can create your own stoplist, or use the system one.

    fts_8

    Here I only have one, though I could not use one and allow all words in the index, which tends to bloat the size.

    Next we have the standard scheduling mechanism in SQL Server. Here it’s applied to the index population, or what you might see as the rebuild.

    fts_9

    No guidance here other than choose what works. If population is an issue, pick a low time to schedule things.

    The last part is the summary.

    fts_10

    My one complaint here is the fact that there is no “Script” button for this. That’s a royal PIA and it’s why I’m showing the GUI here. This is the easiest way to build the index. Once it’s done, you can go into SSMS and get the properties.There’s a script button there.

    fts_11

    However if you press this, you get this

    fts_12

    No scripting for the index, which is a hole. I’ve submitted an item on Connect, which you can vote for.

  • Those Who Can, Do

    Getting a certification like this is good if it teaches you new skills and you use them.
    Getting a certification like this is good if it teaches you new skills and you use them.

    There was a time I considered staying in college, getting a masters or PhD and teaching others. I still might follow that path at some point since I enjoy speaking and teaching others how to better work with SQL Server. At some point, however, I became frustrated with the theoretical approaches many teachers had. Like many 20-something-old students I tended to subscribe to the mantra “those that can, do. Those that can’t, teach.”

    I was reminded of that by this piece: Those Who Can Do, Those Who Can’t, Get Ceritified. It compares IT workers to the computer systems they manage, and it points out that if all that’s required is to pass a test, that’s something a computer can do very well, perhaps even replacing those that can just answer questions in their daily work.

    There’s some truth to that. I always wonder about a person that has 3, 4, or more certifications; do they have actual skills with the product?. Have they actually used the knowledge from those certifications in their work? Is the certification the goal, or is it a way to learn skills and knowledge that can be applied at work? If it’s the former and not the latter, then I’d say your efforts to advance your career through certification are poorly aimed.

    However if the certification gives you structure and focus, if it allows you to improve the skills you have, and bolster the weak areas in your knowledge, it can be beneficial to your career. If you are taking that knowledge and using it in your daily work, or even in your spare time, then the certification is merely a stepping stone to something greater.

    I don’t think that people who are certified are somehow incompetent at their jobs, but they have to showcase more than just the certification for me to believe they are valuable employees.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • The Principle of Least Privilege

    One of the tenets of good security is that no person or process is granted more rights than it needs to accomplish a task. This is the Principle of Least Privilege, and is designed to ensure that your system is as secure, stable, and predictable as possible.

    For a SQL Server instance, this means that unless a person or process needs to perform instance level changes (configuration, patching, etc.), then they should not have instance level privileges. If a process accesses data in a database, there is no need to grant sysadmin privileges, even if they do need db_owner privileges.

    Many software packages require sysadmin access be granted, often without any justification. Developers build software as a sysadmin on their own workstation, or on a development server. They often don’t understand or realize that less privileges will still allow their software to work. As a result we find software running under elevated privileges, which can be a security risk if there are bugs or injection holes in the software.

    It’s hard to make changes later on, and it can be frustrating to argue with vendors that their software doesn’t need elevated privileges. The best way to combat this is to educate yourself and any developers you know about security.

    Adding login or user accounts does not require sa privileges. The securityadmin and db_securityadmin roles can be used. The rights to create objects in a database can be handled with granular permissions in a schema, or with the db_ddladmin role instead of db_owner.

    Work with limited rights when you can, and help other learn how to work with the minimum they need and we’ll all be more secure.