all you do is go to your sound profiles, then go to advanced and set which profile you want to modify the sounds for (most likely normal) then you will see messaging (e-mail address) and SMS as an option under the list, from there you can browse for different sounds.
To change your alerts: Goto profiles, advanced, then click on the profile you are using. Scroll down to SMS Text and the settings will be there to change how the phone will alert you for sms messages. You can then to the same thing but go to each of the Messages accounts where you can change the alert setting for each of your e-mail accounts