SQL Server Reference Guide

In a previous tutorial, I explained how important automation is to the database professional. Automation speeds tasks, can be scaled across multiple systems, and reduces errors. It’s such an important concept that Microsoft includes a tool within SQL Server that provides an automation process, called the SQL Server Agent. It combines steps of work and schedules into something called a “job”, and job information is logged into tables in the msdb system database. In fact, the jobs, steps, schedules and logs are all stored in the msdb database, making the system completely database-driven. That combines with a Windows Service to run either on-demand or through a scheduling mechanism.

In some cases, however, you don’t have access to the SQL Server Agent. The “lower” editions of SQL Server (MSDE, Express and CE) don’t have the Agent enabled. In the most recent versions you will in fact see the components there, but you won’t be able to start the service or use the Agent. In other cases you may not want to use the SQL Server Agent, because you have another automation system that you want to use for more than just SQL Server, you don’t have enough privileges to run on the system, or to have more granular control of the system.

If that’s the case for you, there are options. I’ll explain various tools you have at your disposal, but first I think it’s important to understand what sections of the automation services that the SQL Server Agent provides are useful to duplicate. You don’t have to use all of these, but understanding why they are valuable is important to your final architecture.

I’ll also mention here that I won’t cover third-party automation features. I have a section here at InformIT dedicated to talking about software that doesn’t come “in the box” – meaning in the Windows Operating System or with SQL Server itself. Also, vendor products change quite frequently, so it makes sense to read up on what they offer directly from their documentation. You should, however, read this tutorial to ensure you know what you’re looking to automate, and the components of that automation that you care about.

Replacing the Components of SQL Server Agent

The first place to start is to break out the functions within SQL Server Agent (or any automation feature, actually) and list what benefits they provide. This serves as your “specification” when you write your replacement code. I say “write the code”, even though the choice you make might be graphical in nature.

After you detail the components you want to implement, you can choose the mechanism to code. Keep in mind that you might end up with a “mix and match” scenario – not all mechanisms are suited for both code logic and scheduling or logging, for instance.

The components that I took away from SQL Server Agent are as follows:

• Steps
• Schedules
• Security
• Logging

Let’s examine each one of these components.

Steps

Within SQL Server Agent Jobs are the atomic bits of the system called “steps”. These units, which can be anything from Transact-SQL (T-SQL) statements to PowerShell scripts (in version 2008 and higher) are the basic units of work for your system. While this is the most important section of your system, it’s the most problematic, because most often you’re looking to work with more than just SQL Server in an automation process.

For instance – you may want to back up a database, run a ZIP operation on the file to compress it, copy that file to a new location, and delete files older than today’s backup (or some other interval). While the first part of that process is something that you can do easily in Transact-SQL, the rest is better done at the Operating System level. So when you are making your choices, you need to keep in mind the breadth of operations you need to perform. This is also the place where you think about using more than one mechanism to create your system.

In SQL Server Agent, the Steps have basic branching functionality. For instance, you can have a Step serve as an exit for the Job, continue on success or do something else on failure. You’ll need to consider this as well for your replacement, although I normally consider error handling and flow control as two separate functions. In any case, make sure your system handles this function.

Schedules and Security

As I created my early SQL Server Agent replacement systems years ago, I considered these two functions separately. Schedules dealt with time, and Security had to do with which “principal” account ran the equivalent of a job. As time went on, I learned that the scheduling process often determined the security account, and so I started to think about them together. Even so, security has layers, and your system should as well. Even if you have security set on the scheduling system, if the mechanism for each step can send credentials, you can have each step use the proper level of security as well.

But I’ll consider the scheduling mechanism first. It’s difficult to separate the function from the actual tool you’ll use to schedule things, so I’ll leave until later the discussion of the scheduling feature in the Windows Operating system. The keys are that you think about the ability to schedule based on a re-usable basis (Weekly, Daily at Midnight, that sort of thing) and a specific, ad-hoc, one-time schedule (like Thursday the 10^th at 11:00).

Within your scheduling decision think about the granularity of the time unit. For instance, do you want to schedule the whole “job”, or logical package, or each step? If you schedule the job, the steps can be chained together such that one starts after another. If you decide to schedule each step, you’ll need to “set state”, meaning a marker of some sort in memory or on disk, or as a software broadcast (called an “event”) that tells the next step where it is in the process. Let’s look at a concrete example.

Assume that your “job” has two simple “steps”. The first backs up the database, and the second performs an Index Reorganization. You don’t want those two events to happen at the same time. Both of these processes are T-SQL based, so they will work in a “batch” mode and not simultaneously – as long as they know about each other. So, of your mechanism allows for batch-like processing, then when the backup completes, the re-organization will start. That means you can schedule the whole “job” as a unit.

However, if your mechanism will launch both processes simultaneously (sometimes called parallel operations) then you could end up with a backup running while a re-organization happens. In that case, you might want to either create a schedule for both tasks, with enough time for one to complete before the other, or have the first task create a marker in memory or on disk or through an event notification that the second task checks to see when it can start.

Logging

I like a LOT of logging capability. My normal process is to turn on a lot of logging, and trim it if the process completes successfully. If there’s an issue, then I have the logging and I know what stopped and where.

Think about the granularity you want for your process. Some developers like to code for multiple levels of logging, meaning a “standard” or basic level where only failures are logged, an “enhanced” level where starts and stops of steps are recorded, and a “full” or “debug” set of logging takes place, detailing every operation. They code the process such that you can select the level of logging when you call the code. Personally I don’t find this as useful in automation since I can’t predict when something might fail, and that’s when I want a higher level of logging. I also don’t always want a complete level of logging, even when I am troubleshooting, since that much information isn’t helpful for me.

The simplest option for logging in most instances is to create a text file of the results of the operation. I use this option pretty regularly, but I try and opt for the Windows Event Logs (Application) if I can. It is a standard, always-available, easily read and secured option, and folds in with the other tools I use in the operating system. That way I have a single location to review for all operations on my server.

Options for tools

No matter what option you select, you’ll need the Windows Operating system. It provides the basis for all tools, of course, at least for SQL Server. But this isn’t a limitation. Windows provides a full scheduling engine, a logging facility, and a command-shell to run batch files.

For the scheduler part of my SQL Agent replacements, I use the Windows Task Scheduler.

Figure 1

It has a lot of richness, including security, monitoring, conditions, triggers and more. In fact, I find it more useful than the SQL Server Agent scheduler. When you locate this tool on your server, run it and press F1 for more help and a tutorial on how to create basic or advanced tasks. Again, depending on how you architect your solution, these tasks can either be conceptually treated as Jobs or you can split them up into steps. You’ll find that with the triggers and conditions you can do some basic branching and routing, emulating a job in a limited way.

As a second option, you can use the AT command at the command line. To find out its parameters, simply type AT /? and press RETURN or ENTER at the command line. You get a lot of options, but I find that working in the Windows Task Scheduler to be superior for setting up this kind of schedule.

SQLCMD, osql and isql

The first place you can start for the step logic is with the SQLCMD feature found in SQL Server versions 2005 and higher. SQL Server 2000 and lower include the isql command-line feature, which work in a similar way, but unless you don’t have access to SQLCMD, I don’t recommend you use those. The osql feature is similar, and should also be upgraded to use SQLCMD.

SQLCMD accepts T-SQL commands and passes them through to the Engine just like SQL Server Management Studio (SSMS). There are “switches” that control how that information is passed, security and more. To see a list of these switches, at a command-prompt type:

SQLCMD /?

You should get something like this back:

Microsoft (R) SQL Server Command Line Tool
Version 10.50.1600.1 NT INTEL X86
Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

That’s a lot of information, and you don’t actually need it all. Let’s assume for a moment that you’re working from a default instance of SQL Server where the databases are local, and your Windows account has access to that server. You can simply type two parameters to send a query to the server you’re on to retrieve the version of SQL Server you are running (or run a backup, or any other command you’re allowed to type):

SQLCMD -E -Q “SELECT @@VERSION;”

The –E sets “trusted” authentication, and the –Q (case matters) runs a query and gets out. Everything that is in between the quotes is the query.

But life is rarely that simple. Let’s assume now that you’re on a workstation, and you have an instance called INSTANCE1 on a server named SERVER1. You have a domain user called MyDomain\Bob with a password of Pass@word1. Here’s the same query, this time using a few more paremeters:

SQLCMD -U Mydomain\Bob -P Pass@word1 -S Serveri\Instance1 -Q “SELECT @@VERSION;”

I’ve added the -U (user name) -P (password) and -S (server and instance name) to complete the query.

Most of your steps, however, will be the kind that can be re-used. So you may want to store the actual T-SQL as a script, and have the SQLCMD program read that script for the work. And since it’s an automated step, you need to have somewhere to put the results, since you’re not looking at the screen when it runs. Two final switches are all you need.

Let’s assume now that you have a script called “CheckVersion.SQL” in your “Scripts” directory, and it has this line in it:

SELECT @@VERSION;

To run that query and put the results in a “VersionResults.txt” file in your “Results” directory, here’s all you need:

SQLCMD -U Mydomain\Bob -P Pass@word1 -S Serveri\Instance1 -i c:\Scripts\CheckVersion.SQL
 -o c:\results\VersionResults.txt

Assuming you actually have all of those directories, servers and instances with that name, and security correct, this will return the correct result. You can create .CMD files (Windows batch files) with this kind of information in it, schedule that through the Windows Task Scheduler, and you have the makings of a rudimentary SQL Server Agent replacement.

PowerShell

The problem with using only SQLCMD and the Windows Task Scheduler is that you don’t have great error control, branching and routing. Plus, while you are able to log what SQL Server is up to (with the -o switch) you don’t have insight into what the batch file itself is doing.

Enter PowerShell. PowerShell provides the richness of a programming language (I know, it isn’t a programming language) with the benefits of a batch file (I know, it isn’t a batch file) and the power of a command-shell (I know, it isn’t a shell). While it technically isn’t any of those, it comes very close, and certainly close enough to do all of your maintenance, performance logging and other tasks.

Not only that, PowerShell speaks “natively” to anything .NET - meaning it can read and write Windows registries and logs, work with SQL Server, across domains, through networks y mucho mas. You have better error handling, logging and branching, remoting and other programmatic features.

I’ve written a great deal about PowerShell and SQL Server, and using it and the Windows Task Scheduler you can actually accomplish more than with SQL Server Agent alone. Check the article series out here. Also check the links below for the SQLPSX extensions that make working with SQL Server and PowerShell very easy.

Coding with DMO and SMO

Although working with PowerShell and SQL Server technically involves the Server Management Objects (SMO) library, a lot of its complexities are hidden from you. I’m not a developer, but I have played one once or twice in my career. If you really want a robust, graphical, complete customizable solution, you may want to investigate learning more about SMO. I’ll post references to that at the bottom of this article.

The older version of SMO is the Database Management Objects library, or DMO. I have a series of articles here on InformIT on that library that you can also reference for that.

These are only three of the primary features you have available as a replacement for SQL Server Agent as an automation engine for SQL Server. There are others, such as Perl, RoboScript and many third-party scripting, coding and access programs that can “talk” to SQL Server and provide logging, error handling and more. The key is to clearly define the requirements you need for your system, line up your options, and test, test, test.

InformIT Articles and Sample Chapters

I have an entire series on PowerShell that will explain everything you need to know to get started with this powerful subsystem.

Books and eBooks

My friend Bob Beauchemin and his co-author Dan Sullivan cover the SMO library in greater depth in their book A Developer's Guide to SQL Server 2005. Check it out here.

Online Resources

The SQLCMD utility, along with a full tutorial, is explained here.