SQL University – Tools of the Trade

Welcome to another week of SQL University. This week’s discussion will revolve around the various tools we use in order to get our jobs done. We all have our favorite weapons of choice for various tasks and I will do my best to walk you through what I feel is a natural progression for DBAs.

Think of a master carpenter. They never start out with every possible tool for every possible job. They take years building up their skills and their set of tools. Being a DBA is the exact same thing, you spend years building up your skills and your tools.

Let’s get started.

tempdb

At this level you will use the tools provided right out of the box, the primary one being SQL Server Management Studio (SSMS). Included here would be SQL Server Profiler, the Database Engine Tuning Wizard, the Business Intelligence Development Studio, and even SQL Server Configuration Manager. You should be familiar with each of these tools to the point that you understand their limitations. In fact, I would say that once you have figured out how each of these is limited in some way then you are ready for…

msdb

…finding tools that are provided by vendors, either for free or at a price. Two outstanding vendors that build tools designed to help our Community are Quest and Red Gate. In addition to using vendor tools I would also suggest that you become familiar with tools that help you understand more about server performance, such as the Reliability and Performance Monitor (formally known as perfmon). Quite often the issues you will troubleshoot are outside of SQL itself, but you need to be quite aware of your entire environment.

model

OK, you’ve seen the free tools, you’ve seen the vendor tools, you’ve probably even spent some money on a few that you thought were useful to some degree. What do you do if none of those tools will get the job done?

Years ago I was working in a shop building furniture. I mostly did piecework for frames that would become chairs and sofas. Part of that included wrapping some metal around the edge of the frame. The metal had a thin plastic coating and we needed to “score” the coating with a blade so that when it was fully assembled the plastic would easily tear away. Trouble was we didn’t have a way to score the plastic in a consistent manner. One of the foremen took a piece of plastic, cut a slot, attached a razor blade with a screw, and gave it to me to use. It worked perfectly and it taught me a very valuable lesson: when the right tool for the job doesn’t exist then you build your own.

At that is what you will be doing at this level, you are going to roll your own solution. It doesn’t matter what you want to use (T-SQL, Powershell, sqlcmd, VBScript, Perl, etc.), the point here is that you are going to be comfortable with building your own solution to tackle the issue you are faced with.

master

Once you start putting everything together you will have yourself a nice, shiny toolbox. From time to time you will show off your toolbox for others to admire. And then, inevitably, someone will say “oh, that’s cool, can I borrow that?” And that is when you have hit the master level; when you start building tools that others want to use. It does not have to be some incredibly complex piece of software. It could be something as simple as a few T-SQL scripts, perhaps you have a great script for reindexing, or maybe you want to teach others about how to find out who is active on your instance. When you start sharing the stuff that you build, you’ll be at the master level, and you may find yourself at…

resourcedb

…one of the top resources you could have as far as tools go: Codeplex. There are a handful of other places to go for you to get additional tools and I’ll leave that as an exercise for the reader because (1) I’m lazy and (2) it depends on what you are looking for. I would encourage you to start at places like Codeplex, SQLServerPedia, SQL Server Central, and Simple-Talk.

For previous SQl University posts, click here.