SQL Server Utilities and Extensions | Inside Microsoft SQL Server 2000

SQL Server also includes utilities and extensions that provide increased functionality, such as Internet enabling, monitoring capability, easy setup, and easy data importing. (For in-depth information on any of the following utilities and extensions, see SQL Server Books Online.)

Web Assistant Wizard and Internet Enabling

SQL Server provides dynamic ways to work with the Internet using Web Assistant Wizard and interoperability with Microsoft Internet Information Services (IIS). Although both Web Assistant Wizard and IIS enable SQL Server data to be used with Web pages, they satisfy different needs.

Web Assistant Wizard generates HTML files from the result sets of SQL Server queries, making it simple to publish SQL Server data on the Internet. Let's say, for example, that a parts supplier keeps its inventory list in SQL Server. The supplier can publish its current parts inventory as a Web page (an HTML file) using Web Assistant Wizard. The wizard lets you submit an ad hoc query or stored procedure, do some simple formatting, include links to other Web pages, and use a template for more advanced formatting. The output of the query is written as an HTML table, and a Web page is created. You can also use Web Assistant Wizard to automatically update the Web page at regular intervals or whenever the data changes (via a trigger).

Web Assistant Wizard is distinct from but complementary to IIS in BackOffice. With the wizard, users browsing the Web page work separately from SQL Server because the data on the Web page has been extracted from the database beforehand. The wizard doesn't use or require IIS, and pages that the wizard generates can be viewed using any Internet browser.

IIS uses SQL Server's high-performance native ODBC interface to allow SQL queries to be fired from a Web page when a user accesses a particular region on the page. The results are then dynamically retrieved and combined with the HTML file for up-to-date viewing. In addition to Web Assistant Wizard and the dynamic query capabilities enabled with IIS, SQL Server is Internet-enabled in several other important ways. By minimizing network traffic and handshaking, SQL Server is inherently designed for efficient client/server computing. Extremely rich requests can be packaged via stored procedures or Transact-SQL batches for resolution entirely at the server, with only the results sent back to the initiating client application.

This capability has been a hallmark of SQL Server's client/server architecture from the outset, but nowhere is it more important than on the Internet, where network speed and bandwidth are often quite limited. In addition, SQL Server's networking architecture allows for ease of use and security on the Internet, including network name resolution. For example, Internet users can connect to SQL Server via a friendly name such as www.microsoft.com instead of via an arcane IP address such as 200.154.54.678. Secure encryption of data over the Internet is also possible.

SQL Server 2000 also provides XML support that enables querying and updating of XML documents in a SQL Server 2000 database. Standard SQL statements with the FOR XML clause can be issued from a Web-based client, and the results are returned to the client in XML format.

SQL Profiler

SQL Profiler is a graphical utility that allows database administrators and application developers to monitor and record database activity. SQL Profiler can display all server activity in real time, or it can create filters that focus on the actions of particular users, applications, or types of commands. SQL Profiler can display any SQL statement or stored procedure sent to any instance of SQL Server (if your security privileges allow it) as well as the output or response sent back to the initiating client.

SQL Profiler allows you to drill down even deeper into SQL Server and see every statement executed as part of a stored procedure, every data modification operation, every lock acquired or released, or every occurrence of a database file growing automatically. And that's just for starters. Dozens of different events can be captured, and dozens of data items can be captured for each event.

SQL Profiler is an important tool for tuning and debugging applications and for auditing and profiling the use of SQL Server. I'll discuss SQL Profiler in more detail in Chapter 17.

SQL Server Service Manager

SQL Server Service Manager (Figure 2-5) manages all instances of SQL Server, SQL Server Agent and MS DTC, as well as Microsoft Search and MSSQLServerOLAPService services. It provides a simple way to start, stop, or check the state of any of these services. Once you've used this application after booting the operating system, the application places an icon on the taskbar in the area of the taskbar clock. You can right-click on this icon to retrieve a menu of all the tasks that SQL Server Service Manager supports.

NOTE
On machines running Windows 98, SQL Server Service Manager looks as shown in Figure 2-5. However, the components that can be started aren't services. Machines running Windows 98 don't support services, so SQL Server Service Manager actually starts separate executable programs. (The Microsoft Search service isn't available at all on Windows 98.)

Figure 2-5. SQL Server Service Manager.

System Monitor Integration

SQL Server provides an extension DLL (SQLCTR80.DLL) that integrates with the Windows NT/2000 Performance tool and graphically displays many important performance statistics such as memory use, number of users, transactions per second, and CPU use. Integrating with the Performance tool is advantageous because it allows you to use a single tool to measure all aspects of a system's performance. If SQL Server simply provided its own performance-monitoring tool, you'd still have to check the performance of the operating system and network. Integration with the operating system's Performance tool provides one-stop shopping. A Windows 2000 Performance tool System Monitor graph is shown in Figure 2-6.

click to view at full size.

Figure 2-6. Windows 2000 Performance tool.

Using the Performance tool, you can set an alert on any statistic that's being monitored; when a predefined threshold is reached, the operating system automatically executes a predefined command. As previously mentioned, you can allow SQL Server Agent to respond directly to Performance tool thresholds being crossed.

Client Network Utility

The Client Network utility is used to configure DB-Library and the Net-Libraries that clients use when connecting to various SQL Servers. If you don't use DB-Library or you don't do any customization of network connections, you might not need to use this tool. However, if you want to examine or change the TCP/IP port that your named instance of SQL Server is listening on, you can do that using the Client Network utility. Also, if you want client tools to communicate with any local SQL Server instance using the Shared Memory Net-Library, you must make sure that feature is enabled using the Client Network utility. The Client Network utility also allows you to set up aliases for your server names. You can set up a simple one-word name to use instead of the two-part name normally required to access a named instance of SQL Server. I'll show an example of how to do this when I discuss named instances in detail in Chapter 4.

Server Network Utility

The Server Network utility is used to manage the Net-Libraries on the server machine. Because the Net-Libraries on which SQL Server listens for client requests are specified during installation, most administrators never need to use this utility.

SQL Server Installation

SQL Server's graphical installation program allows you to set up SQL Server with a degree of ease and speed unprecedented for a full-featured DBMS. If you select the defaults, you can install SQL Server in 5 to 10 minutes, depending on the speed of the computer. A custom installation will typically take well under 30 minutes. You can easily specify options so that the installation program is fully automated for numerous machines, or you can encapsulate the installation of SQL Server in the rest of an application's installation process.

OSQL and ISQL

A simple interactive window in which to submit basic SQL commands and get results is a crucial tool for a database developer—as a hammer is to a carpenter. Even though other, more sophisticated power tools are useful, the basic tools are essential. SQL Server provides the basic tools in two styles: OSQL and ISQL.

Both OSQL (OSQL.EXE) and ISQL (ISQL.EXE) are character-based command-line utilities. ISQL is based on the DB-Library interface, and OSQL is ODBC-based. Many parameters, including the SQL statement or the name of the file containing the statement, can be passed to these character-based utilities. Upon exit, they can return status values to the Windows client that can be checked within a command file (CMD or BAT). Consequently, programs commonly launch scripts of SQL commands by spawning one of these character-based utilities and passing the appropriate parameters and filenames. The SQL Server installation program itself spawns OSQL.EXE numerous times with scripts that install various database objects and permissions.

SQL Query Analyzer

SQL Query Analyzer (ISQLW.EXE) provides a clean, simple, graphical user interface (GUI) for running SQL queries and viewing the results. SQL Query Analyzer allows for multiple windows so that simultaneous database connections (to one or more installations of SQL Server) can exist and be separately sized, tiled, or minimized. You can choose to have your query results displayed in a text window or in a grid. SQL Query Analyzer provides a graphical representation of SHOWPLAN, the steps chosen by the optimizer for query execution. It also provides optional reports of the actual commands processed by SQL Server (a server-side trace) and of the work done by the client. SQL Server 2000 Query Analyzer comes with an Object Browser that lets you drag and drop object or table names into your query window and helps you build SELECT, INSERT, UPDATE, or DELETE statements for any table. SQL Query Analyzer also comes with a T-SQL Debugger tool that I'll describe in more detail in Chapter 16.

SQL Query Analyzer is a wonderfully versatile tool for development and for testing batches and stored procedures that you'll incorporate into your SQL Server applications. Take some time to just play with the terrific interface so that you get very comfortable with it. You can use SQL Query Analyzer to:

Format your SQL queries
Use templates for your stored procedures, functions, and basic SQL statements
Drag object names from the Object Browser to the Query window to see their definition
Define your own hotkeys

Bulk Copy and Data Transformation Services

SQL Server provides a character-based utility called bcp, or bulk copy (BCP.EXE), for flexible importing and exporting of SQL Server data. Similar in format to ISQL and OSQL, bcp allows parameters to be passed to it and is often called from command files. A special set of functions exists in ODBC that lets you easily create a custom data loader for your application; the bcp utility is a generalized wrapper application that calls these functions.

SQL Enterprise Manager provides facilities to simplify the transfer of data into or out of SQL Server, and this functionality is incorporated into the Data Transformation Services (DTS) tool. Unlike the command-line bcp utility, DTS allows you to create a new table as you copy data to your SQL Server as well as to easily change column datatypes or formats. DTS goes far beyond just simple import and export of data to or from SQL Server, however. You can also use it to move data to or from any OLE DB data source, applying all kinds of data transformations along the way. Multiple transfers can be defined and stored in a DTS package, which can then be run from a command line using the dtsrun utility (DTSRUN.EXE).

SNMP Integration

SQL Server provides support for Simple Network Management Protocol (SNMP), a standard protocol within TCP/IP environments, via SQL Server Management Information Base (MIB), another standard of the SNMP and TCP/IP environment. A group of database vendors, including Microsoft, cooperated to define a standard MIB that reports certain status data about a database environment for monitoring purposes.

NOTE
This group of database vendors was a subcommittee of the Internet Engineering Task Force (IETF). The draft specification is known as IETF SNMP RDBMS-MIB (RFC 1697). SQL Server MIB is generally based on this proposal but provides additional data beyond that called for in the specification.

For example, status information (such as whether SQL Server is currently running, when it was last started, and how many users are connected) is reported to SNMP via this MIB. A variety of SNMP management and monitoring tools exist and can access this data. If, for example, you use Hewlett-Packard's OpenView in managing your network, SQL Server MIB enables those tools to also monitor multiple statistics regarding SQL Server.

SQL Server Books Online

All SQL Server documentation is available on line as well as in printed form. A powerful viewer for the online documentation makes it easy to search for topics. Even if you favor printed books, you'll appreciate the speed and convenience of the SQL Server Books Online search capabilities. Also, because of the lead time required in the production of the printed manuals that ship with SQL Server, SQL Server Books Online is more complete and accurate than the manuals. Because this book can't hope to and doesn't try to replace the complete documentation set, the SQL Server evaluation CD that accompanies this book also contains the complete SQL Server Books Online documentation. Although you need to install SQL Server to view the complete Books Online, you can view all the SQL Server Setup documentation right from the initial screen of the installation CD.