Intro to SQL Server | Architecture

We are familiar with Structured Query Language (SQL), a programming language which deals with relational Databases. Now, SQL Server is built over SQL. SQL Server is a relational database management system (RDBMS). It was developed and marketed by Microsoft in 1989, as SQL Server 1.0, and written in C/C++. Initially before 2016 it was only supported in Windows environment but later it was made available for Linux, Microsoft Windows Server, Microsoft Windows. SQL server is also tied to Transact-SQL, or T-SQL. Basically, T-SQL is a set of programming extensions from Microsoft which adds several features to the Structured Query Language (SQL), including transaction control, exception and error handling, row processing and declared variables. Basic Info About Databases in SQL Server : Basically, a machine can have one or multiple SQL Servers installed. And, each of whose instance may contain one or multiple databases. Then, there are schemas in the databases which have the database objects such as tables, views, and stored procedures. SQL Server databases are stored in the file system in files which are further grouped into file groups. The users are also differently classified in SQL Server, like when the users gain access to an instance of SQL Server they are identified as a login. But when they gain access to a database they are called as database user. If contained databases are enabled, a database user can be created that is not based on a login. Contained database are the databases which has the database user without logins. It includes all settings related to databases together with its metadata, so system will be having no dependency with SQL server login. A user having access to a database can be given permission to access the objects in the database. It is recommendable to create the database roles, adding the database users to the roles, and then grant access permission to the roles, although permissions can be given to the normal individual users. Since, then it is easier to keep permissions consistent and understandable as the number of users increases. Working with Databases in SQL Server : Mostly people prefer using the SQL Server Management Studio tool while working with DB in SQL Servers. Since, it has graphical user interface for creating databases and the objects in the databases. It can be installed from the SQL Server installation disk, or directly downloaded from MSDN. It also has a query editor where we can write Transact-SQL statements and directly interact with the databases. SQL Server Architecture : Microsoft SQL Server is a client-server architecture. MS SQL Server process starts with the client application sending a request. The SQL Server accepts processes and replies to the request with the processed data. The SQL Server is constituted of two main components:

1. Database Engine
2. SQLOS

These are explained as following below. 1. Database Engine : It is the core component of the SQL Server Architecture which is for storing, processing, and securing data. SQL Server supports at max 50 instances of the Database Engine on a single computer. It provides controlled access and rapid transaction processing to meet the requirements of the highest data consuming applications in the enterprises. Even the database objects like stored procedures, views, and triggers are also created and executed through the Database Engine. Internally, it consists of a relational engine for processing queries and a storage engine to manages database files, pages, index, etc. To state more briefly – Relational Engine : The ultimate task of the relational engine is to process queries and hence is also called the query processor. But some of the tasks of relational engine also may include querying processing, memory management, thread and task management, buffer management, and distributed query processing. It usually requests data from the storage engine for a given input query and processes output on the basis of that. There are 3 major components of the Relational Engine. These are :

• CMD Parser – It is the first component of the relational engine to receive the query data. It mainly check the query for Syntactic and Semantic error. And finally generates a query tree.
• Optimizer – Optimizer’s main task is to find the cheapest, not the best, cost-effective execution plan. And optimization is mainly done for DML (SELECT, INSERT, UPDATE, DELETE) commands and not all of the queries. The ultimate goal is to minimize query run time.
• Query Executor – It calls the Access Method. It provides an execution plan for data fetching logic required for execution. After the data is received from Storage Engine, the result goes into the Protocol layer. And finally, data is sent to the end user.

Storage Engine : It is mainly responsible for of storage and retrieval of data from the storage systems like disks and SAN. It have the following three major components :

• Access Method – It determines whether the query is Select or Non-Select Statement. And then invokes buffer and transfer Manager accordingly.
• Buffer Manager – It manages core functions for Plan Cache, Data Parsing & Dirty Page.
• Transaction Manager – It manages Non-Select Transaction with help of Log and Lock Managers. Also, promotes implementation of Write Ahead logging and Lazy writers.

2. SQLOS : The SQL Server Operating System (SQLOS) is a separate application layer at the lowest level of the SQL Server Database Engine that both SQL Server and SQL Reporting Services run at the top of. It was introduced in SQL Server 2005. It is under the relational engine and storage engine. It actually provides operating system services such as memory and I/O management including other services that includes exception handling and synchronization services. It actually performs the following critical functions for SQL Server :

• Scheduler and IO completion: SQLOS is responsible for signaling threads when IO is completed.
• SQLOS is responsible for managing thread synchronizations.
• Exception handling framework.
• Deadlock detection and management.
• SQLOS can control how much memory a component within SQL Server is consuming.
• Hosting services for external components such as CLR and MDAC.

Hence, we can say that it has an excellent resource management capability and always makes sure SQL server is running.