It is explained how to do so in this thread.
androidforums.com/htc-droid-incredible-2/416535-text-notification-using-different-sound.html
I hate to do this but, I vote to make these instructions a sticky. Since I have seen multiple posts asking the same question on how to do this, that I have answered. I just hate to root my own horn on this one.