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).