Microsoft SQL Server 7.0 System Administration Training Kit
SQL Server is a client/server relational database management system (RDBMS) that uses Transact-SQL to send requests between a client and SQL Server. The following sections define and explain these terms.
After this lesson, you will be able to
- Describe Microsoft SQL Server.
- List the operating system platforms supported by SQL Server.
- Describe how SQL Server takes advantage of the features of the Windows NT operating system.
- Describe how SQL Server integrates with other Microsoft BackOffice products.
Estimated lesson time: 30 minutes
Client/Server Architecture
The terms client, server, and client/server can be used to refer to very general concepts or to specific items of hardware or software. At the most general level, a client is any component of a system that requests services or resources from other components of a system. A server is any component of a system that provides services or resources to other components of a system.
For example, when you print a document from your workstation on a network, the workstation is the client and the machine that does the print spooling is the server.
Any client/server data-based system consists of the following components:
- The server—A collection of data items and supporting objects organized and presented to facilitate services, such as searching, sorting, recombining, retrieving, updating, and analyzing data. The database consists of the physical storage of data and the database services. All data access occurs through the server; the physical data is never accessed directly by the client.
- The client—A software program that might be used interactively by a person or that could be an automated process. This includes all software that interacts with the server, either requesting data from or sending data to the database. Examples are management utilities (those that are part of the SQL Server product as well as those bought separately), ad hoc query and reporting software, custom applications, off-the-shelf applications, and Web server-based applications.
- The communication between the client and the server—The communication between the client and the server depends largely on how the client and server are implemented. Both physical and logical layers of communication can be identified.
When you communicate with someone using the telephone, the telephone system is the physical layer and a spoken natural language is the logical layer of communication. For a data-based system, the physical layer can be a network if the server and the client are on different computers. It can be interprocess communication if the server and the client are on the same computer. The logical communication structure of the physical layer may be low-level operating system calls, a proprietary data access language, or the open Structured Query Language (SQL).
All implementations of data-based systems fall into one of three categories:
- File-based systems—Commonly found on personal computers, these systems use an application that directly accesses data files on a local hard drive or on a network file server. These systems implement the database services and the logical layer of communication as part of the client application; only the physical layer of communication and the physical storage of data are external to the client application. In this implementation, the client application fulfills both the role of client and the role of server. Figure 1.1 illustrates a file-based system.
- Host-based systems—Typically used in legacy mainframe and minicomputer environments, these systems implement all or most of the database services and client functionality on a large central computer. The user views and interacts with the client application remotely, using a terminal. The communication between the client and the database occurs on the host computer, and both the logical layer and the physical layer are implemented in the software and hardware on the host. In this implementation, the host computer fulfills both the role of client and the role of server. Figure 1.2 illustrates a host-based system.
- Client/server systems—These systems are designed to separate database services from the client, allowing the communication between them to be more flexible and open. Database services are implemented on a powerful computer, permitting centralized management, security, and shared resources. So the server in client/server is the database and its services. Client applications are implemented on a variety of platforms using a variety of tools, allowing flexibility and high-quality user applications; this is the client in client/server. The client communicates logically with the database through a database application programming interface (API), and the server performs the physical database access, usually in the file system of the server. Figure 1.3 illustrates the client/server model.
Figure 1.1 A file-based system
Figure 1.2 A host-based system
Figure 1.3 A client/server system
The following table compares some of the advantages and disadvantages of file-based, host-based, and client/server systems. Many organizations now use a mix of these systems. For example, data capture may be performed on a host-based system with thousands of terminals. The data may then be queried, manipulated, and analyzed by a client/server system, either directly on the host or after the data has been transferred to another database.
| File-based | Host-based | Client/server |
|---|---|---|
| Low cost | High initial cost | Variable cost |
| Low security | High security | Medium to high security |
| Low reliability | High reliability | Medium to high reliability |
| Application development possible with few skills | Application development requires skilled staff | Application development requires skilled staff |
| Well suited to small databases and end-user databases | Not appropriate for small databases or end-user databases | Can be used for small databases; not appropriate for end-user databases |
| Scalable to medium databases (± 50 MB) | Scalable to very large databases (1000s of GB) | Scalable to very large data bases (1000s of GB) |
| Minimal centralized management | Excellent centralized management | Excellent centralized management |
| Highly flexible end-user interface | Inflexible end-user interface | Flexible end-user interface |
| Low-to-medium vendor lock-in | High vendor lock-in | Medium vendor lock-in |
| Uses network inefficiently | Uses network efficiently | Can use network efficiently |
Thousands of commercial data-based systems are available, ranging from those comprising a single application running on a single personal computer to those comprising hundreds of applications running on complex networks of mainframe computers, minicomputers, and personal computers. All have the three basic components listed earlier: a server (the database), a client, and some means of communication between the two. Try to identify these components whenever you encounter a data-based system. In a large system, each component may consist of further layers, but you should always be able to distinguish the three basic components.
NOTE
The key to understanding client/server systems (and specifically SQL Server) is to realize that the database server (SQL Server) is a fully functional process or application that provides database services, as opposed to a file on a network file server, which is a static storage structure only. Clients interact with these database services via a clearly defined communication interface, allowing for tight control and security. Clients do not have direct access to data; they always communicate with the database server, which in turn interacts with the physical data. SQL Server's own management utilities are clients that can run on the same computer or on another computer; they have no more direct access to data than other clients do.
Relational Database Management Systems
A relational database is a collection of data organized in two-dimensional tables consisting of named columns and rows. Each table represents the mathematical concept of a relation as defined in set theory. In set theory, columns are known as attributes and rows are known as tuples. The operations that can be performed on tables are similarly based on the manipulation of relations to produce new relations, usually referred to as queries or views.
Relational databases differ from nonrelational databases in that the database user is not aware of system dependencies, if any, stored within the data. No knowledge of the underlying database is required; data can be queried and updated using standard languages (these languages together make up the Structured Query Language, or SQL), which produce a consistent result. SQL Server databases are relational.
A relational database management system (RDBMS) is responsible for
- Storing and making data available in tables
- Maintaining the relationships between tables in the database
- Ensuring the integrity of data by making certain that rules governing the data values and defining the relationships between tables are not violated
- Recovering all data to a point of known consistency in case of a system failure
Transact-SQL
SQL Server uses Transact-SQL, a version of SQL, as its database query and programming language. SQL is a set of commands that allow you to specify the information that you want to retrieve or modify. With Transact-SQL, you can access data and query, update, and manage relational database systems.
The American National Standards Institute (ANSI) and the International Standards Organization (ISO) have defined standards for SQL. Transact-SQL supports the latest ANSI SQL standard published in 1992, called ANSI SQL-92, plus many extensions to provide increased functionality.
SQL Server Platforms
SQL Server runs on the operating systems shown in Figure 1.4. The SQL Server version 7 server software runs only on the Windows 32-bit API-based operating systems, but you can use all of the operating system platforms to create and execute client applications.
Figure 1.4 Operating systems on which the SQL Server client and server software can run
The following table gives more detail about operating systems and how they do or do not support SQL Server 7.
| Platform | Server software | Client software |
|---|---|---|
| Microsoft Windows 95 or later | Yes; runs as an application | Yes |
| Microsoft Windows NT Workstation 4.0 or later | Yes; runs as a service | Yes |
| Windows NT Server | Yes; runs as a service | Yes |
| Windows NT Server Enterprise Edition | Yes; runs as a service | Yes |
| Windows 3.x | No | Yes (only via SQL Server versions 6.0 and 6.5) |
| MS-DOS | No | Yes (only via SQL Server versions 6.0 and 6.5) |
| Third party | No | Yes, such as UNIX and Apple Macintosh |
SQL Server Integration with Windows NT
SQL Server is tightly integrated with the Windows 32-bit platform. In particular, it is designed to take advantage of the features of the Windows NT operating system for large-scale organization and enterprise databases.
Security
SQL Server is integrated with the security system in Windows NT. This integration allows a user to access both SQL Server and Windows NT with a single username and password. SQL Server provides its own security for non-Microsoft clients.
SQL Server can also use the Windows NT encryption features for network security by using the Multiprotocol Net-Library.
NOTE
SQL Server 7 security is more integrated with Windows NT and more flexible than previous versions. Database permissions can now be assigned directly to Windows NT users. You can also now manage database access and permissions using Windows NT groups.
Multiprocessor Support
SQL Server supports the symmetric multiprocessing (SMP) capabilities of Windows NT. It automatically takes advantage of any additional processors that are added to the server computer.
Microsoft Event Viewer
SQL Server writes messages to the Windows NT application, security, and system event logs, providing a consistent mechanism for viewing and tracking problems.
Windows NT Services
SQL Server runs as a service on Windows NT, allowing you to start and stop SQL Server remotely.
Windows NT Performance Monitor
SQL Server sends performance metrics to the Windows NT Performance Monitor, enabling you to monitor the system performance of SQL Server.
Microsoft Index Server
SQL Server uses Microsoft Index Server; a full-text indexing and search engine supported by various Microsoft BackOffice products.
Microsoft Cluster Server
Microsoft Cluster Server (MSCS), a feature of Microsoft Windows NT Server Enterprise Edition, supports the connection of two servers, or nodes, into a cluster for greater availability and better manageability of data and applications. SQL Server works in conjunction with MSCS to switch automatically to the secondary node if the primary node fails.
SQL Server Integration with Microsoft BackOffice
SQL Server integrates well with other Microsoft BackOffice products. BackOffice is a group of server applications that work together to help you build business solutions, as illustrated in Figure 1.5.
Figure 1.5 Integration of SQL Server with other BackOffice products
The following table describes some commonly used BackOffice applications that work with or use SQL Server.
| BackOffice application | Description |
|---|---|
| Microsoft Internet Information Server (IIS) | Allows Internet browser clients access to data via SQL Server |
| Microsoft Exchange Server | SQL Server can send e-mail messages using Microsoft Exchange Server or other Messaging Application Programming Interface (MAPI) compliant providers. SQL Server can send messages when an error occurs or when a scheduled task (such as a database backup) succeeds or fails. |
| Microsoft SNA Server | Links IBM environments running the Systems Network Architecture (SNA) protocol with PC-based networks. You can integrate SQL Server with IBM mainframe or AS/400 applications and data using SNA Server. |
| Microsoft Systems Management Server (SMS) | Manages computer software, hardware, and inventory. SMS requires SQL Server to store its databases. |
Lesson Summary
SQL Server is a client/server relational database management system that is highly integrated with the Windows NT operating system. Using SQL Server, you can develop modern applications that separate the client application and the database services. SQL Server Transact-SQL supports the ANSI SQL-92 standard and provides extensions to the SQL language.