Enter your messages app, hit Menu, then Settings, then notification sound.
For more details visit this document. Remember, long press and Menu are your savior.
You can't set them for individual people, just a unique one for all text messages that is different than your system notification sound. For ringtones, download Zedge, or make them yourself with Ringdroid (apps in Market).
Help prevent distracted driving. Get Sprint Drive First FREE at the Google Play Store.