I have been thinking since long time to pen down a comprehensive series on most commonly asked DBA Interview questions. I have planned to segregate the entire questions series, topic wise to make it easier to explore. So here comes the First blog of this series based on SQL Server installation topic. I hope it will help all those DBAs who are looking for more opportunities in their career as a SQL Server DBA.

Topic: All about SQL Server installation

 

1. What are the various Editions available in SQL Server 2012 version?

Below are the various editions available in Microsoft SQL Server 2012

• SQL Server 2012 Standard Edition
• SQL Server 2012 Enterprise Edition
• SQL Server 2012 Business Edition
• SQL Server 2012 Express Editions
• SQL Server 2012 Web and Developer Editions

2. What are the major differences between paid editions – Standard, Enterprise and Business Editions in SQL Server 2012?

SQL Server 2012 Capabilities	Enterprise	Business Intelligence	Standard
Maximum No. of Cores	OS Max*	16 Cores for DB – OS Max for BI	16 Core
Basic Reporting & Analytics	Yes	Yes	Yes
Enterprise data Management (Data Quality Services, Master Data Services)	Yes	Yes	No
Self-Service Business Intelligence (Power View, PowerPivot for SPS)	Yes	Yes	No
Corporate Business Intelligence (Semantic model, advanced analytics)	Yes	Yes	No
Advanced Security (Advanced auditing, transparent data encryption)	Yes	No	No
Data Warehousing (ColumnStore, compression, partitioning)	Yes	No	No
High Availability (Always ON)	Advanced	Basic	Basic
Maximum memory utilized (per instance of SQL Server Database Engine)	OS max	64 GB	64 GB
Server Core support	Yes	Yes	Yes
Backup compression	Yes	Yes	Yes
Database snapshot	Yes	No	No
Online indexing	Yes	No	No
Data compression	Yes	No	No
Resource Governor	Yes	No	No

3. What are the minimum Software requirements to install SQL Server 2012?

• Internet Explorer 7 or a later version is required for Microsoft Management Console (MMC), SQL Server Data Tools (SSDT), the Report Designer component of Reporting Services, and HTML Help
• SQL Server 2012 does not install or enable Windows PowerShell 2.0; however Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio.
• NET 3.5 SP1 is a requirement for SQL Server 2012 when you select Database Engine, Reporting Services, Replication, Master Data Services, Data Quality Services, or SQL Server Management Studio, and it is no longer installed by SQL Server Setup.
• Dot NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step.  SQL Server Express does not install .NET 4.0 when installing on the Windows 2008 R2 SP1 Server core operating system. You must install .NET4.0 before you install SQL Server Express on a Windows 2008 R2 SP1 Server core operating system.

 

SQL Server Setup installs the following software components required by the product:

 

• Dot NET Framework 4 1
• SQL Server Native Client
• SQL Server Setup support files

 

4. What are the minimum Hardware requirements to install SQL Server 2012 Paid editions?

SQL Server Editions	Memory(RAM)	CPU
SQL Server 2012 Enterprise (64-bit) x64	1GB	1.4 GHz AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
SQL Server 2012 Business Intelligence (64-bit) x64	1GB	1.4GHz AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
SQL Server 2012 Standard (64-bit)	1GB	1.4GHz AMD Opteron, AMD Athlon 64, Intel Xeon with Intel EM64T support, Intel Pentium IV with EM64T support
SQL Server 2012 Enterprise (32-bit)	1GB	1GHz Pentium III-compatible processor or faster
SQL Server 2012 Business Intelligence (32-bit)	1GB	1GHz Pentium III-compatible processor or faster
SQL Server 2012 Standard (32-bit)	1GB	1GHz Pentium III-compatible processor or faster

 

5. 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.  e.g. If Shared Features were selected to  be placed on the “C:\Program Files\Microsoft SQL Server” then logs will be created under

 

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

 

6. 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. Many of the parameter names correspond to the screens and options which you see while installing SQL Server through the wizard.  We can then use the configuration file to install SQL Server with the same configuration instead of going through each of the installation screens.

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

We can find the configuration file in the C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log folder. There will a subfolder based on a timestamp of when the SQL Server 2012 installation was done.

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

9. Do we need to grant Administrator permissions on the Windows server to SQL Service account to run the services or not, why?

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

10. What permissions are required to install SQL Server on a server?

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

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

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

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

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.

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.

14. Can we 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

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

16. What is an RTM setup of SQL Server?

RTM stands for release to manufacturing.

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

18. What is the latest Service pack available for SQL Server 2012 in the market?

SQL Server 2012   Service Pack 2 (SP2)

Version: 11.0.5058.0

Release Date: 6/10/2014

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

Steps to install Service pack in Production environments:

1. 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.
2. Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.
3. List down all the Startup parameters, Memory Usage, CPU Usage etc and save it in a separate file.
4. Install the service pack on SQL Servers.
5. Verify all the SQL Services are up and running as expected.
6. 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.

20. Is it mandatory to restart the Windows server after installing SQL server service pack?

No, it’s not mandatory to restart Windows server after installing SQL Server service pack but it is always a good practice to do so.

21. How to check the SQL Server version and Service pack installed on the server?

select convert(varchar(50),SERVERPROPERTY('productversion')) ,
convert(varchar(50),SERVERPROPERTY ('productlevel')) ,
convert(varchar(50),SERVERPROPERTY ('edition'))

Or

select @@VERSION

22. How to check SQL Server name?

Select @@Servername

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

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

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

SQL Server default instance by default listen on 1433 port.

26. 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. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager

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

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

Step 4. 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.

 

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

Method 2: Windows Event Viewer

Method 3: SQL Server Error Logs

Method 4: sys.dm_exec_connections DMV

Method 5: Reading registry using xp_instance_regread

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

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

SQL Server error logs are stored in the below location.

Instance Root Directory\MSSQL\Log

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

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

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

1. Open SQL Server Management Studio and then connect to SQL Server Instance
2. InObject Explorer, ExpandManagement Node and then right click SQL Server Logs and click Configure as shown in the snippet below.
3. 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