SQL Server provides database, client, and communication components:
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
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 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
SQL Server Agent is a service that works in conjunction with SQL Server to create and manage local or multiserver jobs, alerts, and operators.
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 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 includes a variety of client software for designing and creating databases, querying data, and administering the server.
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.
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 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 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 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.
In this practice, you will view and search the contents of SQL Server Books Online and familiarize yourself with conventions used in the documentation.
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.
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.