Microsoft SQL Server

Microsoft SQL Server
Name	Microsoft SQL Server
Developer	Microsoft
Released	24 April 1989
Programming language	C, C++
Operating system	Microsoft Windows, Linux
Genre	Relational database management system
License	Proprietary software

Microsoft SQL Server. It is a proprietary relational database management system developed by Microsoft. As a core component of the Microsoft Data Platform, it provides a comprehensive environment for storing, retrieving, and managing data across a wide range of applications. The system is widely used in enterprise environments, supporting everything from small-scale applications to large, mission-critical systems for major corporations and organizations.

History

The origins trace back to a partnership between Microsoft and Sybase in the late 1980s, resulting in the release of SQL Server 1.0 for OS/2. Following the dissolution of this partnership in the early 1990s, development diverged. Major milestones include the release of SQL Server 7.0, which represented a significant codebase rewrite, and SQL Server 2005, which introduced foundational features like the .NET Framework integration. Subsequent versions, such as SQL Server 2008, SQL Server 2012, and SQL Server 2016, have added critical capabilities including Transparent Data Encryption and support for in-memory OLTP. A landmark shift occurred with the release of SQL Server 2017, which extended support to the Linux operating system, marking a major strategic expansion beyond the Windows Server ecosystem.

Editions and features

It is offered in several distinct editions to cater to different workloads and organizational sizes. The Enterprise Edition provides the full suite of features, including advanced high availability solutions and comprehensive business intelligence tools. The Standard Edition offers core database capabilities suitable for mid-tier applications. For developers, the Developer Edition includes all the features of the Enterprise Edition but is licensed for non-production use. Other editions include the Express Edition, a free, lightweight version with database size limitations, and the Web Edition, designed for web hosting providers. Specialized versions like Azure SQL Database offer a fully managed Platform as a Service experience in the Microsoft Azure cloud.

Architecture

Its architecture is built around a core database engine, the SQL Server Database Engine, which handles query processing, transaction management, and data storage. Data is stored in primary data files with an .mdf extension and transaction log files with an .ldf extension. The engine utilizes the Tabular Data Stream protocol for client-server communication. For advanced analytics, it integrates the SQL Server Analysis Services engine for OLAP and data mining. The SQL Server Reporting Services component handles report generation and delivery, while SQL Server Integration Services provides a platform for building enterprise-level data integration and transformation solutions.

Tools and utilities

Primary administration and development is performed through SQL Server Management Studio, a comprehensive integrated environment. For business intelligence development, SQL Server Data Tools provides a project-based experience within Visual Studio. Configuration and management of server instances is handled by the SQL Server Configuration Manager. Performance monitoring and diagnostics are available through tools like SQL Server Profiler and Dynamic Management Views. The Database Engine Tuning Advisor provides recommendations for optimizing physical database structures.

Development and programming

It supports Transact-SQL, its proprietary extension to the SQL standard, for data definition and manipulation. Deep integration with the .NET Framework allows developers to write database objects like stored procedures and user-defined functions using languages such as C# and Visual Basic .NET via the SQL CLR. For data access, applications commonly use providers like ADO.NET, the Microsoft JDBC Driver, or the ODBC driver. The system also supports JSON data processing and offers extensive XML capabilities.

Security

Security is implemented through a layered model. Authentication can be managed via Windows Authentication mode, integrating with Active Directory, or using SQL Server Authentication. Authorization is controlled through a granular permissions system involving server logins, database users, roles, and schemas. Data protection features include Transparent Data Encryption for data at rest, Always Encrypted for data in use, and Dynamic Data Masking for obfuscating sensitive data in query results. Compliance with standards like the Payment Card Industry Data Security Standard is supported through these and other auditing features.

Data management and performance

High availability and disaster recovery are addressed through technologies like Always On Availability Groups, Database Mirroring, and Log Shipping. For performance, it includes the Query Optimizer, an in-memory buffer pool, and sophisticated indexing strategies including columnstore indexes. The Resource Governor allows for managing workloads and resource consumption. Advanced data management features support temporal tables for tracking historical data and PolyBase for querying external data in sources like Hadoop or Azure Blob Storage.

Category:Microsoft database software Category:Relational database management systems Category:Proprietary database management systems