Just thought all would like to know that it looks like iOS5 DOES include the ability to use custom SMS tones, as well as email and voicemail tones:
https://twitter.com/#!/rosa/status/77863418148827136
https://twitter.com/#!/rosa/status/77863418148827136