Voice of the DBA

Tag: administration

  • The Daily Routine

    Steve and a horse
    This can be my daily routine some days

    This editorial was originally published on Mar 10, 2008. It is being re-run as Steve is on holiday.

    If you work in an operational area, especially if you’re a DBA, you typically have a fairly tedious routine. You definitely have work, but it’s often the same types of things over and over. You are checking logs, ensuring backups work, testing restores, tuning servers, and more.

    Now this type of work can be fairly boring and many people don’t really like it. They don’t handle the operational routine that well over time. Most often I’ve found that database people would rather be on the development side, solving problems, working on new things, and exercising their brains a little more.

    However I really like the operational area and I’ve been much better at it in my career than the development area. I like the routine, much like the daily routines that I have around the ranch when my wife is out of town (check out the podcast for a look at some of what I do). I have to feed horses, muck out stalls, clean up the barn a little and more.

    When I was a production DBA, I had to handle daily things on a regular basis, and it becomes somewhat tedious. However it’s not always boring, just like it’s not boring around the ranch.

    Last year we had a horse get kicked and hurt by another horse. I wasn’t really sure what to do other than call the vet, which is something my wife has decided I’m qualified to do. When he came over, he wasn’t sure what was wrong, but he had an idea and the first stage was to lock up the horse, give him a few days to rest and re-evaluate. However along with that rest (and feeding/cleaning/etc. in the stall), I had to give the horse aspirin.

    Now it’s actually less fun than it sounds. Just like you probably wouldn’t want to eat (as in chew) any aspirin from the bottle, horses don’t like the pasty stuff we give them. So you have to force it.

    And it’s not like forcing your dog to take a pill. I have a 130lb dog and I don’t think twice about forcing her to take medicine, but the thought of forcing a 1200 lb animal is something else. Especially when they’re taller than you!

    The vet laughed at me a little (and my wife a lot over the phone), but shower me how to bridle the horse, hold it’s mouth and then stick in the tube and squirt some aspirin in the horse’ mouth. Not much fun the first time, but it got easier.

    Managing a crisis and learning new skills is definitely part of the DBA routine. If you can remember the first time you’ve had a production database fail and you had to restore it, you know what I mean. Hopefully not a daily part, but it can, should, and likely will, happen to you.

    A daily routine however easy, does require a balance. You have to remember that there is life outside of work and that you have to get away and recharge. You have to break things up and remember what else you like in your life besides your career. You also sometimes have to let some tasks go for awhile to get that break.

    Because if there’s one thing I’ve learned in a couple decades of working, it’s that the work never ends.

    Steve Jones

    The Voice of the DBA Podcasts

    Everyday Jones

    The podcast feeds are now available at sqlservercentral.podshow.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.

    Overall RSS Feed: or now on iTunes!

    Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

    I really appreciate and value feedback on the podcasts. Let us know what you like, don’t like, or even send in ideas for the show. If you’d like to comment, post something here. The boss will be sure to read it.

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

  • Test Your Restores

    The ultimate testers.

    I was talking with someone the other night about their database systems and they mentioned they had implemented TDE (Transparent Data Encryption) to comply with HIPAA regulations. This person had verified that they were backing up the Database Encryption Key, which you definitely need if you want to restore a backup from a TDE encrypted database. However they weren’t sure if the certificate that protected the DEK, and the master keys on that instance were being backed up. Probably most scary to me, they hadn’t tested any restores of the database.

    Encryption is serious business, and if you are going to implement it in your databases, you had better be sure you understand how the various keys and certificates work. You better be sure you have protected your passwords, and that you can find them in the event of some issue.

    Most importantly, though, is that you need to practice recovering your database to another instance. Preferably you’d learn how to recover on an instance that hasn’t ever enabled encryption as well as one that has a different SMK or DMK.

    Practicing restores isn’t just about encryption and the potential for data loss because you don’t have a key. Practicing restores is important for all of your systems to be sure you have the skills to successfully complete a restore. It helps ensure you know where the files, tapes, disks, or any other resources are located. Most importantly it ensures that your backup process is actually running smoothly.

    Please don’t assume your backup process works. Whether you’re an accidental DBA stuck with their first SQL Server, or a ten year senior DBA that has performed hundreds of restores at previous jobs. You need to test your process and ensure that you can perform restores on the systems you are managing.

    Steve Jones

    The Voice of the DBA Podcasts

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

  • The Default DBA

    When we setup SQL Server, there are a few choices we need to make, some of which require a few items be prepared in advance. Service accounts are one of those preparation tasks that DBAs usually perform in advance of installing an instance. There are a few things, however, that aren’t configured when SQL Server is installed, like alerts and operators. I find that often these items can be missed by inexperienced DBAs, who forget to configure standard alerts. They might also not have a mail account ready, which is required for the instance to send mail.

    Perhaps there’s a better way for SQL Server to request this information when it is installed. If one part of the setup program asked for default mail information, it would be easy to pre-configure the instance to setup alerts and enable them for communication with a DBA. Personally I’d prefer that setup get a mail profile and a default DBA group email address that receives information about the operation of the instance that the DBA should be aware of.

    There can be a hesitation from experienced DBAs that don’t want setup to install mail for them, but it would be easy to make this an optional component, with a checkbox like the one for a blank sa password. You actively choose to install the server differently if necessary, but otherwise setup would push DBAs to ensure that notifications are configured. We could even see a pre-selected list of alerts that will be enabled, with the option to disable all or just selected items.

    I don’t know that Microsoft will build these types of defaults into SQL Server, but I wish they would. We’ve done that with SQL Monitor at Red Gate, and we have seen the advantages that come when you provide good practices and knowledge to less experienced DBAs: they gain some knowledge transfer and have software running at a high level from the very beginning.

    Steve Jones

    The Voice of the DBA Podcasts

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