Q&A

Installing SQL Server Interview Questions and Answers

Installing SQL Server Interview Questions and Answers

Here are listing out few question and answers on installing sql server, lets go through these questions.

1. What are the components installed with the SQL Server 2005 installation?

Ans:

• Server Components
• SQL Server Database Engine
• Analysis Services
• Reporting Services
• Notification Services
• Integration Services
• Client Components
• Connectivity Components
• Communication between clients and servers
• Network libraries for DB-Library, ODBC, and OLE DB.
• Management Tools
• Management Studio
• Profiler
• Configuration Manager
• Database Tuning Advisor
• Development Tools
• Business Intelligence Development Studio
• Documentation and Samples
• Books Online
• SQL Server Samples

2. What are the editions available in SQL Server 2005?

Ans:

• Enterprise Edition (32-bit and 64-bit)
• Evaluation Edition (32-bit and 64-bit)
• Standard Edition (32-bit and 64-bit)
• Workgroup Edition (32-bit and 64-bit)
• Developer Edition (32-bit and 64-bit)
• Express Edition (32-bit only)
• Compact Edition (32-bit only)
• Runtime Edition (32-bit and 64-bit)

3. What are the network protocols that SQL Server supports?

Ans:

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

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

Note   Shared memory is not supported on failover clusters.

4. What are the basic software requirements for installing SQL Server 2005?

Ans:

• Microsoft Windows Installer 3.1 or later
• Microsoft Data Access Components (MDAC) 2.8 SP1 or later
• Microsoft .NET Framework 2.0 Software Development Kit (SDK)
• Microsoft .NET Framework 2.0 (Installed Automatically)
• Microsoft SQL Server Native Client (Installed Automatically)
• Microsoft SQL Server Setup support files (Installed Automatically)

5. What are the file locations for Server components?

Ans:

Server components are installed in directories with the format <instanceID>\<component name>. For example, a default or named instance with the Database Engine, Analysis Services, and Reporting Services would have the following default directories:

• <Program Files>\Microsoft SQL Server\MSSQL.1\MSSQL\ Database Engine
• <Program Files>\Microsoft SQL Server\MSSQL.2\OLAP\ for Analysis Services
• <Program Files>\Microsoft SQL Server\MSSQL.3\RS\ for Reporting Services

6. What about SSIS, Notification services and client components?

Ans:

SQL Server 2005 Integration Services, Notification Services, and client components are not instance aware and, therefore, are not assigned an instance ID. Non-instance-aware components are installed to the same directory by default: <system drive>:\Program Files\Microsoft SQL Server\90\. Changing the installation path for one shared component also changes it for the other shared components. Subsequent installations install non-instance-aware components to the same directory as the original installation.

7. List out the default path for the SQL Server components.

Ans:

Database Engine server components

\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Binn\

Database Engine data files

\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data\

Analysis Services server

\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Bin\

Analysis Services data files

\Program Files\Microsoft SQL Server\MSSQL.n\OLAP\Data\

Reporting Services report server

\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services

\ReportServer\Bin\

Reporting Services report manager

\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services

\ReportManager\Bin\

SQL Server Integration Services

<Install Directory>\90\DTS\

Notification Services

<Install Directory>\90\Notification Services\

Client Components

<Install Directory>\90\Tools\

Components that are shared between all instances of SQL Server 2005

\Program Files\Microsoft SQL Server\90\Shared\

8. Can we change the directory while adding new features?

Ans:

Can’t! You must either install additional features to the directories already established by Setup, or uninstall and reinstall the product.

9. What are the log files generated while Installing\Upgrading\Applying (packages) SQL Server on Windows machine? (SQL Server 2008 / R2)

Ans:

Summary.txt (SQL 2005)

This file shows the SQL Server components that were detected during Setup, the operating system environment, command-line parameter values if they are specified, and the overall status of each MSI/MSP that was executed.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\.

Note:

To find errors in the summary text file, search the file by using the “error” or “failed” keywords.

 

Summary_engine-base_YYYYMMDD_HHMMss.txt

Generated during the main workflow

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap

\Log\<YYYYMMDD_HHMM>\

Summary_engine-base_YYYYMMDD_HHMMss_ComponentUpdate.txt

Generated during the component update workflow.

Location:

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

Summary_engine-base_20080503_040551_GlobalRules.txt

Generated during the global rules workflow.

Location:

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

Detail.txt

Detail.txt is generated for the main workflow such as install or upgrade, and provides the details of the execution. The logs in the file are generated based on the time when each action for the installation was invoked, and show the order in which the actions were executed, and their dependencies.

Location

%\Microsoft SQL Server\100\Setup Bootstrap\Log

\<YYYYMMDD_HHMM>\Detail.txt.

Note:

If an error occurs during the Setup process, the exception or error are logged at the end of this file. To find the errors in this file, first examine the end of the file followed by a search of the file for the “error” or “exception” keywords.

Detail_ComponentUpdate.txt

Detail_GlobalRules.txt

MSI log files

The MSI log files provide details of the installation package process. They are generated by the MSIEXEC during the installation of the specified package.

Location

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log \<YYYYMMDD_HHMM>\<Name>.log.

Note:

At the end of the file is a summary of the execution which includes the success or failure status and properties. To find the error in the MSI file, search for “value 3” and usually the errors can be found close to the string.

ConfigurationFile.ini

The configuration file contains the input settings that are provided during installation. It can be used to restart the installation without having to enter the settings manually. However, passwords for the accounts, PID, and some parameters are not saved in the configuration file.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\

<YYYYMMDD_HHMM>\

SystemConfigurationCheck_Report.htm

Contains a short description for each executed rule, and the execution status.

Location:

%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log

\<YYYYMMDD_HHMM>\

10. How much had disk space required installing the SQL Server 2005/08?

Ans:

2 GB. Before the installation is started windows installer allocated 2 GB of temporary space for installation.

11. What is the space required for each component in SQL Server?

Ans:

Database Engine and data files, Replication, and Full-Text Search – 280 MB

Analysis Services and data files – 90 MB

Reporting Services and Report Manager – 120 MB

Notification Services engine components, client components, and rules components – 50 MB

Integration Services – 120 MB

Client Components – 850 MB

SQL Server Books Online and SQL Server Compact Edition Books Online – 240 MB

Samples and sample databases – 410 MB

(Note that samples and sample databases are not installed by default.)

12. List out the Windows machines which supports the SQL Server 2005 Enterprise edition.

Ans:

32-Bit:

Windows 2000 Server SP4

Windows 2000 Advanced Server SP4

Windows 2000 Datacenter Edition SP4

Windows Server 2003 Server SP1

Windows Server 2003 Enterprise Edition SP1

Windows Server 2003 Datacenter Edition SP1

Windows Small Business Server 2003 Standard Edition SP1

Windows Small Business Server 2003 Premium Edition SP1

Windows Server 2008 Standard (32 bit and 64 bit)

Windows Server 2008 Enterprise (32 bit and 64 bit)

Windows Server 2008 Data Center (32 bit and 64 bit)

Windows Server 2008 Web Edition (32 bit and 64 bit)

64-Bit:

Windows Server 2003 64-Bit Itanium Datacenter Edition SP1

Windows Server 2003 64-Bit Itanium Enterprise Edition SP1

Windows Server 2008 Itanium

13. What are the security considerations for a SQL Server installation?

Ans:

Before Installation:

• Enhance physical security
• Use firewalls
• Isolate services
• Create service accounts with least privileges
• Disable NetBIOS and server message block

After Installation:

• Run SQL Server services with the lowest possible privileges.
• Associate SQL Server services with Windows accounts.
• Use Windows Authentication for connections to SQL Server.
• Always assign a strong password to the sql account.
• Always enable password policy checking.

14. What additional software requires for installing SSRS 2005?

Ans:

• Microsoft Internet Information Services (IIS) 5.0 or later is required for Reporting Services installations.
• Microsoft Internet Explorer 6.0 SP1 is required for installations of the Report Designer component of Reporting Services.

15. Which component performs the configuration check while installing the SQL Server 2005?

Ans:

As part of SQL Server 2005 Setup, the System Configuration Checker (SCC) scans the computer where Microsoft SQL Server 2005 will be installed. The SCC checks for conditions that prevent a successful SQL Server installation. Before Setup starts the SQL Server 2005 Installation Wizard, the SCC retrieves the status of each check item, compares the result with required conditions, and provides guidance for removal of blocking issues. All of the SCC check items are network enabled; checks can run on a local computer, as well as in remote and cluster situations.

 

16. What are the precautions needs to be taken before installing SQL Server 2005?

Ans:

• Be sure the computer meets the system requirements for SQL Server 2005
• Review Security Considerations for a SQL Server Installation.
• Run SCC to identify the blocking issues and resolve before go ahead.
• Make sure you have administrator permissions on the computer where SQL Server will be installed. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share.
• Back up your current installation of SQL Server if you are running an instance of SQL Server on the computer where SQL Server 2005 is to be installed
• Verify that the disk where SQL Server will be installed is uncompressed. If you attempt to install SQL Server to a compressed drive, Setup will fail
• Exit antivirus software while installing SQL Server
• Stop all services that depend on SQL Server, including any service using Open Database Connectivity (ODBC), such as Internet Information Services (IIS). Exit Event Viewer and registry editors (Regedit.exe or Regedt32.exe).

17. List out the Instance-Aware and Instance-Unaware Services

Ans:

Instance-aware services in Microsoft SQL Server 2005 include:

• SQL Server
• SQL Server Agent
• Analysis Services
• Reporting Services
• Full-Text Search

Instance-unaware services in SQL Server 2005 include:

• Notification Services
• Integration Services
• SQL Server Browser
• SQL Server Active Directory Helper
• SQL Writer

18. What is the installation log file location for SQL Server 2005?

Ans:

Summary.txt:

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG

All other files:

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files

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

19. What information stored in Summary.txt?

Ans:

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.  What this file does have is references to the detailed files which will be reviewed next.

20. Is there any possibility to find out the “sa” password from log files?

Ans:

Clear Passwords not stored at anywhere. But !

When it comes to clear text passwords being stored in the detailed log files, it appears as if the sa password validation and confirmation is logged, but the actual password is never stored in clear text during the database services installation.  As an example, the files listed below reference the usage of the sa password:

• SQLSetup0002_MachineName_SQL.log
• SQLSetup0002_MachineName_Tools.log
• SQLSetup0002_MachineName_WI.log

Stored in C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files

After reviewing the SQL Server 2005 installation log files, it appears as if temporary files are referenced.  Do these local files have clear text passwords?  Based on files located at the below location C:\DOCUME~1\ProfileName\LOCALS~1\Temp\*.tmp these files appear to be cleaned up after the installation process and do not appear to pose any additional vulnerability because they do not appear to have the passwords in clear text.

21. What are the prerequisites for installing SQL Server 2008?

Ans:

Microsoft SQL Server 2008 setup requires Windows Installer 4.5 and .Net Framework 3.5 SP1 to be installed. The Windows Installer 4.5 and .Net Framework 3.5 SP1 can be installed independently or it can be installed by double clicking SETUP.EXE within SQL Server 2008 installation media. Both Windows Installer 4.5 and .Net Framework setups are also available in <Drive>:\x86\redist folder of installation media. Once Windows Installer 4.5 and .Net Framework 3.5 SP1 is installed the system requires a restart of the operating system.

22. I have applied SP3 on 2005 instances. Where we can find the log files?

Ans:

C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap

\LOG\Hotfix\Summary.txt (On that day)

Other files like component wise files are also located in the same folder.

23. What is Slipstreaming?

Ans:

It is a term used to describe merging original source media with updates in memory and then installing the updated files. Slipstreaming has been supported by Windows Operating systems for awhile but has just been added to SQL Server 2008 service pack 1. Slipstream allows you to go to the latest and greatest, currently service pack 1 and a CU for service pack 1. Prior to service pack, we supported just update the setup file also referred to as “Patchable Setup”. Since the release of SQL Server 2008 Service Pack 1, it is recommend to use the slipstream procedures instead as patchable setup since the entire product can be updated and you will be using the latest and greatest. Any scenario (install, upgrade, addnode) that is supported by the original media is supported when slipstream.

25. I have installed SQL 2008 Database Engine component to an instance and now I want to add features to this instance using slipstream. How do I add features to an existing instance?

Ans:

In theory you just run the slipstream build again to add features. However, setup will fail since setup does not correct detect that it is installed and needs to be patched versus installed.  In this case, you need to add features using original media and then patch using the service pack installer. We don’t support features within an instance at different levels, so ensure features within an instance are at the same patch level.

26. I have slipstreamed media, but I just want to install the original media (RTM). How can I just install the original media?

Ans:

You need to use your original media.

27. What if I did not copy the ia64 and x64 folders to my original media which is necessary for slipstreaming. Can I still install x86?

Ans:

Technically, it will work. But we do not recommend skipping merging all architectures since at some point, a user might install one of these architectures.  If you merges x86 but not x64, you media is inconsistent. If at a later point in time, you run x64, the result will be unpredictable.

28.I already have SQL Server 2008 instance and now I want to update to service pack 1. Do I need to use slipstream?

Ans:

No, slipstream will not work when the product is already installed.

29. Does PCUSource need to be an absolute path?

Ans:

No, it can be a relative path, such as “.\PCU”. However, when you launch setup.exe your current path needs to point to the setup.exe.

**30. I am hitting the error: “The setting ‘PCUSOURCE’ specified is not recognized.”

Ans:

If you have followed the slipstream instructions, ensure the path to PCUSOURCE does not contain spaces. The setup does not handle the spaces as expected.  The quick workaround is to rename the PCUSource path so it does not contain any spaces. You can confirm this is the problem by viewing the Detail_ComponentUpdate.log located at %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<last session>. Search for “PCUSource”. If you see commas in the PCUSource path, this is the issue:

31: Can I slipstream in any CU?

Ans:

In general, yes.  However, you need to ensure the CU will work the original release (RTM) or SP.  For example, if you are slipstreaming Service Package 1 (SP1), then only CUs based on SP1 can be slipstreamed into the installation. If you are not slipstreaming a service pack, then you need to ensure the CU is for the original release.

 

Uninstalling the Service Pack:

32. After I slipstream the original media and service pack 1, and then uninstall the service pack is it the same as just install the original media?

Ans:

Essentially, it is the same. However some of the components (e.g. SNAC, MSXML 6, and Setup support files) can’t be rolled back. The core features: Database Engine, Reporting Services, Analysis Services, and the other shred components will be rolled back.

33. Can I use slipstream to upgrade from SQL 2005 to SQL Server 2008?

Answer: Yes.

34. Can I use slipstream to upgrade from SQL 2000 to SQL Server 2008?

Answer: Yes.

35.  Is there a way to find out when a patch / sp was last applied to an instance?

Ans:

• Start SSMS, open help menu and go to “about”
• Run Select @@version
• Run SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
• Run XP_READERRORLOG and from the result set we can find the version details
• If nothing worked as far as I know only option there would be to look at the add/remove programs list (or dump this data from the registry to make it easier to deal with) and get that info from there. SQL Server doesn’t track that within the system databases.

36. Can you identify the current version from version no?

Ans:

Yes we can!

Version Latest

2008R2 SP1 – 10.50.2 —.-

2008R2 RTM – 10.50.1 —.-

2008 SP3 – 10.00.5.—

2008 SP2 – 10.00.4.—

2008 SP1 – 10.00.2.—

2008 RTM – 10.00.1.—

2005 SP4 – 9.00.5.—

2005 SP3 – 9.00.4.—

2005 SP2 – 9.00.3.—

2005 SP1 – 9.00.2.—

2005 RTM – 9.00.1.—

2000 SP4 – 8.00.7—-

 

 

Errors While Installing…………………….

1. Can you narrate one critical installation issue that you resolved?

Ans:

Yes we have few.

A) I have got a request to install SQL Server 2005 Standard Edition with SP4. While installing database services it suddenly failed by throwing a pop – up. The error message:

“The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, “How to: View SQL Server 2005 Setup Log Files” and “Starting SQL Server Manually”

Ans:

I gone through the all security credentials and found everything is fine. Gone through the log files and found the windows server is running with 24 processors Where as SQL Server 2005 Standard Edition supports upto 4 processors. To resolve the issue follow the steps below.

1. Update windows config to use only one processor (From MSConfig — IniBoot)

2. Restart the windows server

3. Install SQL Server

4. Apply Service Pack 4

5. Change config to use default configuration

6. Restart the windows server

B) Installed failed on MSXML 6 Service Pack 2”

Ans:

Remove the MSXML 6.0 Parser and related components (with MSXML 6 *.*) from Add and Remove Programs at Control Panel. I was not able to remove it. I found there is a solution to force removal of this component nothing but windows installer cleanup.

We can get it from MSDN download center or we can get it from below link

http://www.eStockCard.com/Software/msicuu2.exe . Once all related components of MSXML 6.0 Parser were removed I ran the setup again and it was completed w/o any issue.

Other Issues:

1 Sample Error from errorlog–>MSI (s) (C4:C8) [19:04:30:953]: Product: Microsoft Office 2003 Web Components — Error 1706. Setup cannot find the required files. Check your connection to the network, or CD-ROM drive. For other potential solutions to this problem, see

C:\Program Files\Microsoft Office\OFFICE11\1033\SETUP.CHM.

Ans:

Solution 1: Uninstall SQL server 2005 completely and then try to manually run the OWC11.msi file available in Tools folder of sql 2005.Then try to install sql 2005 freshly !

Solution 2: Just uninstall Office web components from Add \ Remove programs and then try to install sql 2005

2. “There was an unexpected failure during the setup wizard” – Error 1603

Ans:

Solution1: To work around this problem, set the folders in the correct layout for the SQL Server 2005 installation. The SQL Server 2005 installation uses the following two folders:

• Servers
• Tools

These two folders must be under the same level of a folder or the root folder of a drive. The names of these folders must be exactly Servers and Tools. The Servers folder contains all the files that are required to install major SQL Server 2005 components, such as database engine. The Tools folder contains tools components and Books Online for SQL Server 2005.Then try installing sql from the path\servers\setup.exe

3. SP2 fails to install database services – MSP Error: 29512 “Unable to install Windows Installer MSP file” Error Snippet : MSP Error: 29512 SQL Server Setup was unable add user Clusteruser@FQDN (where FQDN is the actual fully qualified domain name – abbreviated here for security) to local group DOMAIN\SQL2005SQLAgents (where DOMAIN is the actual name of the domain). MSP returned 1603: A fatal error occurred during installation.

Ans:

Solution: KB 926622 — This article is for SP1 and also applicable to SP2 also.
To install SP2, try creating the following path, and restart SP2 setup:
DRIVE:\Microsoft SQL Server\MSSQL.2\MSSQL\FTData

4. SQL Server 2005 Setup program taking very long time to be completed

Ans:

Reason1: This problem may occur when you install software that also installs SQL Server 2005, such as Microsoft System Center Data Protection Manager (DPM) 2007.

Reason2: When the primary domain has many external trust relationships with other domains, or when many lookups are performed at the same time, the time that is required to look up domain group names may increase significantly. Therefore, the time that is required to install SQL Server 2005 may also increase.

Solution1: This problem can be solved by installing a Hotfix. Below is the link for the Hotfix.

http://support.microsoft.com/?kbid=910070

5. MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file Sample Error

Product : Database Services (MSSQLSERVER)
Product Version (Previous) : 1399
Product Version (Final) :
Status : Failure
Log File : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB921896_sqlrun_sql.msp.log
Error Number : 29538
Error Description : MSP Error: 29538 SQL Server Setup did not have the administrator permissions required to rename a file: C:\Program Files\Microsoft SQL Server\MSSQL\Data\mssqlsystemresource1.ldf. To continue, verify that the file exists, and either grant administrator permissions to the account currently running Setup or log in with an administrator account. Then run SQL Server Setup again

Ans:

You can run the install in repair mode and repair your RTM instance. After that, you should be able to install SP2. Take a look at Books Online or template.ini on the original media for repair mode options. Here’s a sample snippet from template.ini to repair all files, reg keys, and shortcuts for a default instance.

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REBUILDDATABASE=1 REINSTALL=All SAPWD= REINSTALLMODE=OMUS

6. SQLEXpress installation error

“An instance with the same name is already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name.”

Solution:

You can get this error if your previous uninstallation of SQLExpress doesn’t get uninstalled properly. You need to cleanup some registry keys to proceed with the installation of SQLExpress. For more information http://blogs.msdn.com/b/astebner/archive/2005/12/08/501885.aspx

7. Error MSP Error: 29537 while installing SQL Server 2005 SP3

Sample Error

Product                   : Database Services (MSSQLSERVER)
Product Version (Previous): 4035
Product Version (Final)   :
Status                    : Failure
Log File                  : C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB955706_sqlrun_sql.msp.log
Error Number              : 29537

Error Description         : MSP Error: 29537  SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client][SQL Server]CREATE DATABASE failed. Some file names listed could not be created. Check related errors.. To continue, correct the problem, and then run SQL Server Setup again.

Ans:

CAUSE:
By default the setup creates the Temp_MS_AgentSigningCertificate_database Database on specified default database location, If the location doesn’t exist, it fails to create the database and the setup fails.

RESOLUTION:

1. In SSMS, go to Server Properties > Database Settings > Database Default
Locations , check default location for Data and Log .
2. Try to access locations specified at Data and Log, Make sure files are available
there.
3. If any of the files missing from above location , we hit the above error.
4. In that case, change the path to point to reall location of mdf/ldf files .
5. Setup should now succeed.

8. “Error 1706. Setup cannot find the required files.

Check your connection to the network, or CD‐ROM drive”

Sol:

Just uninstall Office web components from Add \ Remove programs

and then try to install sql 2005

9. “There was an unexpected failure during the setup wizard” ‐ Error 1603

Sol

Set the folders in the correct layout for the SQL Server 2005 installation. The SQL Server 2005 installation uses the following two folders:

• Servers

• Tools

These two folders must be under the same level of a folder or the root folder of a drive. The names of these folders must be exactly Servers and Tools.