Upraviť

Zdieľať cez

The Microsoft SQL Database Engine

The Microsoft SQL Database Engine is the core service for storing, processing, and securing data. It provides controlled access and rapid transaction processing for a wide variety of commercial and entrepreneurial applications.

The SQL Database Engine is the common underlying engine behind all Microsoft SQL offerings. It's an enterprise-scale, general-purpose relational database management system (RDBMS).

For millions of customers worldwide, in every industry and level of organization, the Microsoft SQL Database Engine is the database service for secure data processing and storage. Demanding applications can reliably read and modify information while preserving integrity at scale. The SQL Database Engine runs as a service that accepts client connections and then executes the requested operations against databases. Secured with enterprise-class data access and encryption features, and with built-in high availability and database recovery features, the SQL Database Engine is a complete database platform.

Multimodal database engine

The SQL Database Engine is a multimodal database engine with purpose-built storage formats, purpose-built index structures, and a single query optimizer that makes cost-based decisions across all data models.

Products that use the SQL Database Engine can be the enterprise-class online transactional processing (OLTP), online analytical processing (OLAP), or non-relational solution for your modern applications. Relational, normalized data might be common for databases, but the SQL Database Engine supports many data models and data formats.

All capabilities appear in the same SQL Database Engine, using the same Transact-SQL (T-SQL) query language, under the same security layer, and using the same HA/DR solutions.

Tooling

The SQL Database Engine comes with a suite of free, industry-leading tools for querying, data architecture, automation, and database development.

The database administrator (DBA) manages backup and restore, performance tuning, security, and high availability.

Recommended tools:

Database fundamentals: ACID compliance

A core tenet of any RDBMS is support for ACID properties of transactions. A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties to qualify as a transaction: atomicity, consistency, isolation, and durability (ACID).

Property Description
Atomicity A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them are performed.
Consistency When completed, a transaction must leave all data in a consistent state. In a relational database, all rules and declared constraints must be applied to the transaction's modifications to maintain data integrity.
Isolation Modifications made by one transaction must be isolated from the modifications made by other concurrent transactions. Partial or intermediate states between transactions aren't allowed. This property is also called serializability because it results in the ability to replay a series of sequential transactions that result in the same database state.
Durability After a transaction completes, it writes to nonvolatile storage, so the system records its effects even in the event of a failure. Transactions committed only to volatile memory (RAM) aren't durable.

For more information on transactions, see Transaction locking and row versioning guide. For a deep dive into the SQL Database Engine, see SQL Server internals and architecture guides.

By design and by default, the SQL Database Engine is a fully ACID compliant database. In the interest of scale or performance, database developers can intentionally bypass some ACID principles in the SQL Database Engine. For example, they can use delayed durability, non-durable tables, or read uncommitted data. In all these cases, the developer makes a choice to trade off some ACID properties to achieve other goals. Such tradeoffs must be made with caution because they can result in data integrity issues and affect business outcomes.

Platform feature support

Most Transact-SQL (T-SQL) features that applications use are fully supported on all SQL Database Engine platforms. For example, core SQL components such as data types, operators, and string, arithmetic, logical, and cursor functions work identically in all platforms. However, there are a few T-SQL differences in data definition language (DDL) and data manipulation language (DML) elements. These differences result in T-SQL statements and queries that are only partially supported in various platforms for design reasons.

In the case of platforms as a service (PaaS) or software as a service (SaaS) platforms, for example, some operating system or local file capabilities are disabled due to logical or physical isolation. In a contained database, for example, T-SQL statements and options aren't available if they configure instance-level options, operating system components, or specify file system configuration.

The following table lists major features of the SQL Database Engine with links to overview documentation and brief descriptions.

Feature Description
Always On availability groups Enterprise high availability and disaster recovery, synchronous or asynchronous, with secondary readable replicas
Always Encrypted Client-side encryption of sensitive data columns
Audit Audit administrative activity
Backup and restore Protect and recover database data
Backup compression Built-in backup compression
Backup encryption Built-in backup encryption
Buffer pool extension Boost I/O by adding nonvolatile RAM to buffer pool
Change data capture Track and capture data changes
Columnstore indexes Columnar storage for analytics workloads
Dynamic data masking Limit sensitive data exposure
Failover Clustering Enterprise failover clustering for high availability and disaster recovery
Full-text search Advanced text search capabilities
Graph tables Model and query graph relationships
In-Memory OLTP Memory-optimized tables and procedures
Indexed views Materialize views by creating indexes
JSON support Store and query JSON data with built-in JSON functions
Ledger Tamper-evident database capabilities
Microsoft Entra authentication Enterprise-wide user and service account authentication
Online index maintenance Maintenance that doesn't disrupt normal activity
Optimized locking Improved transaction locking mechanism
Partitioning Scale with partitioned tables and indexes
PolyBase Data virtualization to query external data sources
Query Store Built-in monitor for query performance tuning
Replication Distribute data across servers
Row-level security Control filtered access to table data
Spatial data Store and query geographic data
Temporal tables Track full history of changes
Transparent data encryption Encrypt database files at rest
Vector support Store and query vector embeddings, vector search on vector indexes. Support for LangChain integration and Semantic Kernel integration.
XML support Store and query XML data, XML indexes

Language and driver support:

Driver Description
.NET ADO.NET driver for SQL
Go Golang go-mssqldb driver for SQL
Java JDBC Driver for SQL
Node.js Node.js driver for SQL
ODBC ODBC Driver for SQL Server
OLE DB OLE DB Driver for SQL Server
PHP PHP driver for SQL
Python Python mssql-python driver for SQL
Ruby Ruby driver for SQL
Spark Spark connector for SQL

Modern platforms using the SQL Database Engine

The following modern platforms use the SQL Database Engine, starting with the flagship SQL Server product.

Product Deployment model
SQL Server On-premises, virtual machines including SQL Server on Azure VM, Arc-enabled, Windows, Linux, and Linux containers
Azure SQL Database Fully managed database, Platform as a Service (PaaS)
Azure SQL Managed Instance Fully managed database instance, Platform as a Service (PaaS)
Fabric Data Warehouse Fully managed warehouse, Software as a Service (SaaS) in Microsoft Fabric
SQL database in Fabric Fully managed database, Software as a Service (SaaS) in Microsoft Fabric

In SQL documentation, the Version selector dropdown list is key to understanding which version applies to an article, syntax reference, tutorial, or other content. Many Learn articles are customized to fit specific SQL platforms and capabilities. In most SQL reference articles, there's also an icon bar listing the applicable platforms for an article. For more information about navigating documentation, see SQL Server docs navigation guide.

Free offers

Get started today, for free. You can try the SQL Database Engine for free, with a free Azure subscription:

Migration

The Azure Database Migration Guides landing page provides links to quickly start migrations from various platforms to various Microsoft SQL platforms.

Related content

  • Last updated on