SQL Server Utilities and Extensions | Inside Microsoft SQL Server 7.0 (Mps)

SQL Server also includes utilities and extensions that provide increased functionality, such as Internet enabling, monitoring capability, easy setup, and easy data importing.

Web Assistant Wizard and Internet Enabling

SQL Server provides dynamic ways for working with the Internet: Web Assistant Wizard and interoperability with Microsoft Internet Information Server (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 could publish its current parts inventory as a Web page (an HTML file) using Web Assistant Wizard. Web Assistant Wizard allows an ad hoc query or stored procedure to be submitted, provides some simple formatting capabilities, allows for the inclusion of links to other Web pages, and allows a template to be used for more advanced formatting. The output of the query is written as an HTML 2 table, and a Web page is created. The process to create or update the Web page can be automated by Web Assistant Wizard to occur at a regular interval or whenever the data changes (via a trigger).

Web Assistant Wizard is distinct from but complementary to IIS in BackOffice. With Web Assistant Wizard, users browsing the Web page work separately from SQL Server, because the data on the Web page has been extracted. Web Assistant Wizard doesn't use or require IIS, and a page generated by Web Assistant Wizard 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 via a friendly name such as www.microsoft.com instead of via an arcane IP address such as 200.154.54.678:1433. Secure encryption of data over the Internet is also possible.

SQL Server Profiler

SQL Server Profiler is a graphical utility that allows database administrators and application developers to monitor and record database activity. SQL Server 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 Server Profiler can display any SQL statement or stored procedure sent to any instance of SQL Server ( assuming your security privileges allow it) as well as the output or response sent back to the initiating client.

SQL Server 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.

The capabilities of SQL Server Profiler provide an important tool for tuning and debugging applications and for auditing and profiling the use of SQL Server. We'll look at SQL Server Profiler in more detail in Chapter 15.

SQL Server Service Manager

SQL Server Service Manager, shown in Figure 2-6, manages SQL Server, SQL Server Agent, MS DTC, and Microsoft Search 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 Windows NT, the application will place an icon in the taskbar in the area of the taskbar clock. You can right-click this icon to retrieve a menu of all the tasks that SQL Server Service Manager supports.

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

Figure 2-6. SQL Server Service Manager.

Windows NT Performance Monitor Integration

SQL Server provides an extension DLL (SQLCTR70.DLL) that integrates with the Windows NT Performance Monitor and graphically displays important performance statistics, such as memory use, number of users, transactions per second, and CPU use, as well as many others. Integrating with the Windows NT Performance Monitor 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 would still have to check the performance of the operating system and network. Integration with the Windows NT Performance Monitor provides one-stop shopping. A Windows NT Performance Monitor graph is shown in Figure 2-7.

Using Windows NT Performance Monitor, you can set an alert on any statistic that's being monitored ; when a predefined threshold is reached, Windows NT automatically executes a predefined command. As mentioned above, you can allow SQL Server Agent to respond directly to Windows NT Performance Monitor 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 if you don't do any customization of network connections, you might not need to use this tool.

click to view at full size.

Figure 2-7. Windows NT Performance Monitor.

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 will listen for client requests are specified during installation, most administrators will 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 choose the defaults, SQL Server can be installed in 5 to 10 minutes, depending on the speed of the computer. A custom installation typically takes well under 30 minutes. Traditional DBMS products usually require several days for installation and often require that you enroll in training classes before installing the product. SQL Server can even be installed on a remote computer, a particularly useful feature if you manage a large number of servers.

The options for the installation program to use can be easily specified so that it can be fully automated for numerous machines, or you can encapsulate the installation of SQL Server in the rest of an application's installation process. The SQL Server installation program is configured so that it automatically runs when the SQL Server CD is inserted into the CD-ROM drive. If the CD is already in the drive and you want to run the installation program, you can just double-click on Autorun.exe in the root directory of the CD.

After you've installed SQL Server, two command-line utilities are available to change options chosen during the installation process:

rebuildm .exe (Rebuild master utility) This program allows you to rebuild the master database to change the character set, sort order, or Unicode collation. You can also use it to fix a corrupted master database.
regrebld .exe (Registry rebuild utility) This program allows you to restore the SQL Server Registry entries if they become corrupted or lost. You are also given the option to back up the current Registry entries.

ISQL and OSQL

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

Both ISQL (ISQL.EXE) and OSQL (OSQL.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 Server Query Analyzer

For interactive use, SQL Server Query Analyzer (ISQLW.EXE) provides a clean, simple, Windows-based interface for running SQL queries and viewing the results. SQL Server 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 Server Query Analyzer provides a graphical representation of SHOWPLAN, the steps chosen by the optimizer for query execution. You can run a tool called Index Analysis from SQL Server Query Analyzer that will recommend useful indexes for any one particular query.

SQL Server Query Analyzer is a wonderfully versatile tool for development and testing of batches and stored procedures that you'll incorporate into your SQL Server applications.

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 loader or unloader for your application; the bcp utility is a generalized wrapper application that calls these functions.

SQL Server 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 into 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 with the dtsrun utility (DTSRUN.EXE).

SNMP Integration

Support for Simple Network Management Protocol (SNMP, a standard protocol within TCP/IP environments) is provided via SQL Server Management Information Base (MIB, another standard of the SNMP and TCP/IP environment). A group of database vendors , including Microsoft, cooperated in defining a standard MIB that would report certain status data about a database environment for monitoring purposes.

NOTE
This group was a subcommittee of the IETF, the Internet Engineering Task Force, and 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 or Computer Associate's CA-Unicenter in managing your network, SQL Server MIB enables those tools to also monitor multiple statistics regarding SQL Server.

SQL Server Books Online

In addition to being available in printed form, all SQL Server documentation is available online. A powerful viewer makes it simple to find and search for topics within seconds. Even if you favor printed books, you'll appreciate the speed and convenience of the search capabilities of SQL Server Books Online. In addition, 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 those 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. You can view the documentation right from the CD, without installing the SQL Server product. The initial screen displayed when autorun.exe is executed gives you the choice of installing SQL Server or browsing the Books Online.