SQL 2012 SP1 CU6 issue with SSIS DB in Availability Group

Recently we were installing SQL Server 2012 SP1 CU6 to fix the slow performance when you run the SSIS Server Maintenance Job to remove old data in SQL Server 2012, you can read about the issue here.  We ran into a serious issue where the installation failed and couldn’t start SQL Server services.

Let me first describe the server, it is a named instance of SQL Server 2012 SP1 Engine and SSIS with AlwaysOn Availability Group, primary instance in main data center and secondary replica in DR site. The AG comprises all databases and SSISDB.

SQL 2012 SP1 CU6 installation failed with the following error:

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.

The issue was related to configuring SSIS db  “SSISDB” with AlwaysON Availability Group. So what happened is that SQL Server service packs usually run in single-user mode, while an availability database must be a multi-user database. Therefore, during installing the CU6, all availability databases including SSISDB will be taken OFFLINE which become inaccessible and thus fail the patch.

To workaround this issue, here are the steps:

  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

That’s all and we are back in business. Click here from more information on how you can make use of SSIS and AlwaysOn Availability Groups.

9 thoughts on “SQL 2012 SP1 CU6 issue with SSIS DB in Availability Group

  1. Hello Ayad, we just ran into this issue while applying CU2 for 2014, thank you for the details on how to fix it.
    Reinaldo

  2. Thanks for this post, very useful. The same issue happens when upgrading from SQL 2012 to SQL 2014 if SSISDB is participating in an Availability Group.

  3. Thanks for posting this! I just learned (the hard way) that this issue still exists for SQL Server 2012 SP2! You’d think MS would have fix it…

  4. Hello Ayad,

    I’ve tried your fix but it fails at step #2 with the error “The AlwaysOn feature must be enabled for the server instance ‘InstanceName’ before you can create an availability group on this instance.” However, when I go to Configuration Manager to enable this feature it is not available because we are not even setup for a failover cluster.

    How can I fix this issue if we do not even have a failover cluster setup?

    Thanks,
    skeletank

    • Hi Skeletank,

      Sorry for my late reply. The issue described here is related to AlwaysOn AG and SSIS db. It looks like you don’t have AlwaysOn configured which requires Windows Cluster Services. Can you share the error you are getting?

      Thanks,
      Ayad

  5. Pingback: AlwaysOn Availability Group error after Windows Update in environment without a failover cluster | CL-UAT

  6. Pingback: AlwaysOn Availability Group error after Windows Update in environment without a failover cluster | Question and Answer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s