DEV Community

Kathir
Kathir

Posted on

Microsoft SQL Server and its Components

#architecture #database #microsoft #sql

SQL Server Instance

An SQL Server instance is an independent SQL Server environment running on a machine. Multiple instances can coexist on the same server, each with its own databases, users, configuration, and services, allowing different applications or environments to be isolated while sharing the same hardware.

Microsoft SQL Server

Microsoft SQL Server is a Relational Database Management System (RDBMS).

Applications and Tools → Connect to a SQL Server Instance → Connect to a Database → Communicate using Transact-SQL (T-SQL)

The application connects to a SQL Server instance. It selects a database within that instance. It sends T-SQL commands (queries and updates). SQL Server executes those commands and returns the results to the application.

SQL Server is a complete database product. It includes:

  • Database Engine
  • SQL Server Agent
  • Integration Services (SSIS)
  • Analysis Services (SSAS)
  • Reporting Services (SSRS)
  • Full-Text Search

The Database Engine is a component of SQL Server. It runs as a SQL Server service on the operating system.

Microsoft uses the same Database Engine technology in several products and platforms, including:

  • SQL Server (installed on your own machine or server)
  • Azure SQL Database (managed cloud database)
  • Azure SQL Managed Instance (cloud service with high SQL Server compatibility)
  • Microsoft Fabric SQL Database (SQL capabilities within Microsoft Fabric)

SQL Server Components

1. Database Engine

The Database Engine is the core service of Microsoft SQL Server. It is the component that actually stores, retrieves, updates, and secures the data.

A Database Engine is the software that manages data stored on disk.

What does the Database Engine do?

When an application sends a T-SQL query, the Database Engine:

  1. Receives the query.
  2. Checks whether the user has permission.
  3. Finds the best way to execute the query.
  4. Reads or modifies the data.
  5. Returns the results.

Responsibilities

  • Stores data
  • Executes T-SQL queries
  • Manages transactions
  • Provides security
  • Performs performance optimization (efficient way to execute a query)
  • Backup and recovery

Main Internal Components of the Database Engine

1. Query Processor

  • Parses and executes T-SQL queries.
  • Creates execution plans.

2. Storage Engine

  • Reads and writes data to disk.
  • Manages pages, indexes, and files.

3. Transaction Manager

  • Ensures data consistency using ACID properties.

4. Security Manager

  • Handles authentication and authorization.

2. Machine Learning Services

Machine Learning Services is a feature in SQL Server that gives the ability to run Python and R scripts with relational data.

It can be used with open-source packages and frameworks, as well as Microsoft Python and R packages, for predictive analytics and machine learning.

The scripts are executed on data without moving the data outside SQL Server or over the network.

Note

The Java Language Extension is a feature of SQL Server used for executing external Java code.

The default runtime used in it is Zulu OpenJRE, and it can also be changed.

3. SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) is one of the major components of Microsoft SQL Server.

Its purpose is moving, transforming, and integrating data between sources.

If you have data in different places like:

  • Excel files
  • CSV files
  • MySQL databases
  • Oracle databases
  • SQL Server databases
  • APIs

SSIS moves the data from these different sources into SQL Server.

SSIS follows the ETL Process

Extract

Extract data from different sources.

Transform

Modify the data.

Examples:

  • Remove duplicate rows
  • Convert text to uppercase
  • Change date formats
  • Calculate totals
  • Replace NULL values

Load

Store the processed data into the SQL Server database.

SSIS does not store data. Its job is to move and transform data.

SSIS can do

  • Import Excel and CSV files
  • Copy data between databases
  • Merge multiple data sources
  • Clean and validate data
  • Schedule data imports
  • Automate repetitive data movements (moving data from one location to another)

4. SQL Server Analysis Services (SSAS)

SQL Server Analysis Services (SSAS) is a component of Microsoft SQL Server used for analyzing large amounts of data and helping users make business decisions.

It is designed for analytics.

Types of Analysis in SSAS

SSAS supports two main models.

1. Multidimensional Model (Cube)

It is called a Cube, which simply means data is organized along multiple dimensions.

Dimensions

Something you want to analyze.

Examples:

  • Product
  • Customer
  • Year

Measures

Measures are numbers that you calculate.

Examples:

  • Sales
  • Profit
  • Quantity

Based on dimensions and measures, SSAS precomputes all the values so that results can be returned without waiting time.

2. Tabular Model

Instead of keeping many pre-made summaries, the model organizes all records into highly optimized columns and performs calculations very quickly whenever they are requested.

It is currently the most commonly used model since it uses DAX, which is also used by Power BI.

SSAS is an analytic engine. It creates analytical models.

5. SQL Server Reporting Services (SSRS)

SQL Server Reporting Services (SSRS) is a component of Microsoft SQL Server used to create, manage, and deliver reports.

SSRS can export reports as:

  • PDF
  • Excel
  • Word
  • CSV
  • XML
  • HTML (Web Page)

It can create:

  • Bar charts
  • Pie charts
  • Line charts
  • Gauge charts
  • Maps

It also allows interactive reports such as:

  • Search
  • Filter
  • Sort
  • Drill down into details

SSRS runs queries, retrieves data, formats it, and displays or exports it.

6. SQL Server Replication Service

Replication means copying and synchronizing data between databases.

It is the process of copying data from one database to another and keeping the copies updated.

Types of Replication in SQL Server

1. Snapshot Replication

  • Copies all the data at once at fixed intervals.
  • Example: Once a week or once a month.
  • Every snapshot copies the entire database again.

Best used when:

  • The database is small.
  • Data does not change frequently.

Not recommended for large databases.

2. Transactional Replication

Transactional Replication sends only the changes made to the database instead of copying the entire database.

Whenever data is inserted, updated, or deleted, those changes are quickly sent to the subscribers.

Best for:

  • Large databases
  • Frequently changing data
  • Real-time reporting
  • High-speed synchronization

Disadvantage

  • One-way updates only (Publisher → Subscriber).

3. Merge Replication

In Merge Replication, both databases can make changes.

SQL Server combines (merges) the changes.

If both databases update the same record differently, a conflict occurs, and SQL Server resolves it using conflict resolution rules.

Best for:

  • Mobile applications
  • Salespeople working offline
  • Branch offices
  • Point-of-Sale (POS) systems

SQL Server Replication Components

Replication involves three main roles.

1. Publisher

The main database that owns the original data.

2. Distributor

Stores replication information and delivers changes.

3. Subscriber

Receives the copied data.

Terminated Services in SQL Server 2025 (Available in SQL Server 2022)

7. Data Quality Services (DQS)

Used for cleaning and standardizing data.

8. Master Data Services (MDS)

Used for managing master data.

Master Data is data that is used by multiple databases.

Examples:

  • Company list
  • Product list

Reason for Termination

  • Low adoption compared to other SQL Server services like the Database Engine.
  • Shift toward Microsoft Fabric and Azure.
  • Better cloud solutions are available.
  • AI has changed data quality processes.
  • High maintenance cost.

Conclusion

Microsoft SQL Server is much more than just a database. Along with the Database Engine, it provides powerful services for data integration (SSIS), analytics (SSAS), reporting (SSRS), machine learning, and replication. Understanding these components helps you build, manage, analyze, and maintain enterprise-level database solutions more effectively.

As Microsoft continues shifting its focus toward Azure and Microsoft Fabric, some legacy services such as Data Quality Services (DQS) and Master Data Services (MDS) are being retired, while the core Database Engine remains the foundation across Microsoft's SQL platforms.

Top comments (0)