March towards SQL Server : Day 1 – SQL DBA Interview Questions Answers – Installation

2

Day-1-Calendar

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

Parvinder Pal

Facebook 

  1. […] March towards SQL Server : Day 1 – SQL DBA Interview Questions Answers – Installation […]

  2. imanpreetsinghbindra@gmail.com March 3, 2015 at 9:44 am

    Nice one, this post will definitely help SQL community and New Techies who are planning to move into DBA domain.

  3. pardeep attri March 4, 2015 at 11:44 am

    Ultimate quote Sir !!

    Keep it up 🙂

  4. Senthil March 5, 2015 at 12:21 am

    Nicely presented. I would suggest to split the answer for Q#14 into two:
    A) Yes for standalone installations and
    B) No for clustered installations.

    • Sachin Diwakar March 9, 2015 at 10:18 am

      Thanks for your inputs.

  5. kasi March 9, 2015 at 7:05 am

    Nicely Presented all the questions and answers. :):)

    Definitely this data is more useful to all sql Dba’s.

    • kasi March 9, 2015 at 7:05 am

      Nicely Presented all the questions and answers.

      Definitely this data is more useful to all sql Dba’s.

      • kiran March 12, 2015 at 2:37 am

        ultimate its very useful

  6. Devesh Srivastava March 25, 2015 at 8:27 am

    Hi,

    I have a question for all the guys here. I am just curious to know that during DBA interviews in Indian IT MNCs, they ask like I should know everything and they already know everything and they are going to make me work on each and every feature of SQL server just like SQL server scientist. And when actually after people get to the company to work, they actually see what the level of stuff they are working on. In one of the companies where I got interviewed for the position, Senior project manager took my interview who left behind technical stuff way back and asked about each and every thing and was looking something at the laptop. Months after that, i got to know that he was asking me each and every question from one of the interview questions portal. Hell!

    Why they do so when we know that whats the standard of DBA stuff here in India in service based companies?

    Thanks
    Devesh

    • Sachin Diwakar March 25, 2015 at 5:08 pm

      Hi Devesh,

      I am not sure if I can answer your questions. While hiring – everyone thinks of hiring the best resource.

      Thanks.

      • Devesh Srivastava April 1, 2015 at 9:51 am

        Hi Diwakar,

        I understand that the companies wants to hire the best resource but after hiring they should provide the opportunities to use the gained experience rather than wasting it and making the skill set go down leaving behind the resource with so much of frustration.

        • Devesh Srivastava April 1, 2015 at 9:53 am

          I am sorry Sachin, misunderstood your first name with the last name. So, ‘Hi Sachin’ 🙂

  7. Varun June 7, 2015 at 11:50 am

    Agreed with Devesh. In India we DBAs get interviewed by managers (sometimes those with little technical experience) who sometimes know very little about the DBA concepts. The interviewing process is totally different in other countries. I have been interviewed around 18-20 times outside India and all the time I was communicating\answering to a Senior DBA or DBA lead. Team managers were sitting there in the interview only for observing my responses and check if my attitude is good for the team. If companies needs a good DBA and best resource then it should have been interviewed by a senior person with the same technology.

    • Sachin Diwakar June 7, 2015 at 8:11 pm

      I agree with you Varun – thats how it should be.

%d bloggers like this: