Rakesh Patil

SQL Server installation and upgrade troubleshooting interview questions

September 21, 2018 by

1. Can a Service Pack or Cumulative Update be uninstalled to roll back in case of failures?

We cannot uninstall a service pack or Cumulative Update on SQL Server 2005 or lower.

Starting with SQL Server 2008, we can uninstall a service pack or Cumulative Update from control panel Add or remove programs a view installed updates window.

2. You might have seen the issue “SQL Server service is not starting” right? Can you describe the top 3 common reasons that cause SQL Server service failure?

Service account password changed but not updated at SQL Server Services

System databases not available or not accessible. Ex: Files might be deleted or corrupted STARTUP parameters having incorrect path locations ex: Master database file location is wrongly specified in SQL Server start-up parameters.

3. Can you describe the top reasons that cause SQL Server installation failures?

  • Missing supporting software. Ex: DOTNET Framework
  • Missing Supporting Files
  • Corrupted Binaries/Files
  • Access Denied
  • PowerShell not installed
  • Partial Uninstallation of previous instance
  • Unsupported versions
  • Registry corruption
  • Using Wrong Services Accounts
  • Anti-virus blocking the installation

4. What are the common issues you faced in upgrading SQL Server?

Common causes

  • SQL Server or the machine running the upgrade loses its network connection.
  • The database in which you were working has run out of log or data space.
  • You are not allowed to perform an update to a table.
  • The database is corrupted.
  • The database is not available (still in recovery). It may be unavailable if the upgrade program begins to work before SQL Server finishes performing recovery after start-up.
  • Unable to restart the server.
  • Can identify the issue from the upgrade log files and resolve the issues and rerun the upgrade advisor.

5. Have you ever come across with the problem “.MSP or .MSI files are missing” while installing or patching a SQL Server? What are these files?

Yes! Starting from SQL Server 2005 till the recent installation of SQL Server 2014 I have seen this issue lot many times while installing SQL Server and applying service packs.

.MSI and .MSP:

When a product is installed by using Windows Installer, a stripped version of the original .msi file is stored in the Windows Installer cache. Every update to the product such as a hotfix, a cumulative update, or a service pack setup, also stores the relevant .msp or .msi file in the Windows Installer cache.

Any future update to the product such as a hotfix, a cumulative update, or a service pack setup relies on the information in the files that are stored in the Windows Installer cache. Without this information, the new update cannot perform the required transformations.

Default Folder for the Windows Installer Cache:

%windir%\installer

6. What are the most common reasons that causes “.MSP/.MSI file missing” issues and how to resolve these issues?

These are the most common reasons that cause Windows Installer cache files missing:

  • Windows Installer Cache files are deleted/removed accidentally
  • Users don’t have proper access to the Windows Installer Cache folder
  • Firewall or Antivirus is blocking the access
  • Downloaded SQL Server ISO is corrupted
  • Installing SQL Server from CD/DVD

If it is a permissions issue the problem can be resolved very easily but if the files are deleted in any case then the resolution will be bit difficult.

Repair: Try to run SQL Server installation with REPAIR from command Prompt

Copy Missing Files using VB Script: As Microsoft suggested there is a VB script “FindSQLInstalls.vbs script” this we can find out from Microsoft support forums. This can identify the missing files and copy them from the original media.

Copy Missing Files Manually: Manual copy is easy for missing .MSI files. But for .MSP there is a different procedure.

Copying .MSP:

Collect the complete details about missing file from the error message, from the setup log file. Copy the same file from the original media to the Windows Installer cache folder.

Copying .MSI:

Collect the complete details about the missing file from the error message, from the setup log file. We should be able to find out details about Original MSP and Cached MSP details. For .MSP files file name will be changed when copied from original media to Cache folder. We need to do the same thing identify the original file name from original media and copy the file into Windows Installer Cache folder and rename the file as you see in setup logs.

Windows Cache Folder is: %windir%\installer

Original Media file location:

<Drive>:\SQL2014_ENU–x64\x64\setup\sql_engine_core_inst_msi\

7. What is the most critical issue you faced while installing SQL Server 2012? You can tell me the recent one.

We had a bad experience with SQL Server 2012 SP1.

Problem:

When SP1 released our team has applied on 5 staging machines. There was no problem with the installation, it went on well but soon we come to know that there were performance issues.

RCA:

When I did a RCA found it was due to high CPU utilization and we have seen there were a lot of warnings in event log on “SQL_Tools_Ans.” Further investigated and found 90% CPU utilization is from msiexec.exe and it was running continuously thereof problems started. We quickly gone through MSDN sites and found there were lot of other people facing the similar kind of issues.

Solution:

Immediately we have uninstalled SQL Server 2012 SP1 from all staging machines. Next minute CPU utilization came down and we didn’t see msiexec.exe on running processes list. This issue got fixed in SQL Server 2012 SP1 CU2

8. Can you describe any strange issue that you faced while installing SQL Server?

Problem:

When we were installing SQL Server 2008 R2, setup continues to install setup support files, the windows suddenly disappeared and next the next window never shows up. Same issue repeated when we tried to install it again.

RCA:

Found an error message from temp folder log file “SQLSetup_1.log” and the error message is “Failed to Launch Process” and “Failed to launch local setup100.exe.

Resolution:

We followed a difficult process to resolve this issue, but it worked out. Make sure you have taken the backup for Windows Registry.

Save this file as .reg

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\100
\Bootstrap]
“BootstrapDir”=”C:\\Program Files\\Microsoft SQL Server\\100\\Setup
Bootstrap\\”
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL
Server\100
\Bootstrap\Setup]
“PatchLevel” = “10.50.1600.00”

Run the saved file

Copy files from source to destination:

Source – SQL Server
Setup File/Media
Destination – Server Local Disk

X64/X86 file C:\Program Files\Microsoft
SQL Server\100\Setup
Bootstrap\SQLServer2008R2
Setup.exe C:\Program Files\Microsoft
SQL Server\100\Setup
Bootstrap\SQLServer2008R2
Resources folder C:\Program Files\Microsoft
SQL Server\100\Setup
Bootstrap\SQLServer2008R2

Note: One of my colleagues had the same issue on another instance while installing SQL Server 2008 R2 and the problem got resolved after installing PowerShell 2.0. As per MSDN blogs the same problem exists in 2008, 2008 R2, 2012, 2014.

9. Can you tell me last issue you faced while installing SQL Server 2012 or 2014?

Problem:

SQL Server 2012 installation failed with an error message “The identities of the manifests are identical, but their contents are different.”

RCA:

This was a known issue with Visual C++ 2005 runtime library setup. A security update was missing for Visual C++ 2005. On SQL Server Setup there is an .MSI file which merged with merge modules of Visual C++ 2005 runtime library.

Resolution:

Download and install Microsoft Visual C++ 2005 Service Pack 1. Or Slipstream SQL Server 2012 with CU1 as the issue got fixed in CU1

10. Have you ever participated in SQL Server Upgrades? If yes can you quickly describe an issue that you faced in SQL Server upgrades?

Problem:

We faced an issue while we were upgrading from SQL Server 2005 to SQL Server 2008 R2. SQL Server 2008 R2 upgrade setup wizard has been failing at upgrade rules page due to “Security Group SID (Security Identifier)” rule failure.

RCA:

I have verified upgrade log files from setup bootstrap folder. When checked it’s clearly showing that it is reading the account details from windows registry and the SID’s which are in registry are not matching/mapping with the account names. These accounts are used for SQL Services.

Resolution:

  • From the log file we got the SID and the registry key details.
  • We have verified the registry and found the same values as in error log file.
  • We have verified the actual/correct SID for these failed accounts from command prompt just types WHOAMI/GROUPS.
  • When compare values are not matching with the registry.
  • Performed a backup for windows registry and updated SID with the original values for the failed accounts.
  • Then restarted the windows machine and re-run the migration wizard and this time it executed without any issue.

11. While installing SQL Server 2008 I am hitting the error: “The setting ‘PCUSOURCE’ specified is not recognized.” How do you resolve it?

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.

12. You are trying to install a service pack on your SQL Server 2008 R2/2012; service pack is ended with a failure. When you try to check the log file there is no log file/folder generated with that date on location: C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\log\. But all existing log files are there in that location. Have you ever faced it earlier?

Yes! We faced it while applying SP1 for SQL Server 2012 instance. The installation got failed but we couldn’t be able to find any log file/folder generated on that date.

RCA:

When we verified there are two files created Summary.txt and Details.txt on D drive.

D:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\log\
Summary.txt: Didn’t give much information on failure
Detail.txt: Found there are error messages “Could not find a part of the
path ‘C:\Program Files\Microsoft SQL Server\110\Setup
Bootstrap\Log\20150519_003216\MSSQLSERVER’.

The path is clearly existing but it’s not able to locate it. Windows setup environment variable for “Program Files” location. This we can know by using run button and type %programfiles%. When we tried it it’s locating D drive program files.

Resolution:

Change the environment variable location for Program Files to C drive.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion

Updated below values in registry

ProgramFilesDir –> C:\Program Files
ProgramFilesDir (x86) –> C:\Program Files (x86)

After the change, I just logged out and logged in. I was able to install SQL Server 2012 SP1 without any issue

13. We have upgraded databases from SQL Server 2005 to SQL Server 2014 and now the upgrade hits the production. Unfortunately, a part of application is not supporting SQL Server 2014. Do we need to Rollback entire process to SQL 2005? Is that the only solution? If it is the only way! Since the databases at production transactions are being run and the data has been updated. How do you handle this?

Since you said a Part of Application is not working properly with SQL Server 2014. This means it should be caught in test environment which should be done before the production environment. However, after the upgraded SQL Server 2014 instance goes into production and starts capturing new data, there will come a point in time when enough new data has been captured that a rollback is no longer realistic.

In-place upgrade, if you encounter problems after the system is in production, adjusting or “patches” to the new application would be a better option than attempting a rollback.

Side-by-Side upgrade, you could employ SSIS to transfer new data from the SQL Server 2014 instance to the legacy SQL Server 2005 to bring it current. Depending on the complexity of the data, this could be a difficult process.

SQL Server Components and Capacity Specifications

14. 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 Edition
  • SQL Server 2012 Web Edition
  • SQL Server 2012 Developer Edition

15. What is the change you most liked in SQL Server 2016 installation procedure?

Till SQL Server 2014 we did not have the capability to create multiple TEMPDB files based on the number of cores during installation, it used to be taken care of by DBA after installing SQL Server. In SQL Server 2016 the install wizard includes TEMPDB configuration feature on the Database Engine Configuration page. As per best practices, TEMPDB data files can be 8 or the number of cores whichever is less, and this configuration can be done during the installation itself.

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
529 Views