Important Questions-INSTALLATION

INSTALLATION

Q1. Where will you find the SQL Server installation related logs?

Installation related logs are stored under the shared feature directory folder which was selected at the time of first SQL Server instance installation.

C:\programfiles\Microsoft SQL Server\110\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

Q2. What is “ConfigurationFile.ini” file?

SQL Server Setup generates a configuration file named ConfigurationFile.ini, based upon the system default and run-time inputs.

The ConfigurationFile.ini file is a text file which contains the set of parameters in name/value pairs along with descriptive comments.

Q3. What is the location of ConfigurationFile.ini file?

C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log folder.

Q4. What is a service account?

Based on the selected components while doing the installation we will find respective 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 and every service through which each service will run. That use is called Service Account of that service.

Mainly we categorize the Service account as below:

Local User Account: This user account is created in the server where SQL Server is installed; this account does not have access to network resources.

Local Service Account: This is a builtin windows account that is available for configuring services in windows.

This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server.

Local System Account: This is a builtin windows account that is available for configuring services in windows.

This is a highly privileged account that has access to all resources in the server with administrator rights.

Network Service Account: This is a builtin windows account that is available for configuring services in windows.

This has permissions to access resources in the network under the computer account.

Domain Account: This account is a part of our domain that has access to network resources for which it is intended to have permission. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.

Q5 . What are 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, DTExec etc.

These are installed in the folder <drive>:\Program Files\Microsoft SQL Server\110\ , where <drive> is the drive letter where components are installed. The default is usually drive C.

Q6. What is an 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 an SQL Server installation.

Q7. Type of Instance and maximum no. of instances which can be installed on a server.

There are two types of Instances.

Default instance

Named Instance

Each computer can run maximum of 50 instances of the Database Engine.  One instance can be the default instance.

A connection request must specify both the computer name and instance name in order to connect to the instance.

Q8. 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.

Default collation:  SQL_Latin1_General_CP1_CI_AS

Q9. What is an RTM setup of SQL Server?

RTM stands for release to manufacturing.

Q10. What is a Service Pack, Patch, Hot fix and its difference?

Service Pack is abbreviated as SP, a service pack is a collection of updates and fixes, called patches, for an operating system or a software program.

Many of these patches are often released before the larger service pack, but the service pack allows for an easy, single installation.

Patch – Publicly released update to fix a known bug/issue

Hotfix – update to fix a very specific issue, not always publicly released

Q11. What’s the practical approach of installing Service Pack?

Steps to install Service pack in Production environments:

First of all raise a change order and get the necessary approvals for the downtime window. Normally it takes around 45-60 minutes to install Service pack if there are no issues.

Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.

List down all the Startup parameters, Memory Usage, CPU Usage etc and save it in a separate file.

Install the service pack on SQL Servers.

Verify all the SQL Services are up and running as expected.

Validate the application functionality.

Note: There is a different approach to install Service pack on SQL Server cluster instances. That will be covered in SQL Server cluster.

Q12. What is a slip stream installation and its usages?

SQL Server 2008 introduced a concept that’s called “Slipstream Installation”. This is a way to deploy a SQL Server instance with all the needed Service pack as part of the installation.

 Everything will be installed in one go, hence there is no need to deploy any other service packs on the installation.

Q13. 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.

Q14. What is the default port of a SQL Server instance? 1433

Q15. 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.

Step 1. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>

Step 2. Right Click on TCP/IP and select Properties

Step 3. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAllgroup. Now change the value to static value which you want to set for SQL Server port.

Q15. How to get the port number where the SQL Server instance is listening?

Below are the methods using which we can get the port information.

Method 1: SQL Server Configuration Manager (TCP/IP Properties)

Method 2: Windows Event Viewer-Event ID 26022

Method 3: SQL Server Error Logs 

(EXEC xp_readerrorlog 0, 1, N'Server is listening on', 'any', NULL, NULL, N'asc')

 Method 4: sys.dm_exec_connections DMV

(select distinct local_net_address, local_tcp_port from sys.dm_exec_connections)

Method 5: Reading registry using xp_instance_regread

Q16. 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.

Q17. What’s the location of SQL Server log files?

Instance Root Directory\MSSQL\Log

Q18. How many SQL Server log files can be retained in the SQL Server error logs be default?

By default, there are seven SQL Server error logs; Errorlog 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.

Q19. Is it possible to increase the retention of Error log files and How?

Yes , 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 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.