There are many, many how-tos on this subject, these are just my notes.

1. Jailbreak phone
2. Install Cydia
3. Install BossPrefs
4. Install OpenSSH

Important Note for ssh: 
username - root
password - alpine

1. Change iTunes import to AIFF
2. Edit mp3 file to selected length
3. Add shortened mp3 file to iTunes Library
4. Right-click on file and choose “Create AIFF Version”
5. Drag new version out of iTunes
6. Rename new files to sms-received#.caf where # is 1-6

1. Create backup of existing sms-received files in /System/Library/Audio/UISounds/
2. scp new files to that directory
3. Restart iPhone

Backup of original files in this archive.

The 3 Animal Collective/Panda Bear tones I'm currently using are in this archive. They are sure to wake you up (and maybe give you a heart attack) when you get a systems page in the middle of the night.