New Tools from the BackOffice 4.5 Resource Kit

Ron Talmage

No, BORK doesn’t have anything to do with Star Wars and isn’t likely to come anywhere near grossing as much as the latter, but in this month’s column, Ron shows why you’ll want a copy.

Microsoft has released the first major "supplemental" package (as opposed to Service Pack, which was available only in beta form as of late May) for SQL Server 7.0 with far less fanfare than it deserves. Many of us are familiar with the older BackOffice Resource Kit (or BORK, for short), which provided a set of useful tools for SQL Server 6.5. The new BORK 4.5 gives us additional SQL Server 7.0 documentation as well as a set of valuable tools.

BORK 4.5 is distributed by Microsoft Press, not Microsoft itself, and it comes in a box that contains four large paperback volumes and two CDs. One of the CDs has the same volumes online and a set of software tools; the other CD is the evaluation version of SQL Server 7.0. BORK 4.5 covers SQL Server 7.0, Exchange, SMS 2.0, and SNA. In this column, we’ll tour the SQL Server part of the kit.

Two products in one

BORK 4.5 is really a "two-fer" since it contains rather independent items–a 700+ page volume called the SQL Server Resource Guide and a CD with an assortment of SQL Server tools and documentation. The Resource Guide has a number of chapters that supplement the Books Online (BOL), and I find it valuable as a narrative alternative to BOL, but there’s also an online version on the CD. The Resource Guide isn’t, shall we say, tightly coupled with the CD’s software tools. It only has a two-page description of the kit’s tools, so you need to consult the tools’ own Help and readme files for documentation. Those starved for additional documentation will prize the Resource Guide, but for me, the real story concerns the software tools that come on the CD.

BORK’s tools

After you install the resource kit, you can access the software tools through an MMC snap-in called the "Tools Management Console." Both the MMC console and the tools Help file list the tools alphabetically, giving them something of a miscellaneous feel. Let’s take a look at all of the tools grouped according to their function.

The tools’ Help file makes it clear that these tools were developed for internal use by Microsoft and aren’t supported (translation: Use them at our own risk). It’s always possible, therefore, that a tool that looks promising right now might be found out by experience to be less useful. It will take time for the SQL Server community to build up enough experience to decide which tools are the most valuable.

1. Data-related tools

The CD contains three tools related to SQL Server data: two that generate sample or test data and a third that estimates table size. You can use both DataSim and DbGen to generate sample data. With DataSim, you write VB scripts in text files to generate the data according to a set of rules. Samples are included with the utility. With DbGen, on the other hand, you specify a table without constraints and DbGen will fill it with random data. The difference between the each tool is the control you have over the results: You can make DataSim produce data following certain rules and masks that you define in the scripts, whereas DbGen generates random data based on a number of distribution patterns that you can choose. I had difficulty getting DbGen to work on some machines, so I e-mailed the author of the utility at Microsoft. He found out that installing VB, even temporarily, might be necessary to make it operate. You can use the third data-related tool, the DataSizer Excel worksheet, to estimate table and index space size.

2. Transact-SQL tools

I think the Log Shipping tool will be the most useful. "Log shipping" is Microsoft’s own internal phrase for the process of periodically copying transaction logs from one SQL Server to another automatically–you "ship the logs," so to speak, from a primary server to a standby server. Building a warm standby can be a very difficult problem, and thus far SQL Server administrators have had to create their own custom solutions. With log shipping, Microsoft has provided us with a way to jump-start the process.

You use scripts to create new tables and stored procedures in the standby server’s msdb database. After you restore a full database backup to the standby server without recovery, you execute the stored procedures on the standby server. The stored procedures create jobs that make previously undocumented calls to SQLMAINT.EXE. This is the same utility that’s invoked by jobs created by the Database Maintenance Plan. These jobs cause the standby server’s SQL Agent to scan for, copy, and then apply the primary server’s transaction log backups to the standby server. Log shipping only copies and applies transaction log backups; you must apply the full database backups yourself.

This will be an extremely useful utility if it will work in practice. Since it’s the tool Microsoft uses internally to support warm standby servers, it should be well-tested. I plan to install it soon in a production environment, and I’ll report later how it goes.

Other T-SQL utilities include two extended stored procedures, xp_timer and xp_sync. You can use xp_timer in your SQL Scripts to calculate the number of seconds from one time to the next, in either integer or float format. You can use xp_sync to synchronize tasks between SQL Server sessions, or between a session and a batch file using the bsync utility described later in the article.

You’ll also find Qr_tsql.doc, a Word document that contains "railway syntax" descriptions of SQL Server’s ANSI compliant T-SQL commands, and a script for demonstrating a cycle-type deadlock.

Finally, the SQL Converter is a VB application, with source code, that reformats T-SQL queries into a string that you can place in your VB programs.

3. Load and stress tools

BORK 4.5 supplies a couple of tools that are old friends: version 2.3.2 of SQLLS.EXE, the SQL Load Simulator, and SQLHDTST.EXE, the SQL hard disk stress utility. The SQL Load Simulator creates many virtual users on a single computer, all querying a SQL Server. You can use the virtual users to simulate a load of many actual users. Each thread connects to a SQL Server and runs a SQL script that you specify. For an in-depth explanation of the utility, you can do no better than consult the utility’s author: Mitch van Huuksloot (see his "SqlLS 2.2: SQL Load Simulator" in the July 1998 issue of SQL Server Professional). You can use SQLHDTST.EXE to stress a hard drive in order to find out potential disk problems. This utility has been around for several years, and it’s now somewhat obsolete because it reads and writes 2K pages to SQL Server 6.x devices. At Microsoft’s May TechEd ’99, I learned that the Microsoft SQL Server group has a newer and more powerful tool that they might release to the public.

4. Trace tools

There are several tools for reading trace files that you create either with SQL Profiler or with the extended trace stored procedures. READTRC.DLL (the Tracereader) is a COM-based SQL Server trace file reader that you can drive from VB or C++, useful in cases where trace files are too large for Profiler to handle. There’s also an OLAP Trace Table Analyzer called TraceAnalyzer.EXE. It analyzes the calls made by OLAP services to SQL Server. Finally, a set of T-SQL scripts demonstrates how you can bypass the Profiler and create your traces using the trace extended stored procedures.

5. Command file tools

Three small executables provide SQL Server-related tools that you can call from command (that is, batch) files. You can use QSVC.EXE (short for "Query Service") to query the status of a local machine’s service, and RSSM.EXE (short for "Remote Service Control Manager") to query the status of a remote service. You can use BSYNC.EXE (the "Batch Synchronization Utility") to create events that can synchronize command files. You can use BSYNC.EXE to create events and then use xp_sync (see the "Transact-SQL Tools" section) to synchronize T-SQL scripts with command files.

6. Browsing SQL namespace objects

The SQL Namespace Browser (BROWSE.EXE) utility is in a class by itself, and, along with the Log Shipping material, is a standout. You can use it to inspect the SQL Namespace objects on a SQL Server. The browser’s tree control dialog box allows you to navigate the Namespace objects, and the right pane of the browser shows the associated dialog boxes of the object. You can then run a command and invoke the dialog box or wizard. Microsoft’s Gert Drapers demonstrated the tool at TechEd ’99–showing how you can also use it to generate VB source code for invoking the Namespace object.

7. The DB-Library gateway

A final utility–undocumented in the tools’ Help file–is DBLibGateway, which provides RPC services from SQL Server 7.0 to applications making calls to the ODS 6.x interface. Unfortunately, since it uses DB-Library, clients of the application can’t access the new data types of SQL Server 7.0.