debugsqlMinor
AlwaysOn Availability Group error after Windows Update in environment without a failover cluster
Viewed 0 times
afterwithouterrorupdategroupenvironmentfailoveravailabilitywindowsalwayson
Problem
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:
-
Start SQL Server service with Trace Flag 902:
Net Start MSSQL$InstanceName /T902
-
Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
-
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
-
Stop SQL Server services:
Net Stop MSSQL$InstanceName
-
Start SQL server service from SQL Server configuration manager
-
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 cluste
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:
-
Start SQL Server service with Trace Flag 902:
Net Start MSSQL$InstanceName /T902
-
Open SQL Server Management Studio, go to Availability Group and remove SSISDB from the availability databases
-
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
-
Stop SQL Server services:
Net Stop MSSQL$InstanceName
-
Start SQL server service from SQL Server configuration manager
-
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 cluste
Solution
Community Wiki answer to document the solution
KenWilson:
Skip all the steps pertaining to AlwaysOn and run the
KenWilson:
Skip all the steps pertaining to AlwaysOn and run the
SSIS_hotfix_install.sql script.Context
StackExchange Database Administrators Q#83611, answer score: 2
Revisions (0)
No revisions yet.