SQL Server installation interview Questions and Answers

1.What is Database Engine?

  • The Database Engine is the primary component of SQL Server. It is the Online Transaction Processing (OLTP) engine of SQL Server.
  • The Database Engine is highly optimized for transaction processing, but offers exceptional performance in complex data retrieval operations.
  • Database Engine is also responsible for the controlled access and modification of data through its security subsystem.
  • SQL Server supports up to 50 instance of the Database Engine on a single computer.

2.What are the Network Protocols that SQL Server support?

SQL Server support the following network protocols.

  • TCP/IP.
  • Named Pipes.
  • Shared memory.
  • VIA

3.What permissions are required for a user install SQL Server on a server?

  • User must have local administrator permission required to install SQL Server on the windows server.

4.What is SQL Server Instance?

  • Separate copy of same software product is called an instance.
  • Each instance manage its own system databases and one or more user databases.
  • Each computer can run maximum 50 standalone instance of the Database Engine. One instance can be the default instance.
  • There are 2 types of instance available in SQL Server.
  1. Default instance.
  2. Named instance.

Default Instance:

  • The default instance has no name. It’s name is equal to system Name.
  • Only ONE default instance can installed per machine.
  • The default services name is MSSQLSERVER.

Named Instance:

  • A Named instance is one where you specify an instance name where installing the instance.
  • The Named instance name is equal to SystemName\InstanceName.
  • We can installed 49 names instances per machine.

5. Can we install multiple instance on the same Disk?

YES,We can install multiple instances on the same disk because each installation create it’s own folders.

6. What is the default port number in SQL Server Instance?


7. What is SQL Server Browser Services Port number.


8.Can we change the default port number of SQL Server?

Yes, We can change the default port number of SQL Server.

9. How to get the SQL Server port numbers?

Below are the methods which we can use to get the port information.

  • SQL Server configuration Manager.
  • Windows Event Viewer.
  • SQL Server Error Loge.
  • sys.dm_exec_connections.
  • Reading registry using xp_instance_regread.
DECLARE @portNumber NVARCHAR(10);
EXEC xp_instance_regread
@rootkey = ‘HKEY_LOCAL_MACHINE’,
@key = ‘Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll’,
@value_name = ‘TcpDynamicPorts’,
@value = @portNumber OUTPUT;
SELECT [Port Number] = @portNumber;

10. What is FILE STREAM?

FILE STREAM was introduced in SQL Server 2008 for the storage and management of unstructured data. The FILESTREAM feature allows storing BLOB data(word document,image file,music etc).

11. How many default SQL Server error log files are generated? 

By default 7 (seven) SQL Server error log files was generated. ERRORLOG and ERRORLOG.1 through ERRORLOG.6. The name of the current log file is ERRORLOG with no extension.The log is re-created every time that you restart SQL Server.When the ERRORLOG file is re-created,the previous log is renamed to ERRORLOG.1, and the next previous log (ERRORLOG.1) is renamed to ERRORLOG.2,and so on. ERRORLOG.6 is deleted. 

SQL Server error log file

12. Is It possible to increase the retention of error log files?

Yes,It is possible to change the number of error logs retention.

13. Is there any possibility to find out the ‘SA’ password from log files?

NO,Clear password not stored at anywhere.

14. What is the Summary.txt file location in SQL Server?

C:\Program Files\Microsoft SQL Server\(90\100\110\120\130)\Setup Bootstrap\Log.

15. What information stored in Summary.txt file?

Installation start and stop time,installed components,machine name,product,version and detailed log file information stored in this file.


Get SQL Dba Tutorial Updates!

Signup now and receive an email once I publish new content.

I agree to have my personal information transfered to MailChimp ( more information )

I will never give away, trade or sell your email address. You can unsubscribe at any time.

Add Comment