Patching SQL Server still has a long way to go !

Patching SQL Server still has many necessary improvements.  It's an endless cycle.  Service Packs and Cumulative Updates.  The release process of the patches is much better, but the technical windows installer portion of the process is painful.  Ever look at a machine that has 4 SQL Server instances?  Take a close look at your c:\windows\, C:\windows\installer, c:\Prog...\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix.  Where I come from we call that a cluster f***!

If you have 20-50 SQL Instances, it's a manageable process, when your dealing with 100's and 1000's it's a nightmare.

Troubleshooting the process is a kin to the "needle in the haystack" analogy.  Recently while applying SP3 to a SQL Server I received the following error:

...
Error Number              : 11032
Error Description         : Unable to install Windows Installer MSP file
------------------------------------------------------

Fortunately I've seen so many errors applying SQL Server service packs and patches (remember we patch 100's to 1000's so I'm not saying these errors are common place, but even 5% can generate a huge volume of work).   Service pack Errors I've Seen.
 http://www.lifeasbob.com/2008/01/29/CumulativeHotfix53215InstallIssues.aspx

When searching for the 11032 error cause, the summary.log leads to another file, some sqlrun_sql.msp.log.  Always search for “return value 3” in any MSI logs to quickly find the error.  I found the following in the log file:

MSI (s) (9C:78) [20:33:17:978]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI7F3.tmp, Entrypoint: InstallFTERef
FTECa.DLL: INFO: FTE: InstallFTERef(), Entering...
FTECa.DLL: INFO: FTE: GetFTERefInstallParams: FTERef : ?*
FTECa.DLL: ERROR: FTE: InstallFTERef: Fail to create FTERef file: D:\data\FTData\noiseCHS.txt,Err=3
MSI (s) (9C:FC) [20:33:18:322]: User policy value 'DisableRollback' is 0
MSI (s) (9C:FC) [20:33:18:322]: Machine policy value 'DisableRollback' is 0
Action ended 20:33:18: InstallFinalize. Return value 3.

Sure enough the path d:\data was wrong, it should be d:\mssql.1\data,  I copied the FTData directory to a new one I created called d:\mssql.1\data and it completed successfully.  It's not even worth the effort to think how this got screwed up, the ftdata directory is picked up in the registry, so someone moved the directory or hacked the registry.

We did find one behavior change with SP3, DBCC CheckIdent.  DBCC CheckIdent used to successfully take Null parameter, now it does not, easy enough to correct by testing for Null and replacing with a zero.  We found this through a job that resets the identity value of table, the job began failing when we applied sp3, sure enough testing on sp2 shows it was successful.  Probably this is a good thing that sp3 tightened up the parameter validation on DBCC CheckIdent.  Example below:

Declare @maxId Integer
Select @maxId = Max([Identity_column]) + 1 From [Some Table]
Print '--Resetting seed to' + Convert(varchar(10),@maxId)
DBCC CheckIdent ('Some TABLE',reseed,@maxId)