Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Error Applying SQL Server 2012 SP2 with Availability Groups


By:   |   Read Comments (3)   |   Related Tips: More > Upgrades and Migrations

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

It's time to apply SQL Server Service Pack 2 to your SQL 2012 instances. Normally the installation of SQL 2012 Service Pack 2 is pretty straightforward even for replicas involved in an Availability Group. But for your SQL Server 2012 instances that include Integration Services for your Availability Groups, you might encounter an error similar to:

The Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 945, state 2, severity 25. 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.
Solution

The Service Pack 2 update for SQL Server 2012 has a hotfix that needs to be applied to the SSISDB database. If the SSISDB database is involved in an Availability Group, it cannot apply the hotfix. To work around this, you need to remove the SSISDB from the Availability Group, apply the hotfix then add the SSISDB back into your Availability Group.

For this kind of error, you need to do the following:

  • Start the MSSQLSERVER instance with trace flag 902 from your cmd window as shown below.
start sql from cmd line with trace flag 902
  • In SQL Server Management Studio (SSMS), remove the SSISDB database from the Availability Group. Alternatively, you may execute the following statement. For this step, you will need CONTROL SERVER permission or at least ALTER AVAILABILITY GROUP permissions.

    ALTER AVAILABILITY GROUP [yourAGgroup] REMOVE DATABASE [databasename];

  • In Windows Explorer, go the location of the binary files for your SQL Server such as (C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Install) and open up the SSIS_HOTFIX_INSTALL.SQL file and execute in a SSMS query window.
  • Stop the MSSQL service from SQL Server Configuration Manager.
  • Start the MSSQL service from SQL Server Configuration Manager as you would normally do.
  • On the Primary server, perform a full backup and transaction log backup of the SSISDB database. Copy the backup files to the secondary server and restore with the NORECOVERY option.
  • From your primary server, add the SSISDB database back into your Availability Group. Alternatively, you execute the following statement:

    ALTER AVAILABILITY GROUP [yourAGgroup] ADD DATABASE [databasename];

After this, you should be able to check that the build number for SQL Server 2012 is something like 11.0.5058 within SSMS. Your Service Pack 2 patch is complete and you can get your coffee now.

Next Steps
  • Perform the necessary health check especially with your Integration Services and SSIS packages prior to any upgrades.
  • For more tips on Availability Group, click here.
  • For more tips on upgrades and migrations, click here.
  • Learn more about Availability Groups in this webcast.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Carla Abanes Carla Abanes works for a private bank in Singapore as a SQL Server DBA.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, June 19, 2017 - 10:24:51 PM - carla abanes Back To Top

Hi Saravanan,

 

Glad to be of help! Happy new work week to you! 

 


Sunday, June 18, 2017 - 2:58:47 PM - Saravanan N K Back To Top

Hi  Carla,

It was really a great work around, we faced similar issue. We were in the middle of Critical DB SP upgrade and we got the same error. And Billion thanks for sharing the work around, it worked great.

Thanks,

SNK.


Monday, June 15, 2015 - 5:01:43 PM - Pavan Srirangam Back To Top

I ran into this issue and got resolved long back even when I installed SP2 , Basically it is trying to execute a T-SQL script on the secondary SSIDB which is not accessible, making it accessible should let the SP2  to execute a script on that. there is other way of doing but this is what is recommended approach, thanks for sharing.


Learn more about SQL Server tools