Lesson 3: Overview of SQL Server Architecture

[Previous] [Next]

SQL Server provides a number of structured architectures that hide underlying technical details, simplifying the development, maintenance, and management of your database applications. This lesson provides an overview of how SQL Server clients communicate with the server, the structure of SQL Server client applications, and the administration model of SQL Server.

After this lesson, you will be able to

• Describe how SQL Server communicates with clients
• Describe the database interfaces available to application developers
• Describe the administrative components of SQL Server
• Describe SQL Server application design

Estimated lesson time: 30 minutes

Communication Architecture

SQL Server uses a layered communication architecture to isolate applications from the underlying network and protocols (see Figure 1.7). This architecture allows you to deploy the same application in different network environments.

Figure 1.7 The layered communication architecture

The components of the communication architecture include applications, database interfaces, network libraries, and Open Data Services.

Applications

An application is developed using a database application programming interface (API) or object model. The application has no knowledge of the underlying network protocols used to communicate with SQL Server. Examples of client applications include SQL Server Enterprise Manager, SQL Server Query Analyzer, osql, ad hoc query and report writers, Web server-based applications, and business applications.

Database Interfaces

The database interface is used by the application to send requests to SQL Server. It also receives results returned by SQL Server and passes them to the application, sometimes processing the results first. The database interfaces for SQL Server are OLE DB, ODBC, and DB-Library.

Network Libraries

A network library is a communication software component that packages the database requests and results for transmission by the appropriate network protocol. A network library, also known as a Net-Library, must be installed on both the client and the server.

Clients and servers can use more than one Net-Library concurrently, but they must use a common network library in order to communicate successfully. SQL Server has Net-Libraries that support network protocols such as TCP/IP, Named Pipes, Novell IPX/SPX, Banyan VINES/IP, and AppleTalk ADSP.

Some Net-Libraries support only one network protocol; for example, the TCP/IP Sockets Net-Library supports only the TCP/IP network protocol. Other Net-Libraries, such as the Multiprotocol Net-Library, support multiple network protocols. All of the SQL Server database interfaces work on any of the Net-Libraries.

Consider two main criteria when choosing a Net-Library. First, review the capabilities of the Net-Libraries and match them to your needs. For example, the Multiprotocol Net-Library supports encrypting data sent across the network, but it does not support server name enumeration. Second, match the Net-Library to your network infrastructure and client network software.

Open Data Services

Open Data Services functions as the interface between server Net-Libraries and server-based applications. SQL Server itself and extended stored procedure DLLs are examples of server-based applications. This component handles network connections, passing client requests to SQL Server for processing and returning any results and replies to SQL Server clients. Open Data Services automatically listens on all server Net-Libraries that are installed on the server.

Data Access Architecture

Users use SQL Server databases through an application that uses a data object interface or an API to gain access to SQL Server (see Figure 1.8).

SQL Server supports commonly used and emerging database interfaces. It supports low-level native APIs as well as easy-to-use data object interfaces.

Figure 1.8 The data access architecture

Application Programming Interfaces

A database API defines how to write an application to connect to a database and pass commands to the database. SQL Server provides native support for two main classes of database APIs, which in turn determine the data object interface that you can use. Use the database APIs to have more control over application behavior and performance. Full access to SQL Server 7 is supported by the ODBC and OLE DB database APIs. The DB-Library API used in previous versions of SQL Server provides backward compatibility but does not support the new functionality found in SQL Server 7.

OLE DB

The OLE DB interface is a Component Object Model (COM) based data access interface. It supports applications written to use OLE DB or data object interfaces that use OLE DB. OLE DB is designed to work with relational databases (such as those in SQL Server) as well as with nonrelational data sources (such as a full-text index or an e-mail message store).

OLE DB uses a provider to gain access to a particular data source. Providers for SQL Server, Oracle, Jet (Microsoft Access databases), and ODBC are supplied with SQL Server. Using the OLE DB provider for ODBC, you can use OLE DB to gain access to any ODBC data source.

ODBC

The ODBC interface is a call-level interface. It directly accesses the SQL Server TDS protocol and supports applications or components that are written to use ODBC or data object interfaces that use ODBC. ODBC is designed to work with relational databases (such as those in SQL Server) only, although there are limited ODBC drivers available for some nonrelational data sources.

ODBC uses a driver to gain access to a particular data source.

Data Object Interfaces

In general, data object interfaces are easier to use than database APIs but may not expose as much functionality as an API. ADO (ActiveX Data Objects) is the data object interface for OLE DB, and RDO (Remote Data Objects) is the data object interface for ODBC.

ActiveX Data Objects

ActiveX Data Objects (ADO) encapsulates the OLE DB API in a simplified object model that reduces application development and maintenance costs. It can be used from Microsoft Visual Basic, Visual Basic for Applications, Active Server Pages (ASP), and the Microsoft Internet Explorer scripting object model.

Remote Data Objects

Remote Data Objects (RDO) maps over and encapsulates the ODBC API. It can be used from Microsoft Visual Basic and Visual Basic for Applications.

Administrative Architecture

SQL Server provides a variety of management tools that minimize and automate routine administrative tasks. Figure 1.9 shows how the administrative tools use different interfaces to communicate with SQL Server.

Figure 1.9 The administrative architecture

SQL Server Administration

You can administer SQL Server using

• Batch utilities provided with SQL Server, such as osql and bcp
• Graphical administrative tools provided with SQL Server (SQL Server Enterprise Manager, SQL Server Query Analyzer, and SQL Profiler)
• COM-compatible applications, written in languages such as Visual Basic

All of these tools use Transact-SQL statements to initiate actions on SQL Server. In some cases you will specify the Transact-SQL statements explicitly; in others the tool will generate the Transact-SQL statements for you.

SQL Distributed Management Objects

SQL Distributed Management Objects (SQL-DMO) is a COM-based object model provided by SQL Server. SQL-DMO hides the details of the Transact-SQL statements; it is suitable for writing administrative applications and scripts for SQL Server. The graphical administrative tools provided with SQL Server are written using SQL-DMO. SQL-DMO is not a data interface model; it should not be considered for writing standard database applications.

SQL Server Agent

SQL Server Agent is a service that works in conjunction with SQL Server to enable the following administrative capabilities: alert management, notification, job execution, and replication. Each of these is discussed in the sections that follow.

Alert Management

Alerts provide information about the status of a process, such as when a job is complete or when an error occurs. SQL Server Agent monitors the Windows NT application event log for events, generating alerts based on them.

Notification

SQL Server Agent can send e-mail messages, page an operator, or start another application when an alert occurs. For example, you can set an alert to occur when a database or transaction log is almost full and then have the alert generate a notification to inform someone that the alert has occurred.

Job Execution

SQL Server Agent includes a job creation and scheduling engine. Jobs can be simple, single-step operations, or they can be complex, multistep tasks that require scheduling. You also can create job steps with Transact-SQL, scripting languages, or operating system commands.

Replication Management

Replication is the process of copying data or transactions from one SQL Server to another. SQL Server Agent is responsible for synchronizing data between servers, monitoring the data for changes, and replicating the information to other servers.

Application Architecture

Before you design an application for SQL Server, it is important to spend time designing a database to model the business accurately. A well-designed database requires fewer changes and generally performs more efficiently. Planning a database design requires knowledge of the business functions you want to model and the database concepts and features you will use to represent those business functions.

A number of application architectures can be used with SQL Server. The application architecture you select depends on your database design and your business needs and affects how you develop, deploy, and manage your software application. This section presents some of the most common architectural designs and deployment options.

Application Layering

Selecting a layered application approach affords flexibility and a choice of management options. You can divide software applications into three logical layers. Each layer, which can physically reside on one or more servers, provides clearly defined services, as shown in Figure 1.10. The three layers are as follows:

• Presentation services—Format and present information to users and provide an interface for data entry.
• Business services—Implement business rules by, for example, checking limits, validating data, and providing calculated or summarized data.
• Data services—Provide storage, retrieval, security, and integrity for data.

Once these layers have been defined, they can be implemented in various ways, as described in the sections that follow.

Intelligent Server (Two-Tier)

With a two-tiered architecture using an intelligent server, most processing occurs on the server, with presentation services handled on the client. In many instances, most of the business services logic is implemented in the database. This design is useful when clients do not have sufficient resources to process the business logic. However, the server can become a bottleneck because database and business services compete for the same hardware resources. Network use can be inefficient because the client has to send all data to the server for verification, sometimes re-sending the same data many times until it is approved by the server.

Figure 1.10 A layered application architecture

Corporate applications designed from a database-centric point of view are an example of this design.

Intelligent Client (Two-Tier)

With a two-tiered architecture using an intelligent client, most processing occurs on the client, with data services handled on the server. This design is the traditional client/server environment that is widely used. However, network traffic can be heavy and transactions longer (that is, clients may lock data for lengthy periods), which can in turn affect performance.

Applications developed for small organizations with products such as Microsoft Access are an example of this design.

N-Tier

In an n-tier approach, processing is divided among a database server, an application server, and clients. This approach separates logic from data services, and you can easily add more application servers or database servers as needed. However, the potential for complexity increases, and this approach may be slower for small applications.

Multitiered enterprise applications and applications developed with transaction processing monitors are examples of this design.

Internet

In the Internet architecture, processing is divided into three layers, with the business and presentation services residing on the Web server and the clients using simple browsers. Any client that has a browser is supported, and software does not need to be maintained on the client.

A Web site that uses several Web servers to manage connections to clients and a single SQL Server database that services requests for data is an example of this architecture.

Lesson Summary

SQL Server has many components, so it is important to understand how they work together.

Database interfaces provide a consistent interface to database services for programmers developing client applications for SQL Server. The administrative architecture of SQL Server allows administrators to easily create complex maintenance plans for SQL Server databases, including automated job schedules and response to events.

Applications should be designed using a layered architecture that clearly defines where the different services in the application will be implemented.