UPDATE – 2009-08-07 9:39 PM EDT – the SQL Server Release Services team has published a blog post that reflects largely what I've said below. However, many questions are still unanswered. For example, they say that the fix for SP2 is not available yet, but they don't *really* say that the fix is available now for SP3. I think that it is, now that I have read the blog post a few times, but I'm not 100% convinced. They also say that the patch is for Management Studio, and that you are only affected if you have a Management Studio build in the ranges mentioned. However, I have never seen a cumulative update change the build # listed in Management Studio; Help|About always shows the major build for the most recent service pack. (I think this is a pre-existing problem with the CU/patch process in general, and not of this particular snafu.) And of course most of my questions and suggestions at the bottom of this post remain unaddressed. But at least it's something.
UPDATE – 2009-08-07 3:05 PM EDT – KB #972687 has appeared. While this particular article currently has a link to "View and request hotfix downloads," those downloads are not yet available (the download page currently shows two update files that are listed later on in this post, but ultimately they do not fix the issue, so I do not understand why they are there).
So why the problem in the first place? It seems this is a regression bug that appeared in several of the builds in the latest round of cumulative updates. Check @@VERSION for SQL Server. If it is in the following ranges, you should be watching for the hotfixes available in the article above:
9.00.3325 – 9.00.3329 (CU14 for 2005 SP2 = 9.00.3328)
9.00.4220 – 9.00.4228 (CU4 for 2005 SP3 = 9.00.4226)
10.00.1806 – 10.00.1813 (CU6 for 2008 RTM = 10.00.1812)
10.00.2714 – 10.00.2725 (CU3 for 2008 SP1 = 10.00.2723)
The regression specifically deals with using the UI to restore a SQL Server 2000 database, and in many cases will result in an error. The preferred workaround until a fix is confirmed is to use RESTORE DATABASE commands instead of the UI. The KB article explains a second workaround which, while more cumbersome, would allow some to still use the UI.
The SQL Server 2008 fixes (for both RTM and SP1 branch) should be available next week, while the SQL Server 2005 fixes are expected to be delivered around Monday, August 17th.
Notice that back on KB #970279, they have added a fifth download file for the x86 and x64 platforms (possibly the same one that was originally swapped for _COD_4229, but who knows really):
SQL_Server_2005_SP3_Cumulative_Update_4
Not that that helps anyone understand if they need any or all of the other four files.
Long story short: I would say hold off on installing CU4 (or any hotfix for either SQL Server 2005 or SQL Server 2008) until KB #972687 has been updated.
Note that part of the mix-up was that on the original file posted toKB #970279, the file was fat-fingered as 972867 instead of 972687. Which leads me to believe there is too much human dependency on these publications (and at the same time, not enough).
Back in June, I told you about a post-SP3 CU that was released for SQL Server 2005: Cumulative Update #4 (9.00.4226). This was exciting news, because it was the first build to introduce locked pages in memory support for the masses (e.g. those running Standard Edition).
However, the fanfare was short-lived. Since the cumulative update was released, it has been kind of a mess, to be quite honest. If you didn't download the fix within the first couple of days, then it has been very confusing. The hotfix download disappeared completely for some time. Then it reappeared, but the build had changed from 9.00.4226 to 9.00.4229. The KB article (http://support.microsoft.com/kb/970279) still says nothing about this change. I also checked the SQL Server Release Services blog and the SQL Server Engineers blog; nothing — they've both been silent for over two weeks. Microsoft's SQL Support presence on Twitter (@MicrosoftSQLCSS), told me yesterday that 4226 was replaced by 4229 (see the status update here). I have since sent them a tweet asking them to respond to this post.
After this change, the hotfix download page for a short while listed the following items which, understandably, has caused confusion. A lot of people (including fellow MVPs) have asked which file(s) they should download and apply, since their names are not completely obvious to a lot of us (SNAC is SQL Native Client, but for the SQLWriter and RS/SharePoint files, I don't remember ever seeing separate files for them in a CU):
SQL_Server_2005_SP3_COD_4229
SQL_Server_2005_SP3_Cumulative_Update_4_SQLWriter
SQL_Server_2005_SP3_Cumulative_Update_4_RS_Sharepo
SQL_Server_2005_SP3_Cumulative_Update_4_SNAC
Today, the list is slightly different. Instead of the _4229_COD file, they replaced it with a file _CU_Updated_Ref_KB_972867:
SQL_Server_2005_SP3_CU_Updated_Ref_KB_972867
SQL_Server_2005_SP3_Cumulative_Update_4_SQLWriter
SQL_Server_2005_SP3_Cumulative_Update_4_RS_Sharepo
SQL_Server_2005_SP3_Cumulative_Update_4_SNAC
Then I thought, "Ooh, okay, that looks promising; maybe KB #972867 will have some explanation of exactly what is happening here!" Nope. Silly Aaron, why would they do that? KB #972867 is not found, of course, at least at the time of writing, which means it either hasn't been written yet, or someone forgot to flip the "make this non-private" switch. Maybe it's not important that the KB actually exists outside of Seattle/Redmond business hours.
So, Microsoft, what is going on? Can you straighten this mess out? Can you label the downloads on the hotfix request page better? I thought that we vowed to never pull / swap releases after the fact, following the mess that was created a few years ago when you released horrible maintenance plan bugs in SP2 for SQL Server 2005, then extremely quietly replaced the download with a newer build? (That was over two years ago, but we haven't forgotten about it, and this seems very reminiscent: you can read more about it here, here and here.)
Most importantly, why haven't you done the right thing, which is:
- update KB #970729 to say the download is no longer available (and why);
- release an out-of-band CU#5, which contains at least build 9.00.4229;
- describe in the new KB article or on the download page what each file is for, and which ones users need in which scenarios; and,
- explain what happened here thoroughly (so users are aware of what bugs may have crept into 4226), and what is being done to prevent yet another repeat.
It is moments like these that I understand why a lot of people have a skeptical attitude toward the decisions you make…
Hi Aaron,
Thanks a lot for your reply. Your blog is also wonderful with interesting articles.
Regards,
S.V.Nagaraj
Raj, my take is this:
For your main server, you should only need to install Cumulative_Update_6. The other updates are so that you can have smaller downloads for systems with only RS, only native client, etc (for example you should always keep SQL Native Client up to date on any client machines where SQLNCLI is used to connect via apps, even if SQL Server isn't installed). I don't know what the XMO update is specifically, but I'm fairly certain that all of the sub-component updates are included in the primary one. So for any machine you're going to update, the full Cumulative_Update_6 should be all you need.
Hi Aarton,
Good post. CU's seperate releases are really confusing.
I plan to install sp3 and then CU 6.Request page shows component level releases.
SQL_Server_2005_SP3_Cumulative_Update_6_SQLWriter -> Sql writer
SQL_Server_2005_SP3_Cumulative_Update_6_RS -> Reoprt service
SQL_Server_2005_SP3_Cumulative_Update_6_SNAC ->Native client
SQL_Server_2005_SP3_Cumulative_Update_6_XMO -> is this one XML?
SQL_Server_2005_SP3_Cumulative_Update_6
Does one need to install all the componets? ie in case on one server i dont use Reporting service., so do i need to install SQL_Server_2005_SP3_Cumulative_Update_6_RS.
Is there any order in which I need to install them? ie 1st SQL_Server_2005_SP3_Cumulative_Update_6
2nd SQL_Server_2005_SP3_Cumulative_Update_6_SQLWriter etc..
Please share some info on how you went about installing these component level files.
Thanks in advance.
Regards,
Raj
Thanks @MicrosoftSQLCSS and thanks also to Bob Ward for a detailed blog post on determining whether your version of SSMS is affected:
/blogs/aaron_bertrand/archive/2009/08/21/how-to-really-detect-the-version-of-ssms-you-are-running.aspx
http://blogs.msdn.com/psssql/archive/2009/08/21/ssms-fails-to-restore-with-sql-2000-instances-and-ssms-versioning-explained.aspx
Details on why the new version was included in the current SQL 2005 SP3 CU4 download –
http://bit.ly/2YVib8.
We do apologize for any inconvenience the update changes caused. Bottom line is customer's who have already installed SQL Server 2005 SP3 CU4 do NOT need to update to the version referenced as "SQL_Server_2005_SP3_CU_Updated_Ref_KB_972687" unless they are experiencing the issue referenced in the link above.
Yes Chris, it would be nice to see a revision list.
Aaron,
The KB for 970279 http://support.microsoft.com/kb/970279 also says Revision 1.3 but where is the revision history? If this was a security bulletin then the history is fully documented. Why cant this be true for SQL KB's too?
Chris
Thanks for the feedback, I've already put in a note to my Dev Ops folks. ^au