Hey there, today we are going to discuss another set of Junior SQL DBA Interview Question Answers. Here basically we’ll be concentrating on SQL Server Installation related interview questions. I would suggest you to go through SQL Interview Q&A Part1 , Part2 and Part3 as well.
1.What are the system requirements for installing SQL Server?
Ans: It is always good to refer the Microsoft documentation for specific version and edition before you plan to Install SQL Server. I can mention few basic requirements which are as follows:
- Minimum 4 GB RAM.
- 64 bit Processor with 2 GHz or faster
- Microsoft recommends to have at least 6 GB free hard-disk space.
- SQL Server 2016 (13.x) and later require .NET Framework 4.6 for the Database Engine. SQL Server setup automatically installs .NET Framework.
- A supported operating system.
- Moreover, You cannot run SQL Server services on a domain controller.
2. What permissions the user needs to install SQL Server?
Ans: The user or the login should have Local Administrator access on the server.
3. What is a SQL Server Instance?
Ans: A Database Engine instance is a version of the sqlservr.exe program that runs as a service on the operating system. It is responsible for managing several system databases, as well as one or more user-created databases. A single server can have multiple instances running on it, and applications connect to a specific instance to access and work with the databases it manages.
4. What are the different types of Instances?
Ans: There are basically two types of SQL Server Instances.
- Default Instance
- Named Instance
The Default Instance has no name. If a connection request specifies only the name of the computer, the connection is made to the default instance. In other words you can connect to the SQL Server by the computer name or even by just mentioning a dot “.” if you are connecting to the local SQL Server default Instance. e.g. the below screen shot shows local default Instance connecting using a dot.
A Named Instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance.
Example of named Instance:
Moreover there is no requirement to install a default instance; all of the instances running on a computer can be named instances.
5. What is the Install/setup log file name and the path?
Ans: “Summary.txt” is the setup log file name and the path is as follows:
%Program Files%\Microsoft SQL Server\nnn\Setup Bootstrap\Log. “nnn” represents the version number. Below example screen shot shows the setup file path for SQL Server 2019
6. Is it possible to Install Multiple Instances on the same drive?
Ans: Yes, it is possible as it will create it’s own folder for the instance.
7. What is the default port number of SQL Instance?
Ans: The default port is 1433. You can refer TCP and UDP Ports explained
8. What is SQL Server Browser Services Port number?
Ans: 1434
9. How to check which port SQL Service is listening to?
Ans: There are couple of ways you can check it.
- SQL Server Configuration Manager
- SQL Server Error log
- Application Event Log
The most common one is SQL Server Configuration Manager if you can login to the server.
Open SQL Server Configuration Manager > SQL Server Network Configuration and then select Protocols for <instance name> on the left panel. Right click on TCP/IP and select Properties from the drop down and check the port mentioned against TCP Port under IPAll as shown below:
If you want to use SSMS (SQL Server Management Studio), you can open Error log and search for “Server is listening on” and check for the message as shown below having <ipv4>.
You can search for same text in Application event log.
10. Is it possible to change the default port and if yes, then how?
Ans: Yes, it is possible to change the default or existing port and that can be done though the SQL Server Configuration Manager. You can refer the answer to question no. 9 above. Navigate to the same path:
Open SQL Server Configuration Manager > SQL Server Network Configuration and then select Protocols for <instance name> on the left panel. Right click on TCP/IP > select Properties and click on port mentioned against TCP Port under IPAll. Here you can specify the new port number.
You need to restart the SQL Service to take affect of the new port number.
