Database Applications and Servers

Microsoft® SQL Server™ 2000 is designed to work effectively as:

• A central database on a server shared by many users who connect to it over a network. The number of users can range from a handful in one workgroup, to thousands of employees in a large enterprise, to hundreds of thousands of Web users.
  
  
• A desktop database that services only applications running on the same desktop.

Server Database Systems

Server-based systems are constructed so that a database on a central computer, known as a server, is shared among multiple users. Users access the server through an application:

• In a multitier system, such as Windows® DNA, the client application logic is run in two or more locations:
  • A thin client is run on the user's local computer and is focused on displaying results to the user.
    
    
  • The business logic is located in server applications running on a server. Thin clients request functions from the server application, which is itself a multithreaded application capable of working with many concurrent users. The server application is the one that opens connections to the database server. The server application can be running on the same server as the database, or it can connect across the network to a separate server operating as a database server. In complex systems, the business logic may be implemented in several interconnected server applications, or in multiple layers of server applications.

    This is a typical scenario for an Internet application. For example, a multithreaded server application can run on a Microsoft® Internet Information Services (IIS) server and service thousands of thin clients running on the Internet or an intranet. The server application uses a pool of connections to communicate with one or more instances of SQL Server 2000. The instances of SQL Server 2000 can be on the same computer as IIS, or they can be on separate servers in the network.

• In a two-tier client/server system, users run an application on their local computer, known as a client application, that connects over a network to an instance of SQL Server 2000 running on a server computer. The client application runs both business logic and the code to display output to the user, so this is sometimes referred to as a thick client.

Advantages of Server Database System

Having data stored and managed in a central location offers several advantages:

• Each data item is stored in a central location where all users can work with it.

  Separate copies of the item are not stored on each client, which eliminates problems with users having to ensure they are all working with the same information. Their system does not need to ensure that all copies of the data are updated with the current values, because there is only one copy in the central location.

• Business and security rules can be defined one time on the server and enforced equally among all users.

  Rule enforcement can be done in a database through the use of constraints, stored procedures, and triggers. Rules can also be enforced in a server application, since these applications are also central resources accessed by many thin clients.

• A relational database server optimizes network traffic by returning only the data an application needs.

  For example, if an application working with a file server needs to display a list of the names of sales representatives in Oregon, it must retrieve the entire employee file. If the application is working with a relational database server, it sends this command:

  SELECT first_name, last_name
  FROM employees
  WHERE emp_title = 'Sales Representative'
    AND emp_state = 'OR'
  

  The relational database sends back only the names of the sales representatives in Oregon, not all of the information about all employees.

• Hardware costs can be minimized.

  Because the data is not stored on each client, clients do not have to dedicate disk space to storing data. The clients also do not need the processing capacity to manage data locally, and the server does not need to dedicate processing power to displaying data.

  The server can be configured to optimize the disk I/O capacities needed to retrieve data, and clients can be configured to optimize the formatting and display of data retrieved from the server.

  The server can be stored in a relatively secure location and equipped with devices such as an Uninterruptable Power Supply more economically than fully protecting each client.

• Maintenance tasks such as backing up and restoring data are simplified because they can focus on the central server.

Advantages of SQL Server 2000 as a Database Server

Microsoft SQL Server 2000 is capable of supplying the database services needed by extremely large systems. Large servers may have thousands of users connected to an instance of SQL Server 2000 at the same time. SQL Server 2000 has full protection for these environments, with safeguards that prevent problems, such as having multiple users trying to update the same piece of data at the same time. SQL Server 2000 also allocates the available resources effectively, such as memory, network bandwidth, and disk I/O, among the multiple users.

Extremely large Internet sites can partition their data across multiple servers, spreading the processing load across many computers, and allowing the site to serve thousands of concurrent users.

Multiple instances of SQL Server 2000 can be run on a single computer. For example, an organization that provides database services to many other organizations can run a separate instance of SQL Server 2000 for each customer organization, all on one computer. This isolates the data for each customer organization, while allowing the service organization to reduce costs by only having to administer one server computer.

SQL Server 2000 applications can run on the same computer as SQL Server 2000. The application connects to SQL Server 2000 using Windows Interprocess Communications (IPC) components, such as shared memory, instead of a network. This allows SQL Server 2000 to be used on small systems where an application must store its data locally.

The illustration shows an instance of SQL Server 2000 operating as the database server for both a large Web site and a legacy client/server system.

The largest Web sites and enterprise-level data processing systems often generate more database processing than can be supported on a single computer. In these large systems, the database services are supplied by a group of database servers that form a database services tier. SQL Server 2000 does not support a load-balancing form of clustering for building a database services tier, but it does support a mechanism that can be used to partition data across a group of autonomous servers. Although each server is administered individually, the servers cooperate to spread the database-processing load across the group. A group of autonomous servers that share a workload is called a federation of servers. For more information, see Designing Federated Database Servers.

Desktop Database Systems

Although SQL Server 2000 works effectively as a powerful database server, the same database engine can also be used in applications that need stand-alone databases stored locally on the client. SQL Server 2000 can configure itself dynamically to run efficiently with the resources available on a client desktop or laptop computer, without the need to dedicate a database administrator to each client. Application vendors can also embed SQL Server 2000 as the data storage component of their applications.

When clients use local SQL Server 2000 databases, applications connect to local instances of the database engine in much the same way they connect across the network to a database engine running on a remote server. The primary difference is that local connections are made through local IPCs such as shared memory, and remote connections must go through a network.

The illustration shows using SQL Server 2000 in a desktop database system.