SQL 2005 SP2 - Make sure to read the readme!

Before you install SP2 you really should read the readme file. I have already seen one post on the MSDN forum who ran into the issue documented in section 5.3.2 of the readme.

You can find the ReadMe file here http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/ReadmeSQL2005SP2.htm#_analysis_services

And the What's new file here http://download.microsoft.com/download/2/B/5/2B5E5D37-9B17-423D-BC8F-B11ECD4195B4/WhatsNewSQL2005SP2.htm

There are a lot of good things in SP2, most of which are listed in the "What's new" document. If you read Mosha's blog you will probably be aware that there are some significant performance improvements in SSAS. SSIS has added extra debugging and diagnostic abilities when dealing with OLEDB data souces and there have been a number of additions to the sample applications.

Here are some of the good, the bad and the ugly things that stood out to me when I went through the readme, but as the title says, you really should read this file yourself (it's not a big document).

The Good

  • 5.3.1 Some Microsoft Office 2007 Features Require SQL Server Analysis Services 2005 SP2
    If you are running Office 2007 you will want to upgrade to SP2 as there are apparently features in Excel 2007 that will only work against SP2, unfortunately I have yet to find a list of what these feature are, but having more features has to be a good thing. :) 

The Bad

  • 5.3.2 Analysis Services Databases that Contain a Linked Measure Group Might Become Unusable After Upgrade
    If you are using linked measure groups, especially if they were created as part of a migration from a virtual cube in AS 2000, you really need to check for this before applying SP2. If you do not address the issue in this item you will make any affected databases the entire server inaccessible. The only thing you can do (which you do at your own risk) is to stop the SSAS service and delete the database(s) in question from the data folder.
  • 5.3.4 Pre-SP2 Backup Files and Data Folders Incompatible with SP2
    The backup and data folders have changed in SP2, so if you do a backup in SP2, you cannot restore it onto an earlier version. You also cannot setup synchronization between an SP2 server and a server on an earlier version.

The Ugly

  • 5.5.1 Scripting Incompatible with Microsoft Windows Vista
    Apparently SSIS uses the VSA (Visual Studio for Applications) engine for the scripting tasks and this does not work on Vista. This means packages that use scripting tasks will not run on a Vista machine and scripting tasks cannot be debugged or edited from a Vista machine. I was thinking of moving my work laptop up to Vista, but this alone might make me hold off for a bit longer (although VPC 2007 is out now so I could keep a WinXP VPC around...).

    Update: As soon as I posted this I noticed there was a new post SSIS- Packages will now work on Vista from SSIS guru and fellow MVP Jamie Thomson that announced that the SSIS team have rushed out a hotfix that allows script tasks to run under Vista

    Update 23 Feb 2007: As pointed out by Kevin in the comments, the issue described in 5.3.2 is actually worse than I first thought, apparently every database on the server is inaccessible until the problem database is removed.

Print | posted on Thursday, February 22, 2007 8:51 PM