Lesson 2: SQL Server Components
SQL Server provides database, client, and communication components:
- The database component of SQL Server is implemented as a group of 32-bit Windows applications or Windows NT services. Tools for installing, configuring, and managing these applications and services are also supplied.
- The client component of SQL Server includes Windows 32-bit-based and command-prompt-based server and database management utilities as well as Transact-SQL query utilities. A number of APIs and object models are supplied to enable custom application development.
- The client-to-database communication for SQL Server is accomplished by two pieces of software: the database interface and a Net-Library. These are installed on the client and are included as part of the server installation. A utility for configuring the installed Net-Library client software is provided.
After this lesson, you will be able to
- Describe the components that make up the SQL Server database services
- Describe the SQL Server client components
- Use the SQL Server Help and Books Online
Estimated lesson time: 45 minutes
SQL Server Services
The SQL Server services are MSSQLServer, SQL Server Agent, and Microsoft Distributed Transaction Coordinator (MS DTC). Figure 1.6 shows these services and the main functions they provide. The Microsoft Search Service is an optional service that can be installed during initial setup, later, or not at all. All SQL Server services run as services on Windows NT or as applications on Windows 32-bit platforms.
Figure 1-6 The SQL Server services
The MSSQLServer Service
The MSSQLServer service is the RDBMS. It is the component that processes all Transact-SQL statements and manages all files that are part of the databases on the server. All of the other SQL Server services are dependent on the MSSQLServer service and exist to extend or complement the functionality of the MSSQLServer service. The MSSQLServer service
- Allocates computer resources among multiple concurrent users
- Prevents logical problems, such as timing requests from users who want to update the same data at the same time
- Enforces data consistency and integrity
- Enforces security
The SQL Server Agent Service
SQL Server Agent is a service that works in conjunction with SQL Server to create and manage local or multiserver jobs, alerts, and operators.
The Microsoft Distributed Transaction Coordinator Service
Microsoft Distributed Transaction Coordinator (MS DTC), also a component of Microsoft Transaction Server, is a transaction manager that allows clients to include several different sources of data in one transaction. MS DTC coordinates the proper completion of distributed transactions to ensure that all updates on all servers are permanent—or, in the case of errors, that all modifications are canceled. This is achieved using a process called two-phased commit. MS DTC is an X/Open XA_compliant transaction manager. (X/Open XA is an open transaction standard.)
The Microsoft Search Service
The Microsoft Search Service provides full-text search capabilities for text column data. This optional service can be installed during the standard SQL Server installation or later.
SQL Server Client Software
SQL Server includes a variety of client software for designing and creating databases, querying data, and administering the server.
SQL Server Enterprise Manager Snap-in for Microsoft Management Console
SQL Server Enterprise Manager is a server administration and database management client. It is a Microsoft Management Console (MMC) snap-in. MMC is a shared user interface for BackOffice server management. This shared console provides a convenient and consistent environment for administrative tools.
SQL Server Query Analyzer
The SQL Server Query Analyzer is a Transact-SQL query tool used to send individual or batched Transact-SQL statements to SQL Server. It also provides query analysis, statistical information, and the ability to manage multiple queries in different windows simultaneously.
NOTE
SQL Server Query Analyzer replaces ISQL/w found in previous versions of SQL Server.
SQL Server Administrative Tools and Wizards
SQL Server provides a number of administrative tools and wizards that assist with particular aspects of SQL Server. The following table describes these tools and wizards.
| Graphical tool | Purpose |
|---|---|
| SQL Server Client Configuration | Utility used to manage the communication components on SQL Server clients |
| SQL Server Performance Monitor | Windows NT Performance Monitor settings file; provides a preconfigured view of some of the common SQL Server counters |
| SQL Server Profiler (previously named SQL Trace) | Utility used to capture a continuous record of server activity and provide auditing capability |
| SQL Server Service Manager | Graphical utility used for starting, stopping, and pausing SQL Server services |
| SQL Server Setup | Application used to install and reconfigure SQL Server and SQL Server clients |
| SQL Server wizards | Collection of tools that guide users through complex tasks |
| Data Transformation Services | A set of components that allow you to import, export, and transform data between multiple heterogeneous sources, using an OLE DB-based architecture |
SQL Server Command-Prompt Management Tools
SQL Server command-prompt management tools allow you to enter Transact-SQL statements and execute script files. The next table describes the most frequently used command-prompt utilities provided with SQL Server. Each file is an executable program.
| Utility | Description |
|---|---|
| osql | Utility that uses Open Database Connectivity (ODBC) to communicate with SQL Server. Primarily used to execute batch files containing one or more SQL statements. |
| bcp | Batch utility used to export and import data to and from SQL Server and non_SQL Server databases. Copies data to or from a standard text or binary data file. |
NOTE
The osql utility replaces the isql utility found in previous versions of SQL Server. The isql utility, which uses DB-Library to communicate with SQL Server, is available for backward compatibility.
SQL Server Help and SQL Server Books Online
SQL Server offers extensive documentation and different types of help to assist you. The following table describes each type of help provided by SQL Server.
| Type of help | Description |
|---|---|
| Application help | Several SQL Server tools—including SQL Server Enterprise Manager, SQL Server Profiler, and SQL Server Query Analyzer—provide context-sensitive help on the application interface. Click the Help button, select a command from the Help menu, or (sometimes) press F1 to open application help. |
| Transact-SQL help | When using SQL Server Query Analyzer, highlight a statement name and press Shift-F1. |
| SQL Server Books Online | SQL Server Books Online provides online indexed, searchable access to all of the SQL Server documentation. |
You can use SQL Server Books Online directly from the Microsoft SQL Server 7 CD-ROM on any Windows 32-bit-based computer without first installing SQL Server. Internet Explorer 4.01 with SP1 or later must be installed on the computer to use SQL Server Books Online. If Internet Explorer 4.01 with SP1 or later is not installed on the computer, you can install it by selecting Install SQL Server 7.0 Prerequisites in the Microsoft SQL Server dialog box when you insert the Microsoft SQL Server 7 CD-ROM.
Practice: Using SQL Server Books Online
In this practice, you will view and search the contents of SQL Server Books Online and familiarize yourself with conventions used in the documentation.
- To view the contents of SQL Server Books Online
- Insert the Microsoft SQL Server version 7.0 CD-ROM into the CD-ROM drive, and when the Microsoft SQL Server dialog box appears, click Browse Books Online.
- In the console tree, review the organization of SQL Server Books Online.
- On the Contents tab, expand Getting Started.
- In the details pane, review the listed topics.
- Click Documentation Conventions and review the contents.
- In the console tree, click Finding Information On The Internet, and then review the contents in the details pane.
- To use the SQL Server Books Online index
- On the Index tab, type system tables.
- Double-click the word Functions in the list of topics, and then view the information in the details pane.
- To search SQL Server Books Online for a word or phrase
- On the Search tab, type a few words to find the topic "How to configure a client to use TCP/IP (Client Configuration)."
- Click List Topics or press Enter to execute the search.
- Double-click "How to configure a client to use TCP/IP (Client Configuration)" to open the topic.
You do not have to type all of the words in the phrase to search for the topic. For example, you can enter TCP/IP client to find the topic.
- To find a word or phrase in a SQL Server Books Online topic
- On the Contents tab, expand Getting Started and click "Using the HTML Help Viewer."
- Click in the details pane to make it the active pane.
- Press Ctrl-F.
- In the Find What box, type operators.
- Click Find Next.
- Notice that the word operators is highlighted.
- Repeat steps 5 and 6 to find each occurrence of the word operators.
- Click Cancel to close the Find dialog box. Close SQL Server Books Online.
Lesson Summary
SQL Server has a number of server components that run as services on Windows NT and as applications on Windows 95 or Windows 98. The server components provide the database services of SQL Server.
SQL Server client components run as applications on Windows 32-bit platforms. The client components are utilities for managing, programming, and analyzing the functionality of SQL Server. Client utilities can be installed on any Windows 32-bit computer on the network to allow remote administration and programming of the SQL Server computer.
