Tips & Tricks on ‘cloning’ Azure SQL virtual machines from captured images
First published on MSDN on Jul 06, 2016 While we have documentation on how to create a VM from captured image under “How to capture a Windows virtual machine in the Resource Manager deployment model”, it doesn’t address some specifics on SQL Server.
Use Trace Flag 902 (TF902) to Bring Up Your SQL Service in Failed Post Upgrade Script
Summary: This article discusses using TF902 as a quick workaround to bring up your SQL Server service if it fails to be started due to post upgrade script failure after installing SQL Server patch. Further troubleshooting steps to address the post upgrade script failure have been discussed in later part of this article. Please be noted that it is not suggested to keep your SQL Server service in this state for long since your SQL Server is in an incomplete patch installation status and considered instable for your production environment. This is because part of your SQL DLL files, MSP files or related registry key record might have already been upgraded while a few other files or metadata are not upgraded. Why We Need To Run Post Upgrade Script After SQL Patch? ------------------------------------------------------------------ After you have finished most part of installing the SQL patch (the scenarioengine.exe process and a few other related SQL and Windows process finished the change made to MSP file, registry key, and DLL files), SQL Server service (sqlservr.exe) still needs to perform another step - to run a few post upgrade TSQL scripts - to successfully complete the change made by patch installation. This is because the patch installation process mainly makes change to the related MSP files, registry key, and DLL files. We still need to apply a few remaining changes made in DLL into SQL database level by running a few system TSQL scripts. Major part of these TSQL scripts are stored in the 'Install' folder for your SQL instance while another part of them are encrypted (i.e. C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Install). If the post upgrade script cannot be successfully finished , SQL Server service cannot be started successfully after the patch as the system databases' status are not ready to be started on service startup process. Symptom ====== After installing SQL Patch, your SQL Server service cannot be started. SQL Server service can still generate new ERROR LOG on starting up attempt. In the latest SQL ERROR LOG file, you found below error as 'Script level upgrade for database '**' failed' indicating that SQL Server failed to perform the script level upgrade for a specific system database and caused subsequent shut down(Below are sample testing error from my LAB when reproducing the same issue) 2021-10-14 11:27:02.98 spid7s Error: 912, Severity: 21, State: 2. 2021-10-14 11:27:02.98 spid7s Script level upgrade for database '%' failed because upgrade step '%' encountered error 3930, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion. 2021-10-14 11:27:02.98 spid7s Error: 3417, Severity: 21, State: 3. 2021-10-14 11:27:02.99 spid7s Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online. 2021-10-14 11:27:02.99 spid7s SQL Server shutdown has been initiated Quick Workaround To Bring Up SQL Service(Not Suggested to Keep it for Long) ===================================================== We can add trace flag 902 to SQL Server service startup parameter to skip the script upgrade process in order to bring up your SQL Server service. However, we suggest to resolve the failure for finishing the post upgrade script as we cannot leave SQL Server service in this state with TF902 for long. It is an incomplete upgrade state and considered unstable. This is because part of your DLL files might have already been upgraded while a few other files or metadata are not upgraded. Further Troubleshooting Steps for Post Upgrade Script Failure ========================================= The failure of post upgrade script can be caused by issue at database level in finishing the scripts or failure at patch installation level. The latter indicates that the post upgrade failure is actually a consequence of an earlier exception in patch installation phase rather than the root cause of the patch installation failure. To go further, we need to investigate the actual exception that causes the subsequent failure. Phase 1: Directly check the error and failed script steps for post upgrade script failure issue 1. As the post upgrade script will also be performed at the end of the patch installation process, you can check the latest SQL Setup Bootstrap log folder for ERRORLOG file details to see in which step and in which script do we encounter the error. One of the common causes can be an orphaned SQL login which cannot be dropped and recreated when running a few of these post upgrade scripts. 2. If we don't have the detailed ERRORLOG that records the script upgrade failure details, we can find a down time , remove trace flag 902 from SQL startup parameter and add trace flag 3601 to SQL Server startup parameter. Then we can try to start SQL Server service to reproduce the issue. Trace flag 3601 will dump script level upgrade details to the SQL Error Log. This is a trace flag used by Microsoft for internal troubleshooting purpose. Please use it under instruction and avoid using it in your PRODUCTION environment. Alternatively, we can configure X-event on service startup to check the errors and TSQL script details when we reproduce the issue. Please be noted that part of these scripts are encrypted. Phase 2: Check the Setup Bootstrap logs for more details of the exception on SQL Server patch installation process If the post upgrade script is actually a consequence of failure encountered during SQL patch installation process , then the issue is not at database level but patch installation phase. We need to investigate the exception encountered on patch installation process in order to address the issue. If this is the case, check the 'Detials.txt' and other logs on the Setup Bootstrap 'Log' folder for this issued patch installation and investigate further.(Default path: “C:\Program Files\Microsoft SQL Server\<YourSQLVersion>\Setup Bootstrap\Log\<YourIssuedPatchInstallationTime>”) In some simple scenarios, minor failure can be caused by file in use or lack of permission issue and can be quickly resolved by 'Repair' option in 'setup.exe' program to repair the corrupted patch installation. 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.Use Orca.exe Tool to Verify Corrupted MSI/MSP file in Failed SQL Patching
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.
Downloading and Installing SQL Server 2008 R2 Master Data Services (MDS) Cumulative Updates
First published on MSDN on Aug 25, 2010 (This post was contributed by Reagan Templin, Lead Technical Writer for the MDS Team)Recently, we released the first cumulative update (CU) for Master Data Services (MDS).Installing and Configuring Master Data Services 2008 R2 November CTP
First published on MSDN on Dec 15, 2009 (this post was contributed by Matt Anderson, Senior Software Engineer on the MDS team) Master Data Services (MDS) is now available as part of the November CTP of Microsoft® SQL Server® 2008 R2! An overview of MDS can be found on the product home page and documentation is available in SQL Server Books Online.Installation of SQL Server 2017 failing with 'VS Shell installation has failed with exit code 1638'
First published on MSDN on Mar 26, 2018 Dear all,Depending on which products were installed on the server beforehand, a SQL Server 2017 setup may fail with the following error :TITLE: Microsoft SQL Server 2017 Setup------------------------------The following error has occurred:VS Shell installation has failed with exit code 1638.
