SQL server–beginning of SQL SERVER architecture–terminology–guest Post

August 30, 2012by pinal DAVE SQL server–beginning of SQL SERVER architecture–terminology–guest Post

SQL Server Architecture is a very deep subject. Covering it in a single post was an almost impossible task.  However, this subject was very popular topic among beginners and advanced users. I had requested my friend Anil Kumar who was expert in SQL Domain to help me write a simple post about Beginning SQL Serv Er Architecture. As stated earlier this subject was very deep subject and in this first article series he had covered basic terminologies. In the future article he'll explore the subject further down. Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several it certifications, such as Oracle 11g, server+, RHCA, SQL Server Training, Prince2 Foundation etc.

In this article we'll discuss about MS SQL Server architecture.

The major components of SQL Server is:

1. Relational Engine
2. Storage Engine
3. SQL OS

Now we'll discuss and understand each one of the them.

1) relational engine:also called as the query processor, relational Engine includes the components of SQL Server that Det Ermine what your query exactly needs to doing and the best of it. It manages the execution of queries as it requests data from the storage engine and processes the results returned.

Different Tasks of Relational Engine:

1. Query processing
2. Memory Management
3. Thread and Task Management
4. Buffer Management
5. Distributed Query Processing

2) Storage Engine:storage Engine is responsible for Storage and retrieval of the data in to the Storage system (Disk, SAN etc.). to understand more, let's focus on the concepts.

When we talk about any database in SQL Server, there is 2 types of files that is created at the disk level– Data fi Le and Log file. Data file physically stores the data in data pages. Log files That is also known as write ahead logs, is used for storing transactions performed on the database.

Let's understand data file and log file in more details:

data file: data File stores data in the form of data Page (8KB) and these data pages is Logica Lly organized in extents.

extents: Extents is logical units in the database. They is a combination of 8 data pages i.e. (KB) forms an extent. Extents can be of types, Mixed and Uniform. Mixed extents hold different types of pages like index, system, data etc (multiple objects). On the other hand, Uniform extents is dedicated to only one type (object).

Pages: As we should know what type of data Pages can is stored in SQL Server, below mentioned is some of them:

• Data Page:it holds the data entered by the user and not the data which is of type text, ntext, nvarchar(max), Var Char(max), varbinary(max), image and XML data.
• Index:it stores the index entries.
• Text/image:it Stores LOB (Large Object data) like text, ntext, varchar(max), nvarchar (max),varbinary(max), Image and XML data.
• GAM & SGAM (Global Allocation map & Shared Global Allocation map): They is used for saving information related to The allocation of extents.
• PFS (Page free Space): Information related to Page allocation and unused Space available on pages.
• IAM (Index Allocation Map): Information pertaining to extents, is used by a table or Index per Allocation unit.
• BCM (Bulk Changed Map): Keeps information about the extents Changed in a Bulk operation.
• DCM (differential change Map): This is the information of extents which has modified since the last BACKUP DATABASE Statem ENT as per allocation unit.

log File: It also known as write ahead Log. It stores modification to the database (DML and DDL).

• Sufficient information is logged to being able to:
  • Roll back transactions if requested
  • Recover the database in case of failure
  • Write Ahead Logging is used to create log entries
    • Transaction logs is written in chronological order in a circular
    • Truncation policy for logs are based on the recovery model

SQL OS: This lies between the host machine (Windows OS) and SQL Server. All the activities performed on database engine is taken care of by SQL OS. It is a highly configurable operating system with powerful API (application programming Interface), enabling automatic loc Ality and advanced parallelism. SQL OS provides various operating system services, such as memory management deals with buffer pool, log buffer and Deadlo CK detection using the blocking and locking structure. Other services include exception handling, hosting for external, and the like Common Language Runtime, CLR etc.

I Guess this brief article gives a idea about the various terminologies used related to SQL Server Architecture. In the future articles we'll explore them further.

Guest Author

The author of the article is Anil Kumar Yadav is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several it certifications, such as Oracle 11g, server+, RHCA, SQL Server Training, Prince2 Foundation etc.

Reference: pinal Dave (http://blog.sqlauthority.com

SQL server–beginning of SQL SERVER architecture–terminology–guest Post