Architecture Overview

SQL Server is an integral part of the Microsoft DNA strategy. In 2000, Microsoft announced its new company strategy of Microsoft.NET, which represents the evolution of its traditional DNA approach. In this section, I’ll talk about some of the core architecture components of SQL Server 2000.

Net-Library

The Net-Library is the core component that manages the connections from different protocols and networks. It enables SQL Server to listen to multiple protocols at the same time. You can easily configure your server to listen to multiple protocols by going to Server Network Utilities under the SQL Server group. Generally speaking, if you receive a communication error when trying to connect to SQL Server, it originates from the Net-Library.

Open Data Services

Open Data Services (ODS) is the component that listens for new connections and requests. ODS manages connections to SQL Server. ODS also handles sudden disconnects and the freeing up of resources. Data is encoded in a private protocol called Tabular Data Stream (TDS). TDS is the format that SQL Server communicates to its client workstations.

In the Trenches

When the packet header indicates that the packet is smaller than the minimum allowed by a TDS header, SQL Server raises an arithmetic error and crashes. The TDS problem can be exploited from inside and outside your network, unless your SQL Server port is protected by a firewall. These problems were rare in SQL Server 7.0 and were fixed rapidly after they were reported (see http://www.microso.com/technet/security/bulletin/ms99-059.asp for the 7.0 bug).

The SQL Server Engines

There are two major components to SQL Server: the relational engine and the storage engine. The relational engine includes the components needed to query data. The storage engine handles the physical storage of the data and modification of the data on disk. (At Microsoft, the developers who work on these two pieces have to worry about both functions.)

Let’s review their components and how they interact when a query is issued. The Command Parser receives requests from ODS and parses the query syntax. If the syntax is invalid, the Command Parser raises an error. If it is valid, the request is passed to the Query Optimizer.

The Query Optimizer is a component of the relational engine that prepares the query for execution. It is the SQL Server component that breaks your queries into manageable chunks and finds the quickest path to retrieve your data by using several granular queries. It had a complete overhaul in SQL Server 7.0 and was the main reason the release performed so much faster than SQL Server 6.5. (I’ll talk much more about how to analyze what Query Optimizer is doing in Chapter 6.) Once the query is broken into small chunks, the query executor executes it.

The storage engine is also a critical part of SQL Server’s operations. The engine handles the following items in SQL Server:

• Recovery from operating system or server problems

• Interaction with the operating system

• Controlling I/O and buffers of the data files

• Handling transactions, locks, and logging