Selecting and assigning Microsoft SQL Server components

Microsoft SQL Server components – When working with information systems previously installed by system administrators, quite often you may encounter the situation that the server, which requires only database management – all components that are supplied in the SQL server distribution. On a reasonable question: “Why are all the components installed?”, you can get the answer “I always put all the components” or “I do not know why I need each of the components, so I installed everything just in case.

It is clear that this approach is fundamentally wrong, because the server runs services that are never used by anyone, and these services, in turn, use the computing resources of the server in vain, which can adversely affect the performance of the server itself and the virtualization host (in case the SQL server is virtualized).

Microsoft SQL Server components

Below is a list of the main components supplied with the SQL server distribution, their brief description and purpose:

Database Engine Services (Database Engine Component Services or DBMS Kernel Services) – is the main service for data storage, processing and protection, replication, full-text search, relational and XML data management tools, as well as Data Quality Services of SQL Server (DQS). You can add optional components to the Database Engine services if the SQL Server functionality requires it:

• Replication: This component is a set of technologies for copying and distributing data and database objects between databases and synchronizing databases to maintain consistency.
• Full-Text Search: This component allows you to perform full-text queries on SQL Server tables for arbitrary character data.
• Data Quality Services: This component enables the detection of inconsistent and incorrect data in the data source and provides computerized and interactive methods of data cleansing.

Note: The Replication and Full-Text Search components are mandatory when installing the Database Engine in a cluster configuration.

Analysis Services – this service contains tools for creating and managing online analytical processing (OLAP) and intelligent data analysis applications. It fundamentally differs from the Database Engine component, which operates in the online transaction processing (OLTP) mode.

Reporting Services – Native – this service uses server and client components to create, deploy, and manage tabular, matrix and graphical free form reports and reports.

Below is a list of common components shared by all instances of SQL Server on the same computer, installed in the same directory. They include the following components:

Reporting Services – SharePoint is a server application for creating, managing and delivering reports by e-mail, but unlike Reporting Services – Native, in SharePoint mode, the functions of viewing and managing reports are integrated into SharePoint products.

Reporting Services Add-in for SharePoint Products – contains user interface components and management tools that allow you to integrate a SharePoint product with the Reporting Services of a report server in SharePoint integration mode.

Data Quality Client is a standalone application that connects to the DQS server and provides an intuitive graphical user interface for data cleansing, data mining and administrative tasks in the DQS.

Client Tools Connectivity – Client Tools include components designed to enable communication between clients and servers, including network libraries for DB-Library, OLEDB for OLAP, ODBC, ADODB, and ADOMD+.

Integration Services – A set of graphical tools and programmable objects to move, copy, and transform data.

Client Tools Backward Compatibility – provides the following components: SQL Distributed Management Objects (SQL-DMO) and Decision Support Objects (DSO).

Client Tools SDK (Client Tools SDK) – contains a software development kit containing resources for programmers.
Documentation Components The documentation components contain components for viewing and managing help content.

Management Tools – Basic – these tools include the following:

• SQL Server Management Studio environment
• Support SQL Server Database Engine component,
• SQL Server Express, the service program sqlcmd
• PowerShell SQL Server

Management Tools – Full – in addition to the basic version components, it also includes:

• Support for SQL Server Management Studio Environment for Reporting Services
• Analysis Services
• Integration Services
• SQL Server Profiler application
• Database Engine Tuning Advisor
• SQL Server Program Management

Distributed Replay Controller -the Distributed Playback Controller controls the consistent actions of distributed playback clients. Only one instance of the controller can be installed in each Distributed Playback environment.

Distributed Replay Client – distributed playback clients work together to simulate the workload on an SQL Server instance. One or more clients can be installed in each Distributed Playback environment.

SQL Client Connectivity SDK – contains the Native Client SQL Server (ODBC/OLE DB) SDK for developing database applications.

Master Data Services – is a platform for integrating data from different systems in the enterprise into a single source of basic data for better accuracy and more convenient audit organization. When you select the Master Data Services option, you install the Master Data Services Configuration Manager, Windows PowerShell snap-in, folders and files for web applications and services.

By default, the SQL Server instance and shared components are installed in the %Program Files%\Microsoft SQL directory Server\. You can change the installation path during the initial installation of the SQL Server. Changing the installation path for one common component causes it to be changed for all other common components. If the common components are subsequently reinstalled, they will be installed in the directory specified during the initial installation.