SQL Server Reference Guide

In any software product, you need a way to interact with the system. For desktop applications, you'll find that interaction most often consists of a single program, called a User Interface (UI) or Graphical User Interface (GUI). That's fine for a desktop application, but when the product you're using is a platform such as a database engine, one program usually isn't enough. Just like when you build any physical object, you'll use multiple tools based on what you want to do.

Here in the "Tools and Downloads" area I’ll show you several the utilities, programs and downloads that you can use to manage and program your SQL Server Instances. I’ll explain the tools that come with the product, and from time to time I’ll review tools that you can get from other vendors.

Over the years I've found that people who work in technology often have a hobby that is very tactile. These hobbies include cooking, cars, and one I share, woodworking. One data professional told me "I work with intangible, invisible things all day long, using only my mind. Each day, I have to repeat the things I did the day before. I want a hobby where I touch something physical, and when I'm done, I want something to look at!" I agree with that, and in my wood shop I have a lot of tools that help me create things that we use in our home. I've visited a few museums that have nothing but woodworking tools in them. The tools themselves are things of beauty.

I mention all that to bring up that carpenters, years ago, would show up to a job site with their own tools. And "finishing" carpenters (the people who do fine, detailed woodworking) would always make their own toolbox with their tools. The foreman, who would do the hiring for the job, would simply look at the toolbox to see how good a carpenter the person was. It was their calling card.

A good data professional is very familiar and competent with their tools. Our script libraries are our toolboxes, and merely by examining the kind of scripts and other tools we use, our prospective employers can tell a lot about us.

In this section I'll cover those tools; using a category of work for each tool. I've covered some tools, especially those that ship with SQL Server, in other sections of this site. Those tools can perform almost all of the tasks I'll show below, so I'll describe them briefly here and link to those larger overviews.

Microsoft delivers a very rich set of management and development tools with SQL Server. For pure development, you can use Visual Studio, which has database constructs in it. I wouldn't recommend using Visual Studio for administration alone — unless you're just managing a development database and aren't very concerned about backups and automated maintenance.

Tools for Managing and Programming SQL Server

For management tasks, you have several tools you can work with, depending on the version of SQL Server you're using.

For SQL Server 2000, you have five main tools: Enterprise Manager (EM), Query Analyzer (QA), SQL Server Profiler, bcp, and osql. I’ve covered all of those tools here.

Enterprise Manager is the graphical tool of choice for working with Instances and Database objects. You can set and alter your configuration, design databases, create and alter objects like databases and tables, and even create scripts for those objects. You can also create and maintain your users and security using this tool.

Query Analyzer is also a graphical tool, but lets you type Transact-SQL commands and run them. It has facilities for creating and altering scripts, the ability to export the results of a query and more. It also has an "object search" feature, which feels more like it should be in Enterprise Manager.

The osql (ODBC-SQL) command is similar to Query Analyzer, only you run it at the command prompt of the operating system. You can also point it at a script file, and you can tell it to export the results to a file as well. It is well suited for scripting.

The bcp (bulk copy program) command is also used at the operating system command line, and it is used to quickly insert or export large amounts of data into or out of your databases. It's still considered one of the fastest export methods around, and is available even in the latest versions of SQL Server.

SQL Server Profiler is used to "watch" your SQL Server system. You turn it on, capture a "trace", and it will record some or all of the activity in your database. You can save these traces to files or directly to a table in another database.

Starting in SQL Server 2005, Microsoft combined the functions of Enterprise Manager and Query Analyzer into a single tool, called SQL Server Management Studio (SSMS). SSMS has all of the features found in the previous tools, and since it is based on Visual Studio, it also has other advantages. I’ve covered SSMS and other tools for SQL Server 2005 here, and for SQL Server 2008 and higher here.

SQL Server 2005 and higher also contains the Profiler, which is an improved version of the previous tool.

SQL Server 2005 retained the osql and bcp tools, but osql is being phased out in favor of a new command line tool called sqlcmd. The sqlcmd command-line tool is the preferred method of working at the command line in SQL Server 2008 and higher.

Starting with SQL Server 2008, Microsoft created a PowerShell provider for SQL Server 2008. If you’re new to PowerShell, you can read this series to find out more. I tend to use PowerShell for almost all of my day-to-day administration and management tasks. It works with almost all versions and editions of SQL Server.

OK – those are the tools provided by Microsoft, and I would argue that you can do just about anything you need with your system with those delivered tools alone. In fact, among all of the database systems I've used over my 20+ years in technology, they are easily the best.

However...there are some other tools, some from Microsoft, some from other places, some free, and some not so free, that are incredibly useful. I certainly haven't covered every vendor’s products that work with SQL Server, but I do post reviews of tools that I am familiar with here from time to time. Some of them are my favorites, based on the job I need to get done. Others are useful, but I'm not sure I'd pay extra for them. All of them come in trial versions, so I recommend you read the reviews, download the product if it looks interesting, and try it yourself. I always include a link to the product in the review. I’ll keep them as up to date as I can, but you should always take a look at the vendor’s site to ensure you’re looking at the latest versions.

Tools for Database Design

You can create paper-and pencil data models, but it is far faster to create a design using a graphical tool, and it's even better when those tools can generate the actual objects from the design.

What you want to look for in this kind of tool is the ability to quickly design a simple set of objects, and be able to make the design more complicated later. You want to be able to design in at least two phases: logical and physical. You might also want to look for a tool that lets you design conceptually as well, which is one level above them all. In any case, the tool should use standard notations, such as Unified Markup Language (UML) or Entity Relationship Diagrams (ERD), or both.

This is the area where Microsoft is lacking the most. There are no database design tools in SQL Server (the Database Designer in SSMS not only doesn’t count, but can be harmful), so you’ll need to look elsewhere for tools that can help you design a database. I’ll review the ones I find online in this section. At the moment I use either Visio (another Microsoft, but not SQL Server, product), Quest Software’s Database Designer, or CA’s community release of ER-WIN. There are a few open-source programs I’ve tried for this purpose, but their quality isn’t always that great and they seem to come and go quite frequently.

Tools for Maintenance

Microsoft includes most of what you need to maintain a system right in the product. But if you have to maintain more than one server, or if you have other database platforms that you have to maintain, then you may need to search for something that will do these tasks.

Maintenance on your database system involves proactive actions that you take and reactive actions. On the proactive side, you need to take regular backups of the database and the database log files. You can do that using osql, sqlcmd, PowerShell, Enterprise Manager (EM) and Query Analyzer (QA in SQL Server 2000 and earlier) and SQL Server Management Studio (SSMS in SQL Server 2005 and higher). I’ve covered that process, and indeed all of the basic maintenance tasks here.

You also need to maintain your indexes, as shown in that checklist. You can do this with any of the command-line or graphical tools you get right in the box.

There are also reactive tasks you need to do — which I’ve outlined in that same checklist. Once again you can use all of the graphical or command-line tools, and in the case of reviewing the SQL Server “Error Logs” (even though they hold more than just errors) you can even use Notepad, since it’s a series of text files.

So everything you need to do for maintenance for a SQL Server Instance is right in the box. But you can also use third-party applications for some of these tasks, and some are better suited for multiple Instances than SSMS or EM is. The big question to ask about these tools is how they track the release cycles of SQL Server. If they don't keep up with Service Packs or hotfixes, you can get into trouble with them. Also, you want to make sure they can handle mixed Service Pack environments, since you'll certainly run into that in larger shops.

Performance Tuning

This is one place that you can always use a little help. Microsoft has a lot of helps in the later versions of the tools for this function, and third-party vendors go much further as well. It’s probably the single-most exploited toolset for vendors in the environment. I have an entire section of this guide devoted to this process, and in that section I explain the tools you have for performing this task. With the right knowledge, you can use the command-line or graphical tools to investigate and record the performance of your system. I’ll focus on the third-party tools in this section.

You can't apply any of these tools blindly — regardless of how good they are. There is no substitute for understanding how a system works, and where you need to tune it. But these tools are like having more than one kind of saw in a woodshop — sure, they all cut wood, but how they do it can make a difference.

I normally check a system using several tools, and then see where they all agree. Those are areas that you can usually trust, and then you can investigate the areas that the tools point out differently.

Automation

SQL Server comes built-in with an automation system, called the SQL Server Agent. But once again, if you want to manage multiple systems, or work across platforms, a good job system can really help.

As I've mentioned for other products, make sure that the vendor you're looking at keeps pace with various Service Pack levels. Microsoft will often fix something with a Service Pack that can change the behavior of an element of the job scheduling system. This recently happened to me when here in the U.S. the government, in its infinite stupidity, messed around with Daylight Savings Time, which is another infinitely stupid concept from the government. Microsoft patched the system to recognize the change, but the job system I used had already accounted for the change, throwing me off by an hour. That might not seem like much, but in a 24x7 shop, one hour can be a really big deal.

Configuration and Reporting

There are about 50 reports in SQL Server 2005 and higher that can tell you everything you want to know about your system, but they are only reports, they cover only one server at a time, and they don't make any recommendations. This is another area where a good documentation tool can be essential. You'll use this documentation for everything from growth management to planning to system recovery.

I’ll cover several tools that you can use for reporting on SQL Server Instances, and you can of course create your own reports using PowerShell or Reporting Services.

For configuration, you can use any of the command-line tools or graphical tools. For more Instances and their objects, you can set configuration by simply right-clicking an object and selecting “Properties” from the menu that you see.

Disaster Recovery

Whenever you have a disaster, you will most likely use the standard tools that come with SQL Server, unless you have implemented a third-party backup and restore product. I won’t cover many of those here, since they are highly dependent on the version, edition, features and so on that you have installed. Also many of them work with certain kinds of hardware. I will cover any tools that I can test and show here on the site.

So there you have it. For the most part you’ll find everything you need to monitor and manage SQL Server right in the box, but there are vendor tools that can help — and I’ll detail the ones I use right here in this section.

Books and eBooks

From the team that brought you the official documentation; the book Inside SQL Server 2005 Tools shows you the ins and outs of the management tools.

I would be remiss if I didn't recommend my own books here on InformIT that detail how you can use the tools I talk about.

Online Resources

Microsoft has published several tutorials on their tools, which you can find here for SQL Server 2005.