Information about the SQL Server 2014 Service Pack 1 snafu

UPDATE 2015-05-15 – SP1 has been re-released as 12.0.4100 – see our Latest Builds of SQL Server 2014 post for more info and the download links.

This week has not been a great one for the SQL Server team. Shortly after releasing SQL Server 2014 Service Pack 1, they were forced to pull the download from the servers due to an untested script against the SSISDB database:

⚠ Warning from Microsoft: “We have chosen to remove SQL Server 2014 Service Pack 1 (SP1) from the Microsoft Download Center due to a significant installation issue discovered shortly after release.”

For those with an SSISDB database, this is a pretty serious issue – the update doesn’t technically damage anything, but it can leave your instance in an inconsistent state, possibly even unable to start.

If you’re not using SSISDB, you’ll have to wait for an update anyway, unless you have already downloaded the file. In which case, you can go ahead and install it on any instances without an SSISDB catalog (as long as you aren’t already relying on any fixes after Cumulative Update #5), since the script that causes the problem will only run when the SSISDB database is present. We have installed it on multiple instances without issue, but none of those instances use SSIS in any form. Please use caution.

If you are using SSISDB, and have already downloaded Service Pack 1 but have not installed it, please trash the file and wait for an update. If you have already installed Service Pack 1, you have probably encountered this error:

Script level upgrade for database 'master' failed because upgrade step 'SSIS_hotfix_install.sql' encountered error 3602, state 251, 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.

Don’t panic; you can recover easily, and your master database has not been corrupted. This was just an error resulting from a missing database reference in part of the code in the SSIS script – it tries to create an index in master on a table that only exists in the SSISDB database. From the updated Release Services Team blog post, with a few minor edits and corrections:

• In SQL Server Configuration Manager, click SQL Server Services.
• In the right pane, right-click SQL Server(InstanceName), and then click Properties.
• On the Startup Parameters tab, in the Specify a startup parameter box, type the parameter “-T902” (used to bypass upgrade scripts), and then click Add.
• Click OK.
• Restart the Database Engine.
• Connect to the SQL Server instance from Management Studio and run the following script to resolve the errors causing the script upgrade to fail. This will temporarily create a table in master and will ensure the problematic index still exists in the SSISDB database.

  USE [master];
  GO
  CREATE SCHEMA internal AUTHORIZATION dbo;
  GO 
  CREATE TABLE internal.object_parameters
  (
      project_id BIGINT,
      project_version_lsn BIGINT
  );
   
  -- to fix the index in SSISDB correctly
  USE SSISDB;
  GO
  IF EXISTS 
  (
    SELECT 1 FROM SSISDB.sys.indexes 
      WHERE name = N'IX_internal_object_parameters_inc'
  )
  BEGIN
    DROP INDEX IX_internal_object_parameters_inc 
      ON SSISDB.internal.object_parameters;
  END
   
  CREATE INDEX IX_internal_object_parameters_inc 
  ON SSISDB.internal.object_parameters(project_id,project_version_lsn);

   

   

• Remove -T902 from SQL Server Configuration manager.
• Restart the SQL Server instance. SQL Server 2014 Service Pack 1 can now be installed correctly, and it will erroneously create a meaningless index on the table you temporarily created in master.
• Now, validate that Service Pack 1 was installed successfully – the engine should start, and the first two lines from SELECT @@VERSION; should yield the following:
  Microsoft SQL Server 2014 – 12.0.4050.0 (X64)
      Feb 19 2015 18:37:03
• Once that is validated, remove the object_parameters table and its schema from master by running the following:

  USE [master];
  GO
  DROP TABLE [internal].[object_parameters];
  GO
  DROP SCHEMA internal;

   

You will want to apply SP1a (my guess as to what it will be called) when it is released, and hopefully this time it will be fully tested and not cause another embarrassing issue like this. I’m trying to think if this kind of thing has ever happened before – oh yes, it has; multiple times. It’s almost like they’re going out of their way to make you doubt the quality of service packs and cumulative updates.

Aaron Bertrand

Aaron (@AaronBertrand) is a Data Platform MVP with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com.

Aaron’s blog focuses on T-SQL bad habits and best practices, as well as coverage of updates and new features in Plan Explorer, SentryOne, and SQL Server.