Voice of the DBA

Author: way0utwest

  • Consolidation Matters

    Throughout my career I’ve been looking to consolidate SQL Servers when I find them. The typical employer I’ve had usually grows their IT infrastructure over time and many projects look like this:

    • Build or buy a software package
    • Buy a new server
    • Install SQL Server and one database for the application

    Over time this means I find lots of individual servers running at much less than full capacity. That’s something that DBAs like, because it means that we can handle the inevitable spikes in resource usage that our workloads will encounter. However that’s not what the rest of the business, especially the financial management, wants. Underused resources mean money that isn’t spent well.

    As a result, I’ve often looked to consolidate instances where possible. Often I let an instance run by its own hardware for a period of months, perhaps even a year, during which I can get a good idea of what level of resources the database and application require. Once I have that, I try to match up the needs with an existing SQL Server that might be underutilized by at least that amount of resources. Typically I’m looking at RAM and CPU since disk resources can often be transferred to a new piece of hardware. It’s not quite as simple as it sounds as I also need to look at workload patterns and potentially match up instances whose workload peaks occur at different times.

    I’ve successfully consolidated many instances this way, often reducing the amount of physical hardware in data centers substantially. As hardware cycles turn over and newer machines are purchased, I can usually repeat the process again and again. The advent of virtualization has made this even easier as bad guesses can usually be reversed or corrected by moving the database to a different instance.

    I suspect that virtualization will become more important in the future, especially as licensing changes in SQL Server make it much more expensive to add the ad hoc instance on its own hardware. I’d encourage you to plan on consolidating new databases from the beginning to ensure that your organization gets the most performance out of the hardware that it has purchased.

    Steve Jones

    Podcast: http://traffic.libsyn.com/voiceofthedba/consolidation_56_v1061.mp3

  • FileTables–Inserting Directories from T-SQL

    Creating a directory in a Filetable share is easy. It looks like this:

    filetable_i

    It’s hard to see, but this was a right click, New, Folder in the share from Windows Explorer.

    However what about creating a directory from T-SQL? That’s almost as easy.

    I created that folder above by running this code:

    -- create a folder
INSERT  INTO dbo.Explorer
        ( name, is_directory )
VALUES  ( 'Books', 1 );

    I provided a name for my folder and then a 1 for the is_directory property. Once I ran this, the folder above appeared in my share.

    Note that this works for folders in the root, but not nested folders. I’ll tackle that in another post.

  • Advice for Newcomers

    A friend recently was asked to give a presentation on their career to a group of 12 year olds. It was a challenge to engage the students, and my friend was surprised that very few of the kids were interested in technology. I was disappointed as well since I think this is a great career choice, and worth a little investigation, especially at that age.

    It’s hard to convince people to enter this business if they don’t have any interest, but if they do, I’m wondering what you might tell them. Today I’d like you to think about your words of wisdom to someone interested in your job.

    What would you tell someone that you wish you knew early on in your career?

    You might impart some hints about how to approach technology. You might give them ideas on how to build skills faster or better? However for me, I’d tell them two things.

    First, you can’t underestimate the value of networking, no matter what field you enter. Whether that’s technology, medicine, law, or anything else, networking will help you. Learn to make contacts and interact with people.

    Second, learn what you don’t like to do. Experiment with the technologies, practice the jobs, and understand what you don’t like. You might not find something you have a passion for, but I’d certainly encourage you not to enter a field that you just don’t enjoy.

    Steve Jones

  • Speaking at SQL Bits XII

    I’m heading back to SQL Bits for the third time and I’m excited to be going again. I think that this is my favorite conference, hands down. The large mix of people, the incredibly welcoming hosts and the casual atmosphere make this one of the events I look forward to every year. It also means I get to swing by the Red Gate office without making a special trip.

    This year the event is in Telford, near some racetrack that’s supposed to be famous. I’m not sure if they is any tie in with the event, but who cares. Chances are you won’t want to leave SQL Bits because you’ll be alternately excited by the content and charmed by the hosts and other attendees.

    I’ll be delivering two talks at the event:

    • Continuous Integration for Databases
    • Branding Yourself for a Dream Job

    I’ve done both of these a few times and they are very popular and thought provoking. I’m hoping that the crowd enjoys them as much as I do.

    If you can make it to the UK, register today and I’d love to see you there. This is a great event, and I’m sure you’ll learn a ton and enjoy yourself while doing so.