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:
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.