An Overview of the DBMS Components

The SQL Server 2005 DBMS comprises a number of interconnected I/O architectures or modules. The following are key components of the database engine you should make a special effort to understand before attempting to program SQL Server 2005:

• The relational database engine   This component comprises several modules or collateral components that interact with each other on a common code base. With the storage engine and its modules, the relational database engine makes up the DBMS. The system also includes statement parsing and processing modules (for both SQL and XML), and the query processing system. The engine receives all communications from the client, passed to it as events from the Net-Libraries. The query containing T-SQL statements are first parsed, then preprocessed, and then compiled into execution plans, which are sent up against the data store, via the storage engine, for extraction. The extracted data is then built into the tabular data formation (the tabular data streams (TDS) protocol) that is sent back to the client through the Net-Libraries.

• The storage engine   This suite of components is responsible for the management of the database files and the management of the space within the files. While we include it as part of the DBMS, it is really at the center of SQL Server’s database architecture, which is discussed in Chapter 2. The storage engine is also responsible for transaction logging and database recovery.

• The transaction architecture   Understanding the transaction architecture is key to understanding most of what makes up SQL Server 2005 and how you adapt the various features to your ends. There are essentially two transaction architectures that you will learn about in this book: a local transaction architecture responsible for the work that performs deletes, updates, inserts, and so on; and the distributed transactions architecture that involves or encompasses data from external sources. The latter is discussed in Chapter 16.

• Concurrency control and transaction management   Part of the transaction architecture, it deserves separate treatment. It is really a system of methods and algorithms that allow multiple users to exploit the databases under the management of an instance of the DBMS. Concurrency in SQL Server is handled by a pessimistic locking architecture built into the relational engine. The locking mechanism allows you to obtain locks on rows, pages, ranges of keys, indexes, tables, and whole databases. Besides the architecture discussed here, locking is also discussed extensively in Chapter 17.