Settings\Personalization\Sounds\Manage App Sounds (tap text under the "Play a sound for" area) and then scroll down in the Get Notifications area. Tap Messaging and change it in the Notification sound drop down.
I just moved over to a 640XL from an 830. My favorite notification sound, alert 2, isn't anywhere to be found. Weird, it had been my text notification for almost five years.