Sql-server – AlwaysOn Availability Group error after Windows Update in environment without a failover cluster

errorssql serversql-server-2012upgrade

Our SQL Server service was down this morning which brought down some of our websites. When I went to check the Windows Event Viewer I saw the following errors:

Script level upgrade for database 'master' failed because upgrade step
'SSIS_hotfix_install.sql' encountered error 942, state 4, severity 25

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.

The first thing that I did was to Google the errors. I eventually found a forum entry with the exact problem and a fix for it (also on a blog entry where I am seeking a solution). The problem has something to do with AlwaysOn Availability groups and the fix requires you to:

  1. Start SQL Server service with Trace Flag 902:

    Net Start MSSQL$InstanceName /T902

  2. Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases

  3. Open New Query, execute the SSIS_hotfix_install.sql script which can be found in Install folder under \Program Files\Microsoft SQL Server\MSSQL11.MSSQL$InstanceName\MSSQL

  4. Stop SQL Server services:

    Net Stop MSSQL$InstanceName

  5. Start SQL server service from SQL Server configuration manager

  6. Add SSISDB back to Availability Group

However, I could not get past step #2 because I got the following error when I tried to expand the "AlwaysOn High Availability" folder:

The "AlwaysOn feature must be enabled for the server instance
'InstanceName' before you can create an availability group on this
instance.

Then I followed the instructions to go to "SQL Server Configuration Manager" and the "AlwaysOn High Availability" tab to turn on the feature. This time the feature was grayed out and there was a message saying that the computer node is not in a failover cluster.

My question is:

How can I fix this issue if we don't even have a failover cluster setup that would use this feature?

I ran dbcc checkdb on master; the results were:

CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'.

AlwaysOn Availability Group is NOT on because I do not even have a failover cluster.

Best Answer

Community Wiki answer to document the solution

KenWilson:

Skip all the steps pertaining to AlwaysOn and run the SSIS_hotfix_install.sql script.

Related Question