SQL Server In-Place Upgrade Failed: Wait on the Database Engine Recovery Handle Failed

A few weeks ago I was performing an in-place upgrade from SQL Server 2014 to SQL Server 2017. I followed each and every pre-check before performing an in-place upgrade, but got an  error message at the end of the installation.

The error in question:

The following error has occurred: 

Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.

I’ll explain the steps I took to troubleshoot the issue, but first let me explain a bit more about the environment. I had a few in-place upgrades from SQL 2014 to SQL 2017 and all of them were completed successfully without any issues, but this one failed. The only thing different on this SQL Server instance was that it hosted the SSISDB catalog database.

Troubleshooting

The error message didn’t say much, so I tried to launch SQL Server in single-user mode using -m flag and below are actual error messages I found in the Error Log:

yyyy-MM-dd HH:mm:ss.SS spid7s      Database 'master' is upgrading script 'ISServer_upgrade.sql' from level 0 to level 500.
yyyy-MM-dd HH:mm:ss.SS spid7s      ---------------------------------------------
yyyy-MM-dd HH:mm:ss.SS spid7s      Starting execution of ISServer_upgrade.SQL
yyyy-MM-dd HH:mm:ss.SS spid7s      ---------------------------------------------
yyyy-MM-dd HH:mm:ss.SS spid7s
yyyy-MM-dd HH:mm:ss.SS spid7s      Taking SSISDB to single user mode
yyyy-MM-dd HH:mm:ss.SS spid7s      Setting database option SINGLE_USER to ON for database 'SSISDB'.
yyyy-MM-dd HH:mm:ss.SS spid7s      Error: 1712, Severity: 16, State: 1.
yyyy-MM-dd HH:mm:ss.SS spid7s      Online index operations can only be performed in Enterprise edition of SQL Server.
yyyy-MM-dd HH:mm:ss.SS spid7s      Error: 917, Severity: 15, State: 1.
yyyy-MM-dd HH:mm:ss.SS spid7s      An upgrade script batch failed to execute for database 'master' due to compilation error. Check the previous error message for the line which caused compilation to fail.
yyyy-MM-dd HH:mm:ss.SS spid7s      Error: 912, Severity: 21, State: 2.
yyyy-MM-dd HH:mm:ss.SS spid7s      Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. 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.
yyyy-MM-dd HH:mm:ss.SS spid7s      Error: 3417, Severity: 21, State: 3.
yyyy-MM-dd HH:mm:ss.SS 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.
yyyy-MM-dd HH:mm:ss.SS spid7s      SQL Server shutdown has been initiated

After doing some research and looking at the error messages, I figured out that it seems like a common issue with SQL Server in-place upgrades with SSISDB database hosted.

Resolution

To fix the issue I removed the SSISDB catalog (after taking a backup) and then repaired the SQL Instance which fixed everything. After this, we manually restored the SSISDB catalog.

In a nutshell, if you’re performing an in-place upgrade for SQL Server instances that have an SSISDB catalog, I recommend it first and then upgrading the instance. After the upgrade, you can restore the SSISDB catalog database to the SQL instance.

In-place upgrade guidelines with SSISDB catalog hosted

If you are performing an in-place upgrade to SQL Server 2016/2017 in the near future some important tips to remember:

1. If you have an SSISDB catalog on the SQL Instance that requires an in-place upgrade it will fail and you won’t be able to start database engine service after the upgrade. This should apply to perform an in-place from SQL Server 2014 to SQL Server 2016/2017.

2. As a workaround, backup the SSISDB database and remove it and then perform the upgrade; things should go smoothly from that point onwards.

3. Once the upgrade completes successfully you can restore the SSISDB database and catalog.

4. At this point, the SSISDB Catalog will not be operational and throw all kinds of errors at you that won’t make any sense. The problem is that SQL Server 2016 and SQL Server 2017 SSISDB Catalog has some additional tables and views that were not there in SQL 2014 (or earlier supported versions)

5. The database upgrade from the catalog also fails and the error again is bizarre with no related info available online.

6. The fix was completed by exporting the packages and then removing the old SSISDB Catalog. Recreate the new Catalog and deploy the packages from the.ISPAC file

7. The problem you’ll face is the environment variables because they’ll be lost. I used a select insert into from the old table (catalog.environment_variables) for adding those values directly to the new SSISDB database tables and it worked like a charm. Ideally, you’d want to keep a backup of the catalog.environment_variables table and export .ISPAC files before removing the old SSISDB and performing the upgrade. After the upgrade finishes, restore and fix the SSISDB catalog database.

8. Another thing (not related to this issue) to keep in mind is that SSRS is a separate service now and upon upgrade, it will be removed. You will have to install it separately and migrate the reports from old to the new version of SSRS.

I hope you found this post helpful. Feel free to drop any questions or share your thoughts in the comments, and make sure to sign up for updates.