By: Carla Abanes | Comments (3) | Related: > Upgrades and Migrations
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.
-
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.
About the author
Carla Abanes is a certified Microsoft SQL Server Database Administrator with extensive experience in data architecture and supporting mission critical databases in production environments.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
