Patching SQL Server with Integration Services (SSIS) installed together with AlwaysOn Availability Group

I recently ran into an issue while patching a SQL 2012 AlwaysOn Cluster.

The installation of service pack 3 would fail, with the following error message(s):

Database ‘SSISDB’ cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 945, state 2, severity 25.

Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it

This results in the SQL Database Engine Service failing to start. Fortunately the problem was easily resolved by uninstalling SP 3 in Control Panel. After a reboot the service started fine. But I still wanted to know what was causing this.

I then started my search on the internet and as it turns out, this is a known issue. It can be resolved by removing the SSISDB fromt the AlwaysOn Availability Group. this will result in the database getting the “Restoring” state on the secondary replica. This wil also block the installation of the Service Pack or CU.

I have dropped the DB on the secondary replica and th SP3 installation ran successfully. Performed a failover and installed SP 3 on the former primary replica and this worked fine.

After the installation, I’ve added the SSISDB back to the AlwaysOn Availability Group.

To resolve/prevent this follow these steps when installing Service Packs  or Cumulative updates for SQL with SSIS and AlwaysOn Availability Groups.

  • 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

 

Patching AlwaysOn Availability Groups explained:

In practice, the exact process will depend on factors such as the deployment topology of your availability groups and the commit mode of each replica. But in the simplest scenario, a rolling upgrade is a multi-stage process that in its simplest form involves the following steps:

Availability Group Upgrade in HADR Scenario
Remove automatic failover on all synchronous-commit replicas
Upgrade all remote secondary replica instances running asynchronous-commit secondary replicas
Upgrade the all local replica secondary instances that are not currently running the primary replica
Manually fail over the availability group to a local synchronous-commit secondary replica
Upgrade or update the local replica instance that formerly hosted the primary replica
Configure automatic failover partners as desired
If necessary, you can perform an extra manual failover to return the availability group to its original configuration.

Resources:

After update windows including SQL 2012 SP2 unable to start SQL service/instance

SQL 2012 SP1 CU6 issue with SSIS DB in Availability Group

Add Check if SSISDB is part of Availability Group prior to install of Service Pack or Cumulative Updates

Upgrading AlwaysOn Availability Group Replica Instances

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

*