SQL Server Reference Guide

For many department size installations of SQL Server or smaller, you'll perform most of your management and maintenance tasks using graphical tools. For SQL Server 2000, that's Enterprise Manager and Query Analyzer, and for SQL Server 2005 you'll use SQL Server Management Studio and the Business Intelligence Management Studio.

If you have to manage both SQL Server 2000 and 2005, you can use 2005's Management Studio. If you have to manage not only multiple versions of SQL Server but also other Relational Database Management Systems (RDBMS) such as Oracle or MySQL, then you might opt for a third-party tool such as those from Embarcadero, Quest, or even some free versions such as AquaData Studio.

But all graphical tools suffer from the same weakness: it's difficult to manage more than one or two objects at a time from a single graphical screen. And jumping around from screen to screen takes time, something most of us don't have in abundance. Not only that, it's difficult to repeat the steps in a graphical process, especially if it's complicated. There are just too many risks that you'll miss something.

If you think that you really can manage a database using just a Graphical User Interface (GUI), try this experiment: add five users to five servers, each of which has five databases the users need to use, with different rights for each user. Track the differences between the systems before and after the change. Now mix the versions or even platforms and you'll see the difficulty involved in using a graphical system. And yet, this is the kind of thing many Database Administrators (DBA's) have to do every day.

So to properly manage multiple systems, especially if you're managing multiple versions or platforms, you need a repeatable, configurable system that can interfaces with each system in a consistent way. You need to run commands at the operating system level, and be able to change only the variables such as server or user names.

In the early days of computing, GUIs were designed for users, and not necessarily for administration. In fact, in the UNIX world, some server installations don't come with a GUI at all. They simply aren't necessary. I've interviewed for Oracle positions on UNIX systems where if I would have started a GUI to perform a task, I would have gotten a few raised eyebrows. I also wouldn't have gotten the job.

So if you're in the camp of having to manage a few dozen servers, or you have a complex environment, you have a few choices to make the best use of your time. In this overview I'll cover a few of the options you have. To be sure, there are dozens, if not hundreds of scripting languages you can use to manage your systems. I'll explain the options I'm most familiar with, and the ones I've seen in use from my days as a consultant.

Before I begin, I need to explain what I mean by the word scripting. For most technical professionals, scripting means to type in commands which will run at the command line. I agree with that definition, but I'll expand it a bit to include some graphical tools that string together objects representing workflow.

And I also need to spend a moment talking about that workflow. While some products can control your database servers, they don't often interface with the operating system well. In other words, they might be able to back up your database, but they can't then compress the backup file and copy it to another server, for instance. Since these kinds of things are part of the normal maintenance process, I only use processes or tools that can support multiple kinds of maintenance tasks. Not only that, but to make the maintenance truly automated, the process or tool has to be able to schedule the tasks to run.

Batch Scripts

This is the most basic kind of scripting solution. You combine a command-line interface for the RDBMS with a "shell" in the operating system to create your maintenance plans. Although it's basic, I still use it, especially in smaller configurations.

Every serious database system on the market (even Open-Source solutions) has a command-line interface. For SQL Server 2000, it's either isql or osql. For SQL Server 2005, you can use osql or sqlcmd. All of these command-line interfaces accept a single query or a text file containing multiple lines of Transact-SQL (T-SQL) commands. That means you can design all your maintenance using familiar T-SQL (or other SQL dialect) commands, save them to a text file, and then run them from the command line.

To meet the requirements I mentioned earlier, you'll have to pair the RDBMS command interface with operating system commands. SQL Server runs on the Windows operating system, and we're talking about the server versions for this discussion.

There are two "shells" that come with the Windows server operating systems. The first is the older COMMAND.COM program. It has limitations for scripting, so I don't use it. I use the newer CMD.EXE shell, by creating my shell scripts in text and using the extension CMD on the end, like this:

MAINTENANCE.CMD

Using this extension automatically invokes CMD.EXE instead of COMMAND.COM. You can run CMD.EXE by clicking your Start menu, then Run, and then typing CMD.EXE in the box that shows. To find out all of the options you have available, just type HELP and press ENTER.

For the scheduling component, I use the built-in AT command in CMD.EXE. You can find out more about that command by typing AT /? at the command line.

The advantages with this approach are the cost and familiarity. Everything you need is included with SQL Server and the operating system. Of course, there isn't a lot of flexibility here, and the scripts can get pretty long as you cobble together all of the functionality you need. I normally have to include several Resource Kit tools and some third-party utilities to do things like reading the Windows Event system. You also don't have a lot of error handling or other programmatic features, but if you need to distribute the solution to a wide range of similar servers and your needs are modest, this might be the best approach.

I've covered osql here, and sqlcmd here.

Where You Get It

You already have it. Each of these commands comes built in with the operating system and with your edition of SQL Server — even the free versions.

Job-based Scripting

The purists might have a problem with these kinds of tools being included in a scripting article, since you can't call them from the command line or interact with them from other programs. But in the real world of the DBA, a job-based system can be the right answer, and they meet the requirements of being able to do things other than SQL Server tasks, with notifications and operating system access. There are four main types of job tools I've used.

SQL Agent Jobs

The first is the SQL Server Agent Job. Jobs are contained within SQL Server, and the SQL Agent that controls them runs as a service on the server. Because of that you can access drive letters, network shares, even mail systems.

Jobs include steps, which can be anything from operating system commands to Transact-SQL statements. They have rudimentary process flow, can access mail or other notification systems, and even have some minor error-handling built in. They are logged events, so you can track them. And you can string them together to make a chain of events. If most of your tasks lie within SQL Server, or you need to ship the scripting (jobs) to various SQL Servers, SQL Server Jobs might be the answer.

I've covered this feature here.

Where You Get It

You already have it. It's built in to SQL Server 2000 and 2005.

Data Transformation Services

2000's Data Transformation Services (or DTS). This system lives within SQL Server in Enterprise Manager. You open the tool, create a Package, which contains steps that reference objects. There are lots of DTS objects, from e-mail to operating system calls, all the way to the "data pump" that imports or exports data. In one shop I worked at we used hundreds of DTS packages.

DTS isn't the end-all solution, however. There are some issues with calling things "out of process," and if you don't put lots of logging in place, you can quickly get lost if one package calls another.

I've covered this feature here.

Where You Get It

You already have it. It's built in to SQL Server 2000 and 2005 can run packages (with caveats) created on a 2000 server.

SQL Server Integration Services

2005 introduces a new product, which isn't really an update to Data Transformation Services. It's a whole new product, not an upgrade to DTS. SQL Server Integration Services (SSIS) is far more powerful, has more robust error tracking, better data handling and a fully interactive model.

The disadvantages are that SSIS has a steep learning curve. Although it is based on graphical objects, there are a lot of properties to set, and many DBA's don't properly document the process.

I've covered this feature here.

Where You Get It

You already have it, if you have SQL Server 2005.

Third-party Job Scheduling Tools

Several companies have job tools for SQL Server, both versions. I've worked with Idera and Embarcadero. Some, like the solution from Idera, just manage SQL Server jobs. Others create their own jobs, and have SQL Server extensions. Microsoft has another solution called the Microsoft Operations Manager that is very interesting as well. I've only worked with it briefly, but it is very powerful, and can run not only operating system commands but control UNIX servers as well.

You'll have yet another learning curve here, which may not be very transportable. If you learn one system they may not use it at the next shop you work at. Not only that, these solutions are not cheap.

Where You Get It

From the respective vendors. Idera is here, Embarcadero is here. Microsoft MOM is here.

Programming Languages

I'm grouping a lot of scripting options as "programming." What this means is that you need to have a little experience with programming to make the solution work, and you'll need to code your own scheduling system. You may also need to compile the completed script into a binary format, which means you've lost some of the openness of scripting.

Bridging these gaps are Visual Basic Scripting (vbscript) and the C-Scripting language (cscript). These languages run on Windows platforms and five you access to both plain-text scripting and programming-like options. This is the solution I've used most often in Windows-only shops.

There are a lot of advantages here — you have access to full programming constructs, and Microsoft even has free versions of their languages and tutorials for them. Programming languages also have full access to the Application Programming Interfaces (API's) on the servers, almost all can access the Windows Management Instrumentation (WMI) system. Although WMI has a learning curve as well, it is extremely powerful.

You can use programming to access SQL Server through the Tabular Data Stream (fairly rare), through Open Database Connectivity (ODBC) calls, using Data Management Objects (DMO) or even the new Server Management Objects (SMO). All of these can both control the server and access data. The sky is the limit.

I've covered vbscript with DMO here.

Where You Get It

You can get the Express (free) versions of Microsoft's programming languages here. You can find out more about vbscript and cscript here.

Scripting Languages

In this section I'm grouping a lot of languages that were invented specifically created for scripting. The lines are blurred a little here with programming languages, since technically they are both programming. Sort of.

What I mean by that is that scripting languages usually bend the programming rules a little, to make for easier development. They often (but not always) don't implement object-oriented designs, and most don't come by default with a full suite of programming constructs, like robust error handling. To be fair, you don't often need that, since the scripts are used by a single technical group of users. You'll also most often test your own scripts repeatedly before you put them into production — if you're smart about it, anyway.

Perl

By far one of the most popular and ubiquitous scripting languages is Perl. Perl is available on almost every platform you can think of, making it an incredibly portable solution. Perl is also very powerful, and is one of the closest scripting languages to programming around. But that also means that it can have a pretty steep learning curve.

Perl has one of the easiest database connection methods around. You simply include the layer you want to access the database with such as TDS or ODBC, and then use the DBI construct to connect to the database. You can use T-SQL scripts or with a little work you can implement DMO or SMO to also manage the server.

Another advantage/disadvantage with Perl is that the commands are VERY short, and can be strung together is a bewildering array of options. Whitespace (spaces or tabs) can be included or not in between keywords, and many seasoned script-writers create a single-line of code that can perform hundreds of steps. Seriously. Some see this as an advantage since they can write very compact code. The rest of us can find this practice really hard to read. Since I intend to share my scripts, I tend to include the whitespaces, indenting and comments to help others use them.

I've worked with Perl off and on throughout my career, especially when I used an Oracle system. If you're willing to put in the time to try and learn it, you can take the knowledge with you anywhere. I find it to be most powerful in large installations and mixed systems.

Where You Get It

You can get Perl for free here.

Powershell

Microsoft figured that there just weren't enough scripting languages on the market, so they've made a new one. Powershell is in fact a new shell, or complete environment, which has access to all of the .NET objects on the server, from the operating system to the database. You can use it with WMI, DMO, SMO, ODBC and just about any other database connection method. It will also be built-in to the next version of the Windows Server operating system.

Powershell works with a verb-noun form of scripting. For instance, to find out the capabilities, you just type get-help. Most object reads start with get-, and most actions start with set-. Other than learning to work with these commands, you'll need to learn about objects and redirection. For instance, to find something you perform a get- command on the object and pipe (with the | symbol) the result to a search command. It's a little strange, but after a while you get used to it.

Microsoft has documented the language well, and you can even get free books on it. I'm trying hard to love Powershell, since it's clearly here to stay. It has a lot going for it, with full access to every part of your system. If you're staying within a Windows-only shop it's worth learning.

Where You Get It

Download it here, for your particular operating system. Check back often, since it seems to be updated frequently.

InformIT Articles and Sample Chapters

For more information about Windows scripting, check out this book.

Online Resources

There are several important links that I pointed out within the body of this tutorial. For more about scripting in general, check out this link.