Lesson 2: What Are the SQL Server 2000 Components?

3 4

SQL Server 2000 provides a number of different types of components. At the core are server components. These server components are generally implemented as 32-bit Windows services. SQL Server 2000 provides client-based graphical tools and command-prompt utilities for administration. These tools and utilities, as well as all other client applications, use client communication components provided by SQL Server 2000. The communication components provide various ways in which client applications can access data through communication with the server components. These communication components are implemented as providers, drivers, database interfaces, and Net-Libraries. An additional component is SQL Server 2000 Books Online, which is implemented as Hypertext Markup Language (HTML) pages.

---

After this lesson, you will be able to

• Describe the SQL Server 2000 server components, how they are implemented, and their function
• Describe the various SQL Server 2000 administrative tools and their function
• Describe SQL Server 2000 client communication components and how they are implemented
• Describe SQL Server 2000 Books Online

Estimated lesson time: 30 minutes

---

Server Components

The server components of SQL Server 2000 are normally implemented as 32-bit Windows services. The SQL Server and SQL Server Agent services may also be run as standalone applications on any supported Windows operating system platform.

Table 1.2 lists the server components and briefly describes their function. It also specifies how the component is implemented when multiple instances are used. Multiple instances are covered in more detail in Chapter 2.

Table 1.2 Server Components and Their Functions

Server Component	Description
SQL Server service	MSSQLServer service implements the SQL Server 2000 database engine. There is one service for each instance of SQL Server 2000.
Microsoft SQL Server 2000 Analysis Services service	MSSQLServerOLAPService implements SQL Server 2000 Analysis Services. There is only one service, regardless of the number of instances of SQL Server 2000.
SQL Server Agent service	SQLServerAgent service implements the agent that runs scheduled SQL Server 2000 administrative tasks. There is one service for each instance of SQL Server 2000.
Microsoft Search service	Microsoft Search implements the full-text search engine. There is only one service, regardless of the number of instances of SQL Server 2000.
Microsoft (MS DTC) service	Distributed Transaction Coordinator manages distributed transactions between instances of SQL Server 2000. There is only one service, regardless of the number of instances of SQL Server 2000.

Note

---

The SQL Server and SQL Server Agent services must be run as applications on Windows Millennium Edition and Windows 98. The Microsoft Search service and the MS DTC service do not run on Windows Millennium Edition or Windows 98.

Client-Based Administration Tools and Utilities

The SQL Server 2000 administration tools and utilities are implemented as clients, meaning that they must establish a local or network connection to SQL Server 2000 using client communication components.

Graphical Tools

Table 1.3 lists the 32-bit graphical tools provided by SQL Server 2000 and briefly describes their function.

Table 1.3 Graphical Tools in SQL Server 2000

Graphical Tool	Description
SQL Server Enterprise Manager	The primary server and database administration tool, it provides a Microsoft Management Console (MMC) snap-in user interface.
SQL Query Analyzer	Used for creating and managing database objects and testing Transact-SQL statements, batches, and scripts interactively.
SQL Profiler	Used to monitor and capture selected SQL Server 2000 events for analysis and replay. Supports C2 security-level auditing.
SQL Server Service Manager	A taskbar application used to start, stop, pause, or modify SQL Server 2000 services.
Client Network Utility	Used to manage the client Net-Libraries and define server aliases containing custom server connection parameters, if needed.
Server Network Utility	Used to manage the server Net-Libraries, including enabling SSL encryption.

Command-Prompt Utilities

Table 1.4 lists the most frequently used command-prompt utilities provided by SQL Server 2000 and briefly describes their function.

Client Communication Components

Users access SQL Server 2000 through client applications. SQL Server 2000 supports two main types of client applications. The first type is relational database applications, which are the more traditional type of client applications used in two-tier client/server environments. These client applications send Transact-SQL statements to the relational database engine and receive results as relational result sets.

The second type is Internet applications, which are part of the emerging Microsoft .NET platform. These client applications send either Transact-SQL statements or XPath queries to the relational database engine and receive XML documents in return.

Table 1.4 Command-Prompt Utilities in SQL Server 2000

Command-Prompt Utility	Description
Osql	This utility allows you to query an instance of SQL Server 2000 interactively using Transact-SQL statements, system procedures, and script files. This utility replaces Isql, which was used with editions of SQL Server before SQL Server 7.0.
Scm	This utility (Service Control Manager) is used to start, stop, pause, install, delete or modify SQL Server 2000 services. It also can start, stop, or pause SQL Server running as an application.
Sqldiag	This utility gathers and stores diagnostic information to expedite and simplify information gathering by Microsoft Product Support Services.
Bcp	This utility copies data between an instance of SQL Server 2000 and a data file in a user-specified format.
Dtsrun	This utility executes packages created using DTS.
Sqlmaint	This utility performs a specified set of maintenance operations on one or more databases. These include performing DBCC consistency checks, backing up data and transaction log files, updating distribution statistics, and rebuilding indexes.

Each of these types of client applications connects to SQL Server 2000 in different ways. It is important for you, as the database administrator, to have a basic understanding of how client applications access SQL Server databases. Certain configuration tasks are related to client communication components that you choose during installation or configure after installation. These tasks are covered in Chapters 2 and 12.

Relational Database Application Programming Interfaces

Relational database applications access SQL Server 2000 through a database API. A database API defines how to code an application to connect to an instance of SQL Server 2000 and pass commands to a SQL Server 2000 database. SQL Server 2000 provides native support for two main classes of database APIs, OLE DB and ODBC.

OLE DB is an API that allows COM applications to consume data from OLE DB data sources. SQL Server 2000 includes a native OLE DB provider. An OLE DB provider is a COM component that accepts calls to the OLE DB API and does whatever is necessary to process that request against the data source. The provider supports applications written using OLE DB, or other APIs that use OLE DB, such as ADO.

ODBC is a Call-Level Interface (CLI) that allows C and C++ applications to access data from ODBC data sources. SQL Server 2000 includes a native ODBC driver. An ODBC driver is a DLL that accepts calls to the ODBC API functions and does whatever is necessary to process that request against the data source. The driver supports applications or components written using ODBC, or other APIs using ODBC, such as Data Access Objects (DAO), Remote Data Objects (RDO), and the Microsoft Foundation Classes (MFC) database classes. ADO is generally replacing DAO and RDO.

SQL Server 2000 also supports the DB-Library (for backward compatibility only) and Embedded SQL APIs.

Net-Libraries

The OLE DB provider or ODBC driver uses a client Net-Library to communicate with a server Net-Library on an instance of SQL Server 2000. The communication can be on the same computer or across a network. Net-Libraries encapsulate requests between clients and servers for the transmission using the underlying network protocol. The communication between client and server Net-Libraries can be encrypted using the Secure Sockets Layer (SSL). SQL Server 2000 clients and a SQL Server 2000 server can be configured to use any or all of the Net-Libraries listed in Table 1.5.

Table 1.5 Net-Libraries Employable with SQL Server 2000

Net-Library	Description
Shared memory	Used to connect to SQL Server 2000 on the same computer using a segment of memory. This is one of the default protocols for SQL Server 2000.
Named pipes	Used to connect to SQL Server 2000 using named pipes. A pipe is a file-system mechanism used for communication between processes. This is one of the default protocols for SQL Server 2000.
TCP/IP Sockets	Used to connect to SQL Server 2000 using TCP/IP. This is one of the default protocols for SQL Server 2000.
NWLink IPX/SPX	Used in the Novell network environment, primarily legacy Novell environments that do not support TCP/IP.
VIA GigaNet SAN	Used to support the new, high-speed SAN technology on GigaNet's cLAN server farm network.
Multiprotocol	Supports any available communication method between servers using Windows NT RPCs over any available network protocol. In earlier versions of SQL Server, this Net-Library was required to enable encryption and support Windows authentication. Today, it is mainly used for backward compatibility.
AppleTalk ADSP	Used in the Macintosh and Apple network environment. ADSP enables Apple Macintosh clients to connect to SQL Server 2000 using native AppleTalk.
Banyan VINES	Used in the Banyan VINES network environment. This protocol runs at the SQL Server 7.0 level of functionality for clients and servers running Windows NT 4.0 and will not be further enhanced.

Open Data Services

Server Net-Libraries communicate with the Open Data Services layer of the relational database engine, which is the interface between the relational database engine and the server Net-Libraries. Open Data Services transforms packets received from server Net-Libraries into events that it passes to the appropriate part of the relational database engine. The relational database engine then uses Open Data Services to send replies back to SQL Server 2000 clients through the server Net-Libraries.

Client-Server Communication

Figure 1.3 shows the client communication components when the client application and the instance of SQL Server are on the same computer.

Figure 1.3

SQL Server on the same computer as the client application.

Figure 1.4 shows a simplified version of the client communication components when the client application and the instance of SQL Server are on separate computers.

Internet Applications

Internet applications access SQL Server 2000 by means of a virtual root defined on an IIS server that points to an instance of SQL Server 2000. SQL Server 2000 provides an ISAPI DLL (sqlisapi.dll) that makes this possible. These applications can use a Uniform Resource Locator (URL), the ADO API, or the OLE DB API for executing XPath queries or Transact-SQL statements.

When the IIS server receives an XPath query or Transact-SQL statement, the IIS server loads the ISAPI DLL. The ISAPI DLL uses the OLE DB Provider for SQL Server (SQLOLEDB) to connect to the instance of SQL Server 2000 specified in the virtual root and pass the XPath query or Transact-SQL statement to SQL Server.

Figure 1.4

SQL Server on a separate computer from the client application.

SQL Server Books Online

SQL Server Books Online is the online documentation provided with SQL Server 2000 and is implemented as HTML pages. SQL Server Books Online provides numerous methods of finding information. You can navigate through SQL Server Books Online by

• Using the contents pane
• Typing a keyword in the index
• Typing a word or phrase in the search engine
• Clicking the Help button in any of the graphical tools to bring up related information
• Pressing F1 in dialog boxes and interface elements to bring up related information
• Selecting a Transact-SQL statement, function, stored procedure, or other Transact-SQL element in SQL Query Analyzer and pressing Shift+F1 to view information about the selected text

Note

---

In addition to the default subsets of SQL Server Books Online provided with SQL Server 2000, you can also define custom subsets of the entire SQL Server Books Online against which to perform searches. To create a custom subset, click Define Subset on the View menu.

Lesson Summary

SQL Server 2000 has a number of server components that are normally implemented as services on Windows 2000 and Windows NT 4.0. SQL Server 2000 provides a plethora of client-based graphical and command-prompt tools and utilities to perform administration tasks. These administrative tools and utilities, as well as all other client applications, use a number of different client communication components to establish a local or a network connection to SQL Server 2000.