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
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:
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:
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.
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
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 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 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.
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.
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.
SQL Server writes messages to the Windows NT application, security, and system event logs, providing a consistent mechanism for viewing and tracking problems.
SQL Server runs as a service on Windows NT, allowing you to start and stop SQL Server remotely.
SQL Server sends performance metrics to the Windows NT Performance Monitor, enabling you to monitor the system performance of SQL Server.
SQL Server uses Microsoft Index Server; a full-text indexing and search engine supported by various Microsoft BackOffice products.
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 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. |
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.