Microsoft SQL Server is a commercial relational database management system developed by Microsoft that stores data in tables, processes queries written in Transact-SQL (T-SQL) and provides transactions, security, high availability and analytics in one product. Among database systems, SQL Server, often shortened to MSSQL, is one of the most widely deployed engines for business applications, especially in organizations that build on Windows, .NET and Microsoft’s cloud.
In Databases practice, the name covers both the core Database Engine and a family of companion services for integration, reporting and analysis. The product runs on Windows and, since the 2017 release, on Linux and in containers, and its engine also powers the managed cloud services Azure SQL Database and Azure SQL Managed Instance.
Architecture of the SQL Server database engine
SQL Server runs as an instance, a service process that manages one or more databases. Each database consists of at least one data file (.mdf, with optional .ndf files) and a transaction log file (.ldf). Data is stored in 8 KB pages grouped into 64 KB extents, and every change is written to the log first, which allows recovery to a consistent state after a crash.
Inside the instance, the relational engine parses and optimizes queries with a cost-based optimizer and caches execution plans for reuse, while the storage engine handles pages, locking, logging and the buffer pool that keeps frequently used pages in memory. The engine includes its own scheduler layer, SQLOS, which manages threads and memory independently of the operating system’s defaults.
| Component | Function | Typical use |
|---|---|---|
| Database Engine | Storage, queries, transactions, security | Application databases |
| SQL Server Agent | Job scheduling and alerts | Backups, maintenance, ETL jobs |
| Integration Services (SSIS) | Extract, transform and load workflows | Data warehouse loading |
| Analysis Services (SSAS) | Tabular and multidimensional models | Business intelligence analytics |
| Reporting Services (SSRS) | Paginated reports | Operational and regulatory reports |
| Query Store | Captures query plans and runtime statistics | Diagnosing plan regressions |
Editions and their limits
SQL Server is sold in editions that share the same engine but differ in scale limits, features and licensing. Choosing the right edition is a major cost decision, because Enterprise licensing costs several times more per core than Standard.
| Edition | Key limits | Typical use |
|---|---|---|
| Enterprise | Limited only by the operating system | Mission-critical, large-scale workloads |
| Standard | Lesser of 4 sockets or 24 cores, capped buffer pool memory | Departmental and mid-size applications |
| Express | 10 GB per database, small CPU and memory caps | Small applications, embedded use, learning |
| Developer | Full Enterprise features, not for production | Development and testing |
| Web | Restricted to public web hosting | Hosting providers |
Exact memory and core caps change between releases, so they should be verified for the specific version being deployed. Licensing is either per core, sold in two-core packs with a minimum per server or virtual machine, or for Standard edition alternatively server plus client access licenses.
Distinctive features of SQL Server
Beyond standard relational functions, SQL Server offers several features that shape how it is used. Always On availability groups replicate databases to secondary replicas for failover and read scaling, while failover cluster instances protect the whole instance using shared storage. Columnstore indexes store data by column with heavy compression, which speeds analytical scans substantially.
- In-Memory OLTP provides memory-optimized tables and natively compiled procedures for very high transaction rates.
- Read committed snapshot isolation (RCSI) uses row versioning so readers do not block writers.
- Temporal tables keep a full history of row changes automatically.
- Transparent Data Encryption and Always Encrypted protect data at rest and sensitive columns.
- Intelligent query processing adapts memory grants and join choices based on runtime feedback.
One frequently missed default is that an on-premises SQL Server database uses locking-based READ COMMITTED unless RCSI is enabled, whereas Azure SQL Database enables RCSI by default. This difference changes blocking behavior noticeably when an application moves between them.
SQL Server compared with other database systems
SQL Server competes most directly with Oracle Database in the enterprise market and with PostgreSQL and MySQL where open-source licensing matters. The comparison below focuses on practical differences.
| Aspect | SQL Server | PostgreSQL | Oracle Database | MySQL |
|---|---|---|---|---|
| License | Commercial, free Express and Developer | Open source | Commercial | Open source and commercial |
| Procedural language | T-SQL | PL/pgSQL and others | PL/SQL | SQL/PSM procedures |
| Default concurrency | Locking, optional row versioning | MVCC | MVCC with undo | MVCC in InnoDB |
| Platform | Windows, Linux, containers | Most platforms | Most platforms | Most platforms |
| Built-in BI stack | SSIS, SSAS, SSRS | External tools | Separate products | External tools |
SQL Server’s strengths are integrated tooling, such as SQL Server Management Studio, strong Windows and Active Directory integration and a mature optimizer. Its main drawbacks are licensing cost at scale and fewer extension options than PostgreSQL.
Operating SQL Server well
Most SQL Server problems in production trace back to configuration defaults and missing maintenance, not to the engine itself.
| Issue | Cause | Remedy |
|---|---|---|
| Transaction log fills the disk | Full recovery model without log backups | Schedule log backups or use simple recovery |
| Server memory pressure | Max server memory left at default | Set max server memory below physical RAM |
| Sudden slow query | Parameter-sensitive plan reuse | Use Query Store to force a good plan |
| Heavy blocking | Locking READ COMMITTED with long transactions | Enable RCSI, shorten transactions |
| Untested recovery | Backups never restored | Run regular restore tests and integrity checks |
In simple terms
Microsoft SQL Server is a large, organized filing system for business data made by Microsoft. Accounting software, online shops and hospital systems store their records in it. They also ask it questions, such as how many orders arrived today.
It is built to keep data safe and consistent even when thousands of people use it at once. It also comes with tools for reports and analysis.
A practical example
A regional insurance company runs its policy administration system on SQL Server Enterprise. The main database holds about 2 TB of policy and claims data and serves several hundred staff during office hours, while actuaries run heavy analytical queries that previously blocked claim processing.
The database team configured an Always On availability group with a synchronous replica in the same data center for automatic failover and an asynchronous replica in a second site for disaster recovery. Analytical reports were redirected to a readable secondary replica, and a nonclustered columnstore index was added to the claims history table. Blocking during working hours disappeared, and a planned failover test completed in under a minute without data loss.