I've noticed a lot of customer confusion around SP1. None of the information below is new -- anyone could piece together the same recommendations by dissecting the various READMEs, release notes, blogs, etc. -- but I think there's value in collecting it in one place. Hope this helps.
This package contains everything you need to bring a client machine up to date. It includes the fixes for all of the Visual Studio SKUs (Standard, Professional, Team Architect, etc.), the VSTS standalone clients (Load Test Agent, Controller), and the TFS client (Team Explorer).
This is a set of additional client-side fixes that addresses Vista compatibility. It targets the same set of SKUs as VS2005 SP1 above, including the TFS clients. (We only split them up so that we could deliver SP1 sooner, since it was in very high demand.) It requires SP1 to be installed first.
As of 2/22/07 it is still in beta. That should change very soon.
3/6/07: updated link to final version. It's now called "Visual Studio 2005 Service Pack 1 Update for Windows Vista."
This is a hotfix for TFS server machines that allows future installers to shutdown & restart the services. Without this hotfix, upgrading a TFS server tier while it was still running could cause database corruption.
This package contains the fixes for TFS server tiers only -- it contains no client-side fixes. You should run this same package on all of the following TFS machine types: Application Tier (AT), Data Tier (DT), combined AT/DT, Version Control Proxy, and Team Build Server. It requires the quiescing GDR.
Important note: the installers above will only update VS2005 from .42 -> .762 and TFS from .147 -> .762. Beta & RC bits will not be upgraded. A few customers have hit this already. (Admittedly, .127 and .147 are easy to confuse). If you have TFS Beta3 or TFS RC installed, upgrade to TFS RTM first. If you have the SP1 Beta installed, uninstall it first.
Note #2: these version numbers are for English builds only. Localized builds will have an additional number at the end. For instance, VS2005 RTM JPN is 50727.42.25.
Edit 4/17/07: here are the version numbers for the TfsVersionControl database schema. If you have separate app & data tiers, this may be the easiest way to find what version your data tier is.
This is apparently the most confusing piece of all. First off, let's emphasize that there are no breaking changes in the TFS client<->server protocol. RTM clients can talk to SP1 servers and vice versa. The only limitations are: (a) RTM clients cannot access the new features added in SP1, such as the QueryMergesWithDetails() API (b) all server tiers must run the same version. Thus, there are really two independent upgrade paths.
 A final note: be careful when you have multiple product SKUs installed. While all products integrate seamlessly into the same VS shell, the installer treats them separately. As a result, the SP1 patch will appear to run multiple times, prompting you each time. For example, if you have VS Team Suite, Team Explorer, and DBPro installed on the same machine, you will have to hit "OK" at least 3X. This can be tricky because the 2nd dialog won't appear until Team Suite SP1 has finished, which may be an hour after you clicked "OK" the first time. It's also easy to click "Cancel" by mistake if the dialog pops up while you're working on something else. If that happens, just rerun the patch -- it should pick up where it left off.
[yet more] from Ed Psyk:
1. Do not install SP1 if you have configured your data tier as a SQL Cluster. We have a fix in progress for this issue.
2. If you have made any configuration changes (machine name changes, port changes, etc.) since the original install try running “Repair” to make sure that you don’t have any issues that would cause theSP1 upgrade to fail.