Microsoft SQL Server 7.0 Administrative Utilities

August 22, 2002

In my latest article, I'm going to briefly describe some useful database server administrative utilities for Microsoft SQL Server 7.0. Most of them have a graphic interface, although some of them also have text clones (more on these in my next article).

Client Network Utility - A Microsoft SQL Server 7.0 client configuration utility that allows administrators to define database sets, network libraries (Network Library and DB-Library) and procedures for database operations, fulfilled by a given client system. A SQL Server DBA can use this tool to specify the default network library used to communicate with the server, change the server aliases SQL Server clients connect to, or modify any parameters associated with the connection address for the network library configuration.

Enterprise Manager - The centralized management console component of the Microsoft Management Console (MMC). Enterprise Manager allows a DBA to administer different types of servers (for example: Microsoft SQL Servers, Microsoft Transaction Servers, Microsoft Message Queue Servers, Microsoft Internet Information Servers, Microsoft SNA Servers, etc), all from a uniform console. The console makes it possible for an administrator to control and maintain all the servers in a global, enterprise-wide company network.

SQL Server Enterprise Manager - The primary administration tool for the database server. Enterprise Manager presents all the SQL Server objects in a hierarchical components tree, allowing the DBA to: 1) Register servers and server groups, 2) Configure all the server configuration options, 3) Manage databases and their object (tables, views, procedures, triggers, constrains, user logins and server-wide / database rights, etc), 4) Define and fulfill administrative tasks on each registered server, and 5) Call different programs - wizards.

Import and Export Data - The Data Transformation Service wizard allows you to import, export, check and transfer data among different OLE DB and ODBC compatible data sources.

Performance Monitor - This tool is intended for Microsoft SQL Server 7.0 real-time performance monitoring (as a customized Windows NT Performance Monitor), viewable as a graphic, report, or log (you can export server activity to a spreadsheet -- Microsoft Excel, for example -- for the subsequent analysis). Warning alerts can also be set up with this tool -- the alerts are fired when the measured metric becomes higher or lower then any pre-defined value. Automatically generated warnings can be tracked, and, when they fire, SQL Server can execute some corrective action in reply to them.

SQL Server defines 16 performance objects, detailing its performance. For example, the object SQL Server:Databases includes these counters: Active Transactions, Transactions/sec, Percent Log Used, Data File(s) Size, etc., each with a separate counter for each existing database. It is also possible to create performance objects for each user. With the appropriate rights, the SQL Server administrator can even monitor several database servers at once.

Profiler - This program is intended for SQL Server events «interception». SQL Server Profiler makes it possible to generate data describing each event and presenting changes in: 1) Events; 2) Data of each event; 3) Events grouping; 4) Events filtering; etc.

Intercepted events can be shown on the screen or recorded to file. Recorded files can then be played back, for example, on a development server rather then on a production server to analyze its behavior.

Also, this file (with its recorded events) will probably be used as a reference workload by the DBA to determine the use of the Index Tuning Wizard for indexes and customization of other database objects (for reaching maximum performance). SQL Server Profiler can be used for:

• Watching SQL Server activity in context of applications and users;
• Debugging stored procedures;
• Determining the «worst performing» queries; and
• Deadlocking reasons identification.