A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright 1999 by Microsoft Corporation
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form or by any means without the written permission of the publisher.
Library of Congress Cataloging-in-Publication Data
Microsoft SQL Server 7.0 System Administration Training Kit /
1. SQL server. 2. Client/server computing. I. Microsoft
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 WCWC 4 3 2 1 0 9
Distributed in Canada by ITP Nelson, a division of Thomson Canada Limited.
Microsoft Press books are available through booksellers and
ActiveX, BackOffice, JScript, Microsoft, Microsoft Press, MS-DOS, Outlook, PivotTable, Visual Basic, Visual FoxPro, Windows, and Windows NT are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other
The example companies, organizations, products, people, and events depicted herein are fictitious. No association with any real company, organization, product, person, or event is intended or should be inferred.
For The Information Management Group
Writers: Sean Nolan, Tom Huguelet
Graphic Artist: Jesse Wolfe
Technical Contributors: Jodi Allen, Dan Christie
For Microsoft (Original Instructor-Led Course Content)
Dan Basica, Homer Christensen, Wendy Cleary, Margo Crandall, Karl Dehmer, Stacey Dickinson, Xandria Eykel,
Cheryl Hoople, Marilyn McGill, Lori Oviatt, Carl Rabeler, Adam Shapiro
For Microsoft Press
Program Manager: Jeff Madden
Project Editor: Michael Bolinger
Technical Editor: Nick Cavalancia
Part No. 097-0002093
This chapter introduces Microsoft SQL Server. It defines some of the key characteristics of SQL Server and explains the environments in which it has been designed to work. You will be introduced to the different
To complete the lessons in this chapter you must have
SQL Server is a client/server relational database management system (RDBMS) that uses Transact-SQL to send
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
can be used to refer to very general concepts or to specific items of hardware or software. At the most general level, a
is any component of a system that requests services or resources from other
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
Figure 1.1 A file-based system
Figure 1.2 A host-based system
Figure 1.3 A client/server system
The following table
|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||
||Can use network efficiently|
Thousands of commercial data-based systems are available,
The key to understanding client/server systems (and
specificallySQL 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. Clientsdo not have direct access to data; they always communicate with the database server, which in turninteracts 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.
is a collection of data organized in two-dimensional tables consisting of named
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||
|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)|
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.
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 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
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.
|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.