So recently I have been charged with updating several SQL Server 2005 clusters from SP2 + GDR to SP3. My experiences have been relatively good but the past few have been downright horrible. I have come across two pretty serious issues, and I am not sure which issue is worse, so I am just going to present them to you in arbitrary order:
The first issue that I have seen on three or four occasions now is the case where the setup routine assures me that patching the database engine has failed; yet, upon rebooting, the sqlservr.exe version, @@VERSION etc. all say 9.00.4035. Also when attempting to install the post-SP3 CU1 update in this case, it never complains and always works without a hitch… and if the instance hadn't been patched successfully, wouldn't it balk? Anyway, the moral of this story is: don't always believe the outcome of the setup program for SP3. It may be telling you that there was a problem but everything about my instances in these cases appears to point to the fact that they are indeed patched and working properly.
The scarier issue I have seen, and luckily this has been restricted to one domain, is that setup for some reason changes mixed mode authentication to Windows authentication only. Initially I thought this was just an anomaly, but I have now seen it on two distinct clusters in this specific environment. I have several questions into Microsoft about this one, and will post more information here when I get some answers. But I wanted to get this out to you because it is something you should watch out for. If you are using mixed mode, please check the authentication mode that is in place immediately following any SP3 and post-SP3 CU1 installs.