Mark:
Graybeard is right for the actual location of the files. However, you could use another notification/ringtone app (I use Zedge) as well. If that's the case, when you go to choose your notification sound it will ask you whether you want to use zedge or not.
To change the actual notification sound: Settings>Sound Settings>Notification Sound
This will change ALL of your notification sounds to this sound. If you want a separate sound for different people, install handcent messaging and use that.