If the other person is willing to set it up, you can create a custom ringtone (there are several apps for this) and have them assign to your contact in the Messages app. I haven't done it in a while, so you are on your own making a ringtone. Has to be done on their device.
Or you could just buy one in the iTunes store. I'm sure there are several thoroughly annoying wakeup ringtones in there somewhere. Also has to be done on the recipients iPone or iPad.
On the recipient's phone, go into the Contacts app, find your contact information and tap Edit. Scroll down to the 'text tone' field and change it to your chosen ring tone. You can use a full length ringtone instead of a text gone, for a good alarm length.
It's mutable, just like any other ring/alert sound, so that's a plus.
The minus is that they will hear this tone every time you text them from whatever devices and/or numbers in their contact information for you, not just for wake ups.