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

Issue

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 this error message at the end of the installation:

The following error has occurred:

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

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

Troubleshooting

Let me explain the steps I took to troubleshoot the issue. 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 in found 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 was I the removed SSISDB catalog (after taking backup) and then repaired the SQL Instance which fixed everything. After this, we manually restored the SSISDB catalog.

In a nutshell, if you are performing in-place upgrade for SQL Server instances which has SSISDB Catalog, I would recommend to remove SSISDB Catalog first and then upgrade the instance. After the upgrade, you can restore the SSISDB catalog database back to 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 a 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 performing an in-place from SQL Server 2014 to SQL Server 2016/2017.

2. As a work around backup SSISDB database and remove it and then perform the upgrade it should go smooth from that point onwards.

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

4. At this point, the SSISDB Catalog will not be operational and throw all kinds of errors at you which 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 Catalog also fails and the error again is bizarre with no related info available online.

6. The way I fixed it was by exporting the packages and then removing the old SSISDB Catalog. Recreate new Catalog and deploy the packages from the .ISPAC file.

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

8. Another thing (not related to this issue) to keep in mind is that SSRS is 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.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s