DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
Multiple reasons can cause the failure of installing SQL Server patch. For example, corrupted registry key record for your SQL components , missing or corrupted MSI/MSP files for your SQL components, lack of permission for the required file or file in use during patch installation process, etc.
One of the common causes is corrupted SQL Server's Windows Installer database file (.MSI) or the Windows Installer patch file (.MSP) cached in your local server's Windows Installer Cache folder located on %windir%\installer. This type of corruption can happen frequently when you have anti-virus software, VM backup tool, or 3rd party security software that scans the file/folder.
As enlightened by a discussion with my colleague Richard Chen, SQL Server Escalation Engineer and a couple of cases investigation, this article discusses a simple way to narrow down the corruption and resolve this type of corrupted MSI/MSP file issue via Orca.exe tool that cannot be easily detected by FixMissingMSI tool nor fixed by 'Repair' option provided by 'setup.exe' program. (Similar error has also been discussed on this blog while a few other common issues for failed SQL patch/uninstallation are discussed already on this official document.)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Depends on the features you have installed for your SQL Server, a few shared components of SQL Database Engine and SQL feature-based components will be verified and patched during one sitting of patch installation process. If any of these required components' MSI/MSP file or registry key record is unreachable(for example, corrupted file, missing registry key removed by 3rd party anti-virus tool, etc.), the patch installation process will fail.
Please be noted that multiple issues can all play a role in your server that contribute to the patch failure. For example, your MSP files are corrupted while you also have a few registry key record missing. In this case, after resolving one issue, you will hit a different exception next time when you try to install. We need to resolve them one by one in order to successfully complete the patch installation process.
(NOTE: It is recommended to take backup of all related files before the change to ensure we don't have any potential data loss.)
Symptoms
=======
You have encountered error 'No valid sequence could be found for the set of updates' in 'Details.txt' log for your failed patch attempt.
You have tried to use FixMissingMSI tool to restore all your issued MSI/MSP files on the server .
You also tried 'Repair' option provided by setup.exe program to recover your SQL instance.
However, the same failure persists.
Troubleshooting
===========
Step 1: Check 'Details.txt' log in your SQL Server version's Setup Bootstrap 'Log' folder to narrow down the cause of the patch installation failure.
The 'Log' folder in the 'Setup Bootstrap' folder stores log and error details for all installation, uninstallation , and repair attempts for your SQL instance on the server.
Default Path: “C:\Program Files\Microsoft SQL Server\<Your SQL Version Number>\Setup Bootstrap\Log\<Your Issued Setup Timestamp>”
Step 2: If you see below error 'no valid sequence could be found' in 'Details.txt' log, search for all of this error's occurrence on this log and note down all the SQL components' name that encounters this exception(example see below).
This exception means the sequence number for this component's cached MSI/MSP file fails to be mapped during the process of installing subsequence patch. It can either be caused by corrupted MSI/MSP file or missing/corrupted record on the registry key that record the related component's MSI/MSP file information
2021-10-20 14:02:01 Slp: No valid sequence could be found for the set of updates.
2021-10-20 14:02:01 Slp: Watson bucket for Msi based failure has been created
2021-10-20 14:02:01 Slp: Error: Action "Install_sql_*****_Action" failed during execution. <---------in here will print the name of the SQL component that encounters this exception during the upgrade process.
Step 3: Go to each of the issued component's separate log (These logs are stored on the same folder) and check if we have hit error "PATCH SEQUENCER ERROR: failed to open **". If that's the case, check the actual cached MSI and MSP files' name used in the sequence patch process in the log as the example in my LAB showed in below.
These MSI and MSP files are used in the patch sequence verification process and some of their information suspected to be compromised to cause the patch sequence verification failure. We will need to verify the actual file's integrity as well as the mapping registry key record to narrow down the problem.
MSI : Opening existing patch 'C:\Windows\Installer\12a3b45.msp'. //*Testing example file name as each of these MSP files should be uniquely named in your server *//
MSI : Opening existing patch 'C:\Windows\Installer\23a4f56.msp'. //*Testing example file name as each of these MSP files should be uniquely named in your server *//
MSI : Note: 1: ******: 3: MsiPatchSequence
MSI : Patch we're running from ==> C:\Windows\Installer\c12d345.msp//*Testing example file name as each of these MSP files should be uniquely named in your server *//
MSI : SequencePatches starts. Product code: *****, Product version: *****, Upgrade code: ****, Product language ****
MSI : Note: 1 MsiPatchSequence
MSI : Note: 1: Target01ToUpgrade01 ****
MSI : PATCH SEQUENCER ERROR: failed to open Target01ToUpgrade01 transform in ****** patch! (****** )
Step 4 : Check the MSP file 's integrity on Windows Installer Cache folder, located in c:\windows\installer. Use Orca.exe tool ( a public tool shipped with Windows SDK - MSI tool in this link) to open each of these MSI/MSP files and check its integrity. A common issue you will see is missing the 'MsiPatchSequence' table for this file on Orca.exe's result. When this happens, the file's internal sequence information is corrupted and thus, will cause the error for failing the sequence patch process. Below is an example of a corrupted MSP file on an issued server and a healthy copy of the same MSP file extracted from its corresponding source SQL patch package.
1) Corrupted MSP file's Orca result : missing the 'MsiPatchSequence' table
2) A healthy copy for the same MSP file extracted from its source SQL patch package
Step 5: In order to fix this corrupted MSI/MSP file, you will need to get a healthy copy for the same file from its source SQL patch package. To do so, you will need to know its source SQL patch's KB number or version and its original name before cached to your local server. You can either directly get it from the Orca.exe's result or use the SQLInstall.vbs script's result to check as sometimes the file may be too corrupted to be queried by the Orca.exe tool. SQLInstall.vbs will script out all your existing SQL instances' MSI and MSP file information as long as they exist on your registry key record. Sometimes if the corresponding registry key record is missing or corrupted, you will also not get any valid information for this MSI/MSP file in the script result. Below is the steps for getting this script's result (you can also refer to this official document that discusses it)
1) Download the FindSQLInstalls.vbs from its raw page in this link
2) Select all of the contents on this page, copy, and paste it to a text file. Save the text file as FindSQLInstalls.vbs.
3) Open an elevated command prompt to the directory to which you saved the FindSQLInstalls.vbs file, and run the command: Cscript FindSQLInstalls.vbs %computername%_sql_install_details.txt.
4) Open the file output from Step 3, and search for your issued MSI/MSP file's actual cached name on your server. Then, you will get its registry key record result as scanned by this script.
Step 6: Once you get the information in step 5, download the SQL patch package and then, you will need to extract the package directly. Once you get the healthy copy of the file, rename it to its cached name as you find on the component's log and replace the corrupted one with this new copy in the Windows Cache folder. You can use below script to extract your SQL patch package.
1) Download the target SQL patch package from Microsoft Catalog
2) Open an elevated CMD window and change the directory to the path where you have stored the downloaded package:
pushd C:\temp\package
3) Run the same command for each “.exe” file in the package to extract installation files into the same destination path (the new folder created in step 2) by changing the file name and the destination path in below script. Each time you execute the command, a pop up window may ask you to specify the destination path. Please ensure it is the path specified.
SQLServer2016SP1-KB3182545-x64-ENU.exe /s /x /b"C:\temp\youroutputfoldername" /v"/qn"
Step 7: Perform the same check for all the SQL components that hit the exactly same exception and ensure the corrupted MSI/MSP files are replaced. Retry the patch installation. If you hit failure again, check the latest 'Details.txt' log first and narrow down the new exception for next step of resolution.