SQL Server DBA Interview Questions and Answers – SQL Server Services

This blog is all about the SQL Server Services which are required for the functionality of SQL Server components. It also contains Questions answers about the important parameters and SQL Server start process.

SQL Server DBA Interview Questions and Answers

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

10) Which types of backups are not supported by SQL Write Service?

SQL Writer does not support:

•                 Log backups
•                 File and filegroup backup
•                 Page restore

11) What is Full-Text Search 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.

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

15) What are the different ways to start and Stop SQL Server services?

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

16) List out the Service Display name, Service Name and Executable for SQL Serve related services?

Common Name	Service Display Name	Service Name	ExecutableName
Distributed Transaction Coordinator (DTC)	Distributed Transaction Coordinator	MSDTC	msdtc.exe
SQL Server	SQL Server (MSSQLSERVER)	MSSQLSERVER	sqlservr.exe
	SQL Server (NamedInstance)	MSSQL$NamedInstance
SQL Server Active Directory Helper	SQL Active Directory Helper Service	MSSQLServerADHelper100	SQLADHLP.EXE
SQL Server Agent	SQL Server Agent (MSSQLSERVER)	SQLSERVERAGENT	SQLAGENT.EXE
	SQL Server Agent (NamedInstance)	SQLAgent$NamedInstance
SQL Server Analysis Services	SQL Server Analysis Services (MSSQLSERVER)	MSSQLServerOLAPService	msmdsrv.exe
	SQL Server Analysis Services (NamedInstance)	MSOLAP$NamedInstance
SQL Server Browser	SQL Server Browser	SQLBrowser	sqlbrowser.exe
SQL Server Full Text Search	SQL Full-text Filter Daemon Launcher (MSSQLSERVER)	MSSQLFDLauncher	fdlauncher.exe
	SQL Full-text Filter Daemon Launcher (NamedInstance)	MSSQLFDLauncher$NamedInstance
SQL Server Integration Services	SQL Server Integration Services 10.0	MsDtsServer100	MsDtsSrvr.exe
SQL Server Reporting Services	SQL Server Reporting Services (MSSQLSERVER)	ReportServer	ReportingServicesService.exe
	SQL Server Reporting Services (NamedInstance)	ReportServer$NamedInstance
SQL Server VSS Writer	SQL Server VSS Writer	SQLWriter

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

Instance level

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

30) What is Distributed Replay?

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.

References: Thanks to the all the SQL Server techies who wrote and shared the valuable information in the below blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.

http://www.mssqltips.com/sqlservertip/2622/windows-services-for-sql-server/

https://msdn.microsoft.com/en-us/library/hh510203.aspx

https://technet.microsoft.com/en-us/library/ms175609(v=sql.90).aspx

http://www.mssqltips.com/sqlservertip/1946/overview-of-the-sql-server-browser-service/