First, set notification sound in K-9/Gmail (e.g. Gmail; Menu key→Settings→[Select your account]→Sound and Vibration). Thereafter set notification sound in Messages (Messages→Menu key→Settings→Notification signal).