Boe - I had the exact same question not too long ago. I actually needed to install a sound picker app to overwrite the default SMS notification sound.
I then decided I wanted to set specific sounds for SMS messages from specific people - so that I know who's sending a text whilst I'm driving so...