Hi friend posted questions related to SQL SERVER services in this section I have mentioned all services.
1) List out various services which are important for SQL Server and its
components functionality?
2) What is SQL Server service and its importance?
SQL Server service is core of SQL Server instance. It runs the Database Engine and
executes the client requests related to data processing. If this service is not
running, no users can connect to the any of the database, hence users will not be
able to fetch, insert, update or delete the data.
3) What is SQL Server Agent service and its importance?
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 and Subscriptions etc. which are referred to as Jobs. 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 Backup, Restore, Log Shipping, SSIS Packages etc.
4) What is SQL Server Analysis service and its importance?
Microsoft SQL Server Analysis Services (SSAS) delivers online analytical
processing (OLAP) and data mining functionality for business intelligence
applications. Analysis Services supports OLAP by letting you design, create, and
manage multidimensional structures that contain data aggregated from other
data sources, such as relational databases. For data mining applications, Analysis
Services lets you design, create, and visualize data mining models that are
constructed from other data sources by using a wide variety of industry-standard
data mining algorithms.
5) What is SQL Server Integration service and its importance?
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server
database software that can be used to perform a broad range of data migration
tasks. SSIS is a platform for data integration and workflow applications. It features
a fast and flexible data warehousing tool used for data extraction, transformation,
and loading (ETL). The tool may also be used to automate maintenance of SQL
Server databases and updates to multidimensional cube data.
6) What is SQL Server Browser?
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. It helps in browsing the list
of servers, locating and connecting to the correct server.
This listener service responds to client requests with the names of the installed
instances, and the ports or named pipes used by the instance.
7) What is SQL Server Reporting Services?
This service is primarily used by SQL Server Reporting Services (SSRS) for
browsing and viewing the reports on Reports Server, through Report Server or
Report Manager interface. It is used to manage the shared data sources, reports,
shared data sets, report parts, folder, etc. hosted on the Report Server. Reporting
services are managed using the Reporting Services Configuration Manager.
8) What is SQL Server VSS Writer?
The SQL Writer Service provides added functionality for backup and restore of
SQL Server through the Volume Shadow Copy Service framework. When running,
Database Engine locks and has exclusive access to the data files. When the SQL
Writer Service is not running, backup programs running in Windows do not have
access to the data files, and backups must be performed using SQL Server
backup.
Use the SQL Writer Service to permit Windows backup programs to copy SQL
Server data files while SQL Server is running. It must be running when the Volume
Shadow Copy Service (VSS) application requests a backup or restore. To
configure the service, use the Microsoft Windows Services applet. The SQL Writer
Service installs on all operating systems.
9) Which types of backups are supported by SQL Write Service?
SQL Writer supports:
SQL Writer does not support:
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.
12) What is SQL Server Active Directory Helper?
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 whenever an SQL Server object
needs to be created in the Active Directory to register an instance of SQL Server.
13) What is Distributed Transaction Coordinator?
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 SQL server instances are
installed. 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.
14) How to check how many SQL Server instances are installed on a Window
Server?
There are multiple ways through which we can check the No. of SQL Server
instances which are running on a server like:
There are different ways through which we can start or stop SQL Server services.
a) Go to Services –> Look for SQL server service related to the Instance
Named Instance: SQL Server(Instance Name)
Default Instance: SQL Server(MSSQLServer)
b) Right Click on the SQL Server instance in management studio and click on
restart
c) Go to SQL Server Configuration Mananger (SQLServermanager10.msc) and
right click on the services and click restart.
d) Net stop command
e) use “Net START” command to list all the running services
f) Use “Net STOP MSSQL$Instancename” to stop the SQL Service for a particular
instance
17) What are the default parameters of SQL Server service start up process
and from where these parameters can be changed?
master database data and log file and error log files are the default parameters
which are passed to SQL Server service.
-dC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
We can add trace flags and other parameters to the startup process from SQL
Server Configuration manager.
18) How to start the SQL Server with minimal configuration?
If there are any configuration problems that prevent the server from starting, you
can start an instance of Microsoft SQL Server by using the minimal configuration
startup option. This is the startup option -f. Starting an instance of SQL Server
with minimal configuration automatically puts the server in single-user mode.
19) How to start SQL Server with single user model?
Under certain circumstances, we may have to start an instance of SQL Server in
single-user mode by using the startup option -m. For example, you may want to
change server configuration options or recover a damaged master database or
other system database. Both actions require starting an instance of SQL Server in
single-user mode.
20) What are trace flags and how can we apply trace flags on a SQL Server
instance?
Trace flags are used to temporarily set specific server characteristics or to switch
off a particular behavior.
For example, if trace flag 3205 is set when an instance of
SQL Server starts, hardware compression for tape drivers is disabled. Trace flags
are frequently used to diagnose performance issues or to debug stored
procedures or complex computer systems.
21) How the trace flags are enabled?
Trace flags are enabled at different levels.
We can use the -T option in the startup configuration for the SQL Server Service
to enable trace at instance level.
Session Level
We can use the DBCC TRACEON and DBCC TRACEOFF commands to enable it on
a session level.
22) How do I know what Trace Flags are turned on at the moment?
We can use the DBCC TRACESTATUS command
23) Name some of the Important Trace flags and their functionality?
Trace Flag: 1204
This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the
Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth
turning this trace flag on, only for debugging purposes.
Trace Flag: 1222
Similar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock
information to SQL Server Error Log in XML format.
Trace Flag: 3226
In an environment where database backup is frequently performed, it is a good
idea to turn trace flag 3226 on, to suppress an entry to the SQL Server Error Log
for each backup. This is beneficial as the bigger the log, the harder it is to find
other messages. However, you will need to ensure that none of your scripts or
systems rely on the backup entry detail on the SQL Server Error Log.
24) What are the mandatory databases to bring SQL Services up?
master, model, resource db, tempdb location.
25) Which system database is associated with SQL Server agent service?
msdb
26) What is Protocol is used by SQL Server Browser service?
SQL Server Browser service uses UDP protocol.
27) Which Port no. is used by SQL Server Browser service ?
1434
28) What will happen if SQL Server Browser service is stopped?
If the SQL Server Browser service is not running, the following connections do not
work:
29) What is the high Level SQL Server start up process?
Distributed Replay is a new functionality of Microsoft SQL Server 2012. It helps
you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL
Server tunings by replaying a trace captured from a productive SQL Server
environment to a new SQL Server test environment.
This new functionality is similar to SQL Server Profiler, but with more possibilities:
e. g. replaying the trace from multiple clients (up to sixteen), use a stress or
synchronization mode, configure options like think time, or connect time etc.
31) What are the various components involved in Distributed Replay
Concepts?
The following components make up the Distributed Replay environment:
Distributed Replay administration tool: A console application, DReplay.exe,
used to communicate with the distributed replay controller. Use the
administration tool to control the distributed replay.
Distributed Replay controller: A computer running the Windows service named
SQL Server Distributed Replay controller. The Distributed Replay controller
orchestrates the actions of the distributed replay clients. There can only be one
controller instance in each Distributed Replay environment.
Distributed Replay clients: One or more computers (physical or virtual) running
the Windows service named SQL Server Distributed Replay client. The Distributed
Replay clients work together to simulate workloads against an instance of SQL
Server. There can be one or more clients in each Distributed Replay environment.
Target server: An instance of SQL Server that the Distributed Replay clients can
use to replay trace data. We recommend that the target server be located in a
test environment.
1) List out various services which are important for SQL Server and its
components functionality?
- SQL Server Service
- SQL Server Agent service
- SQL Server Analysis Service
- SQL Server Browser service
- SQL Server Integration Service
- SQL Server Reporting Service
- SQL Server VSS Writer service
- Distributed Transaction Coordinator
- SQL Server Active Directory Helper
- SQL Full-Text Filter Daemon Launcher
- SQL Server Distributed Replay Client service
- SQL Server Distributed Replay Client service
2) What is SQL Server service and its importance?
SQL Server service is core of SQL Server instance. It runs the Database Engine and
executes the client requests related to data processing. If this service is not
running, no users can connect to the any of the database, hence users will not be
able to fetch, insert, update or delete the data.
3) What is SQL Server Agent service and its importance?
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 and Subscriptions etc. which are referred to as Jobs. 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 Backup, Restore, Log Shipping, SSIS Packages etc.
4) What is SQL Server Analysis service and its importance?
Microsoft SQL Server Analysis Services (SSAS) delivers online analytical
processing (OLAP) and data mining functionality for business intelligence
applications. Analysis Services supports OLAP by letting you design, create, and
manage multidimensional structures that contain data aggregated from other
data sources, such as relational databases. For data mining applications, Analysis
Services lets you design, create, and visualize data mining models that are
constructed from other data sources by using a wide variety of industry-standard
data mining algorithms.
5) What is SQL Server Integration service and its importance?
SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server
database software that can be used to perform a broad range of data migration
tasks. SSIS is a platform for data integration and workflow applications. It features
a fast and flexible data warehousing tool used for data extraction, transformation,
and loading (ETL). The tool may also be used to automate maintenance of SQL
Server databases and updates to multidimensional cube data.
6) What is SQL Server Browser?
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. It helps in browsing the list
of servers, locating and connecting to the correct server.
This listener service responds to client requests with the names of the installed
instances, and the ports or named pipes used by the instance.
7) What is SQL Server Reporting Services?
This service is primarily used by SQL Server Reporting Services (SSRS) for
browsing and viewing the reports on Reports Server, through Report Server or
Report Manager interface. It is used to manage the shared data sources, reports,
shared data sets, report parts, folder, etc. hosted on the Report Server. Reporting
services are managed using the Reporting Services Configuration Manager.
8) What is SQL Server VSS Writer?
The SQL Writer Service provides added functionality for backup and restore of
SQL Server through the Volume Shadow Copy Service framework. When running,
Database Engine locks and has exclusive access to the data files. When the SQL
Writer Service is not running, backup programs running in Windows do not have
access to the data files, and backups must be performed using SQL Server
backup.
Use the SQL Writer Service to permit Windows backup programs to copy SQL
Server data files while SQL Server is running. It must be running when the Volume
Shadow Copy Service (VSS) application requests a backup or restore. To
configure the service, use the Microsoft Windows Services applet. The SQL Writer
Service installs on all operating systems.
9) Which types of backups are supported by SQL Write Service?
SQL Writer supports:
- Full database backup and restore including full-text catalogs
- Differential backup and restore
- Restore with move
- Copy-only backup
- Auto-recovery of database snapshot
SQL Writer does not support:
- Log backups
- File and filegroup backup
- Page restore
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.
12) What is SQL Server Active Directory Helper?
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 whenever an SQL Server object
needs to be created in the Active Directory to register an instance of SQL Server.
13) What is Distributed Transaction Coordinator?
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 SQL server instances are
installed. 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.
14) How to check how many SQL Server instances are installed on a Window
Server?
There are multiple ways through which we can check the No. of SQL Server
instances which are running on a server like:
- Check the SQL services for different Instances
- SQL Server Configuration Manager Start- all programs – Microsoft SQL Server
- 2008 R2 -> configuration tools –> Microsoft SQL Server configuration
- Manager
- List out SQL Services from Net Start command
- Using Powershell commands
- Readging the Registry Keys, Regedit-> HKEY_LOCAL_MACHINE–>SOFTWARE ->Microsoft ->Microsoft SQL Server –> InstalledInstance
There are different ways through which we can start or stop SQL Server services.
a) Go to Services –> Look for SQL server service related to the Instance
Named Instance: SQL Server(Instance Name)
Default Instance: SQL Server(MSSQLServer)
b) Right Click on the SQL Server instance in management studio and click on
restart
c) Go to SQL Server Configuration Mananger (SQLServermanager10.msc) and
right click on the services and click restart.
d) Net stop command
e) use “Net START” command to list all the running services
f) Use “Net STOP MSSQL$Instancename” to stop the SQL Service for a particular
instance
17) What are the default parameters of SQL Server service start up process
and from where these parameters can be changed?
master database data and log file and error log files are the default parameters
which are passed to SQL Server service.
-dC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf;
-eC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG;
-lC:\Program Files\Microsoft SQL
Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
We can add trace flags and other parameters to the startup process from SQL
Server Configuration manager.
18) How to start the SQL Server with minimal configuration?
If there are any configuration problems that prevent the server from starting, you
can start an instance of Microsoft SQL Server by using the minimal configuration
startup option. This is the startup option -f. Starting an instance of SQL Server
with minimal configuration automatically puts the server in single-user mode.
19) How to start SQL Server with single user model?
Under certain circumstances, we may have to start an instance of SQL Server in
single-user mode by using the startup option -m. For example, you may want to
change server configuration options or recover a damaged master database or
other system database. Both actions require starting an instance of SQL Server in
single-user mode.
20) What are trace flags and how can we apply trace flags on a SQL Server
instance?
Trace flags are used to temporarily set specific server characteristics or to switch
off a particular behavior.
For example, if trace flag 3205 is set when an instance of
SQL Server starts, hardware compression for tape drivers is disabled. Trace flags
are frequently used to diagnose performance issues or to debug stored
procedures or complex computer systems.
21) How the trace flags are enabled?
Trace flags are enabled at different levels.
- Global
- Session
We can use the -T option in the startup configuration for the SQL Server Service
to enable trace at instance level.
Session Level
We can use the DBCC TRACEON and DBCC TRACEOFF commands to enable it on
a session level.
22) How do I know what Trace Flags are turned on at the moment?
We can use the DBCC TRACESTATUS command
23) Name some of the Important Trace flags and their functionality?
Trace Flag: 1204
This trace flag lets SQL Server to log detailed deadlock information to SQL Server Error Log in text format. In SQL Server 2008, this trace flag is only available at the
Global Level (i.e. applies to the SQL Server instance). In my experience, it is worth
turning this trace flag on, only for debugging purposes.
Trace Flag: 1222
Similar to trace flag 1204, this trace flag lets SQL Server to log detailed deadlock
information to SQL Server Error Log in XML format.
Trace Flag: 3226
In an environment where database backup is frequently performed, it is a good
idea to turn trace flag 3226 on, to suppress an entry to the SQL Server Error Log
for each backup. This is beneficial as the bigger the log, the harder it is to find
other messages. However, you will need to ensure that none of your scripts or
systems rely on the backup entry detail on the SQL Server Error Log.
24) What are the mandatory databases to bring SQL Services up?
master, model, resource db, tempdb location.
25) Which system database is associated with SQL Server agent service?
msdb
26) What is Protocol is used by SQL Server Browser service?
SQL Server Browser service uses UDP protocol.
27) Which Port no. is used by SQL Server Browser service ?
1434
28) What will happen if SQL Server Browser service is stopped?
If the SQL Server Browser service is not running, the following connections do not
work:
- If we have just one instance installed on machine and it is running on default port 1433, then status of SQL Server Browser service does not make any difference in our connection parameters.
- If there are more than one instances running on the same machine, in that case either we have to start SQL Server Browser service or provide the port number along with IP (or server name) and instance name, to access any other instance than default
- If SQL Server Browser service is stopped and IP along with port number is not provided then connection will be refused.
- If SQL Server instance is configured using dynamic ports then browser service is required to connect to correct port number.
- Also our named instances will not be published in the list of SQL Server instances on the network (which could be a good thing)
29) What is the high Level SQL Server start up process?
- The service is authenticated by verifying the credentials provided in the logon account and the service is started.
- PID is allocated at windows level
- Authentication mode details are verified i.e either MIXED or WINDOWS
- Information of the startup parameters is captured i.e mdf location of master database, SQL Server error log location and ldf file location
- Some memory and CPU settings done at windows level, this is an informational message only
- Starts the master database
- model is the next database to start
- set the port related information
- Tempdb is recreated each time when we restart sql server
- Start msdb and other user databases based on dbid
Distributed Replay is a new functionality of Microsoft SQL Server 2012. It helps
you assess the impact of future upgrades (SQL Server, hardware, OS) or SQL
Server tunings by replaying a trace captured from a productive SQL Server
environment to a new SQL Server test environment.
This new functionality is similar to SQL Server Profiler, but with more possibilities:
e. g. replaying the trace from multiple clients (up to sixteen), use a stress or
synchronization mode, configure options like think time, or connect time etc.
31) What are the various components involved in Distributed Replay
Concepts?
The following components make up the Distributed Replay environment:
Distributed Replay administration tool: A console application, DReplay.exe,
used to communicate with the distributed replay controller. Use the
administration tool to control the distributed replay.
Distributed Replay controller: A computer running the Windows service named
SQL Server Distributed Replay controller. The Distributed Replay controller
orchestrates the actions of the distributed replay clients. There can only be one
controller instance in each Distributed Replay environment.
Distributed Replay clients: One or more computers (physical or virtual) running
the Windows service named SQL Server Distributed Replay client. The Distributed
Replay clients work together to simulate workloads against an instance of SQL
Server. There can be one or more clients in each Distributed Replay environment.
Target server: An instance of SQL Server that the Distributed Replay clients can
use to replay trace data. We recommend that the target server be located in a
test environment.
