MS SQL Server Architecture

Interested in learning Big Data concepts from basics to advance just through small articles? Hit the follow button!

Let’s look at the logical architecture of MS SQL Server!

MS SQL Server is an RDBMS(Relational Database Management System) whose architecture can be divided into 3 broad categories:

1. Database Engine: This core service is designed to meet rapid transaction processing requirements of the data rich applications of the company. This is used for processing, storing and securing the data.
2. SQL Server Management Studio — SSMS: This is designed to manage the Database Server. Users can monitor, configure, and administer the instances of SQL Server. Apart from that, we can write scripts and queries, monitor, deploy, and upgrade data-tier components that are being used by our applications.
3. Integration, Analysis, and Reporting Services:

• Integration Services — SSIS: Designed for not just transferring Data from one location to another(example: to a data warehouse) but for transforming the data as well.
• Analysis Services — SSAS: Designed for analyzing the data along with strong data mining capabilities.
• Reporting Services — SSRS: Designed to help building, deploying, and managing reports.

Usage and Functionality Examples:

• Data Storage: Secure, Efficient, and centralized data storage with easy data retrieval and management.
• Transaction Management: By maintaining ACID (Atomicity, Consistency, Isolation, and Durability) properties this ensures that even in the case of system failure the database still remains in a consistent state.
• Business Intelligence: SSIS, SSAS, and SSRS helps in bringing meaningful insights to help grow the business by transforming the data.
• Application Integration: Can work as a backend system for various applications to support analytics and transactions.

Architecture

Let’s dig deeper into the Database Engine!

1. Databases:

System Databases:

• Master: Stores the entire metadata of an instance like configurations, logins, linked servers, etc. SQL Server will not start without this database, so if this is corrupt, we have to restore it from the previous backup.
• Model: This will act like a template for the Databases that we create. So if we want any default configurations(like stored procedures, security roles, etc) across all our Databases we can set up that here.
• Msdb: This Database is used by SQL Server Agent to schedule any alerts or jobs. By storing details on backups, Service Broker, etc., this is crucial for maintenance tasks or automations.
• Tempdb: When temporary objects, tables, etc., are created during Query processing Tempdb holds them, not just that it also holds the roll back transaction information.

User Databases:

• These are User created Databases for storing, processing, and managing their application data. Example: You might store products, locations, purchase data, etc., from your Ecommerce in the User Database. You might update ‘Model’ to apply new default configurations to this Database, use ‘Msdb’ to schedule daily backups, ‘Tempdb’ to handle your queries.

2. SQL Server Engine:

This is designed for Processing, Storing, and Securing data.

Query Processor:

• Its job is to Parse, Analyze, and execute the Query. It uses the below 3 components to achieve this:

1. CMD Parser: Converts T-SQL commands into a format that SQL Server can understand.
2. Optimizer: This will analyze different Query execution plans and choose the most cost effective plan.
3. Execution Engine: Executes the plan like data retrieval, joins, aggregations, etc.

Storage Engine:

• Designed for managing how the data will be retrieved and stored, handling structure of Database files. It uses the below components for achieving this:

1. Data Files(.mdf/ .ndf): This is the primary and secondary storage for dumping the database data like tables, indexes.
2. Log Files(.Idf): Whatever changes will be made in the database all those transactions will be recorded.
3. Buffer Manager: Whenever the data pages will be stored in memory before loading into the disk, the buffer manager will control the buffer cache.
4. Access Methods: How the data will be accessed.

Transaction Manager:

• Its roles are ACID:

* Atomicity: Makes sure all parts of the transaction are successfully executed else it will abort the transaction.

* Consistency: The Database before and after transaction will be guaranteed to be in a valid state.

* Isolation: Makes sure that all the transactions happening parallelly will be independent of each other(no interference).

* Durability: Makes sure even if the system fails, the last transaction successfully completed will remain unaffected.

It uses the below components for achieving this:

1. Log Manager: Records and manages all the transactions made to the Database.
2. Lock Manager: It will make sure to lock and control concurrency to maintain isolation.
3. Recovery Manager: In case of a crash, it will handle the recovery of the database to a consistent state.

3. SQL Server Services:

SQL Server:

• This is the main executable service that contains SQL Server Database Engine. This will take care of all the operations required to run the database like query processing, transactions, connections, etc.
• Whenever we interact with the database like run a query, update the data, etc., we’re communicating with this service.

SQL Server Agent:

• This is a Microsoft Windows Service that executes scheduled jobs(administrative tasks). This sends alerts to operators in case of any interruption or failure.

SQL Server Browser:

• This is designed to be actively listening to client requests and provide them with the connection information of the instance that they have to connect to. As when a client may not know the port number and IP address of the instance they want to connect to, so the browser gives that information.

4. Security and Authentication:

Login Accounts, Roles and Permissions.

5. SQL Scheduler and Buffer Manager:

• Scheduler manages the assigning and execution of threads for concurrent tasks by distributing CPU time, increasing parallel processing to improve the efficiency with respect to time.
• Example: Let’s say multiple users are running queries at the same time, then SQL Scheduler allocates threads to all these tasks and tries to run them concurrently on different CPUs. If the number of CPUs is less than the number of tasks then Scheduler will use the same CPU and assign execution order to tasks.
• By reducing the number of Disk input outputs we can improve the performance by saving a lot of time. To do so we cache the data in the Buffer pool.
• Example: When we’re requesting some data through a query, the Buffer Manager will first check if that data is already present in the buffer pool. If yes, it’ll return that data quickly, if not, then that data is provided from disk and is also loaded into the buffer pool for future use.

Hit the clap, comment your views if you got any value from this article (You can clap up to 50 times!), your appreciation means a lot to me :)

Feel free to connect and message me on my LinkedIn.