By: Ben Snaidero
Overview
The components of SQL Server can be broken up into 3 main categories:
- The first is what I like to call the core components. These components, although they can and do interact with each other, are essentially their own stand-alone application.
- Next are what I would call ancillary services. These are services that exist separate from the core components, but really only add additional functionality to one of these components and have no other purpose on their own.
- Finally, there are some other tools that I wouldn't classify as core components of the database platform but instead are more just other stand-alone applications that provide additional functionality related to SQL Server but can also be used with other data platforms (not just relational).
SQL Server Core Components
SQL Server Database Engine
The database engine is the main component of the SQL Server database platform. It provides the functionality for storing, retrieving, processing and securing data. In addition to processing traditional relational data the SQL Server database engine, it also natively supports the processing of XML and JSON data. It also provides a few different high availability and disaster recovery solutions, from simple native backup/restore commands for disaster recovery to more complicated mirroring/clustering (AlwaysOn) and log shipping for high availability. It also supports distributing table data to multiple targets using transaction replication.
SQL Server Integration Services (SSIS)
Integration Services is a set of graphical tools and services which allows you to write code that can read, transform and write data to and from SQL Server and/or other database platforms (depending on availability of other platform drivers for connectivity). It has been available since 2005 when it replaced Data Transformation Services (DTS).
SQL Server Analysis Services (SSAS)
Analysis Services is the component that you use to create and manage an online analytical processing (OLAP) environment. You can use this component to create and store data from a multi-dimensional model and perform data mining on this data.
SQL Server Reporting Services (SSRS)
Reporting Services includes client and server components that can be used to create a full-blown reporting applications. With the client side tools you can create and deploy many different types of reports: tabular, graphical, etc. Once deployed, the server-side components can then be used to serve these reports as requested by clients.
SQL Server Ancillary Services
SQL Server Agent
SQL Server Agent runs as a separate Windows service and is used to schedule jobs and handle automated tasks. These jobs can include steps that can run T-SQL queries, PowerShell scripts, Analysis Services queries and Integration Services packages.
SQL Browser
Available since SQL Server 2005, the SQL Browser runs as a Windows service and provides information about the instances installed on a computer. When multiple instances are installed, the service will allow clients to easily find and connect using only the host and instance name and not require a port to be specified.
SQL Server Full Text Search
This service actually requires a couple of items to work correctly. First, you need to have a full-text index defined on table columns that you will be searching on. Once this is in place, full text queries can be issued against this table/column(s) and a linguistic search will be performed on the indexed text data. Note that full text search only works on text-based columns: char, varchar, nchar, nvarchar, text, ntext, image, xml, or varbinary(max) and FILESTREAM.
Other Tools for SQL Server
Master Data Services
Master Data Services is Microsoft’s platform for implementing Master Data Management (MDM). This tool allows you to create a model which defines the master set of data for your enterprise. The model defines the entities that make up your enterprise data, rules for updating the data entities and controls who can make updates to the data. This allows you to bring in data from many different sources and provide a single view of the data for your enterprise.
Data Quality Services
Data Quality Services is product that allows you to build a knowledge base that you can use to perform many different data quality tasks. These could include de-duplication, enrichment and cleansing. It also provides you with the ability to analyze and profile your current data using this knowledge base in order to protect the integrity of your data.
Machine Learning
This feature can be used to execute in-database R and Python scripts. Starting with SQL Server 2016, R services were introduced as an add-on to the SQL Server engine. With SQL Server 2017, SQL Server now provides installation support for a standalone Machine Learning Server which includes both R and Python. The libraries in these packages can be used on a standalone server to process large amounts of data and perform statistical and predictive analysis of this data.
