Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 22, 2002

Microsoft SQL Server 7.0 Administrative Utilities

By Alexzander Nepomnjashiy

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.

Query Analyzer - This utility is used for the execution of Transact-SQL scripts in dialog mode. Users enter Transact-SQL operators, execute them and then see the DBMS answers in an output window.

Query Analyzer is an extremely useful too for clearing up the ways SQL Server interprets and executes Transact-SQL code. The DBA can:

  • See a graphical picture that displays the proposed Transact-SQL query execution plan and T-SQL execution performance statistics;

  • Use the Index Tuning Wizard for:
    • Finding the best mix of indexes for a database;
    • Analyzing the effects of the proposed changes, including index usage, and queries distribution (among tables); and
    • Ways to tune the database for a set of problem queries.

Collected statistics can be viewed in different ways, including views, formats, grids, etc. Query Analyzer has a built-in editor with:

  • Highlighted (by color) reserved T-SQL syntax; and

  • Context-sensitive T-SQL syntax help (in the editor pane, select a Transact-SQL statement, function, stored procedure, or other Transact-SQL element, and press SHIFT, F1 to view information about the selected text).
Server Network Utility - This is SQL Server's own (in comparison to the Client Network Utility) network libraries configuration utility. This tool allows administrators to define a set of network libraries that all clients use to connect to the server. The DBA can edit active network libraries, deactivate them, or activate support of additional ones using the Server Network Utility.

Service Manager - This utility is used for controlling the state of Microsoft SQL Server 7.0 services (MSSQLServer, SQLServerAgent, Distributed Transaction Coordinator, OLAPServices) allowing you to start, pause or stop them.
Books Online - This is not an administrative utility in the full sense of the word, but it is the electronic clone for the original, printed documentation on Microsoft SQL Server 7.0 software.

See All Articles by Columnist Alexzander Nepomnjashiy

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM