Introducing SQL Server for Windows NT
|Chapter 1: Overview of SQL Server for Windows NT|
SQL Server for Windows NT is an integrated set of software products for developing and deploying relational database applications. It consists of a high-performance relational database management system (RDBMS), which runs database servers, and a collection of applications and libraries, which run on database clients. This arrangement, consisting of servers accessed by multiple clients over a network, forms the basis for Sybase's client/server architecture.
This chapter provides an overview of the following topics:
A relational database management system (RDBMS) is a system for storing and retrieving data in which the data is represented in two-dimensional tables. In early relational systems, tables were called relations. A relational database consists of a collection of tables that store interrelated data.
Figure 1-1 shows portions of tables that you might create in a relational database storing related data about books¾ called titles, authors, publishers, and titleauthor.
Figure 1-1: Tables in a database
Each table in the database holds information about different things¾books, publishers, and authors. But some information in each table overlaps with information in another table¾by design. Columns that appear in more than one table and that link the tables together are called keys. Keys, which are discussed in more detail in "Key", allow you to retrieve information that is distributed over multiple tables.
For example, you might want information about books published by Marjorie Greene, which spans the authors and the titles tables. Figure 1-2 shows the key columns for our sample databases and how they allow you to retrieve the correct data from the tables.
Figure 1-2: Retrieving data across tables using key columns
One advantage of relational databases is that they allow you to set up multiple tables, a structure that eliminates redundancy and possible inconsistencies caused by that redundancy. For example, both the sales and accounts payable departments might enter and look up information about publishers. In a relational database, the information about publishers only needs to be entered once, in a table that both departments can access.
Defining how tables represent a body of information, determining how the data should be distributed among the tables, and deciding what keys are needed to define the relationships are the subjects of logical design. Sound database design is the key factor in realizing SQL Server's performance potential and makes your database easy to maintain and update. Many excellent books are available on this subject. The Practical SQL Handbook (Bowman, Emerson, and Damovsky, Addison-Wesley, 1993) offers a clear introduction to logical database design, and it specifically relates the issues to a SQL Server context. (See "Designing Databases".)
SQL (Structured Query Language) is the foundation for entering data in and retrieving data from the server. SQL is the database language designed for the RDBMS model, which makes it easy to set up, use, and maintain a relational database. This is in contrast to database systems in previous computing models, which required large IS departments to be involved in data requests. An excellent reference for learning SQL is the LAN Times Guide to SQL (Groff and Weinberg, Osborne McGraw-Hill, 1994).
End users need not learn SQL in order to access data, however¾in most systems, purchased or developed GUI client software does the job of translating user requests into SQL. Software called application programming interfaces (APIs) or libraries installed on the clients allow applications to communicate with the RDBMS.
Sybase's enhanced version of SQL is called Transact-SQL (see "Transact-SQL").
In addition to storing and retrieving data, SQL Server provides a number of auxiliary facilities for managing the relational databases. These include programs for starting and stopping the server, for backing up and restoring data, for copying data between a database and a file system, for authenticating users of the server and the databases, for managing the physical devices on which the data is stored, for displaying system messages in different languages, for storing and preprocessing a reusable procedure or set of procedures, and so forth. Together with the basic storage and retrieval capabilities, these facilities make up SQL Server's relational database management system.
In a Sybase SQL Server environment, the databases and the RDBMS software reside on one or more servers, where multiple clients can access them concurrently over a network. Applications that access and manipulate the shared data, as well as the libraries that allow the applications to communicate with the RDBMS, reside on the clients. Figure 1-3 illustrates the Sybase client/server configuration.
Figure 1-3: A Sybase client/server environment
The basic client/server relationship is one in which client applications request services from servers, and servers return results to client applications. A typical request for service in a client/server RDBMS is for the client to request that the server retrieve or modify the data that it is storing. A typical result is confirmation by the server that the request was successfully or unsuccessfully carried out, followed by the requested data. Figure 1-4 illustrates a basic client/server interaction.
Figure 1-4: Basic client/server interaction
A client/server model provides the best performance for data sharing among a large number of PCs and workstations because clients and servers share the work¾each performs the work it can do most efficiently. The server handles activities related directly to the maintenance and retrieval of shared data, while other tasks, such as displaying returned data or interpreting requests from users, are off-loaded to the clients. Figure 1-5 represents how the tasks are divided.
Figure 1-5: Division of tasks
The ability to distribute data among multiple servers provides a structure for distributed computing environments in which control is not centralized at a single data source or geographic location. Clients can access the data they need from any server.
Figure 1-6 shows a non-centralized, distributed computing configuration in which data is distributed over several sites and each site houses a different subset of the data.
Figure 1-6: Distributed client/server environment
Some organizations want each site to have a complete copy of the entire data set for two reasons, among others: to improve the time it takes to access the data, and to eliminate downtime in the case of one server's failure. Data replication offers a solution to these organizations.
Replication Server(R), a Sybase product you can purchase separately, can be integrated with your SQL Server for Windows NT setup. Replication Server replicates peer-to-peer data replication across a distributed environment, to and from heterogeneous hardware and data sources. All clients have equally fast and reliable access to all data.
As the section "Client/Server Architecture", described, the basic client/server RDBMS model consists of the database server and third-party or developed client software that communicates with the database server over a network. The integrated set of products in SQL Server for Windows NT also includes software for backing up, monitoring, administering SQL Server as well as client libraries that enable applications to communicate with the server
You install some of the SQL Server for Windows NT software on the server and some on the client. If you are running SQL Server on an Intel-based machine, you can install the client software on your server, if you like. It is convenient to be able to run some of the client administrative tools and utilities from the server desktop.
In addition, on the server you create files for use as database devices, files dedicated to storing data. You use the Transact-SQL disk init command to initialize the devices.
On the client you will most likely install (in addition to the client components of SQL Server) development tools such as PowerBuilder that help you build applications that access SQL Server, the applications themselves, and/or third-party software.
Figure 1-7 shows an overview of the client and server components of your SQL Server system. You may want to install client components tagged with an asterisk on either the server or the client, or both. The sections "Server Components" and "Client Components" describe each of the components in more detail.
Figure 1-7: Overview of SQL Server for Windows NT components
The following SQL Server for Windows NT components reside on the server:
SQL Server is Sybase's high-performance RDBMS. You can get a technical overview of SQL Server release 11.0.x from the product description and the white papers accessible from the Sybase home page on the World Wide Web at the following address:
Also ask your sales representative for more information. There are also several excellent books about SQL Server available from Sybase Press as well as from third-party publishers.
SQL Server also includes several utilities, which are described in SQL Server Utilities Programs for your platform.
Backup Server is a server application that runs concurrently with SQL Server to perform high-speed on-line database dumps and loads. Backup Server is automatically installed when you install SQL Server.
Monitor Server and Monitor Historical Server are the server components of a client/server application called SQL Server Monitor Server, which allows you to capture, display, and evaluate SQL Server performance data and to tune SQL Server performance. Monitor Server captures performance data from SQL Server's shared memory; Monitor Historical Server writes the data to files for off-line analysis. You must install Monitor Server on the same machine as the SQL Server that you want to monitor. Monitor Historical Server performs best when installed on a different machine from the SQL Server being monitored.
Services Manager is a Windows utility that allows you to start, pause, and stop a Sybase server.
Server Config facilitates configuration of SQL Server immediately after installation. See "Configuring SQL Server" for a discussion of these post-installation tasks.
When you install client products on a PC, a program window is created. This program window, shown in Figure 1-8 for NT clients and in Figure 1-9 for Windows clients, shows many (but not all) of the client components.
Figure 1-8: The Sybase Program window on the NT client desktop
Figure 1-9: The Sybase Program window on the Windows client desktop
The following SQL Server for Windows NT components are client components:
SQL Server Manager is a graphical system administration and database administration tool for SQL Server. Its powerful collection of features make exacting administrative tasks easy to perform. Figure 1-10 shows the SQL Server Manager user interface.
Figure 1-10: The SQL Server Manager user interface
SQL Server Manager helps with the following typical system or database administrator's tasks:
This client application gathers data collected by Monitor Server and presents it to the user through a graphical user interface. Monitor Client makes it easy to view SQL Server performance data and tune SQL Server performance parameters. Monitor Client also includes Monitor Client Library, which provides a programmatic interface to Monitor Server.
Open Client contains APIs that enable client applications to interface with SQL Server. These APIs are Client-Library(TM), DB-Library(TM), and CS-Library. Client applications that you purchase or develop, as well as application development tools such as PowerBuilder(R) and Infomaker, require that these Open Client libraries be installed so that they can communicate with SQL Server.
CS-Library contains a collection of utility routines used by all client applications. Client-Library and DB-Library contain a collection of routines that are specific to the programming language being used in an application. Client-Library is an extension of the older DB-Library that accommodates new features supported by SQL Server release 10 and later; DB-Library supports pre-system 10 features. For more information, consult the documentation in the Open Client/Server(TM) collection of SyBooks(TM) (see "Sybase Documentation" for more information).
Open Client includes Net-Library(TM), a library containing network protocol services that support connections between client applications and SQL Server.
Open Client also includes the following utilities:
Icons for frequently used utilities appear on the desktop. These include:
ODBC is the API developed by Microsoft to allow clients to connect to heterogeneous RDBMSs. You will install and use the appropriate ODBC driver, which resides on the client, only if you are developing or running third-party client applications that require access to SQL Server through Microsoft's ODBC interface.
Except for the books included in the SQL Server for Windows NT package, all the product documentation is contained in an online library called SyBooks, which is included on the product media.You can install SyBooks on a single server, where users can access it over the network, or you can install it on individual clients if they have enough disk space.
If you have Internet access and a web browser, you can also access SyBooks from the World Wide Web. Search for "SyBooks" on the Sybase home page to access SyBooks-on-the-Web.
You also have the option to order all the documentation in the SyBooks collection in printed form. Refer to the product ID numbers listed on the SyBooks content list included in the product package.
To order additional documents, U.S. and Canadian customers should call Customer Fulfillment at (800) 685-8225, fax (617) 229-9845.
Customers in other countries with a U.S. license agreement may contact Customer Fulfillment via the above fax number. All other international customers should contact their Sybase subsidiary or local distributor.