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).
IF your ringtone isn't showing up in this list of choices.....make a folder on your SD card called "notifications". Then put the ringtone you want in that folder. Just read that document I linked in the previous post. Its all in there, plus more!