What Is SQL Server? Versions, Editions, Architecture, and Services

December 29, 2023

17893

SQL Server is a dominant database management system (DBMS) used by various organizations throughout the world. This article presents a detailed overview of its multiple versions, editions, architecture, and database platform services.

What is SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS), a part of the Microsoft Enterprise Library. It was initially developed by Microsoft in 1989.

The system is designed to manage and store data. Like other Relational Database Management Software, SQL Server is based on SQL, a programming language for accessing and manipulating relational databases.

You can find more about what is SQL in the related blog post.

What is SQL Server used for?

SQL Server is one of the most widely deployed relational database management systems, or RDBMS, today. It runs on various operating systems, including Linux, Windows, and UNIX. You can also run it on Azure or AWS. SQL Server used for data storage, query, and analysis. It’s a powerful, high-performance database engine that provides a consistent programming model. It boasts high performance, reliability, scalability, security, and manageability and offers a set of powerful tools for data warehousing, analysis, reporting, ETL, and OLAP.

SQL Server version history

In 1989, version 1.0 was released by Microsoft and Sybase. The collaboration between the two ended in the early 1990s. Microsoft retained ownership of the name. Since the 1990s, there have been subsequent versions of the database SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017, 2019, and 2022. Microsoft guarantees to provide at least 10 years of support for each version (five years of mainstream support and five years of extended support).

Let’s take a short look at the significant versions from 2012 onward.

SQL Server 2012

SQL Server 2012 brought a new feature called columnstore indexes that comprises a standard of storing large data in a column-based format for data warehousing and applications. Among the top features of SQL Server 2012, one should also mention user-defined server roles and AlwaysOn Availability Groups which is an advanced functionality designed for high availability and disaster recovery.

SQL Server 2014

SQL Server 2014 brought significant performance enhancements. Among other things, it added two new features that help OLTP applications: In-Memory OLTP and the Buffer Pool Extension. The Buffer Pool Extension integrates SQL Server’s buffer pool memory cache with a solid-state drive, which is an easy and affordable way to boost I/O performance.

SQL Server 2016

SQL Server 2016 was developed as part of Microsoft’s “mobile-first, cloud-first” technology strategy. SQL Server 2016 was the first version of the database management system to run exclusively on 64-bit servers based on x64 processors. It added the capability to run SQL Server in Docker containers that isolate applications from each other on a shared operating system.

SQL Server 2017

Microsoft SQL Server 2017 introduced the support for running SQL Server on Linux, giving Microsoft potential inroads with customers who don’t use Windows or have mixed server environments. Another important feature of SQL Server 2017 is the support for the Python programming language, an open-source language widely used in analytics applications.

SQL Server 2019

SQL Server 2019 introduced the Big Data Clusters functionality designed to let you deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes. Also, SQL Server 2019 brought columnstore index enhancements, data virtualization capabilities, and static data masking.

SQL Server 2022

The latest SQL Server version released in November 2022 features significant enhancements in performance, analytics, security, and hybrid capabilities, primarily Azure integration. Key additions include Azure SQL Managed Instance Link and Azure Synapse Link for SQL, improving replication to and from Azure, along with features like contained availability groups, new data virtualization capabilities, object storage integration, and more.

Popular SQL Server editions

There are five editions of SQL Server: Standard, Web, Enterprise, Developer, and Express. The main differences between the editions are listed below:

Standard edition includes the core functionality required for most applications. This includes common development, data management, and database administration tools for effective database management with minimal resources.
Enterprise edition unlike the Standard one supports a wide array of data warehouse features, adds support for more users, databases, and transactions. It also includes several other advanced features such as data compression, enhanced security, and support for large database sizes. It is a perfect choice for companies focusing on scalability and performance.
Web edition presents a low-cost option and differs from the Standard edition primarily in terms of maximum memory for the buffer pool and maximum compute capacity.
Developer edition is designed for developers who need to create application products that will run on top of SQL Server. It provides the ability to create stored procedures, functions, and views. In fact, it contains all the functionality of the Enterprise edition but is licensed for use as a development and test system, not as a production one.
Express edition is the most limited edition of SQL Server. It is only for use by individuals or small organizations. It does not include any of the advanced functionality of the other four editions. It is the most accessible edition, but it is also the least functional.

SQL Server Architecture

Relational Database Management Systems (RDBMS) provide structured means for storing, manipulating, and retrieving large amounts of information. Like other RDBMS, SQL Server is essentially built around a row-based table structure that joins related data elements in various tables to one another, avoiding the need to store data in various places within a database redundantly. The relational model also stores referential integrity and other integrity constraints to keep data accurate. Adherence to the ACID principles guarantees that database transactions are processed reliably.

Main components of SQL Server

Database Engine

Microsoft SQL Server is a database engine, that stores, processes, and secures data and makes it accessible to users. It includes a relational engine used to process commands and queries and a storage engine that manages the database file, tables, pages, indexes, data buffers, and transactions. Stored procedures, triggers, views, and other database objects are also created and executed by the Database Engine.

SQL Server Operating System (SQLOS)

The SQL Server operating system, or SQLOS, lies at the heart of all database functionality. It manages memory and I/O resources, jobs and processes that access databases, and the TDS protocol that uses streams to transfer data from server to client. Network interfaces handle communication between database servers and clients.

At the user level of SQL Server, DBAs and developers write T-SQL statements to develop and change database structures, manage data, implement security protections, back up databases, etc.

SQL Server services and tools

SQL Server includes many additional services that are either part of SQL Server components or run as out-of-process services. These services are not part of the core database management system. They present their API for interaction. Let’s have a look at the most common ones.

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) provide ETL (Extract, Transform, and Load) capabilities for data import, data integration, and warehousing needs. It also provides tools to create workflow tasks, including Data Flow Tasks, Control Flow Tasks, Script Tasks, Batch Data Operations, Data Conversion, and Data Loading.

SQL Server Data Quality Services

Data Quality Services is a knowledge-driven product. DQS allows you to build a knowledge base and use it to perform various critical data quality tasks, such as correction, enrichment, and standardization.

SQL Server Master Data Services

Master Data Services can be used to manage a master set of your company’s data. You can control who updates the data, organize the data into models, and create rules for updating the data. The master data set can be shared with other users within the organization.

SQL Server Data Tools

Microsoft’s Business Intelligence Development Studio (BIDS) is used for developing data analysis and business intelligence solutions. It is based on the Visual Studio development environment but has different extensions and project types for the database server.

SQL Server Management Studio (SSMS)

The GUI tool for configuring, managing, and administering any SQL infrastructure. The tool presents a graphic interface for working with SQL Server database servers and includes a bunch of script editors and graphical tools for working with objects on the server.

Visual Studio

Native support for data programming can be found in Microsoft Visual Studio. It can be used to write code executed by a database. A data designer that can be used to create, view or edit database schemas graphically is also included. There are two ways to create queries: using code and visually.

SQL Server Analysis Services (SSAS)

SSAS adds OLAP and data mining capabilities to SQL Server. SSAS supports three different types of storage: MOLAP, ROLAP, and HOLAP, which are the methods of storing the cubes. SSAS also supports the XML for Analysis (XMLA) standard as the underlying communication protocol. The Data Mining API, DMX, enables data mining-specific functionality. It includes Decision trees, clustering algorithms, naive Bayes, time-series, sequence clustering, linear and logistic regression, and neural networks.

SQL Server Reporting Services (SSRS)

This is a report generation environment for data gathered from the SQL Server databases. It is administered through a web interface. SSRS has a web services interface to support the development of custom reporting applications. The SSRS tool comes free with SQL Server.

Machine Learning Services technology

The SQL Server Machine Learning service is a powerful platform for machine learning and data analysis. It allows users to conduct data science and machine learning without having to move data around, and it’s a place where you can store machine learning models to be scored against future data. A trained machine-learning model can be stored inside a database and used for scoring.

Full-Text Search Service

SQL Server Full-Text Search service allows for the search of word-based queries over any column with character-based text data. It is also capable of performing queries with the SQL LIKE operator. Using the Full-Text Indexing Service, you can create more efficient queries than those with the LIKE operator.

SQLCMD

SQLCMD is a command-line utility for querying and managing data in SQL Server. It allows you to query the SQL Server databases and execute SQL statements against them. It provides a scripting interface to write scripts to create or maintain databases.

SQL Server instance

Multiple services can be run simultaneously, with each service having its logins, ports, databases, and more. There are two types of instances: Primary instances and Named instances. You can run up to 50 instances at once on a SQL Server 2005 and later. You can have multiple instances on the server, but only one will be the default one. Each SQL Server instance has its own copy of the server files, databases, and security credentials.

Advantages of using SQL Server instances

Install different versions in one machine

You can install different SQL Server versions on a single machine and configure each instance individually, which could be useful for various types of testing, isolation, or performance tweaking.

Reduce cost

Instances can help you reduce the costs of operating the server. You don’t need a license for all services since you can get different services from different instances.

Maintain production, development, and test environments separately

Having different SQL Server instances on a single machine gives you the flexibility to use different instances for development, production, and testing purposes.

Reduce temporary database problems

Most of the problems users encounter are caused by temporary database problems when service instances are run on one single instance server. It’s easy to avoid such issues if you run your services on different occasions.

Separate security privileges

You can focus on securing the instance with the most sensitive service when different services have separate security privileges.

Maintain standby server

A failure of a database instance can lead to an interruption of services. If the current server fails, a backup server must be brought in. This can easily be accomplished with the use of a reserved database.

SQL Server is a perfect choice if you are working with Windows. Its ecosystem is oriented primarily toward large infrastructures. Microsoft SQL Server is significantly more expensive than its open-source competitors, but in the final count, users get access to the regularly updated official ecosystem, an extensive community of experts, and outstanding customer support.

How to find SQL Server Version

When working with SQL Server databases, it’s crucial to know the exact version of your SQL Server. Different versions provide various sets of features, so you need to be sure about the functionality you can expect. Additionally, Microsoft regularly releases security updates and patches. Besides, keep in mind that certain applications or tools may only be compatible with specific SQL Server versions. Therefore, having a quick and easy way to check your SQL Server instance’s version is essential. There are several methods available for this purpose.

Check the SQL Server version with the SQL command

The standard command used for checking the SQL Server version is:

SELECT @@version

You can use the command line or some of the graphical tools like SQL Server Management Studio (SSMS). In the below example, we are using it to demonstrate the SELECT @@version work:

Another way of checking the SQL Server version is provided by the popular software solutions designed to perform database tasks in SQL Server: SSMS and dbForge Studio for SQL Server.

Check the SQL Server version with SSMS

SQL Server Management Studio (SSMS) is a comprehensive Integrated Development Environment (IDE) offered by Microsoft for managing SQL Server databases. This user-friendly and freely available tool offers all the necessary features for standard database-related tasks in SQL Server.

Additionally, SSMS’s capabilities can be greatly extended through a range of add-ins, making it a preferred choice for many SQL Server experts.

SSMS allows the users to check the current SQL Server version in the following way:

Connect to the necessary server with SSMS
Right-click on the connection and select Properties

SSMS will show the window with detailed information

However, the version number is the build number, and it does not specify directly whether you are working with SQL Server 2017, 2019, or 2022. To define that, you need to check the first two digits of the build number, as they identify the SQL Server version.

The following table lists the supported SQL build numbers and their corresponding versions

Build number	SQL Server version	Mainstream Support end	Extended Support end
16.xx	SQL Server 2022	2028	2033
15.xx	SQL Server 2019	2025	2030
14.xx	SQL Server 2017	2022	2027
13.xx	SQL Server 2016	2021	2026
12.xx	SQL Server 2014	2019	2024

In our example, the SQL Server version is 16.0.1105.1. It relates to the SQL Server 2022 version.

Check the SQL Server version with dbForge Studio for SQL Server

Many users who favor working with SQL Server databases through GUI tools often opt for alternatives to the default SSMS IDE. These alternatives offer a wider range of options and enhance the user’s efficiency and comfort while performing tasks.

dbForge Studio for SQL Server stands out as one of the most robust SSMS alternatives available, providing comprehensive functionality to streamline all aspects of database development, management, and administration. It simplifies tasks by allowing users to switch to a visual mode. Additionally, this feature-rich IDE offers the ability to check the current SQL Server version.

Open the Studio and connect to the SQL Server instance
Right-click on the connection and select Properties (or click F4)

The results are provided in the same window, making it more comfortable for the user

Conclusion

Microsoft SQL Server (SQL Server) is a robust, scalable, and flexible data management platform that provides high performance, reliability, and availability. It offers a comprehensive set of tools that you can use to build reliable, scalable, and secure applications.

There are a lot of database software solutions for SQL Server on the market, and among the best are the tools delivered by the Devart company: dbForge Studio for SQL Server that comprises an all-in-one IDE for all possible database-related tasks and dbForge SQL Tools—a pack of advanced standalone database tools and add-ins. dbForge products come with a fully functional 30-day trial so that users can evaluate all the cutting-edge features they have. Try both and choose the one that suits your liking!