Jan 182011

imageWhen you think of a craftsman, a likely image to immediately pop into your head would be one of an older man, working with hand tools on a piece of wood.  Each movement he makes are deliberate and precise, and he seems to know intuitively what needs to happen next.  His tools are so familiar to him, and used so effortlessly that they seem like an extension of his body.

Although database work is a far cry from any form of woodwork, it is still a craft, albeit one that is difficult to garner as much sympathy at family gatherings as the classic crafts.  As a craft, one of the two main things database professionals will talk about is "what have you created or done?", and "which tools do you use?".

This second question is a great question, as you may discover a fantastic tool that you’d never heard of, and which can provide vast improvements in the efficiency of your regular time-consuming tasks, whether by providing necessary information more easily, or by automating tasks you currently do manually.

I frequently get odd looks when I state that I don’t regularly use third-party tools at all (with the exception of backup compression tools). 

What?  No Tools?

The primary reason for this stance is that I work for many different clients, and the majority do not have any tools over what is provided with SQL Server, and for various reasons, cannot justify acquiring any additional tools.

This is not to say that I am against third-party tools – I definitely do make use of them when available, but I believe that for every task that a tool provides assistance with, a database professional should be able to do without the tool at a pinch.

I liken this to our craftsman’s view of power tools.  There are many situations where power tools will greatly increase the speed and ease of creating some projects in the workshop, but for other situations hand-tools reign supreme.  Perhaps the craftsman is visiting family and is asked to repair a broken chair, or has a simple task to do that doesn’t justify setting up the power tools.

Let’s See Some Examples

As an example, Ola Hallengren’s excellent Index Optimisation scripts (amongst other things) are an excellent and free utility for more intelligently performing index maintenance.  However, at a pinch, a DBA should be able to query the sys.dm_db_index_* family of DMVs to determine which indexes require maintenance, and issue the appropriate ALTER INDEX command.

Automatic code-completion is another helpful tool that some people swear by.  As this is a relatively new feature in the SQL Server world (whether a third-party tool, or the one provided as part of SQL Server 2008′s Management Studio), many DBAs are used to not using this.  In the future, however, we will become used to having code completion tools available.  It’s still important to know how to quickly query the database structure using sp_help, sp_helptext, sys.objects, INFORMATION_SCHEMA, etc. (Incidentally, I wow at least 5 people a year by using the Alt-F1 shortcut for sp_help. Simply highlight a table name in a query window in Management Studio, and hit Alt-F1.  Very useful.)

There are a number of tools available that can be used to trace expensive queries, and I do enjoy using a tool developed in-house to provide trace control and analysis.  If this is not available, or if it would take too much time to set up, I’m happy to pull out Profiler, script out a focussed trace, collect some data, and then analyse the resulting trace files with fn_trace_gettable().

There are numerous other examples.  Can you read a deadlock graph, or do you need Profiler to draw a picture?  Can you read a blocking chain in sp_who2, or do you need Adam Machanic’s (blog | twitter) Who Is Active?  What if you are called upon to help with a SQL Server 2000 instance, where this is unavailable?

Regardless of whether you primarily use the base set of tools, or primarily use the "power" tools, it pays to be familiar with both types, and to be able to use each tool in the proper way in the right situation.

Jan 112011

Another T-SQL Tuesday has rolled around, and for once I was prepared for it.  This month’s topic, hosted by Jen McCown (blog|twitter) is on the topic of Resolutions.  I spent a week rolling around ideas in my head for this topic, hit upon some excellent angles, and then had fate completely change the direction of my post.


I’m not a big believer in "New Year’s Resolutions", mostly because they’re so clichéd and popular opinion is that you’re doomed to fail if you have one.  Instead, I appreciate the holiday period as a time to reflect and set more general life directions, as opposed to the focussed task of "exercise more". 

In terms of SQL Server related goals that I want to accomplish this year, a quick brainstorm came up with the following:

  • I really should blog more often, say once per week.   Something 95% of SQL bloggers say every few months.
  • I really should post a bit more on my favourite SQL-based forums.  Two quality posts a day sounds feasible.  This one mostly comes down to time.
  • Wow, that MCM certification looks awfully interesting, challenging, yet achievable.  Let’s do that!
  • 2011 will be the year where I get my head around recursive CTEs!
  • Can I please, please, please use the MERGE command in production code?
  • Denali, I’m playing with you.  Get ready to be swept off your feet!
  • I’m going to slow my handwriting down, and write legibly and neatly for the first time in 15 years. (Not a technical wish, but still important!)

That third point, the MCM should be enough to keep me busy for the first half of the year at least.  I’ll need to do the MCITP 2008: Upgrade from 2005 DBA exam (70-453), and then the MCTS 2008: Developer exam (70-433) (I’ve already done the 70-451, back when it was in beta).

I have already planned for these in a more serious way.  I changed different things in my life in November, December and January, so there’s nothing particularly significant about January 1.  Do it now – don’t wait until some arbitrary date!

However, it is important to consider the non-technical side of your life, which is something that hit home to me again last night as I received news of my wife’s family in flood-ravaged south-east Queensland.  Don’t be so fixated on SQL Server that you dilute or lose connections with your loved ones.  No matter how warm SQL Server may be (especially when you’re standing next to the server during business hours!), it’s a very poor substitute to family and friends.

Jan 072011

When a building is designed, one of the main requirements is to have an appropriate foundation, and a larger and stronger building requires a larger and stronger foundation.  A one-storey house, for example, requires a much simpler foundation than an 85-storey office building.  The deeper the foundation is, the higher the building can be, and the more resistant it is to outside forces, such as strong winds.  The foundations are hidden from the casual glance, but are absolutely crucial to the success of the building.

Careers, particularly in SQL Server administration, are much the same as a building.  You must first create a solid foundation of skills, and, like a building’s foundation, these may not be visible.  This will include both technical knowledge and "soft"skills, such as:

    · The Windows operating system (including monitoring)

    · File system knowledge

    · Knowledge of networking protocols

    · Scripting basics for automation purposes (e.g., Powershell)

    · Server architecture

    · Storage technologies and I/O concepts

    · Basic Windows/Active Directory security concepts

    · Interpersonal relationships and communication

    · Knowledge of table structure and basic SQL syntax

    · Data backups, restores, and recoverability

    · SQL Server security

(I hesitate to put SQL Server Internals in here as they are a foundational skill for more in-depth SQL Server work, but at a much deeper level, and requires the other foundations first.)

You’ll notice that there is not a lot on that list that is specific to SQL Server.  At the heart of it, a database administrator is a specialised system administrator, and requires similar base skills as a Windows sys admin, with the addition some database knowledge.  At the least, a DBA needs to be know how to configure security, query and change data, and provide a measure of data protection in the forms of backups.

The analogy with a building breaks down a little when you realise that, unlike building construction, you don’t have to complete your foundations prior to working on the "visible" parts of your career.  This is a good thing, though.  You can get started with a DBA career without knowing much about Windows Server, and your limited knowledge will be sufficient to keep you from blowing over during normal weather, but will be inadequate if you attempt to configure a clustered SQL Server deployment.

This holiday period is a good time to reflect.  If you made any New Years Resolutions, after a week they’re either sticking or they’re shot, so you can reflect now without having to worry about the stigma of frequently broken New Years Resolutions hanging over your head.  What areas of your foundations are a little shaky, and could use some attention?  Which SQL Server features would you like to focus on this year, but would require an improvement in the foundations before you can really understand it?  As an example, if you want to improve your backup speeds, you may need to improve your knowledge about I/O throughput to your SAN before you can confidently use SQL Server’s backup performance enhancements (compression, striped backup files, etc).

Personally, I need to improve my knowledge of SANs, and more up to speed with the features that they can bring to the table, and the basics how each of these features work – enough to understand the pros and cons of each feature.

So, get to it. Build those foundations stronger and become a rock!