SQL SERVER – Understanding of Architecture

SQL Server architecture is collection of components like pages, extents, tables, indexes, query processor and internal memory structure etc. These components works together to maintain data as per customer requirement. Architecture of SQL Server is very big subject to talk, here is small effort to give you understanding and overview of architecture.

SQL Server architecture is divided into Physical Database Architecture and Relational Database Engine Architecture

Physical Database Architecture

Physical database architecture is collection various logical components that will tell you how they are implemented in database files.

Logical Components of database architecture are pages and extents,tables, indexes and space allocation and reuse.

Pages and extents

Smallest unit of storage is page, it stores user data information, latest understand each in more detail

There many type of data pages stored in SQL Server like data pages, index pages, SGAM or GAM pages, Image page, DCM (Differential change map), IAM (Index allocation map) and PFS (Page free space)

These pages give you information related data storage.

Text/Image page : Stores text/image data and LOB objects

DCM (Differential Change Map) : This page keeps the information regarding the extents that are changed since last full backup.

Physical database file and file groups

Gives you the Information about default physical database and log file location. These data file stores data in the form data pages in physical file, primary file called data file(.mdf) and transaction log file(.ldf)

Data file : basically used to store data pages in physical file location, whenever you create any database it will create two file data file and log file.

Log file : Use to track the transaction like DML and DDL, help you restore the database to specific point in time.

Data page : Stores the user data except text/image and large object

Space Allocation and reuse

Gives you the information related to space allocation and internal process communication to work together for performance improvement.

SGAM or GAM : This is global allocation map and Shared global allocation map, it related allocation of extents.

Table and index Architecture

This will tell how pages  of table and indexes are organised in SQL server database using IAM(Index allocation Map).

Index pages : Stores index data reference and pointers

IAM : Used to store information regarding the extents by a table or index per allocation unit.

Transaction log architecture

Internal structure of log that will tell you how transaction logs are used in SQL server database to keep database consistent.

SQL Server – Transaction log architecture and virtual log files


Relational Database Engine Architecture

Relational database engine handles multiple requests for data from many users and makes processing efficient. SQL Server 2005 database engine is core component which plays most important role in data handling. Data storing, processing and securing is the core feature of Microsoft SQL Server 2005 Database Engine.

The Database Engine provides access to control OLTP transaction processing to meet the requirements of large applications within organization. Whenever request sends by client using SQL statements, SQL server database engine receives those requests and process them to complete transaction.

Enjoy learning !!!

Leave a Reply




3 + = 5