Lesson 1: What Is SQL Server?

[Previous] [Next]

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.



Figure 1.1 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.



Figure 1.2 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.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.