Microsoft SQL Server

What is Microsoft SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS) that supports a wide variety of transaction processing, business intelligence (BI) and data analytics applications in corporate IT environments.

Like other RDBMS software, Microsoft SQL Server is built on top of Structured Query Language (SQL), a standardized programming language that database administrators (DBAs) and other IT professionals use to manage databases and query the data they contain. SQL Server is tied to Transact-SQL (T-SQL), Microsoft's proprietary query language that enables applications and tools to communicate and also connect to a SQL Server instance or database.

Inside SQL Server's architecture: How SQL Server works

SQL Server is primarily built around a row-based table structure that connects related data elements in different tables to one another, avoiding the need to redundantly store data in multiple places within a database. The relational model also provides referential integrity and other integrity constraints to maintain data accuracy. Those checks, part of a broader adherence to the principles of atomicity, consistency, isolation and durability, help ensure reliable processing of database transactions.

The core component of SQL Server is Database Engine, which controls data storage, access, processing and security. Up to 50 instances of Database Engine can be set up on a single computer. It includes a relational engine that processes commands and queries and a storage engine that manages database files, tables, pages, indexes, data buffers and transactions. Stored procedures, triggers, views and other database objects are also created and executed by Database Engine.

To connect to Database Engine, a client library or client tool with at least one client library -- and running in a graphical user interface or command-line interface -- is required. When connecting, information about the instance name where Database Engine is installed must be provided. Users can also optionally provide information about the network protocol and the connection port.

Sitting beneath Database Engine is SQL Server Operating System (SQLOS). SQLOS handles lower-level functions, such as memory, input/output (I/O) management, job scheduling and locking of data to avoid conflicting updates. A network interface layer sits above Database Engine and uses Microsoft's Tabular Data Stream protocol to facilitate request and response interactions with database servers. And, at the user level, SQL Server DBAs and developers write T-SQL statements to build and modify database structures, manipulate data, implement security protections and back up databases, among other tasks.

Microsoft SQL Server versions

Between 1995 and 2016, Microsoft released 10 versions of SQL Server. Early versions were aimed primarily at departmental and workgroup applications, but Microsoft expanded SQL Server's capabilities in subsequent releases, turning it into an enterprise-class RDBMS that could compete with Oracle Database, IBM Db2 and other rival platforms for high-end database uses. Over the years, Microsoft has also incorporated various data management and data analytics tools into SQL Server, as well as functionality to support new technologies and platforms that emerged, including the web, cloud computing and mobile devices.

SQL Server 2012

After SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, Microsoft released SQL Server 2012 and SQL Server 2014. SQL Server 2012 offered new features, such as columnstore indexes, which can be used to store data in a column-based format for data warehouse and analytics applications, and Always On availability groups, a high availability and disaster recovery technology. In July 2022, Microsoft stopped providing support for SQL Server 2012.

SQL Server 2014

SQL Server 2014 added In-Memory OLTP, which lets users run online transaction processing applications against data stored in memory-optimized tables instead of standard disk-based ones. Another new feature in SQL Server 2014 was the buffer pool extension, which integrates SQL Server's buffer pool memory cache with a solid-state drive -- another feature designed to boost I/O throughput by offloading data from conventional hard disks.

Currently, Microsoft offers SQL Server in the following versions with different features and capabilities. Each version is built to meet different organizations' unique performance, runtime and pricing requirements and requires different SQL Server components to be installed:

• SQL Server 2016.
• SQL Server 2017.
• SQL Server 2019.
• SQL Server 2022.

Microsoft SQL Server 2016

Microsoft SQL Server 2016, which became generally available in June 2016, was developed as part of a "mobile first, cloud first" technology strategy adopted by Microsoft two years earlier. Among other things, SQL Server 2016 added new features for performance tuning, real-time operational analytics, and data visualization and reporting on mobile devices, plus hybrid cloud support that lets DBAs run databases on a combination of on-premises systems and public cloud services to reduce IT costs. For example, SQL Server Stretch Database moves infrequently accessed data from on-premises storage devices to the Microsoft Azure cloud, while keeping the data available for querying, if needed.

SQL Server 2016 also increased support for big data analytics and other advanced analytics applications through SQL Server R Services, which enables the DBMS to run analytics applications written in the open source R programming language, and PolyBase, a technology that lets SQL Server users access data stored in Hadoop clusters or Azure Blob Storage for analysis. Also, SQL Server 2016 was the first version of the DBMS to run exclusively on 64-bit servers based on x64 microprocessors. And it added the ability to run SQL Server in Docker containers, a virtualization technology that isolates applications from each other on a shared OS.

Microsoft SQL Server 2017

Microsoft SQL Server ran exclusively on Windows for more than 20 years. But, in 2016, Microsoft said it planned to also make the DBMS available on Linux, starting with a new version released as a community technology preview that November and initially dubbed SQL Server vNext. Later, the update was formally named SQL Server 2017, and it became generally available in October of that year.

The support for running SQL Server on Linux moved the database platform onto an open source OS commonly found in enterprises, giving Microsoft potential inroads with customers that don't use Windows or have mixed server environments. SQL Server 2017 also expanded the Docker support added for Windows systems in the previous release to include Linux-based containers.

Another notable feature in SQL Server 2017 is support for the Python programming language, an open source language that is widely used in analytics applications. With its addition, SQL Server R Services was renamed Machine Learning Services and expanded to run both R and Python applications. Initially, the machine learning toolkit and a variety of other features were only available in the Windows version of the database software, with a more limited feature set supported on Linux.

Microsoft SQL Server 2019

SQL Server 2019 enables users to join SQL Server, Hadoop Distributed File System and Apache Spark containers together using a new Big Data Cluster feature. SQL Server 2019 also introduced columnstore index builds, rebuilds and static data masking. Accelerated data recovery was also new, which performs and undoes a redo phase in the oldest page log sequence number. As an example, this is done in the scenario where the user closes an application that was running for an extended period of time, so the user does not have to wait long for the application to close.

Always On availability groups, available in SQL Server 2012, were changed to simplify administration of availability groups. This added support to MSDB and Master system databases. Other changes to features included the expansion to operations users can perform with Always Encrypted data; additional PolyBase connectors for SQL Server, Oracle, MongoDB and Teradata; additional persistent memory options for storage; and improvements on query processing.

Features and services in Microsoft SQL Server 2022

SQL Server 2022 (16.x) is the latest edition of SQL Server. One notable new feature is data virtualization, which enables users to query different types of data on different types of data sources from SQL Server.

In addition, Microsoft has updated its SQL Server Analysis Services in SQL Server 2022. These updates include the following:

• Enhancement to the schema write operation encryption algorithm.
• Horizontal Fusion query execution plan optimization to reduce the number of data source queries required to return results.
• Parallel execution plans to analyze Data Analysis Expressions queries against a DirectQuery data source and parallel execution of independent storage engine operations against the data source.

SQL Server 2022 now also supports Power BI models with DirectQuery connections to the Analysis Services models.

Some other new features introduced in SQL Server 2022 are the following:

• Near-real-time analytics over operational data with Azure Synapse Link for SQL.
• Object storage integration to the data platform.
• Two types of availability groups: Always On and Distributed.
• Integration with Microsoft Defender for Cloud Apps to better protect SQL servers.
• Microsoft Entra authentication to securely connect to SQL Server.
• Granular access control permissions and built-in server roles to enable adherence with the principle of least privilege.
• Updates to improve performance, such as system page latch concurrency support, Buffer Pool Parallel Scan, improved columnstore segment elimination, thread management and reduced buffer pool I/O promotions.
• Intelligent query processing features to improve the performance of existing workloads.
• Simplified management with Azure extensions, server memory calculations and recommendations, snapshot backup support, Extensible Markup Language compression and asynchronous auto update statistics concurrency.

Users can also access numerous tools with SQL Server 2022, including the following:

• Azure Data Studio.
• SQL Server Management Studio.
• SqlPackage.
• Visual Studio Code.

To install these features and tools, users must use the Feature Selection page of the SQL Server Installation Wizard when installing SQL Server.

Microsoft also bundles a variety of data management, BI and analytics tools with SQL Server. In addition to the R Services and Machine Learning Services technology that first appeared in SQL Server 2016, the data analysis offerings include SQL Server Analysis Services, an analytical engine that processes data for use in BI and data visualization applications, and SQL Server Reporting Services, which supports the creation and delivery of BI reports.

On the data management side, Microsoft SQL Server includes SQL Server Integration Services, SQL Server Data Quality Services and SQL Server Master Data Services. Also bundled with the DBMS are two sets of tools for DBAs and developers: SQL Server Data Tools for use in developing databases and SQL Server Management Studio for use in deploying, monitoring and managing databases.

SQL Server editions

For the currently available SQL Server versions -- 2016, 2017, 2019 and 2022 -- Microsoft provides several editions that users can choose depending on their requirements:

• Enterprise edition. This version delivers comprehensive, high-end data center capabilities and is the premium offering of SQL Server.
• Standard edition. The Standard edition delivers basic data management and BI databases for small organizations or individual departments.
• Web edition. This version of SQL Server is a low-cost option for web hosters and web value-added providers.
• Developer edition. This edition enables developers to build and test applications on top of SQL Server.

In addition to the above, Microsoft provides an Express edition of SQL Server for learning and building desktop and small-server, data-driven applications. This edition is meant for entry-level users and is available free of cost.

Finally, Microsoft provides an Evaluation edition for a 180-day trial period.

However, when SQL Server 2016 Service Pack 1 (SP1) was released in late 2016, Microsoft made some of the features previously limited to the Enterprise edition available as part of the Standard and Express ones. That included In-Memory OLTP, PolyBase, columnstore indexes, partitioning, data compression and change data capture capabilities for data warehouses, as well as several security features. In addition, the company implemented a consistent programming model across the different editions with SQL Server 2016 SP1, making it easier to scale up applications from one edition to another.

SQL Server integration with Microsoft Azure

SQL Server can be deployed on an Azure Virtual Machine or other VM platform. With SQL Server on Azure Virtual Machines, users can use the full version of SQL Server in the cloud, eliminating the need to manage on-premises hardware.

SQL Server can also be integrated with a number of other Azure services, such as Azure Arc to simplify governance and management, and Azure Kubernetes Service to deploy and manage container clusters. It is also possible to set up SQL Managed Instance enabled by Azure Arc on a preferred Kubernetes infrastructure, which makes it easy to manage the service in Azure, while storing data in the user's preferred location.

Security features in SQL Server

The advanced security features supported in all editions of Microsoft SQL Server starting with SQL Server 2016 SP1 include three technologies: Always Encrypted, which lets user update encrypted data without having to decrypt it; row-level security, which enables data access to be controlled at the row level in database tables; and dynamic data masking, which automatically hides elements of sensitive data from users without full access privileges.

Other notable SQL Server security features include transparent data encryption, which encrypts data files in databases, and fine-grained auditing, which collects detailed information on database usage for reporting on regulatory compliance. Microsoft also supports the Transport Layer Security protocol for securing communications between SQL Server clients and database servers.

Most of those tools and the other features in Microsoft SQL Server are also supported in Azure SQL Database, a cloud database service built on SQL Server Database Engine. Alternatively, users can run SQL Server directly on Azure via SQL Server on Azure Virtual Machines; it configures the DBMS in Windows Server VMs running on Azure. The VM offering is optimized for migrating or extending on-premises SQL Server applications to the cloud, while Azure SQL Database is designed for use in new cloud-based applications.

In the cloud, Microsoft also offers Azure Synapse Analytics, a data warehousing service based on a massively parallel processing (MPP) implementation of SQL Server. The MPP version, originally a standalone product called SQL Server Parallel Data Warehouse, is also available for on-premises uses as part of Microsoft Analytics Platform System, which combines it with PolyBase and other big data technologies.

All editions of Microsoft SQL Server include advanced security features for authentication, authorization and encryption. Authentication is meant to confirm who a user is and is performed by Windows, SQL Server and Microsoft Entra ID.

Authorization confirms what the user can do. SQL Server includes authorization features for granting, revoking and denying permissions to set security by roles and to restrict data access to selected data elements.

SQL Server's encryption capabilities enable users to securely store secret data. Files and sources can be encrypted, and encryption can be done by certificate, symmetric key, asymmetric key and passphrase.

Other notable security features in SQL Server include the following:

• Firewall protection.
• Automated auditing.
• Custom trigger-based auditing.

History of SQL Server

In the 1980s, Sybase Inc. -- now owned by SAP -- developed the original SQL Server code, with the software designed to run on Unix systems and minicomputer platforms. Microsoft and Ashton-Tate Corp., then the leading vendor of PC databases, teamed up to produce the first version of what became Microsoft SQL Server, designed for OS/2 and released in 1989.

Ashton-Tate and later Sybase stepped away from the partnership. In 1994, Microsoft took over SQL Server development and marketing for its own OSes. Microsoft had already made SQL Server available on Windows New Technology by creating a 32-bit implementation with added features.

SQL Server has evolved over the years with the latest version -- as of 2024 -- being SQL Server 2022.

See how to manage disaster recovery in SQL Server, and compare AWS, Microsoft, Google and Oracle clou databases. Explore sample databases for SQL Server and how to find them.