SQL Server 2005 Database Engine is the core service that handles storing, processing and securing the data - it provides controlled access and transaction processing. The SQL Server database engine is touted as a secure, reliable and scalable database engine with support for structured and unstructured XML data.

SQL Server 2005 Database Engine Architecture

SQL Server 2005 database engine is comprised of the following components:

• Protocols: this component of SQL Server 2005 is responsible for handling the communication with clients. It performs the necessary translation of the client requests before passing them to the Query Proccessor and then takes any responses from the Query Processor and translates them before passing to the client who submitted the request in the first place. SQL Server supports the following protocols (it can support those protocols simultaneously):
  • Shared Memory - simple protocol, no configuration needed but client can only connect to a SQL Server instance running on the same machine;
  • TCP/IP - common protocol widely used over the Internet to connect to SQL Server databases. It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems.
  • Named Pipes - a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other.
  • Virtual Interface Adapter
• Query Processor: responsible for processing the request (queries) - it parses the query, determines the best execution plan and manages the execution of the query. The SQL Server 2005 Query Processor is comprised of the following sub-components:
  • Parser - checks the T-SQL requests for proper syntax and translates it into the query tree that is then passed on to the Query Optimizer;
  • Query Optimizer - responsible for generating the execution plan - it normalizes each query, optimizes it based on cost (memory requirements, CPU utilization, I/O operations);
  • SQL Manager - responsible for managing SQL Server stored procedures and their plans and also handles the autoparameterization of queries. SQL Server 2005 treats certain kinds of queries similar to parameterized stored procedures - it generates and saves query plans for such queries;
  • Database Manager - responsible for handling the access to the metadata required for the query compilation and optimization such as datatypes of columns and available indexes on a SQL Server database table;
  • Query Executor - responsible for running the execution plan that was produced and handed over by the query optimizer - executes each command also handling the necessary interaction with the SQL Server storage engine;
• Storage Engine: handles all processing of data in the SQL Server database and is made up of the following components:
  • Access Methods - responsible for "finding" the requested data and preparing the OLE DB row sets to return to the Query Processor, as well as going on the other direction, that is, receiving an OLE DB row set from the Query Processor and handling the insert operation - the access methods code contains the instructions to open a table, retrieve qualified data and update data. The Row and Index Operations, Page Allocation Operations and Versioning Operations are components of the SQL Server 2005 Access Methods.
  • Transaction Management - as the name indicates this component of the SQL Server database engine is responsible for managing transactions - it ensures that all transactions adhere to the four basic properties, that is atomicity, consistency, isolation and durability (ACID). The SQL Server 2005 Transaction Management component coordinates logging, recovery and the buffer management. It also coordinates with the locking code the release of the locks based on the isolation level.
    
    In addition of the pessimistic concurrecy model SQL Server 2005 also supports optimistic concurrency which utilizes version store to avoid blocking of readers by writers and vice versa.
  • SQL Operating System (SQLOS) - responsible for handling the communication between all SQL Server engine components and the Operating System, in fact, as far as the rest of the SQL Server is concerned the SQLOS is the Operating System - it handles memory management, scheduling, I/O, locking, deadlock detection, transaction management, exceptions etc.
  • Other Utilities - this includes other SQL Server componets that handle backup and restore operations, DBCC commands, bulk loads, sorting operations and buffer management.

Click here to go back to the SQL Server content index page.