By: Dattatrey Sindol | Comments (2) | Related: > SQL Server Configurations
Problem
I am a BI Developer and have been recently moved to Microsoft BI, I need to get up to speed. As part of my exercise to ramp up on this new platform, I started going through each of the SQL Server components to try to get familiar with them. While I was going through the Windows Services, I have seen a lot of new services that are added after installing SQL Server. I want to get an understanding of the various services related to SQL Server and its components, and their dependencies.
Solution
When SQL Server is installed, a number of Windows Services are also installed depending on the components selected. Apart from that, there are a couple of services which are part of the Windows OS installation that are also used by SQL Server and its components. It is important to understand the significance of each of these services to manage them effectively thereby ensuring effective utilization of available resources on the physical server. In this tip we will outline an initial understanding on the services that are used by SQL Server and its components that are required for proper particular components and features.
Distributed Transaction Coordinator
Display Name: Distributed Transaction CoordinatorService Name: MSDTC
- Highlights of Distributed Transaction Coordinator Windows Service:
- This service coordinates distributed transactions between two or more database servers. Client applications use this service to work with data from multiple sources in one transaction.
- There is always only one instance of MSDTC service running on a computer irrespective of how many database server instances are installed.
- All the database server instances installed on a computer use the same MSDTC service to manage distributed transactions between two or more database servers.
- Not all systems support distributed transactions especially the legacy systems. MSDTC service cannot be used to enforce transactions on such systems.
- This service should be running on each of the servers which handle distributed transactions.
- This service is not a part of SQL Server installation. This service is installed with Windows OS installation.
- This service is required (should be running) to perform the following operations:
- To manage the transactions which span across multiple resource managers like databases (on different server instances), message queues, and file system.
- This service is not required (can be in disabled state) to perform any of the following operations:
- To manage transactions when the distributed transactions span across multiple databases on the same instance of SQL Server.
- This service depends on the following SQL Server related services:
- It does not depend on any SQL Server related services. However, it depends on the following system services - Remote Procedure Call (RPC), Security Accounts Manager.
- Related Information:
SQL Server Active Directory Helper
Display Name: SQL Active Directory Helper ServiceService Name: MSSQLServerADHelper100
- Highlights of SQL Active Directory Helper Service Windows Service:
- This service enables the integration with the Active Directory.
- Irrespective of number of instances of SQL Servers installed on a computer, there is always only one instance of SQL Server Active Directory Helper service.
- This service is automatically started by SQL Server when required and is stopped once the operation is completed.
- This service is required (should be running) to perform the following operations:
- Whenever an SQL Server object needs to be created in the Active Directory to register an instance of SQL Server.
- This service is not required (can be in disabled state) to perform any of the following operations:
- For accessing and querying the SQL Server databases/data.
- This service depends on the following SQL Server related services:
- None.
- Related Information:
SQL Full-Text Filter Daemon Launcher
Display Name: SQL Full-text Filter Daemon Launcher (MSSQLSERVER)Service Name: MSSQLFDLauncher
- Highlights of SQL Full-Text Filter Daemon Launcher Windows Service:
- This service is used by the full-text search feature of SQL Server.
- It helps in starting the filter daemon host process, which manages the full-text indexing, querying, search filtering and word processing as part of the full-text search feature.
- This service is required (should be running) to perform the following operations:
- To use the full-text search feature in SQL Server.
- This service is not required (can be in disabled state) to perform any of the following operations:
- To access the database present in SQL Server database engine.
- To work with any of the other SQL Server components like SSIS, SSAS, SSRS, SQL Server Agent, etc.
- This service depends on the following SQL Server related services:
- None.
- Related Information:
SQL Server
Display Name: SQL Server (MSSQLSERVER)Service Name: MSSQLSERVER
- Highlights of SQL Server Windows Service:
- This is the primary database service and is used by the SQL Server database engine.
- There can be multiple instances of SQL Server on a computer.
- This service is required (should be running) to perform the following operations:
- To query the data from any of the databases present on the instance of SQL Server.
- For SQL Server Agent to successfully perform the scheduled administrative tasks.
- For proper functioning of SQL Server Reporting Services (SSRS).
- To view and manage the SSIS packages (through SSMS) which are stored in msdb database.
- This service is not required (can be in disabled state) to perform any of the following operations:
- For using the client tools like SQL Server Management Studio (SSMS), Business Intelligence Development Studio (BIDS) etc. for development purposes.
- This service depends on the following SQL Server related services:
- None.
- Related Information:
SQL Server Agent
Display Name: SQL Server Agent (MSSQLSERVER)Service Name: SQLSERVERAGENT
- Highlights of SQL Server Agent Windows Service:
- SQL Server Agent is the primary scheduling engine in SQL Server.
- This is used to execute scheduled administrative tasks like SSIS Packages, T-SQL Scripts, Batch Files, Subscriptions etc. which are referred to as Jobs. A Job is a parent level entity which contains one or more steps. A step is the child/low level entity which contains the actual commands/instructions for performing a specific task. Whereas a job defines the sequence of execution of steps, schedule for running steps, etc.
- Jobs can be executed in following ways - on demand, on a schedule, or in response to a specific event.
- It uses msdb database to store the configuration, processing, and metadata information. Apart from SQL Server Agent related information, msdb database also stores similar information related to other SQL Server features like Service Broker, Log Shipping, SSIS Packages etc.
- This service is required (should be running) to perform the following operations:
- To perform the scheduled administrative tasks like execution of SSIS Packages, T-SQL Scripts, Replication, etc.
- For processing of report subscriptions in reporting services.
- This service is not required (can be in disabled state) to perform any of the following operations:
- Querying the SQL Server Agent Job Information from the msdb database.
- For using other components like SSIS, SSAS, SSRS etc. when they do not have any dependent tasks to perform like executing SSIS Packages, processing of cubes, processing of subscriptions, etc.
- This service depends on the following SQL Server related services:
- [SQL Server (MSSQLSERVER)]: SQL Server Agent stores all the metadata, configuration, and processing information in a system database called msdb. SQL Server service is required for the functioning of SQL Server Agent.
- [SQL Server Reporting Services]: SQL Server Reporting Services window service should be running for successful processing of scheduled reporting services tasks like subscriptions etc. which are performed by SQL Server Agent.
- Related Information:
SQL Server Analysis Services
Display Name: SQL Server Analysis Services (default)Service Name: MSSQLServerOLAPService
- Highlights of Analysis Services Component (SSAS) and SQL Server Analysis Services Windows Service:
- SSAS is the OLAP component of SQL Server and is used for reporting and analysis purposes.
- SQL Server Analysis Services windows service is used by the SSAS component to perform various operations including storage and processing of dimensions, partitions, cubes etc.
- Analysis Services supports only Windows Authentication while connecting from SQL Server Management Studio (SSMS).
- This service is required (should be running) to perform the following operations:
- To deploy the cube to the OLAP server and process it.
- Editing and Processing various components like partitions, aggregations etc. through XMLA.
- For the functioning of both ad-hoc and canned reports which pull the data from cubes.
- This service is not required (can be in disabled state) to perform any of the following operations:
- Development of SSAS Objects like dimensions, cubes, data mining structures/models etc. using BIDS.
- This service depends on the following SQL Server related services:
- [SQL Server (MSSQLSERVER)]: If SQL Server is being used as the source for loading data into cube, then the SQL Server service on the corresponding instance of source server should be running.
- [SQL Server Browser]: SQL Server Browser service is required for connecting to the SSAS instance on the computer.
- Any other services associated with the type of source being used for cube.
- Related Information:
SQL Server Browser
Display Name: SQL Server BrowserService Name: SQLBrowser
- Highlights of SQL Server Browser Windows Service:
- This service acts as a listener for the incoming requests for Microsoft SQL Server resources.
- It provides information about the list of installed SQL Server instances on the computer to the client computers/applications.
- Helps in browsing the list of servers, locating and connecting to the correct server.
- This service is required (should be running) to perform the following operations:
- To connect to the named instance of SQL Server if all the parameters like TCP/IP port etc. are not provided.
- To connect to the SQL Server Analysis Services.
- This service is not required (can be in disabled state) to perform any of the following operations:
- For accessing the default instance of SQL Server with default configuration.
- Connecting to SQL Server Integration Services.
- This service depends on the following SQL Server related services:
- None.
- Related Information:
SQL Server Integration Services 10.0
Display Name: SQL Server Integration Services 10.0Service Name: MsDtsServer100
- Highlights of Integration Services Component (SSIS) and Integration Services Windows Service:
- This service is primarily used by the SQL Server Integration Services (SSIS) component of SQL Server.
- Packages stored in SSIS are stored in the SSIS Package Store which can be either msdb database or a designated folder in the Windows file system (Stored Packages folder in Integration Services in SSMS).
- There is always only one instance of Integration Services even when there are multiple SQL Server instances installed on the computer (All the instances belonging to same version of SQL Server).
- Integration Services supports only Windows Authentication while connecting from SQL Server Management Studio (SSMS).
- This service is required (should be running) to perform the following operations:
- Monitoring the execution of packages that are stored in SSIS Package Store.
- Starting, Stopping and Monitoring of packages that are stored/running on local and remote instance of SSIS. This includes the packages which are executing from SSIS package store, file system or through BIDS. Currently running packages can be seen under Running Packages folder in Integration Services in SSMS.
- To view and manage (import, export, delete, reorganize, etc.) the packages which are stored in SSIS Package Store on local and remote instance of SSIS.
- Managing the permissions on the packages that are stored in SSIS Package Store.
- This service is not required (can be in disabled state) to perform any of the following operations:
- Development of SSIS Packages using Business Intelligence Development Studio
- Execution of Packages through BIDS, DTEXEC Command Line Utility, DTEXEC UI, SQL Server Agent or the Import/Export Wizard in SSMS
- Restarting the packages through Checkpoints
- Creation and Scheduling of Packages stored in SSIS Package Store using SQL Server Agent
- Querying the list of packages stored in msdb using T-SQL
- Managing the SSIS related Database Roles in msdb database
- This service depends on the following SQL Server related services:
- [SQL Server (MSSQLSERVER)]: To view and manage (import, export, delete, reorganize etc.) the packages (through SSMS) that are stored in msdb database.
- [Distributed Transaction Coordinator (MSDTC)]: To support the transactions implemented in the packages (if any). Transactions are not supported by all the systems especially some of the legacy systems.
- Related Information:
SQL Server Reporting Services
Display Name: SQL Server Reporting Services (MSSQLSERVER)Service Name: ReportServer
- Highlights of Reporting Services Component (SSRS) and Reporting Services Window Service:
- This service is primarily used by SQL Server Reporting Services (SSRS) component of SQL Server.
- The service can be started or stopped from Reporting Services Configuration Manager, SQL Server Configuration Manager, and Control Panel -> Administrative Tools -> Services.
- Reporting Services Configuration Manager is used to manage the settings for Report Server and Report Manager.
- Reporting Services supports only Windows Authentication while connecting from SQL Server Management Studio (SSMS).
- This service is required (should be running) to perform the following operations:
- Browsing and Viewing the reports on Reports Server, through Report Server or Report Manager interface.
- Managing the shared data sources, reports, shared data sets, report parts, folder, etc. hosted on the Report Server. This includes creation, addition or deletion these objects.
- Managing the permissions on report manager, reports, shared data sources, shared datasets, etc.
- Using the Reporting Services Configuration Manager to manage the settings for Report Server and Report Manager.
- This service is not required (can be in disabled state) to perform any of the following operations:
- Development of SSRS reports in Business Intelligence Development Studio (BIDS) and previewing them.
- Querying the reports related information like list of reports, data sources, report execution history, subscriptions, etc. from ReportServer database.
- This service depends on the following SQL Server related services:
- [SQL Server (MSSQLSERVER)]: SQL Server Service should be running for us to be able to access Report Server and Report Manager Interfaces. If we try to access the Report Manager while Reporting Services service is running but SQL Server service is not running, it will give an error: "The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help"
- [SQL Server Agent (MSSQLSERVER)]: Reports Subscription Processing and Scheduled Reports depend on SQL Server Agent service and it should be running for successful processing of Subscriptions and Scheduled Reports.
- Related Information:
SQL Server VSS Writer
Display Name: SQL Server VSS WriterService Name: SQLWriter
- Highlights of SQL Server VSS Writer Windows Service:
- This service offers additional functionality for backup and restoration operations in SQL Server by using Volume Shadow Copy Service (VSS) Framework.
- A framework is implemented using a set of COM APIs (VSS), which allows volume backups to be performed while the data is being modified/written on to the disks.
- This service is required (should be running) to perform the following operations:
- To permit Windows backup programs/software to copy the SQL Server data files while SQL Server is running and the data is being written to the disk.
- This service is not required (can be in disabled state) to perform any of the following operations:
- Backups which are performed using SQL Server backup feature.
- This service depends on the following SQL Server related services:
- None.
- Related Information:
Additional Information:
- Most of the above listed services can be started/stopped from either SQL Server Configuration Manager or through Control Panel (Control Panel -> Administrative Tools -> Services). However, it is always advisable to manage the services through SQL Server Configuration Manager as using other options might break the services.
- Any of the above listed services can be Started or Stopped from command prompt using the following commands:
- To start the service: NET START "<<Service Display Name>>"
Example: NET START "SQL Server Reporting Services (MSSQLSERVER2K8R2)" - To stop the service: NET STOP "<<Service Display Name>>"
Example: NET STOP "SQL Server Integration Services 10.0"
Note:
- The above information is provided with reference to a Standalone instance of SQL Server 2008/2008 R2 and not a Clustered Environment.
- Service Names mentioned above are for a default instance of SQL Server. For a named instance, the service name will also include the instance name.
Next Steps
- Based on the understanding gained in this tip, adjust the startup options for each of the services in your environment and disable those services which you do not require to ensure effective utilization of system resources.
- Check out the following related tips:
About the author
Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
