Sqlserver关于SSISDB的灾备问题

数据库实例升级或安装补丁时可能会运行SSIS_hotfix_install.sql，Sqlserver 2016版本的默认实例名的情况下，SSIS_hotfix_install.sql此文件存在操作系统的C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Install目录下，此文件主要对SSISDB做update ,drop procdure,create procdure 语句，这些语句只能对着正常online的SSISDB跑，所以SSISDB不适用于restoring和readonly状态，也就是说SSISDB不适用于AG、Mirror、Logshipping等模式的从节点状态，因为从节点升级或安装补丁时也会自动运行SSIS_hotfix_install而非同步来自主节点运行SSIS_hotfix_install后的数据；而且SSISDB在升级或安装补丁的过程在单用户模式下运行。 但是AG模式的数据库必须是多用户数据库，所以SSISDB不适用于AG模式的主节点状态。

如果实例中有实例中有SSISDB，因为数据库实例升级或安装补丁导致SSSIDB失败进而导致Master库更新失败，最终导致实例宕机的的处理方法如下
1、在sqlserver服务的启动参数中加上-T902，启动sqlserver 实例
2、从AG、Mirror中移除SSISDB
3、在sqlserver服务的启动参数中取消-T902，启动sqlserver 实例

结论：SSIDB不适合做灾备，SSISDB作为AG主从节点的数据库在都会在数据库实例升级或安装补丁遇到问题导致实例宕机，SSISDB作为Mirror、Logshipping模式的从节点数据库也会在数据库实例升级或安装补丁遇到问题导致实例宕机。如果实在要对SSIDB做灾备，建议做使用Logshipping模式，不会影响Logshipping模式的主节点，而且SSIDB一般是小库，Logshipping从节点出问题的话，在从节点使用主节点的备份也可以很快恢复

SSISDB在AG中时，AG主从节点在升级或打补丁的情况下都可能遇到的报错
参考官方文档https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/install/windows/sql-server-upgrade-failed-error-945和https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver16#Upgrade

Error:
Database 'master' is upgrading script 'SSIS_hotfix_install.sql' from level 201331031 to level 201331592.
Error: 945, Severity: 14, State: 2.
Database 'SSISDB' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Error: 912, Severity: 21, State: 2.
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.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
SQL Server shutdown has been initiated

SSISDB在Mirror中时，Mirror从节点在升级或打补丁的情况下可能遇到的报错

Error:
2018-03-31 11:44:54.93 spid5s Database 'master' is upgrading script 'SSIS_hotfix_install.sql' from level 184552836 to level 184555396.
2018-03-31 11:44:54.98 spid5s Error: 956, Severity: 14, State: 1.
2018-03-31 11:44:54.98 spid5s Database SSISDB is enabled for Database Mirroring, but has not yet synchronized with its partner. Try the operation again later.
2018-03-31 11:44:54.99 spid5s The failed batch of t-sql statements:

SSISDB在Log Shipping中时，Log Shipping从节点在升级或打补丁的情况下可能遇到的报错

Error:
2023-05-12 07:52:07.48 spid7s      Error: 927, Severity: 14, State: 2.
2023-05-12 07:52:07.48 spid7s      Database 'SSISDB' cannot be opened. It is in the middle of a restore.
2023-05-12 07:52:07.48 spid7s      Error: 912, Severity: 21, State: 2.
2023-05-12 07:52:07.48 spid7s      Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 927, 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.
2023-05-12 07:52:07.48 spid7s      Error: 3417, Severity: 21, State: 3.
2023-05-12 07:52:07.48 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.