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.
The 'Cloud' Workbook 