SQL Server Management Tools

[Previous] [Next]

A quick scan of the SQL Server 6.5 and 7.0 program groups will reveal that many tools are installed to assist the SQL Server administrator in maintaining the SQL server. Unless you are the SQL Server administrator as well as the SMS administrator or you get the necessary education to fully understand and appreciate the product, you will probably use only two or three of these tools. The SQL Server 6.5 program tools are described in Table 18-1; the SQL Server 7.0 program tools are described in Table 18-2.

Table 18-1. SQL Server 6.5 program tools

Tool	Description
ISQL/w	Graphical query interface used to execute maintenance tasks by running Transact-SQL statements or stored procedures.
Microsoft ODBC SQL Server Driver	Online help for configuring and using the Microsoft ODBC SQL Server driver.
MS Query	Graphical interface designed to allow users to build SELECT query statements against any ODBC-compliant database, including SQL Server databases. SMS 2.0 does not allow access to the database in this manner, as it uses the WMI exclusively. Database access can be achieved outside the SMS Administrator Console through any Web-Based Enterprise Management (WBEM)ODBC-compliant application such as Crystal Info 7.0 or Microsoft Access 2000.
Readme.txt	Provides documentation to supplement that included in the Books Online tool.
SQL Client Configuration Utility	Used to set the default server connection and Net-Library configuration for clients.
SQL Distributed Management Objects	Online help outlining the SQL Server Distributed Management Object (SQL-DMO) model.
SQL Enterprise Manager	Graphical interface that facilitates the configuration of the SQL server and the management of SQL Server databases, including devices and databases, space usage, backups, and restores.
SQL Performance Monitor	Runs Windows NT Performance Monitor with performance objects and counters specific to the SQL server.
SQL Security Manager	Enables management of user access to database objects.
SQL Server Books Online	Provides an online version of the documentation set SQL Server Books Online with full searching capabilities.
SQL Server Web Assistant	Converts SQL Server data to HTML format.
SQL Service Manager	Starts, stops, and pauses the SQL Server, SQL Executive, and Microsoft Distributed Transaction Coordinator (MS DTC) services.
SQL Setup	Accesses the setup program used to reconfigure, rebuild, or remove SQL Server.
SQL Trace	Similar to SMS Trace. Used to monitor statements, procedure calls, and so on sent to the server and to follow the corresponding activity of the server.
Books Online	Provides an on-line version of the documentation set SQL Server Books Online with full searching capabilities.
Client Network Utility	Used to set the custom server connection, DB-Library, and Net-Library configuration for clients.
Enterprise Manager	MMC snap-in that facilitates the configuration of the SQL server and the management of SQL Server databases, including devices and databases, space usage, backups and restores, permissions, data import and export, and so on through object-level folders.
Import And Export Data	Launches the Data Transformation Service Wizard, designed to facilitate the import, export, and conversion of data from various data formats.
MS DTC Administrative Console	Launches the MS DTC Administrative Console.
Performance Monitor	Runs Windows NT Performance Monitor with performance objects and counters specific to the SQL server.
Profiler	Used to monitor server events, procedure calls, and other real-time server activity; Profiler can also filter events and direct output to the screen, file, or table.
Query Analyzer	Graphical query interface used to execute maintenance tasks such as Transact-SQL statements or stored procedures.
Readme.txt	Provides documentation to supplement the documentation included in the Books Online tool.
Server Network Utility	Used to configure SQL Server to use Net-Libraries and to specify the network protocol stacks on which the server will listen for SQL Server clients' requests.
Service Manager	Starts, stops, and pauses the SQL Server, SQL Server Agent, and MS DTC services. Also implemented as a taskbar program.
Uninstall SQL Server 7.0	Initiates the Uninstall routine for SQL Server.

Many SQL Server maintenance tasks specific to the SMS database can be configured and scheduled to run through the SMS Administrator Console, including a complete site server backup (see Chapter 17). Database backups can also be effected by using a third-party backup program capable of including SQL Server databases as part of its backup routine. Consequently, as an SMS administrator, you are most likely to use Enterprise Manager to perform any additional or advanced maintenance tasks. Through this interface you can create database devices, manage space usage, configure the server, schedule events, backup and restore the database, and so on.

You can also use the SQL Server 6.5 ISQL/w tool or the SQL Server 7.0 SQL Server Query Analyzer to execute maintenance tasks such as those described in Chapter 17. You can use the SQL Server Security Manager to provide a more granular level of security to the SMS database through SQL Server, and you can use the Performance Monitor tool to analyze server performance and resource allocation.

As with most Microsoft BackOffice applications, when you install SQL Server several performance objects and counters are included to assist you in evaluating the ongoing performance and resource use of your SQL server as well as facilitate the troubleshooting of specific performance-related problems. To view the available SQL Server performance objects and counters, you can start the Windows NT Performance Monitor utility.

However, both versions of SQL Server include a Performance Monitor tool. This tool is linked directly to the Windows NT Performance Monitor and launches a Performance Monitor chart that tracks common counters. Figure 188 shows an example Performance Monitor chart generated using the SQL Server 6.5 Performance Monitor tool, and Figure 18-9 shows an example Performance Monitor chart generated using SQL Server 7.0's Performance Monitor tool.

Figure 18-8. Sample SQL Server 6.5 Performance Monitor chart.

Figure 18-9. Sample SQL Server 7.0 Performance Monitor chart.

These are live Performance Monitor sessions and can be modified to display additional counters, change the intervals, switch to Log or Report view, and so on. These charts can be helpful as you perform your periodic database maintenance tasks (described in Chapter 17). For example, the SQL Server:Database object has a counter called named Data File Size (KB) which will help you monitor the cumulative size of your databases, such as tempdb.