SQL Server Architecture

SQL Server Architecture is made with the subsequent major components.

1. The Network Interface Layer

2. Relational Engine

3. Storage Engine and

4. SQL OS

1. The Network Interface Layer

SQL Server features a layer that permits its communication with the outer environment. This layer is that the SQL Server Network Interface (SNI) layer. It comprises of three important components:

a) The Network Protocols

b) TDS (Tabular Data Stream)

c) The Protocol layer

a) The Network Protocols: There are four protocols supported by SQL Server.

· Shared Memory: this is often the default protocol wont to hook up with a client residing locally (i.e. on an equivalent machine).

· TCP/IP: This protocol helps us to attach to the SQL server over the network via an IP address and a port number. The default TCP port employed by SQL Server is 1433.

· Named Pipes: Originally developed for LAN, this protocol is analogous to TCP/IP, but is efficient for slower networks like WAN. It uses 445 as a port number.

· VIA: Virtual Interface Adapter may be a protocol used for top performance communication between two connected systems, with the assistance of specialized hardware on both ends.

b) Tabular Data Stream (TDS): A protocol used for interacting with the database servers. the instant a TCP/IP connection is established, a corresponding link to the TDS endpoint is formed for the client-server communication.

c) The Protocol Layer: This layer unwraps the TDS packet that was created at the client’s side. Also,  results sent to the client by the server, are packaged by this layer.

 2. Relational Engine

Relational Engine also called because the query processor, It includes the components of SQL Server that determine what your query exactly must do and the way best thanks to rolling in the hay.

The various tasks of the Relational Engine are:

· Query Processing

· Memory Management

· Thread and Task Management

· Buffer Management

· Distributed Query Processing

3. Storage Engine

Storage Engine is liable for storage and retrieval of the info on to the physical storage system ( Hard Disk, SAN, etc.) Let’s understand more in detail as given below.

Any database in SQL server, there are two sorts of files (Data file and Log file) that are created at the disk level.

Data file - file physically stores the info in data pages.

Logfile - Log file used for write logs that are used for storing transactions performed on the database.

See the subsequent more details on file and log file.

Data File: file stores data within the sort of Data Page (8KB) and these data pages are logically organized in extents.

Extents: Extents are logical units within the database. they're a mixture of 8 data pages i.e. 64 KB forms an extent. Extents are often of two types, Mixed and Uniform. Mixed extents to the hold different types of pages like index, system, data etc (multiple objects). On the opposite hand, Uniform extents are dedicated to just one type (object).

Pages: As we should always know what sort of data pages are often stored in SQL Server, below mentioned are a number of them:

· Data Page: It holds the info entered by the user but not the info which is of type text, text, varchar(max), varchar(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 of XML data.

· GAM & SGAM (Global Allocation Map & Shared Global Allocation Map): they're used for saving information associated with the allocation of extents.

· PFS (Page Free Space): Information associated with page allocation and unused space available on pages.

· IAM (Index Allocation Map): Information concerning extents that are employed by a table or index per computer memory unit.

· BCM (Bulk Changed Map): Keeps information about the extents changed during a Bulk Operation.

· DCM (Differential Change Map): this is often the knowledge of extents that have modified since the last BACKUP DATABASE statement as per the computer memory unit.

Log File: It also referred to as a write-ahead log. It stores the modification to the database (DML and DDL). Sufficient information is logged to be able to:

· Rollback transactions if requested.

· Recover the database just in case of failure.

· Write-Ahead Logging is employed to make log entries.

   Transaction logs are written by the chronological order in a circular way.

   The truncation policy for logs is predicated on the recovery model.

4. SQL OS

This lies between the host machine (Windows OS) and SQL Server. All the activities performed by a database engine is taken care of by SQL OS. it's a highly configurable OS with powerful API (Application Programming Interface), enabling automatic locality and advanced parallelism. SQL OS provides the various OS services, like memory management deals with the buffer pool, log buffer and deadlock detection using the blocking and locking structure.

For More Information about SQL Azure ClickHere

 Ph No: +91-9989971070, E-Mail ID: online@visualpath.in