With the RTM (Release to Manufacturer) and Launch I want to give you my top reasons to upgrade with some additional tips to SQL 2008 from a SharePoint Admin/IT Pro perspective. Now that it's RTM SharePoint (both WSS and MOSS) supports SQL 2008!
1. Transparent Encryption - SharePoint doesn't even realize the database has transparent encryption turned on. I wouldn't recommend doing this on the config database and especially would avoid the SSP and Search Databases. If you do decide to use this, it would be on content databases only, and even then I'd recommend using it only on the ones you need it on. Why? For perf reasons encryption is overhead even if it is transparent to the application.
2. Backup Compression - Now this is a big no brainer. You can actually turn this on for the database so when backups happen even with the SharePoint Backup either Web UI or STSADM backup will backup the databases faster and more compressed.
3. Resource Governor - As all content is not created equal this gives you a chance to treat the most important data at a greater level of service. I love the fact that I can limit the exposure as far as performance is concerned with apps that might share the same SQL storage. Whether you're sharing with other SharePoint farms or sharing with other applications the resource governor allows you to be much more granular in deciding who gets what resources.
4. Mirroring enhancements - compressed logs means more reliability and quicker sync. Also other enhancements make it more transparent to setup and configure mirroring. Mirroring has taken some stride with SQL 2008. A transparent failover was demo'ed at the SharePoint Conference with SQL 2008. It wasn't clear in the demo how they dealt with the SSP, but I hope we get more prescriptive guidance from he product team on how to use the new features of mirroring to make DR for SharePoint even better.
5. Data compression - Careful here as well. Data compression can give you more disk space, but don't expect your SharePoint databases to really shrink down. The blobs aren't compressible. The lists are though. You might see 30% compression, but please avoid doing this on the Search database because of overhead unlesss the indexing performance isn't super key. You might get a lot of compression on that database though... just watch for overhead impact.