none
Upgrade from SQL Server 2016 to SQL Server 2017 Fails on script 'ISServer_upgrade.SQL - SSISDB

    Question

  • Last week I performed an upgrade in place of an SQL Server 2016 Standard Edition to SQL Server 2017 Standard Edition. The upgrade failed and the Database Engine would not start. Part of the SQL Error Log Shows the following: 

    Database 'master' is upgrading script 'ISServer_upgrade.sql' from level 0 to level 500.

    2018-03-15 14:06:20.43 spid6s      ---------------------------------------------

    2018-03-15 14:06:20.43 spid6s      Starting execution of ISServer_upgrade.SQL

    2018-03-15 14:06:20.43 spid6s      ---------------------------------------------

    2018-03-15 14:06:20.43 spid6s      

    2018-03-15 14:06:20.43 spid6s      Taking SSISDB to single user mode

    2018-03-15 14:06:20.44 spid6s      Setting database option SINGLE_USER to ON for database 'SSISDB'.

    2018-03-15 14:06:20.80 spid6s      Error: 1712, Severity: 16, State: 1.

    2018-03-15 14:06:20.80 spid6s      Online index operations can only be performed in Enterprise edition of SQL Server.

    2018-03-15 14:06:20.80 spid6s      Error: 917, Severity: 15, State: 1.

    2018-03-15 14:06:20.80 spid6s      An upgrade script batch failed to execute for database 'master' due to compilation error. Check the previous error message for the line which caused compilation to fail.

    2018-03-15 14:06:20.80 spid6s      Error: 912, Severity: 21, State: 2.

    2018-03-15 14:06:20.80 spid6s      Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. 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.

    2018-03-15 14:06:20.81 spid6s      Error: 3417, Severity: 21, State: 3.

    2018-03-15 14:06:20.81 spid6s      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.

    2018-03-15 14:06:20.81 spid6s      SQL Server shutdown has been initiated

    Added Trace Flag 3601 to startup and looked at SQL Error Log again to see what was being run in the script. 'ISServer_upgrade.SQL' was adding tables and indexes to database SSISDB. 

    The last script line executed is:

    CREATE NONCLUSTERED INDEX [index_tasks_ReadyForDispatchTime] 
    ON [internal].[tasks] 
    (
    [ReadyForDispatchTime] ASC
    )
    INCLUDE ([CreatedTime], [CreateWorkerAgentId], [ExecutedCount], [ExpiredTime], [InputData], [IsCritical], [JobId], [LastUpdatedTime], [MaxExecutedCount], [Priority], [Status], [TaskType]) 
    WITH (ONLINE = ON)

    Followed by the Errors:

    2018-03-16 14:54:26.07 spid6s      Error: 1712, Severity: 16, State: 1.
    2018-03-16 14:54:26.07 spid6s      Online index operations can only be performed in Enterprise edition of SQL Server.
    2018-03-16 14:54:26.07 spid6s      Error: 917, Severity: 15, State: 1.
    2018-03-16 14:54:26.07 spid6s      An upgrade script batch failed to execute for database 'master' due to compilation error. Check the previous error message for the line which caused compilation to fail.
    2018-03-16 14:54:26.07 spid6s      Error: 912, Severity: 21, State: 2.
    2018-03-16 14:54:26.07 spid6s      Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. 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.

    Problem should be easy to correct. Open 'ISServer_upgrade.SQL' and remove the syntax WITH (ONLINE = ON) in the create index script since this is a Standard Edition Upgrade. But I cannot locate the script. I have checked <Drive>:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Install, but it is not there. It does not appear to be on the install media either.

    Can someone point me to this scrip? Thanks,

     


    Monday, March 19, 2018 1:16 PM

Answers

  • Hi John,
    I guess you have already opened a support case with SQL Team and I got a chance to review that.
    The failing script is part of the DLL and that's why you are not able to see ISServer_upgrade.SQL file in the OS.

    Looks like RTM installation has this issue where upgrade script was always building index ONLINE and hence failing.

    The new (fixed) code takes care of checking edition and then creating index ONLINE only for ENT and DEV edition.

    Here is the plan for you to fix the issue:

    ++ After upgrade start SQL with trace flag 902 (you have done this already)
    ++ Apply RTM CU5. It should get applied
    ++ Remove trace flag and let the upgrade script run.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by JCRsr Wednesday, April 4, 2018 11:39 AM
    Wednesday, April 4, 2018 11:04 AM
    Moderator

All replies

  • Hi JCRsr,

    What's version of SQL Server 2016 did you use? SP1 or RTM? 

    >>Open 'ISServer_upgrade.SQL' and remove the syntax WITH (ONLINE = ON) in the create index script since this is a Standard Edition Upgrade.

    It looks like that this is an invisible script which only uses inside SQL Server. I suggest you opening a case in https://feedback.azure.com/forums/908035-sql-server

    As a workaround, we can also use the side-by-side upgration, we can first use /T902 to start this instance and then migrate to the SQL Server 2017 instance.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 20, 2018 8:29 AM
    Moderator
  • Teige,

    I am upgrading SQL 2016 SP1 to SQL 2017. This is not an Azure instance.

    I have used the 902 Flag to start the instance and all the upgrades are complete except the update to SSISDB. The script 'ISServer_upgrade.SQL' updates this database. I cannot locate this script on my server or the install media to make changes to it. If the script is a hidden file that is not available to me, my only workaround appears to be to uninstall the instance and re-install it. This is not a good solution.

    Thanks,

    Tuesday, March 20, 2018 11:30 AM
  • Hi John,
    I guess you have already opened a support case with SQL Team and I got a chance to review that.
    The failing script is part of the DLL and that's why you are not able to see ISServer_upgrade.SQL file in the OS.

    Looks like RTM installation has this issue where upgrade script was always building index ONLINE and hence failing.

    The new (fixed) code takes care of checking edition and then creating index ONLINE only for ENT and DEV edition.

    Here is the plan for you to fix the issue:

    ++ After upgrade start SQL with trace flag 902 (you have done this already)
    ++ Apply RTM CU5. It should get applied
    ++ Remove trace flag and let the upgrade script run.


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    • Marked as answer by JCRsr Wednesday, April 4, 2018 11:39 AM
    Wednesday, April 4, 2018 11:04 AM
    Moderator
  • Balmukund,

    I followed the steps in your reply and the SQL instance is online after upgrading.

    To restate your answer.

    1). SQL Instance is running with /902 flag.

    2). Patch instance with SQL 2017 Cumulative Update 5 (14.0.3023.8)

    3). When patch is complete, remove /902 flag and restart instance.

    4). Instance starts normally and completes 'master', 'msdb', & SSISDB updates.

    Thanks,

    John R.

    Wednesday, April 4, 2018 11:46 AM
  • Thanks for the confirmation, John.

     


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, April 4, 2018 2:59 PM
    Moderator