There are a few ways to do this, depending on the program you use. If you are using the built in messaging program, go to settings-sound & display-Notification sound.
From there, you can set your default notification sound. If you want to use your own ringtone, made from an mp3 the easiest way is to download ringdroid from the market, create the notification from an mp3 on your memory card, but be sure to save it as a notification, and not a "ringtone."
This can also be done by opening up the messaging application, pressing menu, selecting settings, scroll down to notification settings and select sound.
Either way, create the notification sound using ringdroid first.