Chapter 7 - Client/Server Architecture

Microsoft SQL Server is designed to work effectively in a number of environments:

  • As a two-tier or multitier client/server database system 

  • As a desktop database system 

Client/Server Database Systems 

Client/server systems are constructed so that the database can reside on a central computer, known as a server, and be shared among several users. Users access the server through a client or server application:

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

    In a multitier client/server system, the client application logic is run in two locations:

    • The 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 and 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. 

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

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. 

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

    This can be done in a database through the use of constraints, stored procedures, and triggers. It can also be done in a server application. 

  • 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 (UPS) 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. 

In large client/server systems, thousands of users may be connected to a SQL Server installation at the same time. SQL Server 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 also allocates the available resources effectively, such as memory, network bandwidth, and disk I/O, among the multiple users.

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


Desktop Database Systems 

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

When clients use local SQL Server databases, one copy of the SQL Server database engine runs on the client and manages all the SQL Server databases on the client. Applications connect to the database engine in much the same way they connect across the network to a database engine running on a remote server.



In most cases, you have to give an application only two pieces of information to connect to a Microsoft SQL Server installation:

  • The network name of the server running SQL Server 

  • Your login ID 

Login IDs are the account identifiers that control access to any SQL Server system. SQL Server will not complete a connection unless it has first verified that the login ID you specified is valid. This verification of the login is called authentication.

There are two types of SQL Server Authentication, each of which has a different class of login ID:

  • SQL Server Authentication 

    A member of the sysadmin fixed server role first specifies to SQL Server all the valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows account or network account. You must supply both the SQL Server login and password when you connect to SQL Server. You are identified in SQL Server by your SQL Server login. 

  • Windows NT Authentication 

    A member of the SQL Server sysadmin fixed server role must first specify to SQL Server all the Microsoft Windows NT accounts or groups that can connect to SQL Server. When using Windows NT Authentication, you do not have to specify a login ID or password when you connect to SQL Server. Your access to SQL Server is controlled by your Windows NT account or group, which is authenticated when you log on to the Windows operating system on the client. When connecting, the SQL Server client software requests a Windows trusted connection to SQL Server. Windows NT will not open a trusted connection unless the client has logged on successfully using a valid Windows NT account. The properties of a trusted connection include the Windows NT group and user accounts of the client that opened the connection. Because it is impossible for a trusted connection to be completed without Windows NT having first authenticated the user, SQL Server does not have to do anything to authenticate your accounts. SQL Server gets the user account information from the trusted connection properties and matches them against the Windows NT accounts defined as valid SQL Server logins. If SQL Server finds a match, it accepts the connection. You are identified in SQL Server by your Windows NT group or user account. 

When SQL Server is running on Windows NT, members of the sysadmin fixed server role can specify one of two authentication modes:

  • Windows NT Authentication Mode 

    Only Windows NT Authentication is allowed. Users cannot specify a SQL Server login ID. 

  • Mixed Mode 

    If a user supplies a SQL Server login ID when they connect, they are authenticated using SQL Server Authentication. If they do not supply a SQL Server login ID, they are authenticated using Windows NT Authentication. 

These modes are specified during setup or with SQL Server Enterprise Manager.

The Windows 95/98 operating system does not support the server side of the trusted connection API. When SQL Server is running on Windows 95/98, it does not support Windows NT Authentication. Every user must supply a SQL Server login when they connect. When SQL Server is running on Windows NT, Windows 95/98 clients can connect to it using Windows NT Authentication.

One of the properties of a login is the default database. When a login connects to SQL Server, this default database becomes the current database for the connection, unless the connection request specifies that another database be made the current database.

Client Components

Users do not access Microsoft SQL Server directly; instead, they use an application written to access the data in SQL Server. This can include utilities that come with SQL Server, third party applications that run on SQL Server, or inhouse applications developed by programmers at the SQL Server site. SQL Server can also be accessed through COM, Microsoft ActiveX, or Windows Distributed interNet Applications Architecture (Windows DNA) components.

Applications are written to access SQL Server through a database application programming interface (API). A database API contains two parts:

  • The language statements passed to the database. 

    The language used with SQL Server is Transact-SQL. Transact-SQL supports all SQL-92 Entry Level SQL statements and many additional SQL-92 features. It also supports the ODBC extensions to SQL-92 and other extensions specific to Transact-SQL. 

  • A set of functions or object-oriented interfaces and methods used to send the language statements to the database and process the results returned by the database. 

Native API support means the API function calls are mapped directly to the network protocol sent to the server. There is no intermediate translation to another API needed. SQL Server provides native support for two main classes of database APIs:

  • OLE DB 

    SQL Server includes a native OLE DB provider. The provider supports applications written using OLE DB, or other APIs that use OLE DB, such as ActiveX Data Objects (ADO). Through the native provider, SQL Server also supports objects or components using OLE DB, such as ActiveX, ADO, or Windows DNA applications. 

  • ODBC 

    SQL Server includes a native ODBC driver. The driver supports applications or components written using ODBC, or other APIs using ODBC, such as DAO, RDO, and the Microsoft Foundation Classes (MFC) database classes. 

An example of nonnative support for an API would be a database that does not have an OLE DB provider, but does have an ODBC driver. An OLE DB application could use the OLE DB provider for ODBC to connect to the database through an ODBC driver. This provider maps the OLE DB API function calls from the application to ODBC function calls it sends to the ODBC driver.

SQL Server also supports:

  • DB-Library 

    DB-Library is an earlier API specific to SQL Server. SQL Server 7.0 supports DB-Library applications written in C. Earlier versions of SQL Server also supported developing DB-Library applications written using Microsoft Visual Basic. Existing DB-Library applications developed against earlier versions of SQL Server can be run against SQL Server version 7.0, but features introduced in SQL Server 7.0 are not available to DB-Library applications. 

  • Embedded SQL 

    SQL Server includes a C precompiler for the Embedded SQL API. Embedded SQL applications use the DB-Library DLL to access SQL Server. 

The Microsoft OLE DB Provider for SQL Server, the SQL Server ODBC driver, and DB-Library are each implemented as a DLL that communicates to SQL Server through a component called a client Net-Library.


See Also 

In This Volume 

Application Development Architecture

In Other Volumes 

"Overview of Building SQL Server Applications" in Microsoft SQL Server Building Applications 

Communication Components

Microsoft SQL Server supports several methods of communicating between client applications and the server. When the application is on the same computer as SQL Server, Windows Interprocess Communication (IPC) components, such as local named pipes or shared memory, are used. When the application is on a separate client, a network IPC is used to communicate with SQL Server.

An IPC has two components:

  • Application Programming Interface (API) 

    The API is a definition of the set of functions called by any software attempting to use the IPC. 

  • Protocol 

    The protocol defines the format of the information sent between any two components communicating through the IPC. In the case of a network IPC, the protocol defines the format of the packets sent between two computers using the IPC. 

Some network APIs can be used over multiple protocols. For example, the Named Pipes API and the Microsoft Win32® RPC API can both be used with several protocols. Other network APIs, such as the TCP/IP Sockets API, can be used with only one protocol.

The following components manage communications between SQL Server and its clients in this sequence:

  1. The client application calls the OLE DB, ODBC, DB-Library, or Embedded SQL API. This causes the OLE DB provider, ODBC driver, or DB-Library DLL to be used for SQL Server communications. 

  2. The OLE DB provider, ODBC driver, or DB-Library DLL calls a client Net-Library. The client Net-Library calls an IPC API. 

  3. The client calls to the IPC API are transmitted to a server Net-Library by the underlying IPC. If it is a local IPC, calls are transmitted using a Windows operating IPC such as shared memory or local named pipes. If it is a network IPC, the network protocol stack on the client uses the network to communicate with the network protocol stack on the server. 

  4. The server Net-Library passes the requests coming from the client to the SQL Server. 

Replies from SQL Server to the client follow the reverse sequence.

This is an illustration of the communication path when the SQL Server application runs on a separate computer from the SQL Server installation. While the illustration shows the OLE DB Provider for SQL Server, SQL Server ODBC driver, and DB-Library DLL using specific Net-Libraries, there is nothing that limits these components to these Net-Libraries. The provider, driver, and DB-Library can each use any of the SQL Server Net-Libraries.


This is an illustration of the communication path when the SQL Server application runs on the same Microsoft Windows 95/98 computer as SQL Server.


If SQL Server is running on a Microsoft Windows NT computer, the Named Pipes Net-Library is used for local communications. For local connections with no network card, Windows NT uses the file subsystem to implement a named pipe connection.

There is a matched pair of client and server Net-Libraries for each IPC API supported by SQL Server.


IPC API used

Win32 client Net-Library

Server Net-Library

Protocols supporting the IPC API


Shared Memory

(Windows 95/98 only)

(Windows 95/98 only)

Memory to memory copy (Local connections only)


Windows RPC



File system (local)

Named Pipes

Named Pipes


(Windows NT only)

File system (local)

TCP/IP Sockets

Windows Sockets




Novell IPX/SPX

Netware IPX/SPX





AppleTalk ADSP

(Windows NT only)

(Windows NT only)


Banyan VINES



(Windows NT only)

Banyan VINES

SQL Server on Windows 95/98 does not support the server Named Pipes and Banyan Vines Net-Libraries because the operating system does not support the server part of the protocol API. SQL Server does support the client side of these Net-Libraries on Windows 95/98, so Windows 95/98 clients can use them to connect to SQL Server installations on Windows NT.

The Shared Memory Net-Library does not run on Windows NT computers and the AppleTalk Net-Library does not run on Windows 95/98 computers.

Some of the Net-Libraries support only one type of protocol stack. For example, the TCP/IP Sockets Net-Library requires a TCP/IP protocol stack and the SPX Net-Library requires an IPX/SPX protocol stack. The Named Pipes and Multiprotocol Net-Libraries support several protocol stacks.

The Microsoft SQL Server Net-Libraries have been tested intensively with the Microsoft protocol stacks and are supported with these stacks. Protocol stacks from other vendors should work, provided that the stacks fully support the APIs used by the Microsoft SQL Server Net-Libraries.

When the Named Pipes or Multiprotocol Net-Libraries are used to connect a client to a server on the same computer, and the computer does not have a protocol stack, the IPC APIs are implemented by the file system.

SQL Server can be listening on any combination of the server Net-Libraries at one time. These are installed during the server portion of SQL Server Setup and the person running the Setup program can choose which combination of Net-Libraries is installed. Here are the default server Net-Libraries installed by SQL Server Setup.

Windows NT

Windows 95/98

TCP/IP Sockets

TCP/IP Sockets



Named Pipes

Shared Memory

Each SQL Server automatically listens on all of the server Net-Libraries installed on the server.

All of the client Net-Libraries are installed with the utilities portion of the Setup program. On the client, you define what Net-Libraries are used to connect to particular servers using the SQL Server Client Network Utility. You can:

  • Specify the default Net-Library used for all connections except those that use a server alias. 

  • Define server aliases that allow the use of specific Net-Libraries and connection parameters when connecting to servers that do not support the defaults. 

For a client to connect to a server running SQL Server, the client must use a client Net-Library that matches one of the server Net-Libraries the server is currently listening on. Also, both the client and server must be running a protocol stack supporting the network API called by the Net-Library being used for the connection. For example, if the client tries using the client Multiprotocol Net-Library, and the server is listening on the server Multiprotocol Net-Library, but the server is running with the TCP/IP protocol while the client computer is running only with the IPX/SPX protocol stack, the client cannot connect to the server. Both the client and the server must be using the same Net-Library and running the same protocol stack.

Each SQL Server client setup installs all of the client Net-Libraries. Setup sets Named Pipes as the default client Net-Library on both Windows NT and Windows 95/98. Because SQL Servers running on Windows NT listen to the Named Pipes Net-Library by default, all clients can use the default settings to connect to any Windows NT SQL Server installation running with the default settings. The server Named Pipes Net-Library is not supported by SQL Server installations running on Windows 95/98. Any client needing to connect to a server running on Windows 95/98 must use the SQL Server Client Network Utility to either change their default Net-Library or define server aliases specifying a Net-Library on which the Windows 95 server is listening.

When running an application on the same computer as SQL Server, you can use these names to reference the SQL Server installation.

Windows NT

Windows 95/98

Computer name

Computer name





*Where "(local)" is the word local in parentheses and "." is a period, or dot.

Using the computer name is recommended. These connections will be made with the Named Pipes Net-Library running through the file system on Windows NT and the Shared Memory Net-Library on Windows 95/98. DB-Library does not support using (local).

SQL Server uses an application-level protocol called Tabular Data Stream (TDS) for communication between client applications and SQL Server. The TDS packets are encapsulated in the packets built for the protocol stack used by the Net-Libraries. For example, if you are using the TCP/IP Sockets Net-Library, then the TDS packets are encapsulated in the TCP/IP packets of the underlying protocol.

You can configure the SQL Server packet size, which is the size of the TDS packets. The size of the TDS packets defaults to 4 KB on most clients (DB-Library applications default to 512 bytes), which testing has shown to be the optimal TDS packet size in almost all scenarios. The size of the TDS packets can be larger than the size of the packets in the underlying protocol. If this is the case, the protocol stack on the sending computer disassembles the TDS packets automatically into units that fit into the protocol packets, and the protocol stack on the client computer reassembles the TDS packets on the receiving computer.

See Also 

In Other Volumes 

"Managing Servers" in Microsoft SQL Server Administrator's Companion 

"Managing Clients" in Microsoft SQL Server Administrator's Companion 

Server Components

Besides the server Net-Libraries, there are several main server components (services) to Microsoft SQL Server:

  • Open Data Services 

  • SQL Server (MSSQLServer service) 

  • SQL Server Agent (SQLServerAgent service) 

  • Microsoft Search service 

  • Microsoft Distributed Transaction Coordinator (MS DTC service) 

When SQL Server is running on Microsoft Windows NT, SQL Server, SQL Server Agent, and MS DTC are implemented as Windows NT services. On Microsoft Windows 95/98, the server components are not implemented as services because the operating system does not support services. The Microsoft Search Service is only available on Windows NT Server or Windows NT Enterprise Edition, not on Windows NT Workstation or Windows 95/98.

The server components can be stopped and started several ways:

  • Windows NT can start each service automatically when the operating system is starting. 

  • Use SQL Server Service Control Manager to start or stop the service. 

  • Use SQL Server Enterprise Manager to start or stop the service. 

  • On Windows NT, use the net start and net stop command prompt commands to stop or start each service. 

Open Data Services is not started or stopped on its own; it starts or stops when SQL Server stops or starts.

Open Data Services Architecture

Open Data Services is an interface between server Net-Libraries and server-based applications. It allows you to develop:

  • Extended stored procedures that extend the power of Transact-SQL and Microsoft SQL Server. 

  • A server application that accepts and processes Transact-SQL statements sent to it from client applications (SQL Server is an example of an Open Data Services application). 

Open Data Services has an API consisting of function calls and macros used to develop Open Data Services server applications using C or C++.

Open Data Services runs on the server. The server Net-Libraries receive client TDS packets and pass them to Open Data Services. Open Data Services transforms the TDS packets into events that it passes to server applications through callback API functions. The server application then uses another set of Open Data Services API functions to send replies back to SQL Server clients. SQL Server is implemented as an Open Data Services application. Open Data Services is started automatically when you start SQL Server.

The main type of Open Data Services applications still written by customers are extended stored procedures. This feature of SQL Server allows you to implement C or C++ functions that can be called directly from Transact-SQL statements. Extended stored procedures can open a connection back to the SQL Server installation that called them, or connect to remote SQL Server installations. Extended stored procedures can include most of the features of Microsoft Win32 and COM applications, and extend Transact-SQL functionality.


In the past, Open Data Services was also used to write server applications, such as gateways, to other database systems. These types of applications have largely been replaced by newer technologies such as:

  • Database APIs that support multiple different databases and other data sources, such as OLE DB and ODBC.

    Applications written to the OLE DB or ODBC APIs have little need for a gateway to access different databases. 

  • The support in SQL Server version 7.0 introduces heterogeneous distributed queries, which allow Transact-SQL queries to pull data from any OLE DB data source without any need for specialized server applications. 

  • MS DTC, which allows distributed transactions to span multiple databases. 

  • Windows NT Component Services, for running midtier application logic. 

See Also 

In Other Volumes 

"Programming Open Data Services" in SQL Server Books Online

MSSQLServer Service

Microsoft SQL Server runs as a service named MSSQLServer on Microsoft Windows NT. It does not run as a service on Microsoft Windows 95/98 because the operating system does not support services. SQL Server can also run as an executable file on Windows NT, although it is usually run as a service.

SQL Server manages all of the files that comprise the databases on the server. It is the component that processes all Transact-SQL statements sent from SQL Server client applications. SQL Server can also execute stored procedures in other remote servers and supports distributed queries that retrieve data from multiple sources, not just SQL Server.

SQL Server allocates computer resources effectively between multiple concurrent users. It also enforces business rules defined in stored procedures and triggers, ensures the consistency of the data, and prevents logical problems such as having two people trying to update the same data at the same time.

SQLServerAgent Service

SQL Server Agent supports features allowing the scheduling of periodic activities on Microsoft SQL Server, or the notification to system administrators of problems that have occurred with the server. The SQL Server Agent components that implement this capability are:

  • Jobs 

    Defined objects consisting of a one or more steps to be performed. The steps are Transact-SQL statements that can be executed. Jobs can be scheduled, for example, to execute at specific times or recurring intervals. 

  • Alerts 

    Actions to be taken when specific events occur, such as a specific error, errors of certain severities, or a database reaching a defined limit of free space available. The alert can be defined to take such actions as sending an e-mail, paging an operator, or running a job to address the problem. 

  • Operators 

    People identified through their network account or e-mail ID who can address problems with the server. They can be the targets of alerts, either through e-mail, a pager, or a net send network command. 

Here are the primary components used in the definition and operation of jobs, alerts, and operators.


  • The definitions are stored by SQL Server in the msdb system database. 

  • When the SQLServerAgent service is started, it queries the system tables in the msdb database to determine what jobs and alerts to enable. 

  • SQL Server Agent executes jobs at their scheduled time. 

  • SQL Server passes any events that occur to the SQL Server Agent. 

  • SQL Server Agent executes any alerts, or sends SQL Mail requests to SQL Server, or sends net send commands to Windows. 

SQL Server version 7.0 is more highly automated than earlier versions of SQL Server, and does a better job of configuring itself automatically to meet processing demands. These features lower the potential for exception conditions that would trigger alerts. Scheduled jobs remain a good feature for implementing recurring tasks such as backup procedures.

See Also 

In Other Volumes 

"Automating Administrative Tasks" in Microsoft SQL Server Database Developer's Companion 

Microsoft Search Service

The Microsoft Search service is a full-text indexing and search engine.

Earlier versions of Microsoft SQL Server supported only basic character search capabilities:

  • For a character value equal to, less than, or greater than a character constant. 

  • For a character value containing a string pattern. 

These searches could be performed only against char and varchar columns in a database, although the PATINDEX function could be used to find the location of a string in a column having a text data type.

Using the Microsoft Search service allows SQL Server version 7.0 to support more sophisticated searches on character string columns.

The Microsoft Search service has two roles:

  • Indexing support

    Implements the full-text catalogs and indexes defined for a database. Accepts definitions of full-text catalogs, and the tables and columns making up the indexes in each catalog. Implements requests to populate the full-text indexes. 

  • Querying support

    Processes full-text search queries. Determines which entries in the index meet the full-text selection criteria. For each entry that meets the selection criteria, it returns the identity of the row plus a ranking value to the MSSQLServer service, where this information is used to construct the query result set. The types of queries supported include searching for:

    • Words or phrases. 

    • Words in close proximity to each other. 

    • Inflectional forms of verbs and nouns. 

The full-text engine runs as a service named Microsoft Search on Microsoft Windows NT. It is installed when the Full-Text Search feature is selected during custom installation. The Microsoft Search service itself is not installed on Microsoft Windows 95/98 or Windows NT Workstation, although Windows 95/98 clients and Windows NT Workstation clients can make use of the service when connected to a SQL Server installation running on Windows NT Server.

The Microsoft Search service runs in the context of the local system account. During setup, SQL Server adds itself as an administrator of the Microsoft Search service. To ensure this relationship is maintained correctly, all changes to the MSSQLServer service account information must be made using the Properties tab of the SQL Server Properties dialog box in SQL Server Enterprise Manager.

The full-text catalogs and indexes are not stored in a SQL Server database. They are stored in separate files managed by the Microsoft Search service. The full-text catalog files are accessible only to the Microsoft Search service and the Windows NT system administrator.


See Also 

In This Volume 

Full-text Query Architecture

Full-Text Catalogs and Indexes

MS DTC Service

The Microsoft Distributed Transaction Coordinator (MS DTC) is a transaction manager that allows client applications to include several different sources of data in one transaction. MS DTC coordinates committing the distributed transaction across all the servers enlisted in the transaction.

A Microsoft SQL Server installation can participate in a distributed transaction by:

  • Calling stored procedures on remote servers running SQL Server. 

  • Automatically or explicitly promoting the local transaction to a distributed transaction and enlist remote servers in the transaction. 

  • Making distributed updates that update data on multiple OLE DB data sources.

    If these OLE DB data sources support the OLE DB distributed transaction interface, SQL Server can also enlist them in the distributed transaction. 

The MS DTC service coordinates the proper completion of the distributed transaction to ensure that either all of the updates on all the servers are made permanent, or, in the case of errors, all erased.


SQL Server applications can also call MS DTC directly to start a distributed transaction explicitly. One or more servers running SQL Server can then be instructed to enlist in the distributed transaction and coordinate the proper completion of the transaction with MS DTC.


See Also 

In Other Volumes 

"Distributed Transactions" in Microsoft SQL Server Database Developer's Companion