Rakesh Patil

SQL Interview questions and answers on Installation and Upgrades – I

October 1, 2018 by

The following is a collection of SQL interview questions and answers, the second in this series, on installing SQL Server as well as installation of service packs and cumulative updates

Service Accounts and Network Protocols

SQL interview question 1. What is a service account?

While doing the installation you will find components that need to be installed, so there is a service to each component in the Windows Services. E.g. SQL Server, SQL Server Agent, SQL Analysis Services, SQL Server integration Services etc. There will be a user for each service through which each service will run. That use is referred to as the Service Account of that service.

SQL Server interview question 2. What are the network protocols that SQL Server supports?

Stand-alone named and default instances support the following network protocols:

  • Shared memory
  • Named pipes
  • TCP/IP
  • VIA

Shared Memory:

Clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer; it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

  • Server – Machine 1
  • Clients – Machine 1

TCP/IP:

TCP/IP is a common protocol widely used over the Internet. It communicates across interconnected networks of computers that have diverse hardware architectures and various operating systems. TCP/IP includes standards for routing network traffic and offers advanced security features. It is the most popular protocol that is used in business today.

  • Server – Machine 1
  • Clients – WAN (Any machine from any network)

Named Pipes:

Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first) or remote (on a networked computer).

  • Server – Machine 1
  • Clients – LAN (Any machine from LAN)

VIA:

Virtual Interface Adapter (VIA) protocol works with VIA hardware. This feature is deprecated.

Note: Shared memory is not supported on failover clusters.

SQL interview question 3. Do I need to grant Administrator permissions on the Windows server to SQL Service account to run the services?

No, it is not required. It’s not mandatory to grant Administrator permissions to the service account.

SQL interview question 4. What permissions are required for a user to install SQL Server on a server?

User through which we are installing SQL Server must have administrator permissions on the Windows server.

Installation & Upgrade

SQL interview question 5. What are the different releases of SQL Server? We usually talk about RTM, RC, SP, CU etc. right, what are the different releases of a SQL Server product and what do you know about them?

CTP (Community Technology Preview): It’s Beta release of SQL Server product.

RC (Release Candidate): A release candidate (RC) is a beta version with potential to be a final product, which is ready to release unless significant bugs emerge.

RTM (Released to Manufacturing): It is the original, released build version of the product. This is the actual product software that we get on DVD or ISO file from MSDN.

CU (Cumulative Update): Cumulative updates contain the bug fixes and enhancements up to that point in time that have been added since the previous Service Pack release and will be contained in the next service pack release.

SP (Service Pack): Larger collection of hotfixes that have been fully regression tested. In some cases, delivers product enhancements.

GDR (General Distribution Release): GDR packages contain only security and critical stability issue fixes. GDR fixes should not contain any of the CU updates.

LDR/QFE/Hot Fix (Limited Distribution Release/Quick Fix Engineering): LDR packages contain “other” fixes that have not undergone as extensive testing, and resolve issues that only a fraction of the millions of users might ever encounter. QFE updates include CU fixes.

SQL interview question 6. What is the Shared Features Directory and its usages?

This directory contains the common files used by all instances on a single computer e.g. SSMS, SQLCMD, BCP etc. These are installed in the folder “<drive>:\Program Files\Microsoft SQL Server\(100\110\120)” where <drive> is the drive letter where components are installed. The default is usually C drive.

SQL interview question 7. What is a SQL Server Instance?

An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages its own system databases and one or more user databases. An instance is a complete copy of a SQL Server installation.

SQL Server interview question 8. What are the type of Instance and maximum no. of instances which can be installed on a server.

There are two types of Instances:

  1. Default instance
  2. Named Instance

Each computer can run maximum of 50 standalone instances of the Database Engine. One instance can be the default 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 or with IP address and the port number.

A named instance is one where you specify an instance name when installing the instance. A connection request must specify computer name/IP Address and instance name/Port number in order to connect to the instance.

SQL interview question 9. Can you install multiple instances on the same disk drive?

Yes, we can install multiple instances on the same disk drive because each installation creates its own folder with the below format MSSQL11.INSTANCENAME.

SQL interview question 10. What is a collation and what is the default collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

The default collation: SQL_Latin1_General_CP1_CI_AS

SQL interview question 11. What is the default port of a SQL Server instance?

SQL Server default instance by default listen on 1433 port.

SQL interview question 12. Can we change the default port of SQL Server, How?

Yes, it is possible to change the Default port on which SQL Server is listening.

  • Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager
  • Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <Instance Name>
  • Right Click on TCP/IP and select Properties
  • In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group. Now change the value to static value which you want to set for SQL Server port.
  • Make sure Dynamic Port ls empty (Remove 0).

SQL interview question 13. How can I get the port number where the SQL Server instance is listening?

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

  • SQL Server Configuration Manager
  • Windows Event Viewer
  • SQL Server Error Logs
  • sys.dm_exec_connections DMV
  • Reading registry using xp_instance_regread

SQL interview question 14. What is a FILESTREAM?

FILESTREAM was introduced in SQL Server 2008 for the storage and management of unstructured data. The FILESTREAM feature allows storing BLOB data (example: word documents, image files, music and videos etc.) in the NT file system and ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table.

SQL interview question 15. How many SQL Server log files can be retained in the SQL Server error

Logs by default?

By default, there are seven SQL Server error logs; Error log and ERRORLOG.1 through ERRORLOG.6.

The name of the current, most recent log 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 interview question 16. Is it possible to increase the retention of Error log files and, if so, how?

Yes, it is possible to change the number of Error logs retention. We can follow the below steps to change the Error log file retention.

Open SQL Server Management Studio and then connect to SQL Server Instance In Object Explorer, expand Management Node and then right click SQL Server Logs and click Configure as shown in the snippet below.

In the Configure SQL Server Error Logs window you can enter the value between 6 and 99 for the number of error logs and click OK to save the changes.

SQL interview question 17. What is the installation log file location for SQL Server?

Here you find logs for each individual component as well as the actions performed at the time of installation.

Summary.txt:

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

All other files:

C:\Program Files\Microsoft SQL Server\(90\100\110\120\130)\Setup Bootstrap\LOG\<Date_Time>\

SQL interview question 18. What information is stored in Summary.txt?

This file has information on the installation start and stop time, installed components, machine name, product, version and detailed log files. Although this file differs slightly based on selected installation components, this file does not have any user names, passwords, service accounts, ports, etc.

This file does have a high-level log and references to the detailed files which will be reviewed next.

SQL interview question 19. Is there any possibility to find out the “SA” password from log files?

No. Clear Passwords not stored at anywhere.

While installing SQL Server, SA password validation and confirmation is logged on few setup related log files. But the actual password is never stored in clear text during the database services installation.

There are other places where we may see SA and service account password logged (Not in Clear

Format) is the temporary file location “C:\DOCUME~1\ProfileName\LOCALS~1\Temp\*.tmp” but these files are removed at the end of SQL Server installation.

SQL interview question 20. I have applied a SP/CU on 2012 instances. Where I can find the log files?

Check the folder with the latest data and time

C:\Program Files\Microsoft SQL Server\(90\100\110\120\130)\SetupBootstrap\LOG\<YYYYMMDD_HHMM>\<Summary_ServerName_Date_Time>Other files like component wise files are also located in the same folder.

SQL interview question 21. What is a silent installation and how can we use this feature?

The procedure to install SQL Server instance through command line using ConfigurationFile.ini file in “Quite” mode is known as Silent installation.

Read the part II article: SQL Interview questions and answers on Installation and Upgrades – II

See more

Check out ApexSQL Plan to view and analyze SQL Server query execution plans, for free, including comparing plans, stored procedure performance profiling, missing index details, lazy profiling, wait times, plan execution history





Rakesh Patil

Rakesh Patil

Data Architect at Phoenix Innovations
I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations.
I have hands-on Experience in Performance tunning, Database Disaster recovery,
AlwaysOn,Replication and AWS.
Rakesh Patil
366 Views