Messages -> Menu -> Settings -> Notification Sound. The bog standard ones are pretty rubbish, but if you mount the phone as a USB Drive to your PC and put any MP3 you want as your ringtone in a folder called notifications, you'll be able to use that.