SSISDB considerations while upgrading/patching SQL Server

by

Issue Description:

While patching SQL server sometimes SQL services fails to come online post patching. It may be due to various reasons like any corruption in system databases, script level upgrade failure for system databases etc. In this post, we will talk about issues that may arise if you have SSISDB (SQL server integration services catalog database) created on the SQL instance.

Below are some example of SQL server error log messages. Please note that you will have to open SQL error logs using file explorer as SQL services will not come up before fixing issue.

Errors encountered

  1. Error: 15151, Severity: 16, State: 1.
    Cannot find the user ‘##MS_SSISServerCleanupJobUser##’, because it does not exist or you do not have permission.
    Error: 912, Severity: 21, State: 2.
    Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 15151, state 1, severity 16. 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.
  2. Database SSISDB is enabled for Database Mirroring, but has not yet synchronized with its partner. Try the operation again later.
    Error: 912, Severity: 21, State: 2.
    Script level upgrade for database ‘master’ failed because upgrade step ‘SSIS_hotfix_install.sql’ encountered error 15151, state 1, severity 16. 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.

Cause: From above error messages, SQL services are not coming up as script level upgrade for ‘master’ database failed because it could not run ‘SSIS_hotfix_install.sql’ script successfully. SQL server executes scripts located at “C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Install” folder after applying the patch. If any script execution fails, SQL server services will not come up stating script level upgrade failure for ‘master’ database.

Prechecks to avoid failure of ‘SSIS_hotfix_install.sql’

  1. Check if SSISDB is configured in database mirroring and acting as mirror. If yes, kindly failover SSISDB and then start applying patch.
  2. Check if SSISDB is configured in AlwaysOn. If yes, kindly remove the database from AlwaysOn and then start applying patch.
  3. Check if SSISDB is having a user ‘##MS_SSISServerCleanupJobUser##’ and it is not orphan. If missing, kindly create user. If orphan, please map it with login ‘##MS_SSISServerCleanupJobLogin##’
  4. Kindly check and ensure that SSISDB is in ONLINE state and in READ-WRITE mode else SSIS hotfix will not be applied leading to script level upgrade failure for master database.

Workaround:  If we miss to perform above prechecks and encounter script level failure for master database due to ‘SSIS_hotfix_install.sql’ encountered error, please follow below steps to fix issue

  • If SSISDB is configured in mirroring and acting as mirror
    • Start MSSQL server instance by applying trace 902.
    • Connect to SQL Server in SSMS.
    • Failover SSISDB to mirroring partner
    • Restart SQL services without trace 902. This time SQL services should come up clean.
    • Failback SSISDB to mirroring partner if desired.
  • If SSISDB is configured in AlwaysOn
    • Start MSSQL server instance by applying trace 902.
    • Connect to SQL Server in SSMS.
    • Remove database from AlwaysOn availability group.
    • Execute script ‘SSIS_hotfix_install.sql’ located in “C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Install” folder.
    • Restart SQL services without trace 902. This time SQL services should come up clean.
    • Re-add database SSISDB in availability group.

  • If user ‘##MS_SSISServerCleanupJobUser##’is missing in SSISDB
    • Start MSSQL server instance by applying trace 902.
    • Connect to SQL Server in SSMS.
    • Create user using below script
      USE [SSISDB]
      GO
      CREATE USER [##MS_SSISServerCleanupJobUser##] FOR LOGIN [##MS_SSISServerCleanupJobLogin##] WITH DEFAULT_SCHEMA=[dbo]
      GO
    • Restart SQL services without trace 902 and now SQL services should come up clean.

Apart from above if any database has been created with name “SSISDB” which is not a SSIS catalog database, we may experience script level upgrade failure for master database as SQL will try to apply hotfix on existing SSISDB which may encounter errors. Hence suggestion is not to use name “SSISDB”, leaving it for SSIS catalog.

Hope this information will help you!

Happy learning!!

Post Views: 339