What is Microsoft SQL Server?

Basics

What is Microsoft SQL Server?

Microsoft SQL Server, sometimes also referred to as SQL Server or MSSQL Server, is a relational database management system by Microsoft, which was first released in 1989.

In addition to the ability to store and manage large amounts of data, Microsoft SQL Server offers many additional functions, extensions and integration options. A recognizable focus is placed on data processing and analysis in an enterprise environment.

Microsoft SQL Server can be used on Windows and Linux operating systems.

Editions

Microsoft SQL Server is available in different editions, which are intended for specific use cases and differ in their capabilities (e.g. available storage space) and range of features.

The latest version, Microsoft SQL Server 2022, is available in the following editions (as of July 2024):

• Express: Edition available free of charge with restrictions in terms of database size, resources and functionality. Suitable for simple applications.
• Standard: Intended for use in small to medium-sized companies and projects in which no massive data volumes are processed and no additional enterprise functionalities are required.
• Enterprise: Edition without restrictions, which offers additional functions for large companies and use in data centers (high availability, business intelligence, …).
• Web: Edition for the use in web hosting. Optimized for availability and scalability for web and cloud applications.
• Developer: Includes the functionality of the Enterprise edition. Can only be used as a development and test system.

Installation

Installation of Microsoft SQL Server

After selecting an edition and installing the Microsoft SQL Server software on a suitable system, multiple databases can be created and managed as part of a server instance

Microsoft SQL Server can be installed on both Windows and Linux systems. The supported Linux distributions are Red Hat Enterprise Server, SUSE Linux Enterprise Server and Ubuntu. Microsoft SQL Server also supports Docker.

Microsoft SQL Server can also be run on virtual machines in the cloud.

Licensing and installation is possible via the Microsoft website.

After installation, users can access the SQL Server instance using the SQL Server Management Studio software (graphical user interface for Microsoft SQL Server) and start creating and managing databases. You can find out more about this in the section Managing and querying the database with SQL Server Management Studio.

A Microsoft SQL Server instance can contain several databases that can be managed separately.

Database basics

Structure of a Microsoft SQL Server database

Microsoft SQL Server uses the relational database model. Data within a database is stored in the form of tables. Each row in a table contains a data record while the columns contain the attributes of a data record. A simple example of a relational database table is shown below.

Tables can be linked with each other using keys. For example, a column in a „Book“ table can contain a reference to a data record in an „Authors“ table.

You can find a more in-depth explanation of relational databases here: What is a database?

In addition to the tables that store data, Microsoft SQL Server databases can contain other objects. In many cases, these are used to facilitate easier access to data:

• Views: Virtual tables that contain the results of database queries. If a view has been created for a specific database query, it can be called up directly without having to perform the query manually.
• Indexes: Provide faster access to frequently required data.
• Stored Procedures: Predefined sequences of SQL commands that can be executed together.
• Trigger: Scripts that are automatically executed when a certain event occurs in the database.
• Schemas: Containers that group objects such as tables, views or stored procedures and separate them organizationally.

Microsoft SQL Server uses the Transact-SQL (T-SQL) query language for database queries. Transact-SQL modifies and extends the SQL standard to enable more complex queries.

Database operation

Managing and querying a database with SQL Server Management Studio

SQL Server Management Studio (SSMS) is a graphical user interface for managing Microsoft SQL Server databases. The software is provided together with SQL Server and can be connected to SQL Server instances. These could be located both on the local computer or on remote servers.

The SSMS environment allows users to write and execute SQL queries, create database objects such as tables and views and perform numerous other database operations.

In addition, SSMS can be used to create backups, manage security functions and optimize and monitor database performance.

The SQL Server Management Studio UI consists of three main areas.

On the left side is the Object Explorer, which displays the structure of the existing databases and database objects and provides access to other functions such as security and administration.

At the top right is the Query Editor, which can be used to write and execute SQL queries.

Below the Query Editor is the Output Window, where the results of database queries are displayed.

Additional tools

Additional tools for Microsoft SQL Server

In addition to the ability to create and manage relational databases, Microsoft SQL Server also integrates additional tools for data processing, analysis and reporting.

SQL Server Analysis Services (SSAS)

SQL Server Analysis Services (SSAS) is a tool for data processing and analytics. It can be installed together with Microsoft SQL Server. With SSAS, data from various sources can be aggregated and stored in data structures that are optimized for fast and targeted queries (data modelling). At the same time, complex analyses and calculations are made possible.

SSAS is primarily used to prepare selected data for business intelligence applications and reports. The creation of projects, integration of data sources and definition of data models is carried out in Microsoft’s Visual Studio development environment.

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) is a data integration tool that can be installed together with Microsoft SQL Server. It can be described as an ETL tool (Extract, Transform, Load), which extracts, cleans, modifies, merges and stores data from various sources within target systems. These processes can be automated using workflows.

SQL Server Reporting Services (SSRS)

SQL Server Reporting Services (SSRS) is a tool for creating and managing reports that uses the Microsoft SQL Server database engine. Users can access reports and key figures through a web portal.

The underlying data can originate from SQL Server databases and other sources and can be presented in various ways (tables, charts, etc.). Reports can be created with Visual Studio and are stored on a report server.

Use cases

Use cases for Microsoft SQL Server

Due to its high flexibility and scalability as well as broad support for interfaces and programming languages, Microsoft SQL Server is one of the most widely used database systems.

The availability of many additional tools and programming interfaces causes the implementation and use of the database functionality to differ considerably between individual use cases.

To illustrate this, we have listed some possible scenarios in which Microsoft SQL Server databases and the associated analysis tools could be used:

• Web applications: Microsoft SQL Server is often used as a backend database for web applications. When a website or web application is being used, it can retrieve data from an SQL Server database and use it to display dynamic content.
• Business software: Business applications such as the ERP system SAP use Microsoft SQL Server as a database to store and manage existing data on customers, orders, etc.
• ETL and Business Intelligence (BI): In combination with tools for data integration, data analysis and report generation, many companies use the Microsoft SQL Server software package to merge data, store it centrally or in a decentralized manner and evaluate it for analysis purposes.
• Cloud databases: In combination with Microsoft Azure or other cloud infrastructures, Microsoft SQL Server databases can be hosted in the cloud. More and more companies do this, usually with the goal to achieve greater scalability and availability and to outsource the management and security of hardware.

The industries in which Microsoft SQL Server is used are also very diverse. For many use cases, relational databases are the first choice due to their advantages when it comes to transaction security, consistency and other benefits. This makes SQL Server databases a popular choice for banks, hospitals, educational institutions and government agencies.

Microsoft SQL Server databases also play an important role for many manufacturing companies – both as part of software systems and as stand-alone databases, e.g. in production lines, where they store data such as production orders, parts lists, logs or machine and environmental data.

Our cloud platform manubes offers database interfaces, including Microsoft SQL Server. With manubes, data from SQL servers can be visualized, used in workflow automations or written to OPC UA Servers and other systems.