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

Comments on this post

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
>You will make any affected databases
>unaccessible.

I ran in to this problem. It's worse that you say - if you have such a database and install SP2, it makes ALL databases inaccessible.
Left by Kevin Dente on Feb 23, 2007 2:50 AM

# SQL2005 SP2 Upgrading Happiness

Requesting Gravatar...
I’m fixing to start upgrading some servers to SP2. Here’s some reading material if you are
Left by roy ashbrook on May 05, 2007 11:49 AM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
As far as I can tell, Excel 2007 requires 2005 SP2 (locally) to connect to SSAS *at all*. But maybe that's because our server was already SP2 patched.
Left by piers7 on Aug 07, 2007 10:19 PM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
How can i tell what version of Service Pack i am running on SQL
Left by nik on Oct 11, 2007 1:47 AM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
Doing a search on google for "SQL Server 2005 build number" turned up the following Microsoft KB

http://support.microsoft.com/kb/321185

If you have a build greater than 3042, then you have one of the post SP2 cumulative update packs installed.
Left by Darren Gosbell on Oct 11, 2007 6:48 AM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
Many people in our development team are experiencing the database engine randomly stopping after applying service pack 2. We are not using analysis services just the database engine in our development. Does anyone else have this problem or can anyone point to a fix?
Left by Andrew Wheeler on Apr 05, 2008 3:46 AM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
@Andrew - This is not good. The database engine is usually pretty solid. Have you checked the windows event logs and the SQL Server log files for issues?

There have been 7 Cumulative Updates released for SP2 and there is currently a issue logged on the connect site for Microsoft to release a third service pack. (see https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=326575) I don't know if this would help in your situation or not. You probably need to check the logs and see if you can isolate what is causing this. I do not think this is a wide spread issue.

There is an MSDN forum here (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1) which is probably a good place to ask this as other MVPs which specialize in the database engine participate and some of the product team in this forum. I tend to specialize more in SSAS.
Left by Darren Gosbell on Apr 05, 2008 7:56 AM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
Like Andrew stated, we are now experiencing random stops of the database engine now that we are on SP2. The only event logged is that "The SQL Server (MSSQLSERVER) service was successfully sent a stop control (event id 7035)" from the service control manager but nothing that we can tell is sending the stop error. Nothing in the event viewer prior to the stop, nothing in the sql logs. Just random stops. This is on 3 different SQL 2005 64-bit servers all running on 64-bit 2003 sp2. Any thoughts?
Left by Shelton Smith on Jun 05, 2008 7:17 AM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
I don't really have any ideas. I have not heard of the Database Engine behaving like this before. I would definitely think it would be worth making a call to product support.
Left by Darren Gosbell on Jun 05, 2008 9:32 AM

# re: SQL 2005 SP2 - Make sure to read the readme!

Requesting Gravatar...
Cool,

but its a good idea to alwyas read the readme files...

Keep up the good work
Left by Web developer on Oct 12, 2009 9:47 PM

Your comment:

 (will show your gravatar)