Edit: ^^ You beat me to it
If you don't want to use an additional app to change the notification sound what I did was on my SDcard I made the folder: /media/audio/notifications and add whatever mp3's I want to use for texts, email, calendar etc in there. Then each app like messaging, gmail, email, calendar, google talk, etc you can go to that apps settings and choose those notifications.
Also, If you want to make a phone ringtone the folder on the sdcard would be: /media/audio/ringtones
And for the alarms: /media/audio/alarms
Technically you could just make a folder called notifications, or ringtones, but I like to keep things neat.